This is Part 1 our blog series discussing performance improvements across subsequent PostgreSQL versions. In this blog, we'll discuss the specific growth areas across multiple PostgreSQL versions. You can read the second part, which look at the dynamic ways in which these improvements benefit businesses, and why it’s important to pay attention to new PostgreSQL releases, here.
Introduction
Each new major release of PostgreSQL comes with many new and exciting features, security enhancements, and performance improvements. But, what specific performance gains are seen when upgrading outdated database clusters to the latest major version, PostgreSQL 15?
This blog post describes the tests the Performance and Engineering team at EDB conducted comparing the performance of the last 7 PostgreSQL major releases in the OLTP context.
Benchmark description
This article compares the performance of several PostgreSQL versions using pgbench. This selection of database versions includes all currently supported versions of Postgres—11 to 15—as well as a few unsupported releases, going back to version 9.6.
This benchmark uses a large database size, and was generated with a scale factor set to 2000. It produced around 300GB (twice the memory size) of data and indexes.
For each major version, pgbench was run with a read/write mixed workload with a number of clients varying from 10 to 390. For each set of clients, the test was run for 20 minutes.
Note: pgbench version 15 was used.
System characteristics
| AWS instance type | c5d.18xlarge |
| Operating System | Rocky8 |
| vCPU | 72 |
| Memory | 144GB |
| $PGDATA | XFS / 900GB NVMe SSD |
| $PGWAL | XFS / 900GB NVMe SSD |
PostgreSQL tuning
The following custom PostgreSQL settings were applied on all database clusters:
shared_buffers='36GB'
max_connections=1000
checkpoint_completion_target='0.93'
checkpoint_timeout='30min'
max_wal_size='300GB'
effective_cache_size='100GB'
random_page_cost=1
seq_page_cost=1
effective_io_concurrency=200
Benchmark results
| Version | Maximum TPS | PostgreSQL 15 max. TPS | Performance gained by going to PostgreSQL 15 |
|---|---|---|---|
| 14 | 48,657 | 50,667 | 4.1% |
| 13 | 48,008 | 50,667 | 5.5% |
| 12 | 45,889 | 50,667 | 10.4% |
| 11 | 43,841 | 50,667 | 15.6% |
| 10 | 41,762 | 50,667 | 21.3% |
| 9.6 | 34,305 | 50,667 | 47.7% |
Conclusion
Thanks to PostgreSQL developers, each new major release of PostgreSQL brings performance improvements. Upgrading to the latest version of Postgres can result in 47.7% more transactions processed in the same amount of time, depending on how out-of-date the current implementation of the database is. This benchmark clearly highlights the benefits of using the most recent PostgreSQL release, especially in the OLTP context.