Contents

• Operations
  • Limitations and Portability
  • Parallel Computation
    • Configuration
    • Strict mode
    • Investigating a mismatch
    • Correctness
    • Snapshot consistency across calls
  • Practical Applications
    • 1. Data Integrity Monitoring
    • 2. Replication Consistency Validation
    • 3. Data Migration Verification
    • 4. Compare Against a Known-Good Value
    • 5. Batch Change Auditing
    • 6. Restore Validation
  • Platform Support

Operations

This page covers correctness, performance and production limitations. For an introduction, read Getting started and Concepts.

Limitations and Portability

These limitations are documented rather than silently ignored.

• Portability is scoped. Logical checksums exclude relation OIDs, heap TIDs and type OIDs. Built-in types, enums, numeric, arrays, composites, domains and ranges use a canonical encoding. Cross-database comparison is intended for the same PostgreSQL major version and compatible schema/type names; custom types whose send/output representation changes between versions may still produce different values.
• Custom types are type-defined. A user-defined type is hashed through its binary send function, or its output function when no send function exists. If that representation differs between databases, the checksum differs.
• Endianness. Logical checksums use a canonical little-endian encoding for the fixed-width integers in arrays, composites and multiranges, and are therefore identical on little-endian and big-endian systems. Physical checksums remain architecture- and storage-dependent.
• Physical checksums are not replica fingerprints. Tuple/table physical checksums depend on tuple locations, MVCC fields, tuple bytes and the relation’s relfilenode/reltablespace; index physical checksums hash raw index pages, including page LSNs. Use physical checksums for on-disk corruption within one cluster, not for comparing replicas.
• Logical index checksums describe expected content, not stored pages. They are derived from visible table rows and the index definition, so a missing or corrupt index entry is not detected by this value. Use pg_index_physical_checksum for actual index storage.
• Coverage gaps. The database physical checksum does not include TOAST content by default, and never includes sequences, large objects (unless include_system = true), FSM/VM forks or relation free space. Foreign table partitions are skipped by partitioned table checksums because they have no local storage in this database.
• Non-cryptographic hashes. The default SQL API uses 32-bit FNV-1a; generic overloads support 32-bit and 64-bit FNV-1a. PostgreSQL page checksums are 16-bit. None of these are cryptographic hashes or provide tamper evidence.
• Collision probability. A random accidental change is missed with probability about 2^-32 (2.3e-10) by a 32-bit checksum and 2^-64 (5.4e-20) by a 64-bit checksum. These are rough estimates, not a cryptographic security claim.
• RLS and column privileges. Relations protected by row-level security are rejected because a direct heap scan cannot apply policies. Table-level SELECT is required; column-only privileges are not sufficient.
• Foreign tables. Foreign partitions/tables have no local storage and are skipped by partitioned checksums.

---

Parallel Computation

Table, index and database checksums can be computed in parallel using PostgreSQL’s parallel worker infrastructure. Each worker scans a subset of the relation’s blocks (or a subset of the database’s relations) using a shared, self-balancing atomic counter, and the partial results are combined by the leader. Logical index checksums parallelize the heap scan and evaluate the index definition in each worker.

Configuration

The degree of parallelism is controlled by the pg_integrity_checksums.workers GUC:

Setting	Behavior
pg_integrity_checksums.workers = 0	Disabled (default, single-backend)
pg_integrity_checksums.workers = N	Use up to N parallel workers

The actual number of workers is clamped by max_parallel_workers and by the number of blocks (or relations) being scanned. Parallelism is also skipped when the function is already running inside a parallel worker, since parallel workers cannot be nested.

-- Compute a table checksum with 4 parallel workers
SET pg_integrity_checksums.workers = 4;
SELECT pg_table_physical_checksum('large_table'::regclass, false);

-- Disable parallelism (restore the default)
RESET pg_integrity_checksums.workers;

Strict mode

Database checksum functions are strict by default (pg_integrity_checksums.strict = on): if a relation in scope cannot be checksummed, the whole call fails. This is deliberate. A partial result is worse than no result for monitoring, because a skipped corrupt table would otherwise look like “nothing changed”.

