Data and consistency | System Design: make decisions, size, and recover systems

Concept and mechanism

The data model should represent business invariants and access patterns. Before choosing storage, identify keys, relationships, transactions, search, retention, and evolution. A read replica can reduce primary load but does not guarantee that a just-committed write is already visible. In the documented PostgreSQL18 example, streaming replication is asynchronous by default; lag can become loss of committed writes if the primary fails before the replica receives them. Synchronous modes have specific waiting conditions and availability costs. The decision depends on the operation and accepted risk, not merely the presence of a second machine.

Guided application

In a fictional scenario, approval reads a limit and updates a reservation while another operation changes the same state. Define which invariants must survive concurrency. In PostgreSQL18 Read Committed, two queries in one transaction can observe different commits because the snapshot is per statement. Stronger isolation may require retrying the whole transaction after serialization failure; retrying only the last write can reuse stale assumptions. For confirmation shown to a user, choose a read with required freshness or communicate pending state. Document the guarantee and boundary: local isolation does not create an automatic transaction across every involved system.

IN PRACTICE

Replication, isolation, and backup address different problems and need their own criteria.

Common pitfalls

Replica as always-current reading; local commit as global completion; partial retry with old decisions.

Related topics: Requirements and architecture decisions · Capacity and latency · Caching, partitions, and queues

Take this idea with you

Choose consistency from the effect that needs protection.

Create account

Reference: PostgreSQL transaction isolation · System design patterns; PostgreSQL18 scoped examples; primary guidance consulted 2026-09-30