Overview
Use Postgres by default. DynamoDB solves one problem well: single-key reads and writes at any scale with predictable single-digit-millisecond latency and no servers to run. The moment an access pattern needs a second lookup (by email and by user ID), DynamoDB needs a Global Secondary Index (GSI) and the data model gets harder; Postgres serves dozens of query patterns with indexes and joins. Reach for DynamoDB only when the access pattern is proven key-value, the write scale outgrows a single Postgres primary, and you are already on AWS. See postgres for the Postgres rule set and aurora-vs-rds-postgres for choosing a managed Postgres on AWS.
When Postgres wins
Postgres is the right choice for nearly every OLTP application.
- Flexible access patterns: a B-tree index on
email, a partial index onstatus = 'active', and a GIN index ontagscan live on one table. DynamoDB needs each access pattern modeled up front as a key or GSI. See postgres-indexes. - Joins: one SQL query joins users, orders, line items, and products. DynamoDB needs denormalized copies or multiple round-trip gets.
- Transactions: cheap and the default in Postgres. DynamoDB’s
TransactWriteItemsworks but adds latency and caps at 100 items per request. - Ad-hoc queries: Postgres accepts any
SELECT. A DynamoDB Scan reads every item and bills for it. - Schema evolution:
ALTER TABLEplus a migration tool (see migrations). DynamoDB has no schema, so evolving the model means dual-writes and backfills in application code. - Full-text search:
tsvectorplus a GIN index covers most needs (see postgres-full-text-search); DynamoDB needs OpenSearch alongside it.
When DynamoDB wins
DynamoDB is the right choice in a narrow set of conditions.
- Session storage at very large scale: a TTL attribute expires items automatically without sharding work. Redis is cheaper and faster when the working set fits in memory; see redis-vs-memcached.
- IoT telemetry and event logs: append-only writes keyed by device ID plus timestamp, no joins, high write throughput that scales without intervention.
- Single-entity state such as game player records, where every read and write is one key at any concurrency.
- Serverless workloads with spiky traffic: on-demand capacity needs no provisioning, and the HTTP API needs no connection pool. Aurora Serverless v2 is the Postgres alternative.
- AWS-native pipelines: DynamoDB Streams feed Lambda, Kinesis, and EventBridge, so change data capture is built in.
Trade-offs at a glance
| Dimension | Postgres | DynamoDB |
|---|---|---|
| Query flexibility | Any SQL; ad-hoc indexes | Primary key + GSIs; no ad-hoc queries |
| Joins | Native | Denormalization or multiple gets |
| Write scaling | Vertical + read replicas | Horizontal, managed |
| Access pattern evolution | Add an index | Add a GSI; partition key cannot change |
| TTL / expiry | pg_cron job + index | Built-in TTL attribute |
| Cost model | Instance + storage | Per request or provisioned capacity + storage |
| Multi-region writes | Aurora Global Database: single writer | Global Tables: multi-writer |
Migration cost
Moving between the two is a data model redesign, not a data transfer.
- Postgres to DynamoDB: list every query the application runs, group them by access pattern, design partition keys and GSIs for each, and rewrite data access against the AWS SDK. Expect a multi-month project for any non-trivial application.
- DynamoDB to Postgres: export the table to S3 and load it with
COPY. The hard part is designing a relational schema from denormalized items and rewriting the queries. - Cheaper than migrating: keep DynamoDB for the high-throughput key path and add Postgres for the relational queries, fed by DynamoDB Streams.
Recommendation
- New SaaS, B2B, or B2C product: Postgres.
- Session storage for a high-traffic consumer app: DynamoDB with TTL, or Redis if sub-millisecond reads matter and the working set fits in memory.
- Existing DynamoDB table that now needs joins: add Postgres for the relational workload and keep DynamoDB for the key-value path.
- Serverless functions with no connection pool: DynamoDB, or Postgres behind a pooler such as PgBouncer or RDS Proxy.
- Multi-region active-active writes: DynamoDB Global Tables.