Download Browse

pg_checksums 1.0.0

This Release
pg_checksums 1.0.0
Date
Status
Stable
Abstract
Multi-level data integrity checksums for PostgreSQL
Description
pg_checksums provides checksum functions for PostgreSQL at six granularity levels (page, cell, tuple, table, index and database), each in a physical and a logical variant (cell level is logical only). Physical checksums detect storage corruption and change after VACUUM FULL, CLUSTER or REINDEX; logical checksums depend only on the data content and stay stable across physical reorganization. The implementation uses the FNV-1a 32-bit hash, supports parallel scans through the pg_checksums.workers GUC, and transparently aggregates partitioned tables.
Released By
VictorNagibator
License
MIT
Resources
Special Files
Tags

Extensions

pg_checksums 1.0.0
Checksum functions at page, cell, tuple, table, index and database level

README

pg_checksums - Multi-Level Data Integrity Extension for PostgreSQL

CI

Overview

pg_checksums is a PostgreSQL extension that provides comprehensive checksum functionality at six granularity levels, with both physical and logical variants (except for cell level). It enables data integrity verification, change detection, consistency checking, and corruption detection across the entire database hierarchy.

Key Features

Multi-Granularity & Dual-Mode Support

  • Six Granularity Levels: Page, Cell, Tuple, Table, Index, and Database
  • Dual Checksum Types: Physical (storage-dependent) and Logical (content-dependent)
  • Type-Aware: Full support for all PostgreSQL data types
  • NULL-Aware: Special CHECKSUM_NULL constant for NULL values
  • MVCC-Aware: Incorporates transaction visibility in physical checksums
  • Primary Key Aware: Logical checksums require and use primary keys
  • Parallel Computation: Table, index, and database checksums can be computed with PostgreSQL parallel workers for faster scans of large relations
  • Partitioned Table Support: Table-level checksums transparently handle partitioned tables by aggregating the checksums of all leaf partitions

Physical and Logical Checksums

Physical Checksums

  • Depend on: Physical storage layout (block numbers, offsets, page headers)
  • Change when: VACUUM FULL, CLUSTER, REINDEX, physical data movement
  • Useful for: Physical corruption detection, storage integrity
  • Includes: Location encoding, MVCC information, page structure

Logical Checksums

  • Depend on: Only logical data content (cell values, primary keys)
  • Stable across: Physical reorganization, VACUUM FULL, CLUSTER, REINDEX
  • Useful for: Logical consistency, replication validation, migration verification
  • Requires: Primary keys for tuple/table/database logical checksums

Installation

Prerequisites

  • PostgreSQL 12 or later (tested on 12, 13, 14, 15, 16, 17, and 18)
  • C compiler and PostgreSQL development headers

Note: PostgreSQL 12 and 13 are past end-of-life upstream, but they are still fully supported by this extension. The build deliberately fails with a clear error on PostgreSQL 11 and older, which lack the table-AM API the extension relies on.

Note: the distribution is named pg_checksums, the same name as the pg_checksums utility shipped with PostgreSQL core. They are unrelated: the core utility verifies data page checksums on disk, while this extension provides SQL functions for computing checksums.

Build and Install

The extension builds with PGXS, so pg_config must be on your PATH, or you can point make at a specific installation with PG_CONFIG=/path/to/pg_config.

# Clone or extract the extension
git clone https://github.com/VictorNagibator/pg_checksums

# Go into extension directory
cd pg_checksums

# Build
make

# Install
make install

# Create extension in your database
psql -d your_database -c "CREATE EXTENSION pg_checksums;"

Or install it straight from PGXN with the pgxn client:

pgxn install pg_checksums

Function Reference

Page Level Functions (Physical Only)

pg_page_checksum(regclass, integer) -> integer

Computes PostgreSQL’s built-in page checksum for physical page corruption detection.

-- Get checksum for page 5 of a table
SELECT pg_page_checksum('my_table'::regclass, 5);

Cell Level Functions (Logical Only)

pg_cell_checksum(regclass, tid, integer) -> integer

Computes logical checksum of a cell value. NULL values return CHECKSUM_NULL (-1).

-- Get checksum for cell (column) 3 of a specific tuple
SELECT pg_cell_checksum('employees'::regclass, '(5,2)'::tid, 3);

Note: Cell checksums are computed for individual values within specific rows, not for entire columns. To compute checksums for all values in a column, you would need to aggregate individual checksums.

Tuple Level Functions

pg_tuple_physical_checksum(regclass, tid, boolean) -> integer

Computes physical tuple checksum based on physical location (block, offset).

-- Physical checksum without header
SELECT pg_tuple_physical_checksum('orders'::regclass, ctid, false) 
FROM orders WHERE order_id = 123;

-- Physical checksum with header (includes MVCC info)
SELECT pg_tuple_physical_checksum('orders'::regclass, ctid, true) 
FROM orders WHERE order_id = 123;

pg_tuple_logical_checksum(regclass, tid, boolean) -> integer

Computes logical tuple checksum based on primary key values and data content. Returns NULL if no PK.

-- Logical checksum (requires primary key)
SELECT pg_tuple_logical_checksum('customers'::regclass, ctid, false) 
FROM customers WHERE customer_id = 456;

