This is Part 2 our blog series discussing performance improvements across subsequent PostgreSQL versions. In the first post of the series, we explored the specific metrics that demonstrate PostgreSQL’s constant evolution. Here, we’ll look at the dynamic ways in which these improvements benefit businesses, and why it’s important to pay attention to new PostgreSQL releases.
Are you running the latest major version of PostgreSQL, released September 2022? PostgreSQL 15 brings with it a series of significant improvements made to performance, security and usability—just like each release before it. But perhaps reading through the release notes isn’t enough to convince your team to make the leap in upgrading to a more recently supported version, especially if you’re already having success in using an earlier release.
EDB’s Performance and Engineering team recently conducted a benchmark comparing PostgreSQL performance cumulatively between major versions 9.6 to 15 in an OLTP setting. These differences are staggering, showing 47.7% faster transaction processing when upgrading from 9.6 to the latest version and significant differences even between individual versions—such as a 4.1% improvement simply upgrading from PostgreSQL 14 to 15.
Julien Tachoires from EDB’s Performance and Engineering team discusses all benchmark results between major PostgreSQL versions in a blog post—check it out to see all findings and learn how these benchmarks were conducted.
The PostgreSQL developer community worked on simplifying high performance data workloads in PostgreSQL 15, and introduced many observable improvements. It is incredibly valuable to review the release notes for each Postgres version that has come out since what you are currently running in production yourself. But if you're interested in just the highlights, this article discusses the feature additions and enhancements built into 15 that we believe contribute to these noticeable differences in performance.
Improved Sorting
Many improvements were seen to sort performance for in-memory and on-disk sorting algorithms. These results are able to be clearly seen in results from the PostgreSQL development team, where internal benchmarks were run that showed speedups of 25% - 400% depending on the sorted data type.
GiST indexes built using sorting have improved lookup performance (Aliaksandr Kalenik, Sergei Shoulbakov, Andrey Borodin).
For sorts that exceed work_mem, Postgres now switches to using a batch sorting algorithm that uses added output streams to improve performance (Heikki Linnakangas).
In-memory sorts also have improved performance and reduced memory consumption (Ronan Dunklau, David Rowley, Thomas Munro, John Naylor).
Using window functions that use row_number(), rank(), dense_rank() and count() will now run faster (David Rowley).
Parallelization
Queries that use SELECT DISTINCT are now able to be executed in parallel (David Rowley). While this doesn’t apply to all queries, those that can be run in parallel are able to run more than twice as fast.
The previous PostgreSQL release introduced support for asynchronous remote queries; this release builds on that work and now allows the foreign data wrapper postgres_fdw to also run asynchronous commits in parallel (Etsuro Fujita).
When referencing multiple foreign tables, queries can now perform foreign table scans in parallel in more cases than previously was possible (Andrey Lepikhov, Etsuro Fujita).
And, pg_dump run in parallel sees enhanced performance for tables with large TOAST tables (Tom Lane).
Compression
Archiving and backup facilities also saw a large number of improvements.
The built-in backup function pg_basebackup now supports server-side compression of backup files using gzip, LZ4 and Zstandard (zstd) (Dipesh Pandit, Jeevan Ladhe) and has better control over compression options (Michael Paquier, Robert Haas). These changes add to the existing support for client-side gzip compression.
Write-ahead log (WAL) files now also have support for LZ4 and zstd compression on full-page writes, resulting in space and performance benefits for specific workloads (Andrey Borodin, Justin Pryzby). WAL processing is also now able to pre-fetch necessary file contents (Thomas Munro) to reduce recovery times.
Other
There are even more enhancements introduced, such as improved performance of spinlocks on high-core-count ARM64 systems (Geoffrey Blake).
When using psql’s \copy command, data is now sent in larger chunks (Heikki Linnakangas).
And, faster speeds should be seen when dumping databases with many objects or using pg_upgrade. (Tom Lane)
Conclusions
When looking to achieve faster OLTP processing, reduce costs and improve profit margin, it’s clear to see that upgrading PostgreSQL versions will help you to reach your goals. Perhaps you have concerns over reliability or unforeseen security issues in the latest version; or maybe upgrading versions requires an involved process for each of your database clusters, and it’s a significant concern to avoid downtime resulting from the upgrade process.
Whatever the case, it’s important to plan for upgrading your database clusters to take advantage of the significant feature additions and updates available. Our staff of over 300 PostgreSQL experts including code contributors to the Postgres project are here to help guide you through upgrading or migrating to any version of Postgres that suits your needs in a manner that minimizes risk to and unavailability of your mission-critical applications.
Reach out to us today for professional Postgres services and support—we’ll help you create an action plan, and will be there for you each step of the way.