# pg_checksums - Multi-Level Data Integrity Extension for PostgreSQL

[![CI](https://github.com/VictorNagibator/pg_checksums/actions/workflows/ci.yml/badge.svg)](https://github.com/VictorNagibator/pg_checksums/actions/workflows/ci.yml)

## 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`.

```bash
# 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](https://pgxn.org/dist/pg_checksums/) with the
[pgxn client](https://github.com/pgxn/pgxnclient):

```bash
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.

```sql
-- 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).

```sql
-- 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).

```sql
-- 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.

```sql
-- 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).

```sql
-- 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.

```sql
-- 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.

```sql
-- 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.

```sql
-- 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.

```sql
-- 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.

```sql
-- 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.

```sql
-- 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.

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

**`pg_text_checksum(text, integer) -> integer`**

Wrapper for text data checksum.

```sql
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:**

```text
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.

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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).

```bash
# 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

```bash
# 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:

```bash
make dist
```

### Automated releases

Pushing a `v*` tag runs the [Release workflow](.github/workflows/release.yml),
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](https://manager.pgxn.org/) when the
   `PGXN_USERNAME` and `PGXN_PASSWORD` repository secrets are configured.

```bash
git tag -a v1.0.0 -m "pg_checksums 1.0.0"
git push origin v1.0.0
```

---

## License

Released under the [MIT License](LICENSE).

## Support

* Source code and issue tracker: <https://github.com/VictorNagibator/pg_checksums>
* Distribution page on PGXN: <https://pgxn.org/dist/pg_checksums/>