DynamoDB vs Postgres for Ledgers

Prerequisite: The Ledger, Distributed Systems

Your Postgres ledger handles 2,000 writes per second without breaking a sweat. Then your settlement account starts receiving 15,000 entries per second, and every write serializes on the same row lock. P99 latency jumps from 12ms to 3 seconds. You add read replicas, connection pooling, partitioning — and the hot-account bottleneck remains because the contention is on a single row in a single table on a single primary. The database is not slow. The concurrency model is wrong for this workload.

DynamoDB does not have row locks. It does not have transactions in the Postgres sense. It does not have SQL. What it has is single-digit-millisecond reads at any scale, conditional writes that function as compare-and-swap operations, and horizontal scaling that requires no capacity planning beyond setting a throughput target. Both databases can build a correct ledger. The architecture required to achieve correctness looks completely different.

How Each Prevents Double-Spend

The double-spend problem is the ledger’s hardest constraint: a single account must never be debited below its available balance, even under concurrent writes. Postgres and DynamoDB solve this with fundamentally different mechanisms.

Postgres uses serializable transactions. You read the balance, check the constraint, insert the entry, and update the balance — all inside a single transaction boundary. If two transactions attempt to debit the same account simultaneously, Postgres detects the serialization conflict and aborts one.

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;
-- Application checks: balance >= debit_amount

INSERT INTO entries (account_id, amount, ...) VALUES ($1, -$2, ...);
UPDATE accounts SET balance = balance - $2 WHERE id = $1;

COMMIT;

The FOR UPDATE acquires an exclusive row lock. The second transaction blocks until the first commits or rolls back. Correctness is guaranteed by the database. The cost is serialization — concurrent writes to the same account execute one at a time.

DynamoDB uses conditional writes. You read the current balance, compute the new balance in application code, and issue a PutItem or UpdateItem with a condition expression that asserts the balance has not changed since you read it. If another write landed between your read and your write, the condition fails and DynamoDB returns a ConditionalCheckFailedException. Your application retries.

response = table.update_item(
    Key={"account_id": account_id},
    UpdateExpression="SET balance = :new_balance",
    ConditionExpression="balance = :expected_balance",
    ExpressionAttributeValues={
        ":new_balance": current_balance - debit_amount,
        ":expected_balance": current_balance,
    },
)

The conditional write is an optimistic concurrency control pattern. No lock is held between the read and the write. Under low contention, this is faster than Postgres — no lock acquisition, no lock wait queue. Under high contention, retries compound. If 50 concurrent writes target the same account, 49 fail on the first attempt. The retry storm becomes the bottleneck.

Mechanism	Contention model	Failure mode	Where correctness lives
Postgres SERIALIZABLE	Pessimistic — lock, then write	Lock wait queue grows	Database
DynamoDB conditional write	Optimistic — write, then check	Retry storm under contention	Application code

DynamoDB conditional writes are per-item. There is no cross-item atomic operation equivalent to a Postgres transaction that inserts an entry and updates a balance in a single commit. DynamoDB Transactions exist but are limited to 100 items and cost 2x the write capacity units.

Deriving Balances: SUM vs Pre-Computed Deltas

In Postgres, you can always answer “what is the balance?” with a query.

SELECT SUM(amount) FROM entries WHERE account_id = $1;

This is correct by construction — it reflects every entry ever posted. With a covering index on (account_id, amount), Postgres reads amounts directly from the index. For an account with 10,000 entries, the query returns in under 2ms. For an account with 5 million entries, it returns in under 1ms with the covering index, but the scan still touches every index entry. See Balance Derivation at Scale for the full performance progression.

DynamoDB has no SUM. There is no query planner, no aggregate functions, no way to ask the database to compute a derived value across items. If you store each ledger entry as a separate item, answering “what is the balance?” requires your application to scan every entry for that account, page through the results, and compute the sum client-side. A scan of 10,000 items at 1MB per page takes multiple round trips and hundreds of milliseconds.

The only way to get O(1) balance reads from DynamoDB is to pre-compute. Every write must also update a balance item — either in the same DynamoDB Transaction or asynchronously via DynamoDB Streams. The balance item is a cache that your application is responsible for keeping correct.

Operation	Postgres	DynamoDB
Current balance	SUM(amount) or cached column	Pre-computed item (application-managed)
Historical balance at time T	SUM(amount) WHERE created_at <= T	Sum of pre-computed temporal deltas
Balance across date range	Single SQL query	Application-side aggregation of delta items
Ad-hoc aggregation	SQL	Export to analytics store

The consequence is write amplification. Fragment pre-computes balance deltas at four temporal granularities (hour, day, month, year) across account hierarchies. A single ledger entry triggers approximately 80 balance updates. That 80x write amplification is the cost of making reads fast on a database that cannot derive values at query time.

Ad-Hoc SQL vs Application-Level Consistency

