This has performance results for Postgres 18 beta1 and several older Postgres releases using the sysbench benchmark and a small server. Results like this from me are usually boring because Postgres has done a great job at avoiding performance regressions over time. This work was done by Small Datum LLC and not sponsored.
tl;dr - from Postgres 17.5 to 18 beta1
- there might be small regressions (1% to 4%) in a few range query microbenchmarks
- there might be a few small improvements (<= 3%) in other microbenchmarks
- overall there are neither big improvements nor big regressions which is great news
- 18 beta1 ranges from 7% slower to 13% faster
- there are more improvements than regressions
- the largest regressions occur on range query microbenchmarks
Builds, configuration and hardware
I compiled Postgres from source using -O2 -fno-omit-frame-pointer for versions 14.0, 14.18, 15.0, 15.13, 16.0, 16.9, 17.0, 17.5 and 18 beta1.
The server is an ASUS ExpertCenter PN53 with and AMD Ryzen 7 7735HS CPU, 8 cores, SMT disabled, 32G of RAM and one NVMe device for the database. The OS has been updated to Ubuntu 24.04 -- I used 22.04 prior to that. More details on it are here.
For Postgres versions 14.0 through 17.5 the configuration files are in the pg* subdirectories here with the name conf.diff.cx10a_c8r32. For Postgres 18 beta1 the configuration files are here and I used 3 variations, which are here:
- conf.diff.cx10b_c8r32
- uses io_method='sync' to match Postgres 17 behavior
- conf.diff.cx10c_c8r32
- uses io_method='worker' and io_workers=16 to do async IO via a thread pool. I eventually learned that 16 is too large but I don't think it matters much on this workload.
- conf.diff.cx10d_c8r32
- uses io_method='io_uring' to do async IO via io_uring
Benchmark
I used sysbench and my usage is explained here. To save time I only run 27 of the 42 microbenchmarks and most test only 1 type of SQL statement. Benchmarks are run with the database cached by Postgres.
The tests run with 1 client and 1 table with 50M rows. The read-heavy microbenchmarks run for 300 seconds and write-heavy run for 600 seconds.
The tests run with 1 client and 1 table with 50M rows. The read-heavy microbenchmarks run for 300 seconds and write-heavy run for 600 seconds.
The command line to run all tests is: bash r.sh 1 50000000 300 600 $deviceName 1 1 1
Results
I don't provide graphs in this post to save time and because there are few to no regressions from Postgres 17.5 to 18 beta1. 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 do provide tables below with relative QPS. The relative QPS is the following:
(QPS for $some_version) / (QPS for $base_version)
And $base_version is either Postgres 17.5 or Postgres 14.0 as specified below. 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 also provided. Theses can help to explain why something is faster or slower because it shows how much HW is used per request.
Results: Postgres 18 beta1 vs 17.5
Results: Postgres 18 beta1 vs 17.5
For the results here $base_version is Postgres 17.5 and that is compared with Postgres 18 beta1 using the three configurations listed above: cx10b_c8r32, cx10c_c8r32 and cx10d_c8r32.
The hardware efficiency metrics, counters from iostat and vmstat normalized by QPS, are here.
The hardware efficiency metrics, counters from iostat and vmstat normalized by QPS, are here.
From the relative QPS results below, there might be small regressions for the microbenchmarks highlighted in yelllow -- range-covered-pk*, range-covered-si*, range-notcovered-pk*, read-only-distinct*. All of these do range queries and the last also does aggregation. If there is a regression it is small - between 1% and 4%. However, that requires more investigation because small regressions are not easy to detect with sysbench.
The HW efficiency metrics show there is more CPU per query. The results are here for range-covered-pk*, range-covered-si*, range-notcovered-pk*, read-only-distinct*.
The HW efficiency metrics show there is more CPU per query. The results are here for range-covered-pk*, range-covered-si*, range-notcovered-pk*, read-only-distinct*.
I will revisit this.
The results also show possible small improvements for many of the microbenchmarks. I highlight them in green and again am not certain about the results. The changes (both good and bad) are small enough in most cases that they can be from normal variance.
The results also show possible small improvements for many of the microbenchmarks. I highlight them in green and again am not certain about the results. The changes (both good and bad) are small enough in most cases that they can be from normal variance.
Relative to: PG 17.5 with cx10a_c8r32
col-1 : PG 18beta1 with cx10b_c8r32
col-2 : PG 18beta1 with cx10c_c8r32
col-3 : PG 18beta1 with cx10d_c8r32
col-1 col-2 col-3 --> point queries
1.01 1.01 0.99 hot-points_range=100
1.01 1.00 1.01 point-query_range=100
1.00 0.99 0.98 points-covered-pk_range=100
0.99 0.99 0.99 points-covered-si_range=100
1.00 1.01 1.01 points-notcovered-pk_range=100
1.01 1.00 1.00 points-notcovered-si_range=100
1.00 1.01 1.01 random-points_range=1000
1.03 1.01 1.02 random-points_range=100
1.01 1.01 1.02 random-points_range=10
col-1 col-2 col-3 --> range queries, part 1, no aggregation
0.98 0.98 0.98 range-covered-pk_range=100
0.98 0.99 0.99 range-covered-si_range=100
0.99 0.98 0.98 range-notcovered-pk_range=100
0.99 0.99 1.00 range-notcovered-si_range=100
1.02 1.02 1.03 scan_range=100
col-1 col-2 col-3 --> range queries, part 2, with aggregation
1.04 1.03 1.02 read-only-count_range=1000
0.97 0.96 0.96 read-only-distinct_range=1000
1.00 1.00 0.99 read-only-order_range=1000
1.00 1.00 1.00 read-only_range=10000
1.00 0.99 0.99 read-only_range=100
1.00 0.99 1.00 read-only_range=10
1.01 1.00 1.00 read-only-simple_range=1000
1.02 1.02 1.02 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
1.00 0.99 0.99 read-write_range=100
1.00 0.99 1.00 read-write_range=10
0.99 1.00 1.00 update-index_range=100
1.00 0.99 0.99 update-inlist_range=100
1.00 1.00 1.00 update-nonindex_range=100
1.00 1.00 1.00 update-one_range=100
1.00 0.99 1.00 update-zipf_range=100
0.99 0.99 1.00 write-only_range=10000
Results: Postgres 14.0 through 18 beta1
For the results here $base_version is Postgres 14.0 and that is compared with more recent Postgres releases. The purpose for this is to see how performance changes over time. Postgres 18beta1 is between 7% slower and 13% faster than 14.0 and there are more improvements than regressions. This is yet another boring result from Postgres, but it is great to see that it continues to focus on CPU efficiency.
The hardware efficiency metrics, counters from iostat and vmstat normalized by QPS, are here.
The data below is also here which can be easier to read.
Relative to: PG 14.0 cx10a_c8r32
col-1 : PG 14.18 cx10a_c8r32
col-2 : PG 15.0 cx10a_c8r32\
col-3 : PG 15.13 cx10a_c8r32
col-4 : PG 16.0 cx10a_c8r32
col-5 : PG 16.9 cx10a_c8r32
col-6 : PG 17.0 cx10a_c8r32
col-7 : PG 17.5 cx10a_c8r32
col-8 : PG 18beta1 cx10b_c8r32
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 --> point queries
1.00 1.03 1.03 1.03 1.03 1.98 1.94 1.95 hot-points_range=100
1.04 1.03 1.02 1.03 1.04 1.05 1.04 1.05 point-query_range=100
1.00 1.02 1.02 1.03 1.02 1.00 1.01 1.01 points-covered-pk_range=100
0.99 0.99 1.00 1.01 0.99 0.99 0.99 0.98 points-covered-si_range=100
1.00 1.00 1.00 1.02 1.01 0.99 0.99 0.99 points-notcovered-pk_range=100
0.99 0.99 0.99 0.99 0.99 0.99 0.98 0.99 points-notcovered-si_range=100
0.99 1.01 1.00 1.02 1.02 0.99 0.99 0.98 random-points_range=1000
1.00 1.00 1.00 1.01 1.01 0.99 0.98 1.00 random-points_range=100
1.01 1.02 1.01 1.03 1.03 1.02 1.00 1.01 random-points_range=10
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 --> range queries, part 1, no aggregation
0.98 0.98 0.95 0.97 0.97 0.98 0.98 0.95 range-covered-pk_range=100
0.97 0.97 0.96 0.98 0.97 0.97 0.97 0.95 range-covered-si_range=100
1.01 1.00 1.00 0.98 0.98 0.98 0.98 0.97 range-notcovered-pk_range=100
0.99 0.99 0.98 0.99 0.98 1.00 0.97 0.96 range-notcovered-si_range=100
0.90 0.80 0.78 0.89 1.02 0.91 0.91 0.93 scan_range=100
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8. --> range queries, part 2, with aggregation
0.99 0.98 0.99 1.00 0.97 0.96 0.96 0.99 read-only-count_range=1000
1.01 1.03 1.02 1.03 1.02 1.01 1.02 0.99 read-only-distinct_range=1000
1.00 1.03 1.03 1.04 1.03 1.05 1.06 1.06 read-only-order_range=1000
1.00 1.03 1.03 1.05 1.04 1.04 1.04 1.04 read-only_range=10000
1.00 1.00 1.00 1.01 1.01 1.01 1.01 1.01 read-only_range=100
1.00 1.01 1.00 1.00 1.00 1.01 1.01 1.00 read-only_range=10
1.01 1.00 1.01 1.00 1.00 1.00 1.00 1.00 read-only-simple_range=1000
1.01 1.00 1.01 1.01 1.00 1.00 0.99 1.01 read-only-sum_range=1000
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 --> writes
1.00 1.02 0.98 0.98 0.98 1.09 1.10 1.09 delete_range=100
0.98 1.00 1.00 0.98 0.98 1.07 1.06 1.05 insert_range=100
1.00 1.00 1.00 1.01 1.01 1.02 1.03 1.02 read-write_range=100
1.00 1.01 1.00 1.01 1.01 1.01 1.01 1.01 read-write_range=10
0.99 1.01 0.99 1.00 1.00 1.07 1.07 1.06 update-index_range=100
1.01 1.02 1.01 1.01 1.02 0.99 1.02 1.02 update-inlist_range=100
1.01 1.03 1.00 1.01 1.02 1.07 1.09 1.09 update-nonindex_range=100
1.00 1.01 0.98 1.00 1.01 1.13 1.12 1.11 update-one_range=100
1.01 1.04 1.00 1.01 1.03 1.11 1.13 1.13 update-zipf_range=100
1.00 1.02 1.00 1.00 1.01 1.06 1.07 1.06 write-only_range=10000
No comments:
Post a Comment