Monday, September 29, 2025

Postgres 18.0 vs sysbench on a 24-core, 2-socket server

This post has results from sysbench run at higher concurrency for Postgres versions 12 through 18 on a server with 24 cores and 2 sockets. My previous post had results for sysbench run with low concurrency. The goal is to search for regressions from new CPU overhead and mutex contention.

tl;dr, from Postgres 17.6 to 18.0

  • For most microbenchmarks Postgres 18.0 is between 1% and 3% slower than 17.6
  • The root cause might be new CPU overhead. It will take more time to gain confidence in results like this. On other servers with sysbench run at low concurrency I only see regressions for some of the range-query microbenchmarks. Here I see them for point-query and writes.

tl;dr, from Postgres 12.22 through 18.0

  • For point queries Postgres 18.0 is usually about 5% faster than 12.22
  • For range queries Postgres 18.0 is usually as fast as 12.22
  • For writes Postgres 18.0 is much faster than 12.22

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 server is a SuperMicro SuperWorkstation 7049A-T with 2 sockets, 12 cores/socket, 64G RAM. The CPU is Intel Xeon Silver 4214R CPU @ 2.40GHz. It runs Ubuntu 24.04. Storage is a 1TB m.2 NVMe device with ext-4 and discard enabled.

Prior to 18.0, the configuration file was named conf.diff.cx10a_c24r64 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:

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 16 clients and 8 tables with 10M rows per table. The purpose is to search for regressions from new CPU overhead and mutex contention.

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

Results per microbenchmark from vmstat and iostat are here.

For point queries, 18.0 often gets between 1% and 3% less QPS than 17.6 and the root cause might be new CPU overhead. See the cpu/o column (CPU per query) in the vmstat metrics here for the random-points microbenchmarks.

For range queries, 18.0 often gets between 1% and 3% less QPS than 17.6 and the root cause might be new CPU overhead. See the cpu/o column (CPU per query) in the vmstat metrics here for the read-only_range=X microbenchmarks.

For writes queries, 18.0 often gets between 1% and 2% less QPS than 17.6 and the root cause might be new CPU overhead. I ignore the write-heavy microbenchmarks that also do queries as the regressions for them might be from the queries. See the cpu/o column (CPU per query) in the vmstat metrics here for the update-index microbenchmark.

Relative to: 17.6
col-1 : 18.0 with the x10b config
col-2 : 18.0 with the x10c config
col-3 : 18.0 with the x10d config

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

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

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

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

Results: Postgres 12 to 18

For the Postgres 18.0 results in col-6, the result is in green when relative QPS is >= 1.05 and in yellow when relative QPS is <= 0.98. Yellow indicates a possible regression.

Results per microbenchmark from vmstat and iostat are here.

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
0.98    0.96    0.99    0.98    2.13    2.13    hot-points_range=100
1.00    1.02    1.01    1.02    1.03    1.01    point-query_range=100
0.99    1.05    1.05    1.08    1.07    1.05    points-covered-pk_range=100
0.99    1.08    1.05    1.07    1.07    1.05    points-covered-si_range=100
0.99    1.04    1.05    1.06    1.07    1.05    points-notcovered-pk_range=100
0.99    1.05    1.04    1.05    1.06    1.04    points-notcovered-si_range=100
0.98    1.03    1.04    1.06    1.06    1.04    random-points_range=1000
0.98    1.04    1.05    1.07    1.07    1.05    random-points_range=100
0.99    1.02    1.03    1.05    1.05    1.04    random-points_range=10

col-1   col-2   col-3   col-4   col-5   col-6   range queries without aggregation
1.02    1.04    1.03    1.04    1.03    1.01    range-covered-pk_range=100
1.05    1.07    1.06    1.06    1.06    1.05    range-covered-si_range=100
0.99    1.00    1.00    1.00    1.01    0.98    range-notcovered-pk_range=100
0.97    0.99    1.00    1.01    1.01    1.01    range-notcovered-si_range=100
0.86    1.06    1.08    1.17    1.18    1.20    scan_range=100

col-1   col-2   col-3   col-4   col-5   col-6   range queries with aggregation
0.98    0.97    0.97    1.00    0.98    0.97    read-only-count_range=1000
0.99    0.99    1.02    1.02    1.01    0.99    read-only-distinct_range=1000
1.00    0.99    1.02    1.05    1.05    1.02    read-only-order_range=1000
0.99    0.99    1.04    1.07    1.09    1.06    read-only_range=10000
0.99    1.00    1.00    1.01    1.02    0.99    read-only_range=100
1.00    1.00    1.00    1.01    1.01    1.00    read-only_range=10
0.99    0.99    1.00    1.01    1.01    0.99    read-only-simple_range=1000
0.98    0.99    0.99    1.00    1.00    0.98    read-only-sum_range=1000

col-1   col-2   col-3   col-4   col-5   col-6   writes
0.98    1.09    1.09    1.04    1.29    1.27    delete_range=100
0.99    1.03    1.02    1.03    1.08    1.07    insert_range=100
1.00    1.03    1.04    1.05    1.07    1.05    read-write_range=100
1.01    1.09    1.09    1.09    1.15    1.14    read-write_range=10
1.00    1.04    1.03    0.86    1.44    1.42    update-index_range=100
1.01    1.11    1.11    1.12    1.13    1.12    update-inlist_range=100
0.99    1.04    1.06    1.05    1.25    1.25    update-nonindex_range=100
1.05    0.92    0.90    0.84    1.18    1.15    update-one_range=100
0.98    1.04    1.03    1.01    1.26    1.26    update-zipf_range=100
1.02    1.05    1.10    1.09    1.21    1.18    write-only_range=10000
at No comments:
Labels: , ,

Friday, September 26, 2025

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:

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


at No comments:
Labels: ,

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 No comments:
Labels: , ,

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
at No comments:
Labels: ,

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
at No comments:
Labels: ,
Subscribe to: Posts (Atom)

My time at Oracle: functional and design specification reviews

I worked at Oracle from 1997 to 2005 for 3 years on the app server team in Portland and the last 5 on DBMS query execution in Redwood Shores...