Thursday, September 11, 2025

Postgres 18rc1 vs sysbench

This post has results for Postgres 18rc1 vs sysbench on small and large servers. Results for Postgres 18beta3 are here for a small and large server.

tl;dr

  • Postgres 18 looks great
  • I continue to see small CPU regressions in Postgres 18 for range queries that don't do aggregation on low-concurrency workloads. I have yet to explain that. 
  • The throughput for the scan microbenchmark has more variance with Postgres 18. I assume this is related to more or less work getting done by vacuum but I have yet to debug the root cause.

Builds, configuration and hardware

I compiled Postgres from source for versions 17.6, 18 beta3 and 18 rc1.

The servers are:
  • small
    • an ASUS ExpertCenter PN53 with AMD Ryzen 7735HS CPU, 32G of RAM, 8 cores with AMD SMT disabled, Ubuntu 24.04 and an NVMe device with ext4 and discard enabled.
  • large32
    • Dell Precision 7865 Tower Workstation with 1 socket, 128G RAM, AMD Ryzen Threadripper PRO 5975WX with 32 Cores and AMD SMT disabled, Ubuntu 24.04 and and NVMe device with ext4 and discard.
  • large48
    • an ax162s from Hetzner with an AMD EPYC 9454P 48-Core Processor with SMT disabled
    • 2 Intel D7-P5520 NVMe storage devices with RAID 1 (3.8T each) using ext4
    • 128G RAM
    • Ubuntu 22.04 running the non-HWE kernel (5.5.0-118-generic)
All configurations use synchronous IO which is the the only option prior to Postgres 18 and for Postgres 18 the config file sets io_method=sync.

Configuration files:

Benchmark

I used sysbench and my usage is explained here. To save time I only run 32 of the 42 microbenchmarks 
and most test only 1 type of SQL statement. Benchmarks are run with the database cached by Postgres.

For all servers the read-heavy microbenchmarks run for 600 seconds and the write-heavy for 900 seconds.

The number of tables and rows per table was:
  • small server - 1 table, 50M rows
  • large servers - 8 tables, 10M rows per table
The number of clients (amount of concurrency) was:
  • small server - 1
  • large32 server - 24
  • large48 servcer- 40
Results

The microbenchmarks are split into 4 groups -- 1 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. 

I provide charts below with relative QPS. The relative QPS is the following:
(QPS for some version) / (QPS for Postgres 17.6)
When the relative QPS is > 1 then some version is faster than PG 17.6.  When it is < 1 then there might be a regression. Values from iostat and vmstat divided by QPS are also provided here. These can help to explain why something is faster or slower because it shows how much HW is used per request.

The numbers highlighted in yellow below might be from a small regression for range queries that don't do aggregation. But note that this does reproduce for the full table scan microbenchmark (scan). I am not certain it is a regression as this might be from non-deterministic CPU overheads for read-heavy workloads that are run after vacuum. I hope to look at CPU flamegraphs soon.

Results: small server

I continue to see small (~3%) regressions in throughput for range queries without aggregation across Postgres 18 beta1, beta2, beta3 and rc1. But I have yet to debug this and am not certain it is a regression. I am also skeptical about the great results for scan. I suspect that I have more work to do to make the benchmark less subject to variance from MVCC GC (vacuum here). I also struggle with that on RocksDB (compaction), but not on InnoDB (purge).

Relative to: Postgres 17.6
col-1 : 18beta3
col-2 : 18rc1

col-1   col-2   point queries
1.01    0.98    hot-points_range=100
1.01    1.00    point-query_range=100
1.02    1.02    points-covered-pk_range=100
0.99    1.01    points-covered-si_range=100
1.00    0.99    points-notcovered-pk_range=100
1.00    0.99    points-notcovered-si_range=100
1.01    1.00    random-points_range=1000
1.01    0.99    random-points_range=100
1.01    1.00    random-points_range=10

col-1   col-2   range queries without aggregation
0.97    0.96    range-covered-pk_range=100
0.97    0.97    range-covered-si_range=100
0.99    0.99    range-notcovered-pk_range=100
0.99    0.99    range-notcovered-si_range=100
1.35    1.36    scan_range=100

