SQL to NoSQL Sync Validation › Validating DynamoDB Against PostgreSQL at Scale

# Validating DynamoDB Against PostgreSQL at Scale

This page answers a concrete migration question: how do you prove that billions of rows migrated from PostgreSQL into DynamoDB arrived intact, when the two engines have incompatible scan models, type systems, and consistency semantics? It sits under the SQL to NoSQL sync validation reference and specialises it for the PostgreSQL-to-DynamoDB pair at a scale where you cannot load either side into memory and cannot afford a full table scan per validation. The goal is to detect silent truncation — the rows that never made it — without the validation itself becoming the bottleneck.

# Problem Framing

You migrated a 10-billion-row events table from PostgreSQL into DynamoDB and closed the ticket when the item count matched. Then an analyst finds a customer whose events are missing. Count parity proved cardinality, not fidelity: a batch could have been dropped and an equal batch double-written, netting to the same total. Validating this at scale means reconciling by key and value, not count, across two engines that scan completely differently — PostgreSQL by an indexed key range, DynamoDB by parallel segment scans over a partition key. The design aligns the two by hashing each side into per-key-range digests that can be compared without co-locating the data, so memory tracks the range size, not the table.

# Implementation

The validator drives PostgreSQL with keyset pagination and DynamoDB with parallel segment scans, reduces each side to a canonical per-key hash, and diffs the hash maps per key range so truncation surfaces as missing keys.

import hashlib
import json
import logging
from dataclasses import dataclass
from typing import Dict, Iterator, List

logging.basicConfig(
    format="%(asctime)s | %(levelname)s | %(name)s | %(message)s", level=logging.INFO
)
logger = logging.getLogger("recon.arch.dynamo_pg")


def canonical_value_hash(item: Dict) -> str:
    """Hash a row/item independent of engine type spelling: sorted keys, string-coerced."""
    normalized = {k: (str(v) if v is not None else None) for k, v in item.items()}
    payload = json.dumps(normalized, sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()


@dataclass(frozen=True)
class RangeDiff:
    key: str
    kind: str            # "missing_in_dynamo", "extra_in_dynamo", "value_diverged"


def index_pg_range(pg_rows: Iterator[Dict], key: str) -> Dict[str, str]:
    """Keyset-paginated PostgreSQL rows -> key -> canonical hash (bounded to one range)."""
    return {str(r[key]): canonical_value_hash(r) for r in pg_rows}


def index_dynamo_segment(items: Iterator[Dict], key: str) -> Dict[str, str]:
    """One DynamoDB parallel-scan segment -> key -> canonical hash."""
    return {str(i[key]): canonical_value_hash(i) for i in items}


def diff_range(pg: Dict[str, str], dynamo: Dict[str, str]) -> List[RangeDiff]:
    """Set + value diff for one aligned key range; memory is O(range), not O(table)."""
    diffs: List[RangeDiff] = []
    for k in pg.keys() - dynamo.keys():
        diffs.append(RangeDiff(k, "missing_in_dynamo"))
    for k in dynamo.keys() - pg.keys():
        diffs.append(RangeDiff(k, "extra_in_dynamo"))
    for k in pg.keys() & dynamo.keys():
        if pg[k] != dynamo[k]:
            diffs.append(RangeDiff(k, "value_diverged"))
    if diffs:
        logger.warning("range diff: %d discrepancy(ies)", len(diffs))
    return diffs

# Scan Strategy Trade-Off

The two engines’ scan models force a choice about how to align them.

# Key Implementation Notes

• Align scan models by key range, not by scan order. PostgreSQL scans by indexed key, DynamoDB by partition segment; reduce both to a key -> hash map over the same key range so the comparison is order-independent and each range is bounded.
• Hash canonically across type systems. DynamoDB’s N and S types and PostgreSQL’s typed columns serialize differently; coerce to a canonical form before hashing — the discipline from modeling type coercion across engines — so a 42 and a "42" do not read as divergence when they are logically equal.
• Pin a read time to tame consistency. A live DynamoDB table changes under a scan; validate against a point-in-time export or a pinned snapshot so the comparison is against a stable target, not a moving one.
• Parallelise by segment, bound by range. DynamoDB parallel scans and PostgreSQL keyset pagination both shard cleanly, so run one worker per aligned range; memory stays O(range) and throughput scales with worker count, echoing parallel row extraction techniques.

# Verification

Assert that a missing key, an extra key, and a value divergence are detected, and that type-spelling differences do not create false positives.

pg = {"1": canonical_value_hash({"id": 1, "amt": 10}),
      "2": canonical_value_hash({"id": 2, "amt": 20})}
dynamo = {"1": canonical_value_hash({"id": "1", "amt": "10"}),   # string-typed, same value
          "2": canonical_value_hash({"id": 2, "amt": 99}),       # diverged
          "3": canonical_value_hash({"id": 3, "amt": 30})}       # extra
kinds = {(d.key, d.kind) for d in diff_range(pg, dynamo)}
assert ("2", "value_diverged") in kinds
assert ("3", "extra_in_dynamo") in kinds
assert ("1", "value_diverged") not in kinds                     # type spelling ignored
logger.info("dynamodb vs postgresql validation verified")

# Operational Considerations

Drive DynamoDB validation from a point-in-time export to S3 rather than scanning the live table, which avoids consuming production read capacity and gives a stable snapshot, and reconcile it against a PostgreSQL logical export pinned to the same instant. Size the number of parallel segments to the DynamoDB table’s partition count and the PostgreSQL ranges to the index, so both sides shard evenly, and expose ranges_validated_total, missing_in_dynamo_total, and peak_range_bytes so an operator can confirm coverage and bounded memory. Route confirmed discrepancies through discrepancy routing and remediation and record the pinned read time and canonical hash config with each run, so a “the migration was complete” claim is reproducible and defensible under audit.

# Related

• SQL to NoSQL sync validation — the parent reference whose relational-to-document parity this specialises.
• How to validate SQL vs NoSQL data parity — the general method this scales to billions of rows.
• Modeling type coercion across engines — the canonical typing the value hash relies on.
• Parallel row extraction techniques — the sharded scan pattern both sides use.