Perf regressions in Postgres from 9.0 to 16 with sysbench and a small server

This has results for sysbench vs Postgres on a small server. I have results for versions from 9.0 through 16. My last report only went back to Postgres 11. The goal is to document where things get faster or slower over time for a low-concurrency and CPU-bound workload. The focus is on CPU regressions. 

My results here aren't universal, but you have to start somewhere:

• The microbenchmarks here mostly measure CPU overheads
• Things won't look the same with an IO-bound workload
• Things won't look the same with a workload that has more concurrency 
• Things won't look the same with a workload that has complex queries

Summaries

Sections after this explain how the microbenchmark results are grouped.

Comparing Postgres 16.2 with 9.0.23:

• point query, part 1
• Postgres 16.2 is faster than 9.0.23 for all but one microbenchmark
• point query, part 2
• Postgres 16.2 is faster than 9.0.23 for all microbenchmarks
• range query, part 1 & part2
• About half of the microbenchmarks are ~20% slower in 16.2 vs 9.0.23
• The big regression occurs between 9.0 and 9.1
• For part 2 where aggregation is done the problem is worse for shorter range scans
• writes
• Postgres 16.2 is faster than 9.0.23 for all microbenchmarks

Comparing Postgres 16.2 with 10.23

• Postgres 16.2 is faster than 10.23 for all microbenchmarks

Comparing Postgres 16.2 with 9.0.23

• point query, part 1
• Postgres 16.2 is at most 4% slower than 14.10
• point query, part 2
• Postgres 16.2 is at most 1% slower than 14.10
• range query, part 1
• Postgres 16.2 is at most 5% slower than 14.10
• range query, part 2
• Postgres 16.2 is as fast or faster than 14.10
• writes
• Postgres 16.2 is at most 1% slower than 14.10

Build + Configuration

I used these versions: 9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.17, 13.13, 14.10, 14.11, 15.5, 15.6, 16.1 and 16.2.

The configuration files are in the subdirectories named pg9, pg10, pg11, pg12, pg13, pg14, pg15 and pg16 from here. They are named conf.diff.cx9a2_bee.

Benchmarks

I used sysbench and my usage is explained here. There are 42 microbenchmarks and each tests ~1 type of SQL statement and is run for 1200 seconds.

Tests were run on a small server I have at home (see here). The server is an SER4 from Beelink with 8 cores, 16G of RAM and 1 m.2 storage device with XFS and Ubuntu 22.04. The test tables are cached by Postgres.

The benchmark is run with:

• one connection
• 30M rows and a database cached by Postgres
• each microbenchmark runs for 1200 seconds
• prepared statements were enabled

The command line was: bash r.sh 1 30000000 1200 1200 nvme0n1 1 1 1

Results

For the results below I split the microbenchmarks into 5 groups -- 2 for point queries, 2 for range queries, 1 for writes. For the range query microbenchmarks, part 1 has queries that don't do aggregation while part 2 has queries that do aggregation. Unfortunately, I included the full scan microbenchmark (scan_range=100) in part 2 but it doesn't do aggregation. The spreadsheet with all data and charts is here and is easier to read.

All of the charts have relative throughput on the y-axis where that is (QPS for $me) / (QPS for $base), $me is a version (for example 5.7.20) and $base is the base version. The base version is specified below and one of 5.6.21, 5.7.10 and 8.0.13 depending on what I am comparing. The y-axis doesn't start at 0 to improve readability.

The legend on under the x-axis truncates the names I use for the microbenchmark and I don't know how to fix that other than sharing the link to the Google Sheet I used. File I used to create the spreadsheets are here.

Results: from 9.0 through 16.2

Summary:

• point query, part 1
• Postgres 16.2 is faster than 9.0.23 for all but one microbenchmark
• point query, part 2
• Postgres 16.2 is faster than 9.0.23 for all microbenchmarks
• range query, part 1 & part2
• About half of the microbenchmarks are ~20% slower in 16.2 vs 9.0.23
• The big regression occurs between 9.0 and 9.1
• For part 2 where aggregation is done the problem is worse for shorter range scans
• writes
• Postgres 16.2 is faster than 9.0.23 for all microbenchmarks

This table has summary statistics from Postgres 16.2 for each microbenchmark group. The numbers represent the relative QPS (relative to 9.0.23) and a value > 1 means that 16.2 is faster than 9.0.23.

	min	max	avg	median	stdev
point-1	0.92	1.32	1.18	1.20	0.12
point-2	1.07	1.18	1.12	1.13	0.04
range-1	0.77	1.68	1.09	1.00	0.37
range-2	0.78	1.34	1.01	0.85	0.25
writes	1.11	4.64	2.21	1.97	1.10

Results: from 10.23 through 16.2

Summary

• Postgres 16.2 is faster than 10.23 for all microbenchmarks

This table has summary statistics from Postgres 16.2 for each microbenchmark group. The numbers represent the relative QPS (relative to 10.23) and a value > 1 means that 16.2 is faster than 10.23.

	min	max	avg	median	stdev
point-1	1.02	1.11	1.07	1.08	0.03
point-2	1.04	1.08	1.06	1.06	0.01
range-1	1.07	1.13	1.10	1.10	0.02
range-2	1.04	1.09	1.06	1.05	0.02
writes	1.02	1.15	1.08	1.06	0.04

Results: 14.10, 14.11, 15.5, 15.6, 16.1, 16.2

Summary

• point query, part 1
• Postgres 16.2 is at most 4% slower than 14.10
• point query, part 2
• Postgres 16.2 is at most 1% slower than 14.10
• range query, part 1
• Postgres 16.2 is at most 5% slower than 14.10
• range query, part 2
• Postgres 16.2 is as fast or faster than 14.10
• writes
• Postgres 16.2 is at most 1% slower than 14.10

This table has summary statistics from Postgres 16.2 for each microbenchmark group. The numbers represent the relative QPS (relative to 14.10) and a value > 1 means that 16.2 is faster than 14.10.

	min	max	avg	median	stdev
point-1	0.96	1.07	1.00	1.00	0.03
point-2	0.99	1.00	1.00	1.00	0.01
range-1	0.95	1.00	0.98	0.99	0.02
range-2	1.00	1.07	1.02	1.00	0.03
writes	0.99	1.04	1.01	1.02	0.01