col-1   col-2   range queries with aggregation
1.02    1.03    read-only-count_range=1000
1.00    1.00    read-only-distinct_range=1000
0.99    0.99    read-only-order_range=1000
1.00    1.00    read-only_range=10000
1.00    0.99    read-only_range=100
0.99    0.98    read-only_range=10
1.01    1.01    read-only-simple_range=1000
1.02    1.00    read-only-sum_range=1000

col-1   col-2   writes
0.99    0.99    delete_range=100
0.99    1.01    insert_range=100
0.99    0.99    read-write_range=100
0.99    0.99    read-write_range=10
0.98    0.98    update-index_range=100
1.00    0.99    update-inlist_range=100
0.98    0.98    update-nonindex_range=100
0.98    0.97    update-one_range=100
0.98    0.97    update-zipf_range=100
0.99    0.98    write-only_range=10000

Results: large32 server

I don't see small regressions in throughput for range queries without aggregation across Postgres 18 beta1, beta2, beta3 and rc1. I have only seen that on the low concurrency (small server) results.

The improvements on the scan microbenchmark come from using less CPU. But I am skeptical about the improvements. I might have more work to do to make the benchmark less subject to variance from MVCC GC (vacuum here). I also struggle with that on RocksDB (compaction), but not on InnoDB (purge).

Relative to: Postgres 17.6
col-1 : Postgres 18rc1

col-1   point queries
1.01    hot-points_range=100
1.01    point-query_range=100
1.01    points-covered-pk_range=100
1.01    points-covered-si_range=100
1.00    points-notcovered-pk_range=100
1.00    points-notcovered-si_range=100
1.01    random-points_range=1000
1.00    random-points_range=100
1.01    random-points_range=10

col-1   range queries without aggregation
0.99    range-covered-pk_range=100
0.99    range-covered-si_range=100
0.99    range-notcovered-pk_range=100
0.99    range-notcovered-si_range=100
1.12    scan_range=100

col-1   range queries with aggregation
1.00    read-only-count_range=1000
1.02    read-only-distinct_range=1000
1.01    read-only-order_range=1000
1.03    read-only_range=10000
1.00    read-only_range=100
1.00    read-only_range=10
1.00    read-only-simple_range=1000
1.00    read-only-sum_range=1000

col-1   writes
1.01    delete_range=100
1.00    insert_range=100
1.00    read-write_range=100
1.00    read-write_range=10
1.00    update-index_range=100
1.00    update-inlist_range=100
1.00    update-nonindex_range=100
0.99    update-one_range=100
1.00    update-zipf_range=100
1.00    write-only_range=10000

Results: large48 server

I don't see small regressions in throughput for range queries without aggregation across Postgres 18 beta1, beta2, beta3 and rc1. I have only seen that on the low concurrency (small server) results.

The improvements on the scan microbenchmark come from using less CPU. But I am skeptical about the improvements. I might have more work to do to make the benchmark less subject to variance from MVCC GC (vacuum here). I also struggle with that on RocksDB (compaction), but not on InnoDB (purge).

I am skeptical about the regression I see here for scan. That comes from using ~10% more CPU per query. I might have more work to do to make the benchmark less subject to variance from MVCC GC (vacuum here). I also struggle with that on RocksDB (compaction), but not on InnoDB (purge).

I have not see the large improvements for the insert and delete microbenchmarks on previous tests on that large server. I assume this is another case where I need to figure out how to reduce variance when I run the benchmark.

Relative to: Postgres 17.6
col-1 : Postgres 18beta3
col-2 : Postgres 18rc1

col-1   col-2   point queries
0.99    0.99    hot-points_range=100
0.99    0.99    point-query_range=100
1.00    0.99    points-covered-pk_range=100
0.99    1.02    points-covered-si_range=100
1.00    0.99    points-notcovered-pk_range=100
0.99    1.01    points-notcovered-si_range=100
1.00    0.99    random-points_range=1000
1.00    0.99    random-points_range=100
1.00    1.00    random-points_range=10

