Postgres 17beta3 vs sysbench on a small server: looking good

This has benchmark results for Postgres 17beta3 using sysbench and a small server. By small, medium or large server I mean < 10 cores for small, 10 to 19 cores for medium, 20+ cores for large.

A recent result for Postgres 17beta3 on a medium server is here.

tl;dr - 17beta3 looks great

• There are no regressions
• Throughput on write microbenchmarks is often ~5% to ~10% better than 16.x
• Throughput on hot-points (read-only) is more than 2X faster in 17beta than 16.x

Builds, configuration and hardware

I compiled Postgres versions 10.23, 11.22, 12.20, 13.16, 14.13, 15.7, 15.8, 16.0, 16.1, 16.2, 16.3, 16.4, 17beta1, 17beta2 and 17beta3 from source.

The server is named v3 or PN53 here and has 8 AMD cores with SMT disabled, 16 G of RAM and uses Ubuntu 22.04 and XFS with 1 m.2 device. I need to switch to ext-4 soon to match what I use elsewhere.

The configuration files have the name conf.diff.cx10a_c8r32 and are in the pg* subdirectories here.

Benchmark

I used sysbench and my usage is explained here. There are 42 microbenchmarks and most test only 1 type of SQL statement. Benchmarks are run with the database cached by Postgres with 1 table and 50M rows. There is 1 client thread (connection), read-heavy microbenchmarks run for 300 seconds and write-heavy run for 600 seconds.

The command lines for my helper script was:

bash r.sh 1 50000000 300 600 md0 1 1 1

Results

For the results below I split the 42 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. The spreadsheet with all data is here. 

Values from iostat and vmstat divided by QPS are here. This can help to explain why something is faster or slower because it shows how much HW is used per request.

The numbers in the spreadsheets are the relative QPS as below. When the relative QPS is > 1 then $version is faster than Postgres 10.23.

(QPS for $version) / (QPS for Postgres 10.23)

Notes on the charts

• the y-axis starts at 0.9 to make it easier to see differences
• for the first graph, the y-axis truncates one outlier (hot-points) from Postgres 17beta where the relative QPS is greater than 2.0 for Postgres 17beta. That is a great improvement

Point queries, part 1

• The y-axis ends at 1.2 and truncates the (great) outliers for hot-points where it is ~2.2X faster in Postgres 17beta

Point queries, part 2

Range queries, part 1

• While the scan benchmark has some variance that I don't yet understand the trend is positive starting in Postgres 13.x and continuing through 17beta3

Range queries, part 2

Writes

• Many of these are ~5% to ~10% faster in Postgres 17beta3 vs 16.x