ADR: Aurora vs DynamoDB for a Double-Entry Ledger in Core Banking

A double-entry ledger is, by definition, a system of invariants. Every financial transaction produces exactly two entries — debit and credit — whose values must be equal and opposite. The algebraic sum of all balances must be zero. This property is non-negotiable: any violation, even temporary, represents an accounting error with serious regulatory and operational consequences.

The system in question must guarantee:

1. Atomicity — debit and credit are written together or neither is written.
2. Isolation — balance reads during an in-flight transaction do not return intermediate states.
3. Immutability — written entries cannot be modified; corrections are made by reversal (new entry).
4. Auditability — every operation must have a traceable trail with timestamp, operator identifier, and reference to the originating business event.

Beyond these correctness invariants, there are operational forces: entry volume grows with the customer base, read patterns are heterogeneous (point-in-time balance, paginated statement, period-based reconciliation reports), and cost must be justifiable in a financial product with controlled margins.

It was in this scenario that the team faced the classic choice: a relational database with native ACID versus a massively scalable NoSQL database. The pressure to adopt DynamoDB came from engineering teams that operated other platform services with it successfully. The pressure to keep PostgreSQL came from product and compliance teams, who knew the relational model well and distrusted eventual consistency at any layer of the financial stack.

The most common argument for DynamoDB in financial systems is throughput: the ability to scale to millions of operations per second without degradation. It is a valid argument for very high-volume payment systems — card processors, for example. But for a mid-sized institution's core banking ledger, that argument is, in practice, a red herring.

The real problem is not write throughput. It is the diversity of read patterns.

A ledger needs to answer at least four fundamentally different query classes:

• Current account balance — point-in-time read, latency-critical, high frequency.
• Paginated statement — range scan by account_id + timestamp, ordered, with optional filters by entry type.
• Daily reconciliation — aggregation of all entries in a period, grouped by account and type, potentially crossing millions of rows.
• Specific transaction audit — lookup by transaction_id, returning both sides of the entry and the associated business event.

In DynamoDB, each of these patterns requires a different key design. The single-access model via partition key + sort key means you need multiple Global Secondary Indexes (GSIs) or data duplication to cover all patterns. GSIs in DynamoDB have eventual consistency by default — acceptable for many cases, but not for balance reads in a system that must guarantee the balance reflects exactly the state after the last confirmed transaction.

You can work around this with strongly consistent reads on the primary index and careful modeling. But the design and operational cost of that solution begins to exceed the cost of simply using a relational database with well-designed indexes. The complexity does not disappear — it migrates from the database into application code and into modeling conventions that must be maintained by the entire team.

Aurora PostgreSQL, on the other hand, treats all these patterns as standard SQL queries. The query optimizer handles execution complexity. The team maintains a single mental model — tables, indexes, transactions — rather than multiple access patterns encoded in composite keys.

Neither database offers record immutability as a native primitive in the sense that financial regulators require. Both allow deletion and update by default. The difference lies in where and how you implement the constraint.

In DynamoDB, the cleanest approach is via IAM policy: you create an application role that has PutItem permission but not UpdateItem or DeleteItem. This is elegant and hard to accidentally bypass. The problem is that it does not solve the audit question of who wrote what and when with database semantics — you depend on CloudTrail for that, and CloudTrail has latency and does not capture item content.

In Aurora PostgreSQL, the approach is different: you implement a BEFORE UPDATE OR DELETE trigger that rejects any operation on ledger tables (except by a specific system role for audited reversals). PostgreSQL's WAL (Write-Ahead Log), combined with Aurora's continuous backup, creates a low-level audit trail that captures every change with microsecond precision. For high-level auditing, you add an audit_log table populated by trigger, or use an extension like pgaudit.

The combination of immutability trigger + pgaudit + Aurora Backtrack (which allows rewinding the database to any point in the last 72 hours) creates an auditability layer that is difficult to replicate in DynamoDB without additional infrastructure.

There is a pattern I consider superior for both cases: event sourcing with projection. The primary ledger is a table of immutable events (ledger_events) where each row represents an accounting entry and is never modified. Balances are projections computed over those events — either in real time via query, or materialized in a balance table updated by trigger. This pattern works well in Aurora and would be the only safe way to use DynamoDB for this case. But if you are implementing event sourcing anyway, Aurora offers more tools to maintain projection consistency within the same transaction.