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

This has benchmark results for Postgres 17beta3 using sysbench and a medium 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 17beta2 is here.

tl;dr

• 17beta3 looks good
• Write microbenchmarks are much faster in 17beta1 and 17beta2 vs 16.3
• There might be a regression in Postgres 16 for two of the update-only benchmarks. More work is in progress to explain this.
• Read microbenchmarks have similar performance between 16.3, 17beta1 and 17beta2

Builds, configuration and hardware

I compiled Postgres versions 12.19, 12.20, 13.15, 13.16, 14.12, 14.13, 15.7, 15.8, 16.3, 16.4, 17beta1, 17beta2 and 17beta3 from source.

The server is a c2d-highcpu-32 instance type on GCP (c2d high-CPU) with 32 vCPU, 64G RAM and SMT disabled so there are 16 cores. It uses Ubuntu 22.04 and storage is ext4 (data=writeback) using SW RAID 0 over 2 locally attached NVMe devices.

The configuration files have the name conf.diff.cx10a_c24r64 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 8 tables and 10M rows/table. There are 12 client threads, 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 8 10000000 300 600 md0 1 1 12

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 12.19.

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

Notes on the charts

• the y-axis starts at 0.8 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 ~1.85. That is a great improvement

Point queries, part 1

• the value for hot-points with 17beta is an outlier at ~1.85 and truncated 
• in most cases, there are no regressions and small improvements from 12.19 to 17beta

Point queries, part 2

in most cases, there are no regressions and small improvements from 12.19 to 17beta

Range queries, part 1

• in most cases, there are no regressions and small improvements from 12.19 to 17beta
• for scan there were improvements in Postgres 13 that have not gone away

Range queries, part 2

• there are no regressions and some small improvements

Writes

• for Postgres 17beta there are no regressions and some small improvements
• for Postgres 16 there might be small regressions in update-index and update-one. I have more tests in progress to figure that out. From the iostat and vmstat results the CPU overhead (cpu/o) and context switch rates (cs/o) increase from Postgres 15 to 16 and then drop in 17beta. See here for update-index and for update-one.