Contents
Benchmarks
How long a checksum takes, compared with reading the same data. The
measure that transfers to other hardware is the ratio to a sequential scan
(x seq scan): the checksum reads every page just like the scan, so the
ratio shows what hashing adds on top of the I/O.
Setup
- AMD Ryzen 5 5600 (6 cores, 12 threads), Linux under WSL2 with 8 GB of memory, data on the WSL virtual disk on an NVMe SSD.
- PostgreSQL 18.7 built from source,
shared_buffers = 2GB,--data-checksums; pg_integrity_checksums 2.9.1, which reads blocks through a read stream (see Before the read stream for 2.9.0). - Data:
pgbench -i, tablepgbench_accounts(an integer key, two integers and achar(84)column) with its primary key, frozen and analyzed. - Each case runs once to warm up, then three times; the table shows the
median.
workers = 4sets bothmax_parallel_workers_per_gather(for the plain SQL queries) andpg_integrity_checksums.workers.
The cases:
| Case | Query |
|---|---|
| seq scan | SELECT count(*) FROM pgbench_accounts |
| SQL hash | SELECT sum(hashtextextended(t::text, 0)) FROM pgbench_accounts t, a hand-written order-independent row hash |
| table logical | pg_table_logical_checksum('pgbench_accounts'), default fnv32, and with 'xxh3' |
| table physical | pg_table_physical_checksum('pgbench_accounts', false) |
| database logical | pg_database_logical_checksum(), all user tables and indexes |
About 1 GB (fits in memory)
pgbench -i -s 68: 871 MB heap, 146 MB of indexes.
| Case | serial, ms | x seq scan | 4 workers, ms | x seq scan |
|---|---|---|---|---|
| seq scan | 301 | 1.0 | 208 | 1.0 |
| SQL hash | 3413 | 11.3 | 841 | 4.0 |
| table logical, fnv32 | 1080 | 3.6 | 252 | 1.2 |
| table logical, xxh3 | 769 | 2.6 | 201 | 1.0 |
| table physical, fnv32 | 977 | 3.2 | 204 | 1.0 |
| database logical | 1292 | 4.3 | 1043 | 5.0 |
About 10 GB (larger than memory)
pgbench -i -s 680: 8711 MB heap, 1457 MB of indexes, more than the
machine’s memory, so most of every run reads from disk.
| Case | serial, ms | x seq scan | 4 workers, ms | x seq scan |
|---|---|---|---|---|
| seq scan | 3052 | 1.0 | 1994 | 1.0 |
| SQL hash | 35805 | 11.7 | 10728 | 5.4 |
| table logical, fnv32 | 10671 | 3.5 | 7304 | 3.7 |
| table logical, xxh3 | 8783 | 2.9 | 6277 | 3.1 |
| table physical, fnv32 | 9461 | 3.1 | 6614 | 3.3 |
| database logical | 13067 | 4.3 | 10566 | 5.3 |
Timings of the 10 GB runs vary by up to about 30% from run to run on this machine, depending on what the operating system still has cached.
Reading the results
- On one core a logical table checksum costs about three sequential scans,
in memory and from disk alike: the read stream keeps the disk busy while
the rows are hashed. It is about three times faster than hashing
row::textin SQL, and does not depend on the text output format,DateStyleor other session settings. - With workers and the data in memory, a table checksum takes about as long as a parallel sequential scan. From disk the workers help less: a parallel table checksum takes about three times a parallel sequential scan.
xxh3is faster than the defaultfnv32on this table because of its 84-character column; on tables of short integers and short stringsfnv32can be faster (see Concepts).- A database checksum gives whole relations to its workers, so a database
that is mostly one large table gains little from them. To checksum such a
table in parallel, call
pg_table_logical_checksumfor it with workers set.
Before the read stream
Up to 2.9.0 the extension read one block at a time. In memory that makes no difference; from disk, on the 10 GB data set, it took about twice as long serially:
| Case | 2.9.0 serial, ms | read stream, ms | 2.9.0, 4 workers, ms | read stream, ms |
|---|---|---|---|---|
| table logical, fnv32 | 21233 | 10671 | 7520 | 7304 |
| table logical, xxh3 | 19009 | 8783 | 7168 | 6277 |
| table physical, fnv32 | 19797 | 9461 | 7235 | 6614 |
| database logical | 22187 | 13067 | 16529 | 10566 |
PostgreSQL 12 to 16 have no read stream and still read block by block.
Run it yourself
bench/compare.sh loads the data and prints both tables, against a running
server with the extension installed (libpq environment):
bench/compare.sh # pgbench scale 68, about 1 GB
SCALE=680 bench/compare.sh # about 10 GB
SETUP=no WORKERS="0 2 4 8" bench/compare.sh # reuse the data, more worker counts
bench/run_bench.sh measures every function on several data types and is
meant for comparing two builds; see Testing.