Saturday, September 23, 2023

Postgres 16.0, sysbench and a small server

I recently shared results for Postgres 16rc1 vs sysbench on a small server. Now I have results for Postgres 16.0 and the news continues to be good.


  • insert QPS might have dropped by ~5%, otherwise there are no regressions for writes
  • for everything else QPS in Postgres 16.0 is within ~2% of 15.2, which is a great result


I compiled Postgres 15.2, 15.3, 15.4, 16 beta1, 16 beta2, 16 beta3, 16 rc1 and 16.0 from source. The builds are named o3_native_lto which is shorthand for using: -O3 -march=native -mtune=native -flto.


I used sysbench and my usage is explained here. Postgres was configured to cache all tables.

This benchmark used the Beelink server explained here that has 8 cores, 16G RAM and 1TB of NVMe SSD with XFS and Ubuntu 22.04. 

The benchmark is run with 1 client and 1 table with 20M rows. The read-only tests ran for 600 seconds each and the other tests ran for 1200 seconds each. The command line for my wrapper scripts is:

bash 1 20000000 600 1200 nvme0n1 1 1 1

The benchmark used a1 config.


A spreadsheet with the results is here. It has two sheets: one with absolute QPS for each version tested, the other with the relative QPS per version. The relative QPS is: (QPS for me) / (QPS for 15.2). With relative QPS it is easy to quantify improvements and regressions.

There are ~42 tests and each can be called a benchmark step or microbenchmark. I will call them benchmark steps. These are put into one of 5 groups based on the workload:

  • point query, part 1 - point queries that don't use the random-points benchmark step
  • point query, part 2 - point query variants that use the random-points benchmark step
  • range query, part 1 - range queries without aggregation
  • range query, part 2 - read-only variants of the original sysbench workload that does range queries with aggregation
  • writes - benchmark steps that do insert, update and delete

The y-axis starts at 0.8 rather than 0 to make it easier to see the changes.

  • point queries, part 1 - there are no regression. There is too much variance for the second and third benchmark steps from the left -- point-query.pre_range=100 and point-query_range=100. I have to explain the cause.
  • point queries, part 2 - there are no regressions
  • range queries, part 1 - QPS might have dropped by ~2% or there might be noise
  • range queries, part 2 - there are no regressions
  • writes - insert QPS might have dropped by ~5%, otherwise there are no regressions

No comments:

Post a Comment