An analyst on your team needs to answer: “How much money flowed through settlement accounts last Tuesday between 2pm and 4pm?” In Postgres, that is a query.

SELECT account_id, SUM(amount)
FROM entries
WHERE account_type = 'settlement'
  AND booking_time BETWEEN '2024-03-12 14:00' AND '2024-03-12 16:00'
GROUP BY account_id;

The analyst writes the query, runs it against a read replica, and has the answer in seconds. No engineering involvement. No data pipeline. No waiting for a nightly export.

In DynamoDB, that question requires a full table scan filtered by attributes that are not part of the key schema — or a Global Secondary Index that was designed in advance to support exactly this query pattern. If the GSI does not exist, the analyst cannot answer the question without engineering work. If the data has been exported to a warehouse, the analyst queries the warehouse — but the warehouse is hours behind, not seconds.

This is the fundamental operational tradeoff. Postgres gives you a general-purpose query engine that handles questions you did not anticipate. DynamoDB gives you predictable performance for the questions you designed for and no answers for the ones you did not.

See The Ledger. Auditors ask unpredictable questions. A ledger that cannot answer ad-hoc queries pushes audit response time from hours to weeks, because every new question requires engineering effort to extract and transform the data.

For regulated financial systems, this matters. Examiners from the OCC or state regulators do not submit query requests through your engineering backlog. They ask questions during an examination and expect answers within the examination window — typically days, not sprints. A Postgres ledger answers most examiner questions with SQL. A DynamoDB ledger answers them with a data pipeline that must already exist.

Schema Flexibility

Postgres schemas evolve with ALTER TABLE. Add a column, rename a field, add an index — the migration runs against the existing data, and every query immediately sees the new schema. A non-trivial migration on a table with 500 million rows takes time (hours for some operations without tools like pg_repack or pgroll), but the operation is well-understood and reversible.

DynamoDB’s key schema — partition key and sort key — is immutable after table creation. If your original key design was account_id (partition) and entry_id (sort), and you later need to query entries by booking_time, you add a Global Secondary Index. GSIs are eventually consistent by default, limited to 20 per table, and each one doubles the write cost for the attributes it projects. Changing the partition key itself requires creating a new table and migrating all data.

Schema change	Postgres	DynamoDB
Add a column	ALTER TABLE ADD COLUMN	Add attribute to items (schemaless — no migration needed)
Add an index	CREATE INDEX CONCURRENTLY	Create GSI (backfill is automatic but eventually consistent)
Change primary key structure	Possible with migration	New table + full data migration
Rename a field	ALTER TABLE RENAME COLUMN	Application-level: write both names during transition
Query on a new dimension	Ad-hoc WHERE clause (may be slow without index)	Requires GSI or full table scan

DynamoDB’s schemaless items are flexible — you can add any attribute to any item without a migration. But the access patterns are rigid. Your key design determines what you can query efficiently, and that decision is made at table creation. Getting the key design wrong is expensive to fix.

Write Contention at Scale

Postgres serializes writes to the same row through its lock manager. When 100 concurrent connections try to update the same account’s balance, 99 wait in a lock queue. Each holds a connection from the pool while waiting. If your connection pool has 100 connections and all 100 are stuck waiting on the same row, no other work gets done. The hot account starves the entire system.

Mitigation strategies exist — advisory locks, SKIP LOCKED queues, batching writes at the application layer — but they all add application complexity to work around a database-level serialization constraint. Postgres’s row-level locking is not a bug. It is the mechanism that provides serializable consistency. The cost appears when one account receives a disproportionate share of writes.

DynamoDB’s conditional writes do not hold locks, so a hot account does not block other accounts. But the conditional write retry loop has its own scaling problem. Under heavy contention, the probability of a successful write on the first attempt drops. With N concurrent writers, N-1 fail and retry. The retries create more contention, which creates more retries. Without exponential backoff and jitter, this feedback loop saturates your write capacity.

Fragment addresses this by splitting the write path. The transactional balance — the one that prevents double-spend — uses a conditional write on a single DynamoDB item. The temporal delta balances (hour/day/month/year) cascade asynchronously through DynamoDB Streams. The conditional write still contends under heavy concurrent access to the same account, but the cascade balances do not — they batch and coalesce updates, reducing write volume at each level.

Contention scenario	Postgres	DynamoDB
100 concurrent writes to one account	99 wait in lock queue	99 conditional check failures, retry with backoff
100 concurrent writes to 100 accounts	No contention	No contention
Hot account + cold accounts mixed	Hot account’s lock queue delays cold accounts sharing the connection pool	Hot account retries do not affect cold accounts

Fragment: A DynamoDB Ledger in Production

Fragment chose DynamoDB for its ledger and published detailed performance data. The architecture illustrates what a DynamoDB-native ledger requires — and what it costs.