col-1   col-2   range queries without aggregation
0.99    0.99    range-covered-pk_range=100
0.98    0.99    range-covered-si_range=100
0.99    0.99    range-notcovered-pk_range=100
1.01    1.01    range-notcovered-si_range=100
0.91    0.91    scan_range=100

col-1   col-2   range queries with aggregation
1.04    1.03    read-only-count_range=1000
1.02    1.01    read-only-distinct_range=1000
1.01    1.00    read-only-order_range=1000
1.06    1.06    read-only_range=10000
0.98    0.97    read-only_range=100
0.99    0.99    read-only_range=10
1.02    1.02    read-only-simple_range=1000
1.03    1.03    read-only-sum_range=1000

col-1   col-2   writes
1.46    1.49    delete_range=100
1.32    1.32    insert_range=100
0.99    1.00    read-write_range=100
0.98    1.00    read-write_range=10
0.99    1.00    update-index_range=100
0.95    1.03    update-inlist_range=100
1.00    1.02    update-nonindex_range=100
0.96    1.04    update-one_range=100
1.00    1.01    update-zipf_range=100
1.00    1.00    write-only_range=10000




Tuesday, September 2, 2025

Postgres 18 beta3, large server, sysbench

This has performance results for Postgres 18 beta3, beta2, beta1, 17.5 and 17.4 using the sysbench benchmark and a large server. The working set is cached and the benchmark is run with high concurrency (40 connections). The goal is to search for CPU and mutex regressions. This work was done by Small Datum LLC and not sponsored

tl;dr

  • There might be small regressions (~2%) for several range queries that don't do aggregation. This is similar to what I reported for 18 beta3 on a small server, but here it only occurs for 3 of the 4 microbenchmarks and on the small server it occurs on all 4. I am still uncertain about whether this really is a regression.
Builds, configuration and hardware

I compiled Postgres versions 17.4, 17.5, 18 beta1, 18 beta2 and 18 beta3 from source.

The server is an ax162-s from Hetzner with an AMD EPYC 9454P processor, 48 cores, AMD SMT disabled and 128G RAM. The OS is Ubuntu 22.04. Storage is 2 NVMe devices with SW RAID 1 and 
ext4. More details on it are here.

The config file for Postgres 17.4 and 17.5 is x10a_c32r128.

The config files for Postgres 18 are:
  • x10b_c32r128 is functionally the same as x10a_c32r128 but adds io_method=sync
  • x10d_c32r128 starts with x10a_c2r128 and adds io_method=io_uring

Benchmark

I used sysbench and my usage is explained here. To save time I only run 32 of the 42 microbenchmarks and most test only 1 type of SQL statement. Benchmarks are run with the database cached by Postgres.

The tests are run using 8 tables with 10M rows per table. The read-heavy microbenchmarks run for 600 seconds and the write-heavy for 900 seconds.

Results

The microbenchmarks are split into 4 groups -- 1 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. 

I provide charts below with relative QPS. The relative QPS is the following:
(QPS for some version) / (QPS for Postgres 17.5)
When the relative QPS is > 1 then some version is faster than PG 17.5.  When it is < 1 then there might be a regression. Values from iostat and vmstat divided by QPS are also provided here. These can help to explain why something is faster or slower because it shows how much HW is used per request.

Relative to: pg174_o2nofp.x10a_c32r128
col-1 : pg175_o2nofp.x10a_c32r128
col-2 : pg18beta1_o2nofp.x10b_c32r128
col-3 : pg18beta1_o2nofp.x10d_c32r128
col-4 : pg18beta2_o2nofp.x10d_c32r128
col-5 : pg18beta3_o2nofp.x10d_c32r128