Situation	strict = on (default)	strict = off
Invalid index, logical checksum	error: relation "..." cannot be checksummed	skipped, counted in the summary WARNING
Invalid index, physical checksum	included (its pages can still be read)	included
Index with an access method that has no physical checksum (for example a third-party AM)	error: relation "..." cannot be checksummed	skipped, counted in the summary WARNING
Corrupt page, unreadable file	the original error, with a CONTEXT line naming the relation	skipped, WARNING per relation plus the summary WARNING
Relation dropped while the scan runs	error	skipped with a WARNING
Unlogged table on a standby (it has no data there)	error naming the table	skipped with a WARNING
Query cancellation, statement_timeout, out of memory, shutdown	always propagated	always propagated

An error looks like this:

SELECT pg_database_logical_checksum(false, true);
-- ERROR: relation "orders_old_idx" cannot be checksummed
-- DETAIL: The index is invalid and has no content to checksum.
-- HINT: Set pg_integrity_checksums.strict = false to skip relations without
--       a usable checksum.

or, for a failure while reading the relation:

ERROR:  invalid page in block 0 of relation base/5/16414
CONTEXT:  while computing the database checksum of relation "orders" (OID 16414)

Tables without a primary key are supported, and logical index checksums work for every access method. Physical index checksums cover B-tree, Hash, GiST, GIN, SP-GiST and BRIN.

For ad-hoc use you can allow skipping, per session or persistently:

SET pg_integrity_checksums.strict = off;
SELECT pg_database_logical_checksum(false, true);
-- WARNING: skipping relation 16438 while computing database checksum
-- WARNING: database checksum skipped 1 relation(s)
-- HINT: Set pg_integrity_checksums.strict = on to fail with the reason ...

ALTER DATABASE appdb SET pg_integrity_checksums.strict = off;  -- persistent

With strict mode off, a skipped relation is excluded from the result and is only reported as a warning (in parallel workers only in the server log), so do not use that value for integrity monitoring. Before 2.5.0 the default was off. The CLI’s --strict flag is separate from this setting; it controls the CLI’s own preflight, not the server GUC.

Investigating a mismatch

A database or table checksum differs from the baseline. Work from the outside in:

1. Physical or logical? A physical checksum also changes after VACUUM FULL, CLUSTER, REINDEX or moving a table to another tablespace; that is expected. A logical difference means the data (or a column or type name) changed.
2. Freeze the view of the data. Run the comparison in one transaction, so that every call sees the same snapshot:

   BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;

1. Find the table. Compare per-table logical checksums on both sides (or against the baseline) and diff the two lists:

   SELECT c.oid::regclass AS tbl, pg_table_logical_checksum(c.oid) AS checksum
   FROM pg_class c
   WHERE c.relkind IN ('r', 'm') AND c.relnamespace = 'public'::regnamespace
   ORDER BY 1;

The CLI does this for you: pg_integrity_checksums verify reports each object that differs. 4. Find the rows. Within a table, compare row checksums by key:

   SELECT id, pg_tuple_logical_checksum('orders'::regclass, ctid)
   FROM orders ORDER BY id;

1. If the data is identical but the storage is suspect, verify the pages against their stored checksums (needs data_checksums = on). The check reads the on-disk copy of each page, so run CHECKPOINT first; pages that have not been written yet are reported as new:

   SELECT blk, pg_page_checksum_status('orders'::regclass, blk) AS status
   FROM generate_series(0,
        (pg_relation_size('orders') / current_setting('block_size')::int)::int - 1) AS blk
   WHERE pg_page_checksum_status('orders'::regclass, blk) = 'mismatch';

1. If a database call fails instead of returning a value, see relation "..." cannot be checksummed.

Correctness

Because aggregation is order- and partition-independent, the parallel result is always identical to the serial result for the same data state, regardless of the number of workers. The same consistent snapshot is propagated to all workers, so every tuple is checksummed as of the same point in time.

Snapshot consistency across calls

One SQL statement sees one snapshot. A single pg_table_logical_checksum or pg_database_logical_checksum call is therefore internally consistent, but two separate calls under the default READ COMMITTED isolation can see different points in time. A table and its index, or several tables, should be checked inside one transaction when the comparison matters:

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT pg_table_logical_checksum('orders'::regclass);
SELECT pg_table_logical_checksum('customers'::regclass);
COMMIT;

The CLI already does this for capture and verify. A long repeatable-read transaction keeps an xmin horizon and can delay VACUUM; keep checksum transactions as short as the workload allows and monitor long-running sessions.

---

Practical Applications

1. Data Integrity Monitoring

-- Create checksum monitoring system
CREATE TABLE checksum_audit_log (
    audit_time timestamptz DEFAULT now(),
    table_name text,
    checksum_type text,  -- 'physical' or 'logical'
    checksum_value bigint,
    PRIMARY KEY (table_name, checksum_type, audit_time)
);

