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.
Elizabeth 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 MoreElizabeth ChristensenJesse Soyland
Elizabeth ChristensenJesse Soyland
Moving a Postgres database isn’t a small task. Typically for Postgres users this is one of the biggest projects you’ll undertake. If you’re migrating for a new Postgres major version or moving to an entirely new platform or host, you have a couple options: • Using pg_dump and pg_restore : pg_dump is a very reliable way to collect an entire database and restore it to a new place. This includes the entire schema, all tables, and special database elements. If you’re migrating a small database, that...
Read MoreBrandur Leach
Brandur Leach
Postgres 18 was released today . Well down page from headline features like async I/O and UUIDv7 support, we get this nice little improvement: This release adds the capability to access both the previous (OLD) and current (NEW) values in the RETURNING clause for INSERT, UPDATE, DELETE and MERGE commands. It's not a showstopper the way async I/O is, but it is one of those small features that's invaluable in the right situation. A simple demonstration with to get all old and new values: Say w...
Read MoreElizabeth Christensen
Elizabeth Christensen
I had a chance last week to sit down and read the original academic paper announcing Postgres as a platform and the original design goals from 1986. I was just awestruck at the forethought - and how the original project goals laid the foundation for the database that seems to be taking over the world right now. The PostgreSQL creators totally nailed it. They laid out a flexible framework for a variety of business use cases that would eventually become the most popular database 30 years later....
Read MorePaul Ramsey
Paul Ramsey
I am excited to announce PostGIS 3.6 and GEOS 3.14. The PostGIS spatial extension to PostgreSQL and the GEOS computational geometry library taken together provide much of the functionality of PostGIS, and are the open source focus of the ( Crunchy Data ) Snowflake PostGIS team. Each year we work hard to ensure a release before the autumn PostgreSQL release, to ensure that the latest and greatest PostGIS and GEOS ready to be packaged with the latest PostgreSQL . All the critical issues are...
Read MoreElizabeth ChristensenDavid Christensen
Elizabeth ChristensenDavid Christensen
Postgres 18 will be released in just a couple weeks! Here’s some details on the most important and exciting features. Postgres 18 is adding asynchronous i/o. This means faster reads for many use cases. This is also part of a bigger series of performance improvements planned for future Postgres, part of which may be multi-threading. Expect to see more on this in coming versions. What is async I/O? When data isn’t in the shared memory buffers already, Postgres reads from disk, and I/O is needed...
Read More