POSTGRES
INTERNALS
A ground-up journey through Postgres — storage, querying, performance, reliability, and scale. Written as a living series, one layer at a time.
19 parts published
Staff Prep 01: Postgres Schema Design Under Constraints
Schema design is the most consequential decision you make. A bad schema costs you three years of migration pain. Here is how staff engineers think about it.
Staff Prep 02: Reading EXPLAIN ANALYZE Like a Query Planner
EXPLAIN ANALYZE is your window into the query planner. Once you can read it fluently, slow queries stop being mysteries and become solvable engineering problems.
Postgres Internals 101: Tables, Types & Constraints
Everything you thought you knew about tables — and the silent bugs hiding in TIMESTAMP, FLOAT, and missing constraints.
Staff Prep 03: Postgres Indexing Strategy — What, When, and in What Order
Wrong index order kills performance as badly as no index. Learn B-tree internals, composite index column ordering, partial indexes, and covering indexes with INCLUDE.
Postgres Internals 102: Views & Materialized Views
A view is just a saved query. A materialized view is a cache you control. Here's when each one saves you — and when each one bites you.
Staff Prep 04: Scaling Postgres Reads — Replicas, Pooling & Caching
Read replicas alone will not save you. PgBouncer with the wrong mode will. Here is the full stack of read scaling: replicas, connection pooling, and query-level caching.
Postgres Internals 103: Schemas & Partitioning
Schemas keep your database organised. Partitioning keeps it fast when tables hit hundreds of millions of rows. Here's how both work — and when each one is the right call.
Staff Prep 05: Scaling Postgres Writes — Batching, UPSERT & Partitioning
Single-row inserts at scale are a silent performance tax. UPSERT semantics are subtle and wrong implementations cause lost updates. Here is the complete write-scaling playbook.
Postgres Internals 104: Indexes
A missing index turns a 2ms lookup into a 30-second full table scan. The wrong index is worse — it gives you false confidence. Here's how indexes actually work, when to use each type, and when Postgres ignores yours entirely.
Staff Prep 06: Transactions & Concurrency — Isolation, Deadlocks & Race Conditions
Isolation levels are not theoretical. The wrong level causes real data corruption. Deadlocks are preventable. And FOR UPDATE is the tool most engineers reach for too late.
Postgres Internals 105: Scaling Reads
A single Postgres instance maxes out under real production load. Read replicas, connection pooling with PgBouncer, and Redis caching are how you scale past it — if you understand what breaks when you add each layer.
Staff Prep 07: Zero-Downtime Postgres Migrations
ALTER TABLE on a 200M-row table locks it for minutes. The expand-contract pattern lets you make any schema change safely while the system runs under full load.
Postgres Internals 106: Scaling Writes
Read replicas handle read scale. But all writes go to one primary. Partitioning, batch inserts, and UPSERT patterns are how you push that ceiling before you need to rethink your architecture entirely.
Postgres Internals 107: Transactions & Concurrency
Every read and write in Postgres happens inside a transaction. Understanding MVCC, isolation levels, and locking is the difference between correct concurrent code and subtle data corruption bugs.
Postgres Internals 108: EXPLAIN & Query Planning
When a query is slow, guessing is expensive. EXPLAIN ANALYZE tells you exactly what Postgres is doing, what it expected, and where it went wrong. This is how you actually fix slow queries.
Postgres Internals 109: Vacuum & Autovacuum
Postgres never overwrites rows in place — it writes new versions. Dead tuples accumulate until VACUUM cleans them up. Ignore this and your database bloats, slows, and eventually wraps around its transaction ID counter.
Staff Prep 21: Postgres MVCC — Multi-Version Concurrency Under the Hood
MVCC is why Postgres readers never block writers. Understanding xmin, xmax, and snapshot visibility explains a class of concurrency bugs that no other framework exposes.
Staff Prep 22: Postgres Partitioning & Sharding — When and How
Partitioning manages large tables within one Postgres instance. Sharding splits data across multiple instances. Most teams partition far too early and shard far too late.
Staff Prep 23: Postgres VACUUM & Table Bloat — Dead Tuples & Autovacuum Tuning
A table that is 40% dead tuples is 70% slower than it should be. Autovacuum default settings are tuned for small databases. Here is how to tune it for production workloads.