Elizabeth Christensen
Elizabeth Christensen
Postgres databases are very compliant, they do what you tell them until you tell them to stop. It is really common for a runaway process, query, or even something a co-worker runs to accidentally start a never ending transaction in your database. This potentially uses up memory, i/o, or other resources. Postgres has no preset default for this. To find out your current setting: A good rule of thumb can be a minute or a couple minutes. This is a connection-specific setting, so you’ll need to rec...
Read MoreElizabeth Christensen
Elizabeth Christensen
Beyond a basic query with a join or two, many queries require extracting subsets of data for comparison, conditionals, or aggregation. Postgres’ use of the SQL language is standards compliant and SQL has a world of tools for subqueries. This post will look at many of the different subquery tools. We’ll talk about the advantages and use cases of each, and provide further reading and tutorials to dig in more. I’ll take a broad definition of “subquery”. Why am I calling all of these subqueries? The...
Read MoreElizabeth Christensen
Elizabeth Christensen
We recently gave a talk at SCaLE (Southern California Linux Expo) about common problems and solutions for managing large Postgres databases. One of the topics we covered was data skewing and partial indexing. This piqued some conference discussion afterwards so we wanted to do a deeper dive. Skewed data is when your data is kind of bunched up - essentially it is not evenly distributed. You might have one really large customer with a customer id that takes up more than half the rows in your eve...
Read MoreElizabeth Christensen
Elizabeth Christensen
Since humans first started recording data, they’ve been keeping track of time. Time management is one of those absolutely crucial database tasks and Postgres does a great job of it. Postgres has a lot of options for storing and querying time so I wanted to provide an overview of some of the most common needs for storing and retrieving time data. This blog is also available as a hands on tutorial running in your local browser via our Postgres playground. If you ask Postgres what time it is, You...
Read MoreElizabeth Christensen
Elizabeth Christensen
Custom data types is one of those many features that makes PostgreSQL flexible for development of a huge variety of business and application use cases. Data types will help you primarily for data integrity, ensuring your data is stored in the database as you want it to be. A common surprise gift of using strict data types is that you can end up reducing your long term data maintenance. There’s two main ways to customize data types in Postgres: • Create s, which are value constraints added to bu...
Read MoreElizabeth Christensen
Elizabeth Christensen
Crunchy Data hosted the 4th annual PostGIS Day on November 17, 2022. PostGIS Day always comes a day after GIS Day which occurs annually on the 3rd Wednesday of November. We had speakers from 10 different countries and attendees from more than 70 countries. PostGIS is the most popular spatial relational database worldwide with: • An extensive catalog of spatial functions • Rich ecosystem of in-db extensions for routing, event management, external database linkage, point clouds, rasters and m...
Read MoreElizabeth Christensen
Elizabeth Christensen
Multi-PgBouncer is our affectionate in-house name for a special use case of running multiple instances of PgBouncer. PgBouncer is the venerable go-to tool for managing connection pooling at the database layer for Postgres. For some of our customers with notably large databases and high throughput, running multi-PgBouncer has been a great way to keep up with load and connections. David Christensen recently did a talk at PGConf.NYC entitled “ How to Tame a Mastodon: Lessons for PostgreSQL at Sca...
Read MoreElizabeth Christensen
Elizabeth Christensen
So you’re new to Postgres and you want to test it out and see how it works. Or maybe you’re building a new app and you need to seed your database. How do you get data in your Postgres database? There’s a lot out there on the topic of data migration for Postgres and some of it can be complicated and overwhelming. I’m going to give you some quick and simple ways to get test data IN your database. If you need to load data from csv files, the psql command in Postgres is a utility that can come...
Read MoreElizabeth Christensen
Elizabeth Christensen
In a world where everything is stored in following IaC ( infrastructure as code ) you may want the same from your database. For many following this style of engineering modernization we see a focus on IaC and K8s. We have many users standardizing on our PGO Kubernetes Operator to help. But following an IaC approach doesn’t mean you always want to manage your database and be in Kubernetes. For those wanting to forget about their database and trust the uptime, safety, and security of it to s...
Read MoreElizabeth Christensen
Elizabeth Christensen
Vanilla Postgres has native partitioning? Yes! And it's really good! We frequently get questions like: Can Postgres handle JSON? Can Postgres handle time series data? How scalable is Postgres? Turns out the answer is most usually yes! Postgres, vanilla Postgres, can handle whatever your need is without having to go to a locked in proprietary database. Unless you're really close to the Postgres internals and code releases you might have missed that Postgres natively has partitioning. Our head of...
Read More