A payments table that polices its own rules
A unique idempotency key turns a retry into an error, not a second debit. CHECK stops negative amounts despite app bugs.
Rows and columns only?
Bad: A store per data shape
Documents, geo and vectors each need their own database.
Good: JSONB plus extensions
GIN-indexed JSONB, PostGIS “within 3 km”, pgvector, pg_trgm. Daily filters still deserve columns.
State the guarantees precisely
- OK only after the WAL is fsynced
- MVCC: readers and writers never block each other
- Read Committed default; Serializable means retry on
40001 - Replicas lag, typically under a second
3k→40
connections, pooled by PgBouncer
Each connection is a whole process. Little’s law: 3,000 transactions/s × 5 ms = 15 busy at once, so a pool of 40 covers bursts.
All writes hit one primary. So scaling is a ladder.
Replicas scale reads; partitions tame big tables. Only sharding, with Citus or by key, adds write capacity.
A two-second ALTER TABLE becomes an outage. Why?
Why
It waits for an ACCESS EXCLUSIVE lock behind one long query, and every new query queues behind it. Set a short lock_timeout and retry.
Default to Postgres, except when…
- Writes outgrow one box, no shard key → distributed SQL
- One key, enormous scale → DynamoDB or Cassandra
- Relevance search → Elasticsearch, fed by CDC
- Billions of rows of analytics → columnar warehouse
Remember
Postgres holds the truth when invariants span rows: durable commits, conditional UPDATEs, replicas for reads, partitions for size, Citus by account for writes. Key lookups at huge scale, search and analytics live beside it.