swbanga.com
adr-001Supersededby Super Washington Banga

PostgreSQL over SQL Server for Ledger-Core Immutability Engine

  • ledger-core
  • postgresql
  • database
  • immutability
← All ADRs

Context

Ledger-Core needs a durable, append-only store for the double-entry ledger with transactional guarantees, optimistic concurrency control, and audit replay. SQL Server was already licensed in the org, but the immutability engine runs on Linux containers in Azure Container Apps and must support fine-grained access to the write-ahead log for reconciliation tooling.

Decision

Adopt PostgreSQL 16 as the Ledger-Core immutable store. Rows are never updated or deleted after settlement; mutations append versioned records, and the write path is wrapped in serializable transactions so reconciliation can replay the ledger from a single snapshot.

Consequences — Positive

  • Append-only patterns map directly to native MVCC and WAL-based replication
  • Fully open-source and license-free inside container deployments
  • JSONB allows schema-flexible audit metadata without migrations

Consequences — Negative

  • No built-in temporal query syntax; replay logic must be application-owned
  • SQL Server migration tooling for legacy ledgers must be custom-written
  • Team familiarity skews toward SQL Server; ramp-up cost on PG tuning

Why not SQL Server

SQL Server is a strong operational database, but the Ledger-Core constraints worked against it: the engine must run on Linux containers without a licensing surface, the append-only write pattern maps poorly to row-level locking and page-fill habits, and the reconciliation pipeline needs raw visibility into transaction and WAL semantics. The legacy-license argument lost to the container-first deployment posture.

The immutable write path

Rows are versioned, never overwritten. A check constraint guards the version sequence so a concurrent writer cannot silently clobber a settled entry:

sql
CREATE TABLE ledger_entries (
  id            BIGSERIAL PRIMARY KEY,
  version       INT NOT NULL CHECK (version > 0),
  account_id    UUID NOT NULL,
  amount        NUMERIC(19, 4) NOT NULL,
  settled_at    TIMESTAMPTZ,
  audit_json    JSONB,
  UNIQUE (account_id, version)
);

Replay and audit

Because the store is append-only, the auditor's view is a pure function of the WAL and the versioned table — no temporal extensions, no triggers, no shadow tables. The cost is that all replay logic is application-owned: the serializable-transaction write path is the sole writer, and anything that bypasses it breaks the audit chain.

Related Case Studies