Elizabeth ChristensenDavid Christensen
Elizabeth ChristensenDavid Christensen
New to Postgres 18, features like asynchronous i/o, uuid v7, b-tree skip scans, and virtual generated columns.
Greg Sabino Mullane
Greg Sabino Mullane
In a recent Postgres patch authored by Greg Sabino Mullane, Postgres has a new step forward for data integrity: data checksums are now enabled by default. This appears in the release notes as a fairly minor change but it significantly boosts the defense against one of the sneakiest problems in data management - silent data corruption. Let’s dive into what this feature is, what the new default means for you, and how it impacts upgrades. A data checksum is a simple but powerful technique to ver...
Read MorePaul Ramsey
Paul Ramsey
There’s nothing simple about simplification! It is very common to want to slim down the size of geometries, and there are lots of different approaches to the problem. We will explore different methods starting with ST_Letters for this rendering of the letter “a”. This is a good starting point, but to show the different effects of different algorithms on things like redundant linear points, we need a shape with more vertices along the straights, and fewer along the curves. Here we add in vert...
Read MoreElizabeth Christensen
Elizabeth Christensen
The secret to unlocking performance gains often lies not just in what you ask in a query, but in how Postgres finds the answer. The Postgres system is great for understanding how data is being queried. One of secretes to reading EXPLAIN plans is understanding the type of scan done to retrieve the data. The scan type can be the difference between a lightning-fast response or a slow query. Today I’ll break down the most common scan types, how they work, and when you’ll see them in your qu...
Read MorePaul Ramsey
Paul Ramsey
One of the temptations database users face, when presented with a huge table of interesting data, is to run queries that interrogate every record. Got a billion measurements? What’s the average of that?! One way to find out is to just calculate the average. For a billion records, that could take a while! Fortunately, the “Law of Large Numbers” is here to bail us out, stating that the average of a sample approaches the average of the population, as the sample size grows. And amazingly, the sample...
Read MorePaul Ramsey
Paul Ramsey
In this series , we talk about the many different ways you can speed up PostGIS. A common geospatial operation is to clip out a collection of smaller shapes that are contained within a larger shape. Today let's review the most efficient ways to query for things inside something else. Frequently the smaller shapes are clipped where they cross the boundary, using the ST_Intersection function. The naive SQL is a simple spatial join on ST_Intersects. When run on the small test area shown in the...
Read MoreElizabeth Christensen
Elizabeth Christensen
Postgres has an awesome amount of data collected in its own internal tables. Postgres hackers know all about this - but software developers and folks working with day to day Postgres tasks often miss out the good stuff. The Postgres catalog is how Postgres keeps track of itself. Of course, Postgres would do this in a relational database with its own schema. Throughout the years several nice features have been added to the internal tables like psql tools and views that make navigating Postgres’...
Read MorePaul Ramsey
Paul Ramsey
In the third installment of the PostGIS Performance series , I wanted to talk about performance around bounding boxes. Geometry data is different from most column types you find in a relational database. The objects in a geometry column can be wildly different in the amount of the data domain they cover, and the amount of physical size they take up on disk. The data in the “admin0” Natural Earth data range from the 1.2 hectare Vatican City, to the 1.6 billion hectare Russia, and from the 4 poin...
Read MoreChristopher Winslett
Christopher Winslett
My first thought seeing a temporal join in 2008 was, “Why is this query so complex?” The company I was at relied heavily on database queries, as it was a CRM and student success tracking system for colleges and universities. The query returned a filtered list of users and their last associated record from a second table. The hard part about the query isn’t returning the last timestamp or even performing joins, it’s returning only their last associated record from a second table. Back in 2008,...
Read MorePaul Ramsey
Paul Ramsey
In this series , we talk about the many different ways you can speed up PostGIS. Today let’s talk about looking across the queries with pg_stat_statements and some basic tuning. A reasonable question to ask, if you are managing a system with variable performance is: “what queries on my system are running slowly?” Fortunately, PostgreSQL includes an extension called “pg_stat_statements” that tracks query performance over time and maintains a list of high cost queries. Now you will have to leave...
Read MoreDavid Christensen
David Christensen
When someone asks about Postgres tuning, I always say “it depends”. What “it” is can vary widely but one major factor is the read and write traffic of a Postgres database. Today let’s dig into knowing if your Postgres database is read heavy or write heavy. Of course write heavy or read heavy can largely be inferred from your business logic. Social media app - read heavy. IoT logger - write heavy. But …. Many of us have mixed use applications. Knowing your write and read load can help you make ot...
Read MorePaul Ramsey
Paul Ramsey
I am kicking off a short blog series on PostGIS performance fundamentals. For this first example, we will cover fundamental indexing. We will explore performance using the Natural Earth “admin0” (countries) data (258 polygons) and their “populated places” (7342 points). A classic spatial query is the “spatial join”, finding the relationships between objects using a spatial contain. “How many populated places are there within each country?” This returns an answer, but it takes 2200 milliseco...
Read More