-- Periodic monitoring of critical tables
INSERT INTO checksum_audit_log (table_name, checksum_type, checksum_value)
SELECT 
    'transactions',
    'physical',
    pg_table_physical_checksum('transactions'::regclass, false)
UNION ALL
SELECT 
    'transactions',
    'logical',
    pg_table_logical_checksum('transactions'::regclass);

2. Replication Consistency Validation

-- Compare source and replica logical content.
-- Logical checksums exclude relation OIDs, heap TIDs and type OIDs, so they
-- are the appropriate values to compare when schema/type names and row
-- content match.  Physical checksums include page LSNs, tuple locations and
-- other storage details, so they will normally differ on a standby.
-- Run on source and replica and compare logical_cs only:
SELECT
    c.relname,
    pg_table_logical_checksum(c.oid) AS logical_cs,
    pg_table_physical_checksum(c.oid, false) AS physical_cs
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind IN ('r', 'p', 'm')
ORDER BY c.relname;

3. Data Migration Verification

-- Verify migration integrity.  Logical checksums omit relation OIDs and heap
-- TIDs, so values are comparable between source and target databases.
-- Run this query on both databases and compare the two result sets.
WITH table_checksums AS (
    SELECT
        c.relname,
        pg_table_logical_checksum(c.oid) AS checksum
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public'
      AND c.relkind IN ('r', 'p', 'm')
)
SELECT * FROM table_checksums ORDER BY relname;

4. Compare Against a Known-Good Value

A checksum is useful only when compared with a value recorded at a known-good point. The audit table from the first example provides that history:

WITH expected AS (
    SELECT checksum_value
    FROM checksum_audit_log
    WHERE table_name = 'transactions'
      AND checksum_type = 'logical'
    ORDER BY audit_time DESC
    LIMIT 1
)
SELECT
    pg_table_logical_checksum('transactions'::regclass) AS current_value,
    expected.checksum_value AS expected_value,
    pg_table_logical_checksum('transactions'::regclass) = expected.checksum_value AS matches
FROM expected;

pg_page_checksum only calculates PostgreSQL’s 16-bit page checksum. It does not compare the value with the stored pd_checksum, and 0 is ambiguous (new page or a genuine zero). Use pg_page_checksum_status when you want a verification result. data_checksums = on is required for stored page checksums to be meaningful; without it the status is checksums_disabled.

5. Batch Change Auditing

Record checksums after a batch, ETL run or maintenance window. Do not compute a whole-table checksum in a per-row trigger: that makes every write O(table) and would flag every legitimate change as suspicious.

-- Record the state once, after the batch has finished.
INSERT INTO checksum_audit_log (table_name, checksum_type, checksum_value)
SELECT 'transactions', 'logical',
       pg_table_logical_checksum('transactions'::regclass)
UNION ALL
SELECT 'transactions', 'physical',
       pg_table_physical_checksum('transactions'::regclass, false);

Compare later runs with the most recent known-good row, for example with the query in section 4.

6. Restore Validation

Record a checksum before the backup, then compare the restored table with that recorded value:

-- Before backup: record a known-good logical checksum.
INSERT INTO checksum_audit_log (table_name, checksum_type, checksum_value)
VALUES ('transactions', 'logical',
        pg_table_logical_checksum('transactions'::regclass));

-- After restore: compare with the latest recorded value.
WITH expected AS (
    SELECT checksum_value
    FROM checksum_audit_log
    WHERE table_name = 'transactions'
      AND checksum_type = 'logical'
    ORDER BY audit_time DESC
    LIMIT 1
)
SELECT
    pg_table_logical_checksum('transactions'::regclass) = expected.checksum_value AS restored_ok
FROM expected;

A database-level checksum is also available when the recorded value was produced by pg_database_logical_checksum; do not compare a table checksum with a database checksum.

---

Platform Support

CI runs regression and TAP tests for PostgreSQL 12–19 on:

• Linux x86_64 and arm64
• macOS arm64
• Windows x86_64
• FreeBSD amd64 and arm64
• Linux s390x (big-endian, manually triggered Big-endian workflow)

Logical checksums use a canonical little-endian encoding for the few fixed-width integers in the canonical form (array shape, composite field count and multirange count). They are therefore identical on little-endian and big-endian systems. Physical checksums remain storage- and architecture-dependent by design; use logical checksums for cross-platform comparisons.

---