Technology · Relational database
PostgreSQL
The default relational database. Transactions and MVCC, indexes worth knowing, how far one node goes, and the ways to scale it.
11 min read
PostgreSQL is a safe default for most systems' primary data. It gives you transactions, constraints, flexible queries, JSON, full-text search and a huge ecosystem. In an interview, picking Postgres needs little justification; picking something else should come with a reason.
When to reach for it
- Data with relationships and invariants: users, orders, payments, inventory, bookings.
- You need transactions across several rows or tables.
- Query patterns will change, so you want ad hoc SQL and secondary indexes.
- Volume fits a primary plus replicas, or a modest number of shards. That covers most products.
How it works
- Every write goes to the write-ahead log (WAL) first, then to data pages. The WAL makes commits durable and drives replication.
- MVCC: an update writes a new row version. Readers see a consistent snapshot without blocking writers; VACUUM later removes dead versions.
- Default isolation is Read Committed. Repeatable Read gives snapshot isolation, and Serializable detects anomalies and aborts one transaction, which the app must retry.
- Indexes are separate structures pointing at rows. B-tree by default.
Indexes worth knowing
B-tree
- Equality, ranges, sorting
- Composite and covering (INCLUDE)
- Every index slows writes
Pick it when: The default for almost everything.
GIN
- Many values per row
- Slower to update
Pick it when: JSONB containment, arrays, full-text search.
GiST / SP-GiST
- Geometric and range types
- Specialized
Pick it when: PostGIS location queries, exclusion constraints on time ranges.
Partial
- Small, targeted
- Only matches queries with the same condition
Pick it when: Index only active rows: WHERE status = 'open'.
How far one node goes
- Simple indexed readsmore from replicas and caches
- tens of thousands per second
- Writesdepends on row size and indexes
- ~5,000–20,000 transactions per second
- Comfortable data sizelarger works, with care
- single-digit TB
- Connectionsuse a pooler like PgBouncer
- hundreds
Scaling it
- Tune first: right indexes, fix N+1 queries, connection pooling.
- Cache hot reads in Redis or at the CDN.
- Read replicas for read-heavy traffic, routing read-your-writes flows to the primary.
- Partition tables (declarative partitioning) by time or tenant to keep indexes small and drop old data cheaply.
- Shard across clusters by a key such as tenant id, with Citus or application-level routing, when one primary can't take the writes.
Where it bites
- Long transactions hold back VACUUM, bloating tables, and can hold locks that queue up other writers.
- Schema changes on big tables can lock them. Add columns without defaults that rewrite, build indexes
CONCURRENTLY, and backfill in batches. - Hot rows (one counter, one popular item) serialize writers on a row lock. See the contention pattern.
- Failover with async replicas can lose the last moments of writes.
Try it
Event Ticketing (Ticketmaster) HLD
Sell seats without double-booking, with Postgres at the core.
Learn the ideas
Practice it
URL Shortener HLDTurn long URLs into short links. Keep redirects fast when a link goes viral.E-commerce Checkout HLDCoordinate orders, stock and payment with idempotent steps and compensation.Online Schema Migration Platform HLDMigrate a live dataset through snapshot backfill, ordered change capture and fenced cutover.Event Ticketing (Ticketmaster) HLDSell seats for popular events without double-booking, even when millions arrive at once.
Related
Wide-column databaseCassandraA leaderless, wide-column database built for huge write volumes across many nodes and regions. Query-first data modeling, tunable consistency, and its sharp edges.Managed key-value storeDynamoDBA fully managed key-value and document database with predictable single-digit-millisecond latency at any scale. Partition and sort keys, single-table design, indexes, capacity and its limits.In-memory storeRedisAn in-memory data structure server used as a cache, rate limiter, leaderboard, lock service, queue and pub/sub hub. What it's good at, how it scales, and where it bites.Distributed SQLSpanner and CockroachDBGlobally distributed SQL databases providing serializable ACID transactions across multiple regions. Raft-replicated ranges, TrueTime vs hybrid logical clocks, and external consistency.Document storeMongoDBA document database storing flexible BSON records. When to embed vs reference, indexes, replica sets with elections, sharding mechanics, and write concerns.Graph databaseGraph DatabasesSpecialized storage engines optimized for highly connected data and recursive graph traversals. Labeled property graphs, index-free adjacency, social relationships, fraud detection, graph sharding bottlenecks, and relational alternatives.Vector databaseVector DatabasesSpecialized storage and approximate nearest neighbor (ANN) retrieval engines for dense embeddings. HNSW graphs, inverted file indexes (IVFFlat), recall versus latency trade-offs, pre-filtering challenges, pgvector vs dedicated stores, semantic search, and RAG architectures.ScalingScaling readsMost systems read far more than they write. The ladder of fixes from indexes to caches, replicas and CDNs, in the order to apply them, and what each costs in freshness.ConsistencyDealing with contentionMany requests competing for the same thing, such as the last seat, one inventory count or a shared counter. Locks, optimistic concurrency, atomic operations, reservations and queues, and when to use each.GeospatialProximity SearchEfficient 2D spatial indexing for geospatial radius and nearest-neighbor queries. Geohashes, hierarchical quadtrees, Google S2 and Uber H3 hexagonal cells, PostGIS R-trees, Redis GEO, moving driver location updates, and nearby places retrieval.