A strong default when relationships and correctness matter#
Payments, reservations, accounts, and commerce often change related records together. PostgreSQL offers tables, constraints, joins, indexes, and transactions in one coherent model. Start here when access patterns may evolve and correctness matters; do not flee to NoSQL merely because traffic can grow.
Transactions protect product invariants#
Reserve inventory and create an order together
BEGIN
verify inventory is available
decrement inventory
insert order + payment-attempt record
COMMITThe transaction boundary should protect a named invariant such as “confirmed orders never exceed inventory.” Isolation choice and contention behavior still matter; review transactions and concurrency rather than memorizing SQL syntax.
Indexes and MVCC support useful concurrency#
B-tree indexes are a common default access path. Composite column order follows query shape; partial indexes cover selected rows; unique indexes or constraints protect uniqueness. Every index consumes storage and write work, so link the canonical index lesson before proposing more.
PostgreSQL uses MVCC so statements observe row versions according to transaction and isolation behavior. Readers do not simply block every writer. This improves concurrency but does not eliminate write conflicts, long transactions, cleanup, or the need to select isolation from invariants.
Primary and standbys separate write authority from read capacity#
Streaming replication is asynchronous by default, so read replicas may lag and failover may lose recently committed-but-unreplicated changes. Synchronous configurations can wait for selected standby feedback, increasing durability confidence and write latency. PostgreSQL provides replication mechanisms; transparent automatic failover depends on the deployment and orchestration around it.
Scale through the least complex effective step#
indexes and query fixes
→ vertical capacity
→ caching
→ read replicas
→ table partitioning
→ application sharding only when justifiedEach step addresses a different bottleneck. Caching does not improve write contention; replicas do not scale the primary write path; partitioning a table is not automatically distributed sharding.
When it is - and is not - the best fit#
| Good fit | A specialized system may fit better |
|---|---|
| Related rows, constraints, joins, rich transactions, evolving queries | Enormous predictable key-only workload designed for a distributed KV service |
| Primary authoritative business data | Large object bytes, retained event streaming, or dedicated relevance search at scale |
| Structured and selected JSON use | A requirement whose operating model conflicts with one relational primary |
PostgreSQL can support many of the right-hand workloads at some scale. The question is operational and access-pattern fit, not whether a feature exists.
- Jumping to NoSQL before testing indexes, queries, and capacity.
- Calling read replicas immediately consistent.
- Assuming replication includes automatic failover.
- Adding indexes without accounting for write cost.
- Confusing table partitioning with independent database shards.
Use these prompts to extend the design. The useful answer is a reasoned change, not a memorized sentence.
- Which invariant requires a transaction?
- What query does each index serve?
- Can a replica return stale data to this user?
- What happens if the primary fails before replication catches up?
- At what measured bottleneck would sharding become justified?
Related practice problems
Use the manifest-backed prompts below to apply this lesson in a complete design.
- PostgreSQL MVCC introductionPostgreSQL Documentation
- PostgreSQL indexesPostgreSQL Documentation
- PostgreSQL warm standby and streaming replicationPostgreSQL Documentation