Table Level Functions

pg_table_physical_checksum(regclass, boolean) -> bigint

Aggregates physical tuple checksums for entire table (order-independent).

-- Table physical checksum
SELECT pg_table_physical_checksum('inventory'::regclass, false);

-- With tuple headers included
SELECT pg_table_physical_checksum('inventory'::regclass, true);

pg_table_logical_checksum(regclass) -> bigint

Aggregates logical tuple checksums for entire table. Requires primary key.

-- Table logical checksum (requires PK)
SELECT pg_table_logical_checksum('products'::regclass);

Partitioned tables: Both pg_table_physical_checksum and pg_table_logical_checksum accept partitioned tables. Since a partitioned table has no storage of its own, the extension recurses into every leaf partition and aggregates their checksums into a single order-independent value. Logical checksums require the primary key to be defined on the partitioned table (partitions inherit it). Nested (multi-level) partitioning is supported as well.

-- Checksum a partitioned table (recurses into all partitions)
SELECT pg_table_physical_checksum('sales'::regclass, false);
SELECT pg_table_logical_checksum('sales'::regclass);

Index Level Functions

pg_index_physical_checksum(regclass) -> integer

Computes physical checksum of index structure. Supports B-tree, Hash, GiST, GIN, SP-GiST, BRIN.

-- Physical checksum of a B-tree index
SELECT pg_index_physical_checksum('idx_product_name'::regclass);

pg_index_logical_checksum(regclass) -> integer

Computes logical checksum of index key values. Stable across REINDEX.

-- Logical checksum of index (key values only)
SELECT pg_index_logical_checksum('idx_product_name'::regclass);

Database Level Functions (Superuser Only)

pg_database_physical_checksum(boolean, boolean) -> bigint

Aggregates physical relation checksums for entire database.

-- Database physical checksum (excluding system tables)
SELECT pg_database_physical_checksum(false, false);

-- Include system catalogs and TOAST tables
SELECT pg_database_physical_checksum(true, true);

Note about shared system tables: The pg_database_checksum function only includes tables from the current database. Cluster-wide shared system tables (e.g., pg_authid, pg_database) are not included in the checksum calculation as they belong to the entire PostgreSQL cluster, not to individual databases.

pg_database_logical_checksum(boolean, boolean) -> bigint

Aggregates logical relation checksums for entire database. Skips tables without PK.

-- Database logical checksum
SELECT pg_database_logical_checksum(false, false);

Utility Functions

pg_data_checksum(bytea, integer) -> integer

Generic checksum for arbitrary binary data using FNV-1a.

SELECT pg_data_checksum(E'\x01020304'::bytea, 0);

pg_text_checksum(text, integer) -> integer

Wrapper for text data checksum.

SELECT pg_text_checksum('sample text', 0);

Algorithm Details

FNV-1a 32-bit Hash Algorithm

The extension uses the FNV-1a (Fowler-Noll-Vo) 32-bit hash algorithm, identical to PostgreSQL’s internal checksum implementation.

Algorithm:

hash = FNV_BASIS_32;
for each byte in data:
    hash = hash ^ byte;
    hash = hash * FNV_PRIME_32;

Constants:

FNV_BASIS_32 = 2166136261U
FNV_PRIME_32 = 16777619U
CHECKSUM_NULL = 0xFFFFFFFF (signed -1)

Order-Independent Aggregation

For table, index, and database checksums, the extension uses an order-independent aggregate so that the result does not depend on scan order:

  1. Collect all individual checksums
  2. Fold each through a 32-bit (or 64-bit) avalanche finalizer
  3. Combine them with XOR

XOR is both commutative and associative, which makes the aggregate not only order-independent but also partition-independent. This is what allows a single backend and any number of parallel workers to produce byte-for-byte identical results without extra coordination.

Type-Specific Handling

The extension properly handles all PostgreSQL type categories:

  • Pass-by-value types (int4, float8, bool): Binary representation hashed
  • Varlena types (text, bytea): Detoasted and entire structure hashed
  • C-string types: Null-terminated string hashed
  • Pass-by-reference types: Pointer dereferenced and data hashed

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.

Configuration

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

Setting Behavior
pg_checksums.workers = 0 Disabled (default, single-backend)
pg_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_checksums.workers = 4;
SELECT pg_table_physical_checksum('large_table'::regclass, false);

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

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.

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 consistency
-- Run on source:
SELECT 
    table_name,
    pg_table_logical_checksum(table_name::regclass) as logical_cs,
    pg_table_physical_checksum(table_name::regclass, false) as physical_cs
FROM information_schema.tables 
WHERE table_schema = 'public';

-- Run same query on replica and compare results

3. Data Migration Verification

-- Verify migration integrity
WITH source_checksums AS (
    SELECT 
        table_name,
        pg_table_logical_checksum(table_name::regclass) as checksum
    FROM information_schema.tables
    WHERE table_schema = 'public'
),
target_checksums AS (
    -- Same query on target database
)
SELECT 
    s.table_name,
    s.checksum = t.checksum as consistent