col-1   col-2   col-3   col-4   col-5
0.98    0.99    0.99    1.00    0.99    hot-points_range=100
1.01    1.01    1.00    1.01    1.01    point-query_range=100
1.00    1.00    0.99    1.00    1.00    points-covered-pk
1.00    1.01    1.00    1.02    1.00    points-covered-si
1.00    1.01    1.00    1.00    1.00    points-notcovered-pk
1.00    1.00    1.01    1.02    1.00    points-notcovered-si
1.00    1.00    1.00    1.00    1.00    random-points_range=1000
1.00    1.01    1.00    1.00    1.00    random-points_range=100
1.00    1.00    1.00    1.00    1.00    random-points_range=10
1.00    0.97    0.96    0.98    0.97    range-covered-pk
1.00    0.97    0.97    0.98    0.97    range-covered-si
0.99    0.99    0.99    0.99    0.98    range-notcovered-pk
1.00    1.01    1.01    1.00    1.01    range-notcovered-si
1.00    1.02    1.03    1.03    1.02    read-only-count
1.00    1.00    1.00    1.01    1.01    read-only-distinct
1.00    1.00    1.00    1.00    1.00    read-only-order
1.01    1.01    1.02    1.02    1.01    read-only_range=10000
1.00    0.99    0.99    0.99    1.00    read-only_range=100
1.01    0.99    0.99    1.00    0.99    read-only_range=10
1.00    1.01    1.01    1.01    1.01    read-only-simple
1.00    1.02    1.03    1.03    1.02    read-only-sum
1.00    1.13    1.14    1.02    0.91    scan_range=100
1.00    1.13    1.13    1.02    0.90    scan.warm_range=100
1.00    0.99    0.99    0.99    0.99    delete_range=100
0.99    1.00    1.02    0.99    1.00    insert_range=100
1.01    1.00    1.00    1.00    0.99    read-write_range=100
1.00    0.98    1.00    1.01    0.99    read-write_range=10
0.99    0.99    1.02    0.98    0.96    update-index
1.00    1.01    1.00    1.00    1.01    update-inlist
0.98    0.98    0.99    0.98    0.97    update-nonindex
0.95    0.95    0.94    0.93    0.95    update-one_range=100
0.97    0.98    0.98    0.97    0.95    update-zipf_range=100
0.98    0.99    0.99    0.98    0.98    write-only_range=10000

Monday, September 1, 2025

Postgres 18 beta3, small server, sysbench

This has performance results for Postgres 18 beta3, beta2, beta1 and 17.6 using the sysbench benchmark and a small server. The working set is cached and the benchmark is run with low concurrency (1 connection). The goal is to search for CPU regressions. This work was done by Small Datum LLC and not sponsored

tl;dr

  • There might be small regressions (~2%) for several range queries that don't do aggregation. This is similar to what I reported for 18 beta1.
  • Vacuum continues to be a problem for me and I had to repeat the benchmark a few times to get a stable result. It appears to be a big source of non-deterministic behavior leading to false alarms for CPU regressions in read-heavy tests that run after vacuum. In some ways, RocksDB compaction causes similar problems. Fortunately, InnoDB MVCC GC (purge) does not cause such problems.
Builds, configuration and hardware

I compiled Postgres versions 17.6, 18 beta1, 18 beta2 and 18 beta3 from source.

The server is a Beelink SER7 with a Ryzen 7 7840HS CPU, 32G of RAM, 8 cores with AMD SMT disabled, Ubuntu 24.04 and an NVMe devices with discard enabled and ext4 for the database.

The config file for Postgres 17.6 is x10a_c8r32.

The config files for Postgres 18 are:
  • x10b_c8r32 is functionally the same as x10a_c8r32 but adds io_method=sync
  • x10b1_c8r32 starts with x10b_c8r32 and adds vacuum_max_eager_freeze_failure_rate =0
  • x10b2_c8r32 starts with x10b_c8r32 and adds vacuum_max_eager_freeze_failure_rate =0.99

Benchmark

I used sysbench and my usage is explained here. To save time I only run 32 of the 42 microbenchmarks and most test only 1 type of SQL statement. Benchmarks are run with the database cached by Postgres.

The tests are run using 1 table with 50M rows. The read-heavy microbenchmarks run for 600 seconds and the write-heavy for 900 seconds.

Results

The microbenchmarks are split into 4 groups -- 1 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. 

