# Concepts This page explains the ideas behind the functions. Read it once, then use the [function reference](functions.md) 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](functions.md#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`, `CLUSTER` or 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 SHARE` and `FOR KEY SHARE`; - foreign key checks: inserting or updating a referencing row takes `FOR KEY SHARE` on the referenced row, so writes to a child table change the physical checksum of the parent; - an `UPDATE` or `DELETE` that was rolled back: its `xmax` stays 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`: ~~~sql 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`: ~~~sql 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 (`fnv64` or `xxh3`); - a faster hash for large values and pages: `xxh3` (64-bit) or the hardware-accelerated `crc32c` (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` ~~~sql 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](functions.md). ## 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](cli.md#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_checksums` or `amcheck`; see [Compared with other tools](#compared-with-other-tools). ## Compared with other tools | | pg_integrity_checksums | `pg_checksums` / `data_checksums` | `amcheck` | `pg_comparator` | |---|---|---|---|---| | Answers | Do two copies hold the same data? | Is a page damaged on disk? | Are a table and its indexes structurally valid? | Which rows differ between two tables? | | Compares two databases | yes, live or against a saved baseline | no | no | yes, both online at once | | Unaffected by dump/restore, VACUUM FULL, CPU architecture | yes (logical mode) | — | — | yes | | Runs on a live server | yes, in one MVCC snapshot | `data_checksums` on read; the `pg_checksums` tool needs a stopped cluster | yes | yes | | Finds the differing rows | yes, by key buckets, no primary key needed | — | — | yes, needs a primary key, and can sync them | **PostgreSQL data checksums** (`initdb --data-checksums`, or the `pg_checksums` tool on a stopped cluster) store a 16-bit checksum in every page and check it when the page is read. They detect a page damaged by the storage after it was written. They cannot detect a page that was never written, rows lost by a broken restore, or a replica that diverged: every page there is valid. `pg_relation_page_checksum_status` reports the same page check on a running server, per relation. **`amcheck`** (`bt_index_check`, `verify_heapam`, the `pg_amcheck` tool) checks structural invariants inside one cluster: B-tree ordering, heap tuple headers, index entries pointing to existing tuples. It is the right tool for "is this cluster internally consistent". Logical index checksums here are computed from the table rows, not from the index pages, so they do not find a corrupt index entry; `amcheck` does. **`pg_comparator`** connects to two databases at once and compares tables recursively by key, and it can synchronize them, including across DBMSs. pg_integrity_checksums compares PostgreSQL databases only and does not change data, but the two sides do not have to be online at the same time: a checksum or a CLI baseline taken today can be compared with a restored backup next month. **`pg_dump` and a diff** works for small databases, but the dump is as large as the data, row order is not guaranteed, and the output depends on `pg_dump` version and settings. Typical setup: `data_checksums = on` and periodic `amcheck` for corruption inside each cluster, and logical checksums for the question whether a replica, a restore or a migration has exactly the data of its source. ## Permissions and safety Table, page, cell, tuple and index checksums read relation storage directly: - they require `SELECT` on 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](getting-started.md) — run your first checksum. - [Function reference](functions.md) — exact signatures and return types. - [Operations](operations.md) — limitations, performance and use cases. - [Troubleshooting](troubleshooting.md) — common errors.