The design review goes well. The team presents a single table, five Global Secondary Indexes, and every access pattern named and justified. Fourteen months later, a financial authority asks for every transaction on a set of instruments across eighteen months, including cancelled and amended trades. The design document has no answer, because the question did not exist when it was written. The PostgreSQL team writes a query against a read replica and produces the file in twenty minutes. The DynamoDB team triggers an export pipeline to object storage, spins up a query engine, and waits for the data to become available.

Architectural slides for new clearing platforms or ledgers often feature a key-value store icon. The rationale is infinite scale and zero maintenance. These are true statements, but they answer the wrong questions. A core ledger processing every equity trade for a mid-sized pension fund might peak at a few hundred transactions per second. A single modern relational database handles that without strain. The database rarely breaks under the weight of throughput in institutional finance. It breaks under the weight of changing questions.

Query volatility versus data velocity

DynamoDB requires you to enumerate your access patterns at design time. A partition key is a business decision in a technical costume; you cannot alter it in place. If the key you chose turns out to be wrong, you do not migrate the table. You build a new one, dual-write, backfill, and cut over. PostgreSQL lets you add a column or an index on a Tuesday.

PostgreSQL absorbs flexibility and charges you in operations. DynamoDB enforces constraint and charges you in design. Both invoices are real; they arrive on different schedules. When a business requests three new ways to filter a settlement queue, the PostgreSQL team adds three indexes. The DynamoDB team creates three new Global Secondary Indexes, pays to store and write the same data four times, and spends weeks writing and verifying the backfill script.

The precision illusion

DynamoDB will store 38 significant digits. Your application runtime will hand you fifteen. Nothing between the two will tell you.

The database’s number type holds the precision flawlessly. The wire format is a string. The low-level SDKs expose that string, but the document and resource layers in several languages convert it to the language’s native number type, typically an IEEE 754 double. A microservice quietly rounds off the sixteenth decimal place. No exception is thrown. The break surfaces three weeks later in reconciliation, when a fraction of a cent per unit drifts across a position large enough to matter.

PostgreSQL forces strict, native arbitrary-precision types from the disk through the wire protocol to the client. The database did not round the number; the trip out of it did.

State, time, and multi-row invariants

Asset management runs on effective dates. A fee schedule, a tax domicile, or a compliance rule applies from one specific timestamp to another. Two position records for the same account cannot have overlapping validity ranges.

PostgreSQL handles this natively with exclusion constraints and range types, physically preventing the system from inserting overlapping records. DynamoDB has no equivalent. The application must coordinate through a sentinel item, and that sentinel becomes the hottest key in the table the moment one account gets busy.

Double-entry accounting means at least two rows, atomically. Effective-dated positions mean no two ranges may overlap. Both are rules the database can enforce in PostgreSQL and the application must enforce in DynamoDB. DynamoDB transactions cover up to 100 items and 4 MB. There are no range locks and no exclusion constraints. It is ACID inside a single request, not ACID across your domain.

The economics of the marginal query

In PostgreSQL, the ten-thousandth query costs nothing extra at the margin because you already paid for the CPU and I/O. In DynamoDB, it costs exactly what the first one did.

On-demand DynamoDB is materially more expensive per request than provisioned capacity at steady state, and free when idle. PostgreSQL charges a fixed instance cost. Until you hit the ceiling, ad hoc queries are free. Then one query is very expensive.

The ratio that matters is peak-to-median. Above roughly 10:1 with long idle periods, pay-per-request wins. Below roughly 3:1 and steady, provisioned PostgreSQL usually wins. When you add the cost of the export pipeline you built for reporting, and the engineer who runs it, the gap narrows or inverts.

What to check before signing

  1. Name the queries. Name the ten queries you know about today and the three you are afraid of. If you cannot name the three, lean toward PostgreSQL.
  2. Find the invariants. If business rules span more than one item and need to hold atomically, use PostgreSQL.
  3. Measure schema volatility. If the schema will undergo more than a couple of expected changes, use PostgreSQL.
  4. Measure peak-to-median write ratio. Above 10:1 favors pay-per-request DynamoDB; below 3:1 favors provisioned PostgreSQL.
  5. Check item sizes. Above 100 KB is a warning; above 400 KB is a blocker for DynamoDB.
  6. Assess team structure. Single-table design concentrates the data model in one or two heads. If you have a generalist team with regular turnover, a legible relational schema is more survivable.
  7. Check portability. If data residency or on-premises operation is a requirement, PostgreSQL runs everywhere. DynamoDB’s API and storage format are proprietary.

Where each fits

DynamoDB is clearly right for idempotency keys, deduplication tables, session state, rate-limit counters, device tokens, feature flags, event ingestion with a known partition scheme, caching layers, and short-TTL data.

PostgreSQL is clearly right for ledgers, positions, reference data with heterogeneous attributes, corporate actions, anything with ad hoc reporting, anything with multi-row invariants, and anything an auditor will ask an unplanned question about.

The realistic answer is often both. DynamoDB at the ingest edge; PostgreSQL as the system of record. The seam is an outbox, a queue, or a change data capture stream, and the seam is where the bugs live.

The ability to add an index later is usually worth more than the ability to avoid running a database server today.