PostgreSQL Performance Improvements - Part 1: Tracking PostgreSQL Performance Across Versions

March 16, 2023
EDB

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.


 

 

 

Share this

More Blogs

PGVector as Embedding Store in PrivateGPT

EDB has a long history of open source contributions, and while we’re best known for our contributions to Postgres, that’s not the only project we contribute to. e.g Barman, CloudNativePG...
June 05, 2024

pgAdmin User Management in Server Mode

pgAdmin can be deployed as a web application by configuring the app to run in server mode. One can check out server deployment on how to run pgAdmin in server...
August 24, 2023

pgAdmin CI/CD

Almost exactly three years ago I wrote a blog on my personal page entitled Testing pgAdmin which went into great detail discussing how we test pgAdmin prior to releases. Back...
August 24, 2023