The write path processes 14,578 entries per second. Each entry triggers a synchronous conditional write to update the transactional balance, followed by an asynchronous cascade through DynamoDB Streams → EventBridge → SQS that updates approximately 80 pre-computed delta balances. The transactional balance read returns in 33ms p95 with strong consistency. Historical balance reads — summing the relevant temporal deltas — return in approximately 130ms with eventual consistency and roughly 10 seconds of staleness at p95.

Fragment’s reasons for choosing DynamoDB over Postgres:

1. Predictable latency. DynamoDB’s single-digit-millisecond reads do not degrade as data volume grows. No query planner means no plan regressions. No vacuum means no periodic latency spikes.
2. Horizontal scale without sharding logic. DynamoDB partitions automatically by partition key. No application-level shard routing, no cross-shard queries, no rebalancing operations.
3. DynamoDB Streams for CDC. The change data capture mechanism that powers the async delta cascade is built into the database. No WAL decoding, no Debezium, no additional infrastructure.

What they gave up:

1. SQL. Every query pattern must be designed into the key schema or a GSI. Ad-hoc analysis requires exporting data to a warehouse.
2. Transactions. DynamoDB Transactions are limited to 100 items and 4MB, and cost 2x write capacity. The entry insert and balance update are separate operations coordinated by application logic, not a database transaction boundary.
3. 80x write amplification. Every entry generates 80 balance delta writes. At 14,578 entries per second, that is over 1.1 million balance writes per second. DynamoDB charges per write capacity unit.

Fragment’s 80x write amplification means their DynamoDB bill scales with entries × 80. At on-demand pricing ($1.25 per million write request units), 14,578 entries/sec × 80 = ~1.17M writes/sec = ~4.2B writes/hour, which is approximately $5,250/hour. Even with provisioned capacity, the cost is roughly $760/hour. The equivalent Postgres workload on an r6g.2xlarge RDS instance costs approximately $1.07/hour. The DynamoDB architecture trades dollars for latency predictability.

Choosing Your Database

The decision is not which database is better. It is which set of tradeoffs matches your constraints.

Dimension	Choose Postgres	Choose DynamoDB
Team expertise	SQL-fluent team, standard tooling	Team experienced with NoSQL, event-driven architectures
Query patterns	Ad-hoc queries, reporting, audit response	Known access patterns, pre-designed key schema
Regulatory environment	Examiners expect SQL-queryable records	Warehouse export pipeline already satisfies exam requirements
Write contention	Low-to-moderate (< 5K writes/sec to hottest account)	High, with tolerance for conditional write retries
Latency requirements	Sub-100ms acceptable, occasional variance tolerated	Single-digit-ms reads required, no variance tolerated
Operational complexity	Schema migrations, vacuum tuning, connection pool management	Key design lock-in, GSI management, write amplification accounting
Cost model	Predictable per-instance pricing	Per-request pricing scales linearly with write amplification

Most fintech companies start with Postgres because the SQL query capability is load-bearing for compliance, debugging, and analytics. DynamoDB becomes the right choice when Postgres’s concurrency model — row locks, connection pools, vacuum — becomes the bottleneck, and when the team is willing to move all consistency logic into the application layer.

The Hybrid Path

The highest-throughput production systems often use both. Postgres holds the system of record — the immutable append-only ledger that auditors query and examiners inspect. DynamoDB holds the hot-path balances — the pre-computed values that power real-time authorization decisions.

┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│  Application │────▶│   Postgres   │────▶│  DynamoDB    │
│              │     │  (entries)   │ CDC │  (balances)  │
│  Write entry │     │  System of   │────▶│  Hot-path    │
│  + balance   │     │  record      │     │  reads       │
└──────────────┘     └──────────────┘     └──────────────┘

The write path inserts the entry into Postgres. A change data capture pipeline (Debezium on the WAL, or a transactional outbox) pushes the entry to DynamoDB, which updates the pre-computed balance. The read path for “can this account spend right now?” hits DynamoDB — 5ms, no variance. The read path for “show me all entries for this account last quarter” hits Postgres — full SQL, covering indexes, ad-hoc filters.

The consistency gap between the two stores is the design parameter. CDC pipelines introduce latency — typically 100ms to 2 seconds depending on configuration. During that window, the DynamoDB balance is stale. If a debit authorization reads the DynamoDB balance before the CDC pipeline delivers the most recent credit, the authorization decision is based on an outdated balance.

Mitigation: route balance-sensitive writes (debits, holds, authorization checks) through the synchronous path that updates both stores in sequence. Route balance-insensitive writes (credits, settlement entries, fee postings) through the async CDC path. This is the same sync/async split that Modern Treasury uses — the caller declares which consistency guarantee the operation requires.

The hybrid model gives you Postgres’s query power for compliance and DynamoDB’s latency guarantees for authorization. The cost is operating two databases, maintaining a CDC pipeline, and designing every write path to declare its consistency requirement. For systems processing more than 10,000 entries per second with hot-account contention, that operational cost is lower than the engineering cost of making either database do both jobs alone.