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:

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?
Database yes yes Did anything in the database change?

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.

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). Two caveats remain: renaming a type or a column-bearing relation changes the checksum, because type names are part of the hash, and values of types that store OIDs (oid, regclass, …) are hashed as stored.

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.

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.

Optional 64-bit API

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 (fnv64);
  • 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 is 4 bytes and fnv64 is 8 bytes. Table and database physical checksums always return 8 bytes because the relation/database identity is part of the value.

The 32-bit and 64-bit paths share the same canonical value handling and aggregation rules. A 64-bit digest is wider, but FNV-1a remains a non-cryptographic checksum.

Converting a digest to bigint

SELECT pg_checksum_bigint(pg_table_logical_checksum('orders'::regclass, 'fnv64'));

For 4-byte fnv32 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; FNV-1a is not 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 both 32-bit and 64-bit paths.

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 (fnv32 or fnv64) capture --hash fnv64

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 is not cryptographic. It detects 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.

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