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




at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

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