PostgreSQL, DynamoDB and Cassandra
Relational, key-value and wide-column databases optimize different query and consistency models; compare them using concrete access patterns.
PostgreSQL, DynamoDB and Cassandra
Relational, key-value and wide-column databases optimize different query and consistency models; compare them using concrete access patterns. PostgreSQL offers relational constraints, joins, transactions, MVCC and flexible indexed queries. DynamoDB uses partition/sort keys and access patterns designed up front; GSIs create alternate query paths. Cassandra distributes partitioned rows and favors query-first denormalized tables with tunable consistency.
Mechanism and worked example
An order system with multi-row invariants and reporting may fit PostgreSQL. High-volume user activity by user_id and time may fit DynamoDB. A globally distributed time-series workload with known partition queries may fit Cassandra if hot partitions, tombstones and repair are managed.
| Question | PostgreSQL | DynamoDB | Cassandra |
|---|---|---|---|
| Transactions | Rich relational | ACID transaction within documented limits | Partition-scoped lightweight transactions |
| Queries | Flexible SQL/indexes | Key patterns and GSIs | Partition-key-first |
| Scaling | Vertical, replicas, partitions | Managed partitioned throughput | Distributed ring/replication |
| Operations | Vacuum, locks, WAL | Hot keys, capacity, indexes | Compaction, tombstones, repair |
Failure modes and operations
PostgreSQL can hit connection, lock, vacuum or write scaling limits. DynamoDB can throttle hot keys or amplify GSI writes. Cassandra can suffer tombstone-heavy reads, compaction debt, repair lag and queries missing partition keys.
Trade-offs and design boundary
PostgreSQL reduces application-side coordination for relational invariants but needs careful connection/write scaling. DynamoDB offers managed partitioned throughput but requires access-pattern discipline. Cassandra offers distributed write scale with consistency and operations complexity.
Interview transfer exercise
For a wallet transfer, identify the atomic debit/credit invariant and choose a transaction boundary. A key-value store can support it only with explicit conditional/transaction semantics and a clear cross-account boundary.
Read the diagram
List invariants and query shapes → Map keys, indexes and transaction boundaries → Estimate skew, throughput and retention → Choose engine and recovery operations