Add a Data Integrity Reconciliation Check

Compare row counts and content fingerprints between primary and replica on a schedule, and alert on drift before it reaches a report.

~35 minpostgresqldata-qualityreplication

Task

Detect missing or altered rows between primary and replica with a repeatable reconciliation query.

When to use this

A replica falls behind or an operator fixes a row only on the primary. Weeks later a report reads from the replica and shows totals that do not match billing.

Prerequisites

PostgreSQL 13 or newer
Read access to both primary and replica
A table with an ordered primary key and a bounded time window

Steps

1. Compute a row count and a deterministic content fingerprint for a closed time window.

SELECT count(*) AS rows,
       md5(string_agg(id::text || ':' || total_cents::text, ',' ORDER BY id)) AS fingerprint
FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01';

Expected output: A healthy pair returns identical rows and fingerprint values on both servers.

2. Run the query on both servers and fail the job when the fingerprints differ.

const [a, b] = await Promise.all([query(primary, SQL), query(replica, SQL)]);
if (a.fingerprint !== b.fingerprint) {
  console.error(`drift in orders: rows ${a.rows} vs ${b.rows}`);
  process.exitCode = 1;
}

Expected output: A mismatch names the table and window and makes the scheduled job exit non-zero.

3. Bucket the table so a mismatch points at a small slice instead of the whole month.

SELECT (id % 16) AS bucket, count(*) AS rows,
       md5(string_agg(id::text || ':' || total_cents::text, ',' ORDER BY id)) AS fingerprint
FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01'
GROUP BY 1 ORDER BY 1;

Expected output: Fifteen buckets match and one differs; only that bucket needs a row-level comparison.

4. Wait for the replica to replay the primary's LSN so lag is not misreported as drift.

-- on the primary
SELECT pg_current_wal_lsn() AS lsn;
-- on the replica, wait until this is true
SELECT pg_last_wal_replay_lsn() >= '0/16B6C50'::pg_lsn AS ready;

Expected output: The check starts only after ready returns true, so in-flight replication does not create false alarms.

5. Prove the check detects drift by changing one row on the primary in a test environment.

psql -h primary -d appdb -c "UPDATE orders SET total_cents = total_cents + 1 WHERE id = 42;" && node scripts/reconcile-orders.mjs; echo exit=$?

Expected output: drift in orders: rows 12000 vs 12000, exit=1, and the runbook points to the divergent bucket.

Troubleshooting

Problem

The fingerprint differs but the rows look identical

Solution

Text representation, collation, or time zone can differ. Cast the same columns the same way and compare a closed window with no in-flight writes.

Problem

The check is slow and competes with production traffic

Solution

Limit the window, bucket the query, set statement_timeout, and run off-peak against the replica rather than the primary.

Problem

Drift is reported on every run

Solution

You are comparing an open window or ignoring replication lag. Close the window in the past and gate the start on the LSN check.

Limitations

Fingerprint comparison detects that rows differ, not which change caused it.
Only the columns included in the hash are validated; a change elsewhere is invisible.
Full scans are expensive on large tables, so bucketing or sampling trades coverage for cost.

Silent divergence is worse than an outage

An outage is visible. A replica that is missing one row, or a primary that received a manual fix nobody replayed, is invisible until a report or a customer notices. By then the divergence is weeks old and the cause is buried. Reconciliation makes divergence a scheduled, testable signal instead of an incident discovery.

Fingerprint a closed window

count(*) alone catches missing rows but not modified ones. Pair it with a hash over the ordered rows: md5(string_agg(id::text || ':' || total_cents::text, ',' ORDER BY id)). The ordering makes the hash deterministic, and the explicit column list documents what the check covers. Always use a window that ended in the past, so writes in flight cannot make the two sides disagree legitimately.

SELECT count(*) AS rows,
       md5(string_agg(id::text || ':' || total_cents::text, ',' ORDER BY id)) AS fingerprint
FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01';

Handle lag before comparing

A replica that is a few seconds behind is not corrupt. Capture pg_current_wal_lsn() on the primary at the start of the run, then wait on the replica until pg_last_wal_replay_lsn() is at least that value. Without this barrier, every run during heavy write traffic reports false drift and the alert is muted within a week.

Bucket before you page

A full-table hash is expensive and gives a useless binary answer. Group by id % 16 or by day, compare buckets, and only drill into the one that differs. The job can then report “bucket 7 of orders, October” and a responder has a small, actionable target. Keep a per-bucket statement timeout so a runaway scan cannot affect users.

Verify the detector

A reconciliation job that has never caught anything may be broken. In a test environment, change a single row on the primary and confirm the job fails with the expected table and window. Then restore the row and confirm it passes. Review the alert runbook at the same time; a detector without a next step still costs an hour of incident time.