Postgres 19 beta1 vs sysbench on a small server

This has results from sysbench on a small server with Postgres 19 beta1, 18.4 and 17.10. Sysbench is run with low concurrency (1 thread) and a cached database. The purpose is to search for changes in performance, often from new CPU overheads.

tl;dr

• 19beta1, 18.4 and 17.10 have mostly similar performance
• There might be small regressions (about 2%) from 17.10 to 19beta1 but my tests are not good at spotting that.
• 19beta1 is much faster on one test (read-only-count) thanks to a new query plan

Builds, configuration and hardware

I compiled Postgres from source. 

The server is a Beelink SER7 7840HS with an AMD Ryzen 7 7840HS CPU and 32G RAM. Storage uses an NVMe device with ext-4 and discard enabled. The OS is Ubuntu 24.04. 

The config files are here for 17.10, 18.4 and 19 beta1.

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 (rQPS). 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.

Here, base version is Postgres 17.10 and some version is either 18.4 or 19 beta1.

I describe performance changes (changes to relative QPS) in terms of basis points. Performance changes by one basis point when the difference in rQPS is 0.01. When rQPS decreases from 0.95 to 0.85 then it changed by 10 basis points.

Results: point queries

Summary:

• 19beta1 is better than 17.10 by ~3 basis points for most tests
• 19beta1 is slightly better than 18.4

Relative to Postgres 17.10

col-1 : Postgres 18.4

col-2 : Postgres 19 beta1

col-1   col-2

1.01    1.00    hot-points

0.98    0.97    point-query

1.01    1.03    points-covered-pk

1.00    1.04    points-covered-si

1.00    1.02    points-notcovered-pk

1.00    1.03    points-notcovered-si

0.99    0.99    random-points_range=10

1.00    1.03    random-points_range=100

1.01    1.03    random-points_range=1000

Results: range queries without aggregation

Summary:

• 19beta1 is worse than 17.10 by ~3 basis points in 4 of 5 tests
• 19beta1 is better than 17.10 by 5 basis points in the scan test
• 19beta1 and 18.4 are similar except for the scan test where 19beta1 did better

Relative to Postgres 17.10

col-1 : Postgres 18.4

col-2 : Postgres 19 beta1

col-1   col-2

0.98    0.97    range-covered-pk

0.96    0.96    range-covered-si

0.98    0.98    range-notcovered-pk

0.99    0.99    range-notcovered-si

0.95    1.05    scan

Results: range queries with aggregation

Summary:

• 19beta1 is worse than than 17.10 on two tests
• 19beta1 is better than 17.10 on five tests
• 19beta1 and 17.10 are the same on one test
• 19beta1 is ~2.5X better than 17.10 on the read-only-count test
• 19beta1 and 18.4 have similar results except for the read-only-count test

The query for the read-only-count test appears to have a different plan in 19beta1 and that might explain the ~2.5X speedup. In 17.10 and 18.4 it gets Index Scan while in 19beta1 it gets Index Only Scan.

Query plans for the read-only-count test ...

For 17.10

explain SELECT count(c) FROM sbtest1 WHERE id BETWEEN 17704460 AND 17705459

        Aggregate  (cost=1424.42..1424.43 rows=1 width=8)

          ->  Index Scan using sbtest1_pkey on sbtest1  (cost=0.56..1421.93 rows=996 width=121)

                Index Cond: ((id >= 17704460) AND (id <= 17705459))

For 18.4

explain SELECT count(c) FROM sbtest1 WHERE id BETWEEN 11575278 AND 11576277

        Aggregate  (cost=1310.09..1310.10 rows=1 width=8)

          ->  Index Scan using sbtest1_pkey on sbtest1  (cost=0.56..1307.89 rows=882 width=121)

                Index Cond: ((id >= 11575278) AND (id <= 11576277))

For 19beta1

explain SELECT count(c) FROM sbtest1 WHERE id BETWEEN 11686801 AND 11687800

        Aggregate  (cost=32.32..32.33 rows=1 width=8)

          ->  Index Only Scan using sbtest1_pkey on sbtest1  (cost=0.56..30.13 rows=878 width=0)

                Index Cond: ((id >= 11686801) AND (id <= 11687800))

Relative to Postgres 17.10

col-1 : Postgres 18.4

col-2 : Postgres 19 beta1

col-1   col-2

1.04    2.47    read-only-count

1.00    0.99    read-only-distinct

1.02    1.01    read-only-order

0.98    0.97    read-only_range=10

1.00    1.00    read-only_range=100

1.02    1.03    read-only_range=10000

1.09    1.09    read-only-simple

1.01    1.01    read-only-sum

Results: writes

Summary:

• 19beta1 is worse than 17.10 by 2 to 5 basis points
• 18.4 is worse than 17.10 by 2 to 3 basis points

Relative to Postgres 17.10

col-1 : Postgres 18.4

col-2 : Postgres 19 beta1

col-1   col-2

0.97    0.97    delete

0.99    0.96    insert

0.98    0.97    read-write_range=10

0.98    0.98    read-write_range=100

0.96    0.95    update-index

0.99    0.97    update-inlist

0.97    0.96    update-nonindex

0.97    0.95    update-one

0.97    0.95    update-zipf

0.98    0.97    write-only