FROM source_checksums s
JOIN target_checksums t USING (table_name);

4. Corruption Detection and Repair

-- Find potentially corrupted tuples
SELECT ctid, * 
FROM my_table
WHERE pg_tuple_physical_checksum('my_table'::regclass, ctid, false) != 
      expected_checksum;

-- Validate index integrity
SELECT 
    indexname,
    pg_index_physical_checksum(indexname::regclass) as current,
    expected_checksum,
    CASE WHEN pg_index_physical_checksum(indexname::regclass) != expected_checksum 
         THEN 'CORRUPT' 
         ELSE 'OK' 
    END as status
FROM index_monitoring;

5. Change Detection for Auditing

-- Trigger-based change detection
CREATE FUNCTION audit_table_changes()
RETURNS trigger AS $$
DECLARE
    current_logical bigint;
    current_physical bigint;
BEGIN
    current_logical := pg_table_logical_checksum(TG_TABLE_NAME::regclass);
    current_physical := pg_table_physical_checksum(TG_TABLE_NAME::regclass, false);

    -- Compare with last known good checksums
    IF EXISTS (
        SELECT 1 FROM checksum_baseline 
        WHERE table_name = TG_TABLE_NAME 
        AND (logical_cs != current_logical OR physical_cs != current_physical)
    ) THEN
        INSERT INTO unauthorized_changes 
        VALUES (TG_TABLE_NAME, current_logical, current_physical, now());
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE EVENT TRIGGER audit_changes 
ON ddl_command_end 
EXECUTE FUNCTION audit_table_changes();

6. Point-in-Time Recovery Validation

-- Verify restored database matches expected state
SELECT 
    'Restored DB' as source,
    pg_database_logical_checksum(false, false) as checksum
UNION ALL
SELECT 
    'Backup timestamp ' || backup_time,
    expected_checksum
FROM backup_catalog 
WHERE backup_time = '2024-01-15 03:00:00';

Performance Characteristics

Computational Complexity

Function Complexity Notes
pg_cell_checksum O(1) Single cell, minimal overhead
pg_tuple_*_checksum O(tuple size) Requires buffer lock
pg_table_*_checksum O(rows * columns) Order-independent aggregation
pg_index_*_checksum O(index size) Bulk read strategy
pg_database_*_checksum O(total DB size) Periodic interrupt checks

Table, index, and database scans can be parallelized with pg_checksums.workers; the scan work is divided among the workers with linear speedup up to the point where the scan becomes I/O-bound.

Test Suite

The pg_checksums extension includes a comprehensive test suite that validates functionality at all granularity levels and ensures algorithm correctness. The tests are divided into two categories: regression tests (SQL-based) and TAP tests (Perl-based).

# Run the regression tests
make installcheck

# Run the regression and TAP tests (TAP needs the IPC::Run Perl module and the
# PostgreSQL test modules shipped with the matching server release)
make installcheck enable_tap_tests=yes PROVE=prove

CI runs both suites on every supported major version (PostgreSQL 12 through 18) on Linux, macOS, and Windows.

Tests cover:

  1. Basic functionality at all granularity levels
  2. Physical and logical checksum differences
  3. NULL handling and type support
  4. MVCC behavior and concurrency
  5. Stability of logical checksums across physical reorganizations (VACUUM, CLUSTER, REINDEX)
  6. Error conditions and edge cases
  7. Parallel vs serial checksum consistency at the table, index, and database levels
  8. Partitioned tables (single- and multi-level, with and without primary keys)

Automated Testing with run_tests.sh

To streamline the development workflow, the extension includes a helper script run_tests.sh that automates the complete build–install–test cycle. The script performs the following steps:

  • Cleans previous builds (make clean)
  • Builds the extension with parallel jobs (make -j$JOBS, JOBS defaults to 4)
  • Installs it into the target PostgreSQL installation (using the provided pg_config)
  • Starts the PostgreSQL server (if not already running)
  • Runs the regression and TAP tests (make installcheck)
  • Stops the server automatically, even if tests fail
# Run it from the extension’s root directory
./run_tests.sh

Customization

The script uses the same environment settings as your manual commands. You can adjust the following variables inside the script to match your environment:

  • PG_CONFIG – path to pg_config of the target PostgreSQL installation

  • PGCTL – path to pg_ctl

  • PGDATA – data directory of the test cluster

  • JOBS – number of parallel build jobs (passed to make -j)

Building a Release

The dist target packs the current HEAD into the archive that PGXN expects (pg_checksums-<version>.zip, with everything under a pg_checksums-<version>/ directory), so commit and tag the release first:

make dist

Automated releases

Pushing a v* tag runs the Release workflow, which:

  1. checks that the tag, pg_checksums.control and META.json all carry the same version;
  2. builds the archive with make dist and verifies its contents;
  3. creates the GitHub Release and attaches the archive (tags containing -rc, -beta or -alpha are marked as pre-releases);
  4. uploads the archive to PGXN when the PGXN_USERNAME and PGXN_PASSWORD repository secrets are configured.
git tag -a v1.0.0 -m "pg_checksums 1.0.0"
git push origin v1.0.0

License

Released under the MIT License.

Support