I provide charts below with relative QPS. The relative QPS is the following:
(QPS for some version) / (QPS for Postgres 17.6)
When the relative QPS is > 1 then some version is faster than PG 17.6.  When it is < 1 then there might be a regression. Values from iostat and vmstat divided by QPS are also provided here. These can help to explain why something is faster or slower because it shows how much HW is used per request.

The numbers highlighted in yellow below might be from a small regression for range queries that don't do aggregation. But note that this does reproduce for the full table scan microbenchmark (scan). I am not certain it is a regression as this might be from non-deterministic CPU overheads for read-heavy workloads that are run after vacuum. I hope to look at CPU flamegraphs soon.
  • the mapping from microbenchmark name to Lua script is here
  • the range query without aggregation microbenchmarks use oltp_range_covered.lua with various flags set and the SQL statements it uses are here. All of these return 100 rows.
  • the scan microbenchmark uses oltp_scan.lua which is a SELECT with a WHERE clause that filters all rows (empty result set)
Relative to: x.pg176_o2nofp.x10a_c8r32.pk1
col-1 : x.pg18beta1_o2nofp.x10b_c8r32.pk1
col-2 : x.pg18beta2_o2nofp.x10b_c8r32.pk1
col-3 : x.pg18beta3_o2nofp.x10b_c8r32.pk1
col-4 : x.pg18beta3_o2nofp.x10b1_c8r32.pk1
col-5 : x.pg18beta3_o2nofp.x10b2_c8r32.pk1

col-1   col-2   col-3   col-4   col-5 -> point queries
1.00    1.00    0.98    0.99    0.99    hot-points_range=100
1.00    1.01    1.00    1.00    0.99    point-query_range=100
1.00    1.02    1.01    1.01    1.01    points-covered-pk
1.00    1.00    1.00    1.00    1.00    points-covered-si
1.01    1.01    1.00    1.00    1.00    points-notcovered-pk
1.01    1.00    1.00    1.00    1.00    points-notcovered-si
0.99    1.00    0.99    1.00    1.00    random-points_range=1000
1.01    1.00    1.00    1.00    1.00    random-points_range=100
1.01    1.01    1.00    1.00    0.99    random-points_range=10

col-1   col-2   col-3   col-4   col-5 -> range queries w/o agg
0.98    0.99    0.97    0.98    0.96    range-covered-pk_range=100
0.98    0.99    0.96    0.98    0.97    range-covered-si_range=100
0.98    0.98    0.98    0.97    0.98    range-notcovered-pk
0.99    0.99    0.98    0.98    0.98    range-notcovered-si
1.01    1.02    1.00    1.00    1.00    scan

col-1   col-2   col-3   col-4   col-5 -> range queries with agg
1.02    1.01    1.02    1.01    0.98    read-only-count_range=1000
0.98    1.01    1.01    1.00    1.03    read-only-distinct
0.99    0.99    0.99    0.99    0.99    read-only-order_range=1000
1.00    1.00    1.01    1.00    1.01    read-only_range=10000
0.99    0.99    0.99    0.99    0.99    read-only_range=100
0.99    0.99    0.99    0.98    0.99    read-only_range=10
1.01    1.00    1.00    1.00    1.01    read-only-simple
1.01    1.00    1.01    1.00    1.00    read-only-sum_range=1000

col-1   col-2   col-3   col-4   col-5 -> writes
0.99    1.00    0.98    0.98    0.98    delete_range=100
0.99    0.98    0.98    1.00    0.98    insert_range=100
0.99    0.99    0.99    0.98    0.99    read-write_range=100
0.98    0.99    0.99    0.98    0.99    read-write_range=10
1.00    0.99    0.98    0.97    0.99    update-index_range=100
1.01    1.00    0.99    1.01    1.00    update-inlist_range=100
1.00    1.00    0.99    0.96    0.99    update-nonindex_range=100
1.01    1.01    0.99    0.97    0.99    update-one_range=100
1.00    1.00    0.99    0.98    0.99    update-zipf_range=100
1.00    0.99    0.98    0.98    1.00    write-only_range=10000

Postgres 18rc1 vs sysbench

This post has results for Postgres 18rc1 vs sysbench on small and large servers. Results for Postgres 18beta3 are here for a small and larg...