Contents
Concepts
This page explains the ideas behind the functions. Read it once, then use the function reference as a lookup table.
Checksum levels
The extension works at six levels, plus sequences:
| Level | Physical | Logical | Typical question |
|---|---|---|---|
| Page | yes | — | Is this one storage page intact? |
| Cell | — | yes | Does this one value have the expected hash? |
| Tuple | yes | yes | Does this row still match its expected bytes or content? |
| Table | yes | yes | Did the table’s storage or logical content change? |
| Index | yes | yes | Is the physical index intact, or does it contain the expected entries? |
| Sequence | — | yes | Was the sequence (definition and last_value) carried over? |
| Database | yes | yes | Did anything in the database change? |
A logical table checksum can also be split by key bucket or block range to find which rows differ between two copies of a table; see Finding differing rows.
Page checksums are PostgreSQL’s built-in 16-bit page checksum. Page-level
64-bit checksums do not exist in this extension; the other levels have both
physical and logical forms. pg_page_checksum calculates the checksum but does not
compare it with the stored pd_checksum; use pg_page_checksum_status for
verification, and pg_relation_page_checksum_status to verify every page of a
relation.
Logical vs physical
Logical checksums
A logical checksum is a fingerprint of data content. It deliberately excludes:
- heap TIDs and tuple locations;
- relation and type OIDs;
- page layout and MVCC headers;
- physical row order.
It is stable across VACUUM, VACUUM FULL, CLUSTER, REINDEX, dump/restore
and OID changes. That makes it suitable for:
- comparing a primary and a replica;
- checking data after a restore or migration;
- detecting logical data changes in CI;
- comparing the same schema in two databases.
A primary key is not required. Duplicate rows are preserved by a sum plus count aggregate, so two identical rows contribute twice.
Since 2.4 the value also does not depend on dropped columns (columns are
identified by their position among the live columns) or on the session’s
search_path (user-defined types are identified by their unqualified name).
It does not depend on quote_all_identifiers either.
Renaming a table or one of its columns does not change the table’s logical
checksum. The logical database checksum does change when a table or index is
renamed or moved to another schema: it identifies each relation by its
qualified name (schema.relname, not the OID, which differs after a dump and
restore), so moving rows between two tables of the same structure is detected. Two caveats remain: type names are part of the hash, so renaming a
type changes the checksum of every table with a column of that type
(including a table whose row type is used as a column type elsewhere: renaming
that table changes the checksum of the tables that use it), and a plain
oid column is hashed as the stored number, so it does not survive a dump and
restore. The reg* types (regclass, regtype, regproc, regprocedure,
regoper, regoperator, regconfig, regdictionary, regnamespace,
regrole, regcollation) are hashed by the qualified name of the object they
reference (a function or operator with its argument types), so they are
stable across a dump and restore.
Physical checksums
A physical checksum is a fingerprint of one cluster’s storage. It includes physical locations, tuple headers, MVCC fields, page/index bytes and relation storage identity. It changes after:
VACUUM FULL,CLUSTERor a table rewrite;REINDEX;- moving a tuple to another block or offset;
- changing the relation’s relfilenode or tablespace;
- locking a row, although its data did not change (see below).
Table and tuple physical checksums include each tuple’s xmin, xmax and
command id, whether or not the tuple header is requested. A row lock is
stored in xmax, so these statements change the physical checksum of the
rows they touch without changing any data:
SELECT ... FOR UPDATE,FOR NO KEY UPDATE,FOR SHAREandFOR KEY SHARE;- foreign key checks: inserting or updating a referencing row takes
FOR KEY SHAREon the referenced row, so writes to a child table change the physical checksum of the parent; - an
UPDATEorDELETEthat was rolled back: itsxmaxstays on the row until the row is updated again or frozen.
VACUUM clears such an xmax when it freezes the row, which can change the
physical checksum back. Logical checksums are not affected by any of this.
Index physical checksums are not either: index pages do not store xmax.
Use physical checksums to detect on-disk corruption or to confirm that a maintenance operation changed exactly what you expected. Do not compare physical checksums between different clusters, replicas or databases.
A simple rule
| You want to know… | Use |
|---|---|
| Has the data changed? | logical |
| Has this cluster’s storage changed? | physical |
| Is an index entry missing or corrupt? | physical index |
| Does the index logically match the table? | logical index |
Which API should I use?
The extension provides two forms of the same functions.
Default 32-bit API
The default functions use FNV-1a 32-bit and return an ordinary SQL integer
or bigint:
SELECT pg_table_logical_checksum('orders'::regclass);
SELECT pg_table_physical_checksum('orders'::regclass, false);
This is the simplest interface and the right default for most users. A 32-bit checksum is sufficient for detecting accidental data changes and corruption; it is also the format used by v1 CLI baselines.
Generic API: choose the algorithm
The generic overloads take an algorithm name and return raw bytea:
SELECT pg_table_logical_checksum('orders'::regclass, 'fnv64');
SELECT pg_table_logical_checksum('orders'::regclass, 'fnv32');
Choose this form when you need:
- a wider 64-bit digest (
fnv64orxxh3); - a faster hash for large values and pages:
xxh3(64-bit) or the hardware-acceleratedcrc32c(32-bit); - an explicit algorithm in the call;
- a cross-system format that is not tied to a SQL integer type;
- a v2 CLI baseline.
Digests are big-endian bytes: fnv32 and crc32c are 4 bytes, fnv64 and
xxh3 are 8 bytes.
Table and database physical checksums always return 8 bytes because the
relation/database identity is part of the value.
All algorithms share the same canonical value handling and aggregation rules; checksums of different algorithms are never comparable with each other. A 64-bit digest is wider, but none of the algorithms is cryptographic.
| Algorithm | Width | Use it for |
|---|---|---|
fnv32 |
32 bits | the default API, v1 baselines, narrow rows |
fnv64 |
64 bits | existing 64-bit baselines, narrow rows |
crc32c |
32 bits | 32-bit checksums of large values and of pages |
xxh3 |
64 bits | 64-bit checksums of large values and of pages |
FNV-1a hashes one byte at a time, so it is slow on long inputs but has no
per-call overhead. crc32c (hardware-accelerated where PostgreSQL uses CPU
CRC instructions) and xxh3 are much faster on long inputs and slightly
slower on short ones. Measured on one machine: physical table and index
checksums, which hash whole pages, took a third to a seventh of the FNV time;
logical checksums of a table of large (TOASTed) values took less than half;
logical checksums of tables with narrow rows (integers, short text) took
10-20% longer than with FNV. Measure with your own data
(bench/run_bench.sh) if speed matters.
Converting a digest to bigint
SELECT pg_checksum_bigint(pg_table_logical_checksum('orders'::regclass, 'fnv64'));
For 4-byte (fnv32, crc32c) digests, the one-argument form sign-extends. Use
pg_checksum_bigint(digest, true) when you need the unsigned value (for
example, to compare with a table logical checksum from the default API).
The detailed signatures are in the function reference.
Collision probability
A 32-bit checksum has 2^32 possible outputs. For an unrelated accidental
change, the rough chance of an unnoticed collision is about 2^-32, or
2.3e-10. For a 64-bit checksum it is about 2^-64, or 5.4e-20.
The final table/database checksum is still only as wide as the selected output. These numbers are approximate and assume a random change; none of the algorithms (FNV-1a, CRC-32C, XXH3) is cryptographic, and an adversary who can recompute the checksum can produce a collision. The 32-bit default is adequate for ordinary change and corruption detection.
How aggregation works
Table, index and database checksums are order-independent:
- physical aggregates combine hashes with XOR;
- logical aggregates use sum plus count.
The count is important: a pure XOR would cancel duplicate rows.
Parallel workers split the scan by blocks or relations and the leader combines
their partial results. With pg_integrity_checksums.workers > 0, serial and
parallel executions produce identical checksums, for every algorithm.
Baseline formats
The CLI stores a snapshot in a versioned JSON baseline.
| Format | Algorithm | Written by |
|---|---|---|
v1 |
FNV-1a 32-bit | capture by default |
v2 |
explicit hash (fnv64, crc32c or xxh3) |
capture --hash fnv64 (or crc32c, xxh3) |
verify and update inherit the algorithm from the baseline they read.
Older CLI releases reject v2 instead of comparing incompatible digests.
See CLI baseline formats.
What this is not
- FNV-1a, CRC-32C and XXH3 are not cryptographic. They detect accidental changes, not a malicious attacker who can recompute the hash.
- Physical checksums are tied to one cluster and database.
- Logical checksums describe content, not physical layout.
- The extension does not replace backups,
pg_checksumsoramcheck.
Permissions and safety
Table, page, cell, tuple and index checksums read relation storage directly:
- they require
SELECTon the relation (or the base table for an index); - relations with row-level security are rejected, because a direct heap scan cannot apply RLS policies;
- database-level functions require superuser.
Capture and verify take one repeatable-read, read-only snapshot. The CLI never modifies application data.
Read next
- Getting started — run your first checksum.
- Function reference — exact signatures and return types.
- Operations — limitations, performance and use cases.
- Troubleshooting — common errors.