Postgres 18.0 vs sysbench on a small server

This has benchmark results for Postgres 18.0 using sysbench on a small server. Previous results for 18 rc1 are here.

tl;dr

• From 12.22 to 18.0
• there are no regressions larger than 2% but many improvements larger than 5%. Postgres continues to do a great job at avoiding regressions over time.
• From 17.6 to 18.0
• I continue to see small CPU regressions (1% or 2%) in Postgres 18 for short range queries on low-concurrency workloads. I see it for shorter but not for longer range queries so my guess is that this is new overhead in query execution setup or optimization. I hope to explain this.

Builds, configuration and hardware

I compiled Postgres from source for versions 12.22, 13.22, 14.19, 15.14, 16.10, 17.6, and 18.0.

The HW is 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.

Prior to 18.0, the configuration file was named conf.diff.cx10a_c8r32 and is here for 12.22, 13.22, 14.19, 15.14, 16.10 and 17.6.

For 18.0 I tried 3 configuration files:

• conf.diff.cx10b_c8r32 (x10b) - uses io_method=sync
• conf.diff.cx10c_c8r32 (x10c) - uses io_method=worker
• conf.diff.cx10d_c8r32 (x10d) - uses 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 read-heavy microbenchmarks run for 600 seconds and the write-heavy for 900 seconds.

The benchmark is run with 1 client, 1 table and 50M rows. The purpose is to search for CPU regressions.

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 base version)

When the relative QPS is > 1 then some version is faster than base version.  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.

I present results for:

• versions 12 through 18 using 12.22 as the base version
• versions 17.6 and 18.0 using 17.6 as the base version

Results: Postgres 17.6 and 18.0

For the read-only_range=X benchmarks there might be small regressions (1% or 2%) when X is 10 or 100 but not 10000. The value of X is the length of the range scan. I have seen similar regressions in the beta and RC releases. Given that this occurs when the range scan is shorter, the problem might be new overhead in query execution setup or optimization. But I have yet to explain this.

Relative to: 17.6 with x10a

col-1 : 18.0 with x10b and io_method=sync

col-2 : 18.0 with x10c and io_method=worker

col-3 : 18.0 with x10d and io_method=io_uring

col-1   col-2   col-3  point queries

1.01    1.01    0.97    hot-points_range=100

1.01    1.00    0.99    point-query_range=100

1.01    1.01    1.00    points-covered-pk_range=100

1.01    1.02    1.01    points-covered-si_range=100

1.01    1.01    1.00    points-notcovered-pk_range=100

1.01    0.99    1.00    points-notcovered-si_range=100

1.02    1.02    1.03    random-points_range=1000

1.01    1.00    0.99    random-points_range=100

1.00    1.00    0.99    random-points_range=10

col-1   col-2   col-3  range queries without aggregation

0.99    0.99    0.98    range-covered-pk_range=100

1.00    0.99    1.00    range-covered-si_range=100

1.00    0.99    0.98    range-notcovered-pk_range=100

0.99    0.99    0.99    range-notcovered-si_range=100

1.04    1.04    1.04    scan_range=100

col-1   col-2   col-3  range queries with aggregation

1.01    1.00    1.01    read-only-count_range=1000

1.01    1.00    1.00    read-only-distinct_range=1000

0.99    1.00    0.98    read-only-order_range=1000

1.01    1.00    1.00    read-only_range=10000

0.99    0.99    0.98    read-only_range=100

0.98    0.99    0.98    read-only_range=10

1.01    1.00    0.99    read-only-simple_range=1000

1.00    1.00    0.99    read-only-sum_range=1000

col-1   col-2   col-3  writes

1.00    1.00    0.99    delete_range=100

0.99    0.99    0.98    insert_range=100

0.99    0.99    0.98    read-write_range=100

0.98    0.99    0.98    read-write_range=10

0.99    1.00    0.99    update-index_range=100

0.99    1.00    1.00    update-inlist_range=100

0.99    1.00    0.98    update-nonindex_range=100

0.99    0.99    0.98    update-one_range=100

0.99    1.00    0.99    update-zipf_range=100

1.00    1.00    0.99    write-only_range=10000

Results: Postgres 12 to 18

From 12.22 to 18.0 there are no regressions larger than 2% but many improvements larger than 5% (highlighted in greeen). Postgres continues to do a great job at avoiding regressions over time.

Relative to: 12.22

col-1 : 13.22

col-2 : 14.19

col-3 : 15.14

col-4 : 16.10

col-5 : 17.6

col-6 : 18.0 with the x10b config

col-1   col-2   col-3   col-4   col-5   col-6   point queries

1.06    1.05    1.05    1.09    2.04    2.05    hot-points_range=100

1.01    1.03    1.03    1.02    1.04    1.04    point-query_range=100

1.00    0.99    0.99    1.03    0.99    1.01    points-covered-pk_range=100

1.04    1.03    1.02    1.05    1.01    1.03    points-covered-si_range=100

1.01    1.00    1.01    1.04    1.01    1.02    points-notcovered-pk_range=100

1.01    1.02    1.03    1.05    1.02    1.04    points-notcovered-si_range=100

1.02    1.00    1.02    1.05    1.00    1.02    random-points_range=1000

1.01    1.01    1.01    1.03    1.01    1.02    random-points_range=100

1.01    1.01    1.01    1.02    1.01    1.01    random-points_range=10

col-1   col-2   col-3   col-4   col-5   col-6   range queries with aggregation

0.99    1.00    1.00    1.00    0.99    0.98    range-covered-pk_range=100

1.01    1.01    1.00    1.00    0.99    0.99    range-covered-si_range=100

1.00    1.00    1.01    1.01    1.00    1.00    range-notcovered-pk_range=100

1.00    1.00    1.00    1.01    1.02    1.01    range-notcovered-si_range=100

1.00    1.30    1.19    1.18    1.16    1.20    scan_range=100

col-1   col-2   col-3   col-4   col-5   col-6   range queries without aggregation

1.04    1.02    1.00    1.05    1.02    1.03    read-only-count_range=1000

1.00    1.00    1.03    1.04    1.03    1.04    read-only-distinct_range=1000

1.00    1.00    1.04    1.04    1.06    1.06    read-only-order_range=1000

1.01    1.01    1.04    1.07    1.06    1.07    read-only_range=10000

1.00    1.00    1.01    1.01    1.02    1.01    read-only_range=100

1.00    1.00    1.00    0.99    1.01    0.99    read-only_range=10

1.01    1.01    1.02    1.02    1.03    1.03    read-only-simple_range=1000

1.01    1.00    1.00    1.03    1.02    1.02    read-only-sum_range=1000

col-1   col-2   col-3   col-4   col-5   col-6   writes

1.01    1.02    1.01    1.03    1.13    1.12    delete_range=100

0.99    0.98    0.97    0.98    1.06    1.05    insert_range=100

0.99    1.00    1.00    1.01    1.02    1.02    read-write_range=100

0.99    1.01    1.01    1.01    1.03    1.01    read-write_range=10

1.00    1.00    1.01    1.00    1.09    1.08    update-index_range=100

1.00    1.10    1.09    1.09    1.10    1.09    update-inlist_range=100

1.03    1.05    1.06    1.05    1.15    1.14    update-nonindex_range=100

0.99    0.98    0.99    0.98    1.07    1.06    update-one_range=100

1.01    1.04    1.06    1.05    1.18    1.17    update-zipf_range=100

0.98    1.01    1.01    0.99    1.07    1.07    write-only_range=10000