Database
Advanced 17 min read Database

PostgreSQL Deep Dive

Go below the surface of PostgreSQL: MVCC tuple mechanics, the write-ahead log, vacuum and bloat, replication, memory tuning and the introspection views that reveal what a production server is really doing.

PostgreSQLMVCCWALVACUUMbloatreplicationshared_bufferspg_stat
Prerequisites: Indexing & Query Optimization Transactions & ACID
Introduction

What is this?

PostgreSQL is an open-source relational database built on MVCC, the write-ahead log and an extensible type system. Every update creates a new row version (tuple) and leaves the old one for VACUUM to reclaim. The WAL guarantees durability and feeds streaming replication to read replicas. Production work centers on tuning memory settings, controlling bloat and vacuum, and reading pg_stat_* views.

Motivation

Why do you need it?

  • MVCC gives snapshot isolation with zero reader-writer blocking — the basis of PostgreSQL's concurrency.
  • WAL provides crash safety and streams to replicas for high availability and scale-out reads.
  • The extension ecosystem (PostGIS, pgvector, TimescaleDB) makes one server cover spatial, vector and time-series work.
  • A rich type and index family (btree, hash, GIN, GiST, BRIN) handles JSON, full-text, spatial and vector data.
  • A cost-based optimizer plus EXPLAIN makes performance work measurable rather than guesswork.
Think about it

Mental model

Imagine

A whiteboard you never erase, copying rows downward

Think of

PostgreSQL MVCC

New versions are written below the old ones so a mid-read is never disturbed; the janitor (VACUUM) eventually wipes the old notes once everyone has left.

Mechanics

How it works

  • An INSERT writes a new tuple into the heap with xmin = the current transaction; its WAL record is written first.
  • An UPDATE writes a new tuple and marks the old one dead (xmax set), without modifying the original page in place.
  • A reader takes a snapshot and checks xmin/xmax to decide tuple visibility — no locks are taken.
  • autovacuum sweeps dead tuples, reclaiming space and refreshing the free-space map.
  • WAL segments are flushed at commit (subject to synchronous_commit) to guarantee durability.
  • WAL archiving and replication slots stream changes to standby servers for failover and read load.

PostgreSQL's MVCC write path and why VACUUM exists.

UPDATE a row
Write-ahead log
New heap tuple
autovacuum
Index and FSM upkeep
Compact, healthy heap
Reference

Key commands

SELECT version();

Show the server version, build and platform.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

Run a query and report actual timings, buffer reads and rows.

SHOW shared_buffers; SHOW work_mem;

Inspect key memory settings for the session.

shared_buffers: 128MB\nwork_mem: 4MB
SELECT pg_size_pretty(pg_total_relation_size('orders'));

Report a table's size including indexes and bloat.

1 GB
SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';

Check live vs dead tuples to gauge bloat and vacuum state.

SELECT pid, state, wait_event_type, query FROM pg_stat_activity;

List running queries, durations and wait states.

Hands-on

Examples

Analyzing a hot query and tuning work_mem

Analyzing a hot query and tuning work_mem
-- Measure actual timings, buffer reads and row counts
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, count(*)
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.email
ORDER BY count(*) DESC;
 
-- A sort-heavy query spills to disk when work_mem is too small.
-- Raise it for the session and compare the plan.
SET work_mem = '64MB';
 
SHOW work_mem;
SHOW max_connections;

EXPLAIN (ANALYZE, BUFFERS) shows actual execution time, buffer reads and whether the sort or hash spilled to disk. If you see 'Sort Method: external merge Disk', the query exceeded work_mem; raising it per-session can keep the sort in memory. SHOW work_mem and max_connections reveal the math behind a memory budget — work_mem is multiplied across concurrent operations.

Real world

In production

  • OLTP applications run Postgres as the system of record with streaming replicas for reads and failover.
  • Geospatial teams use PostGIS for spatial indexes and geocoding on top of standard tables.
  • AI applications store embeddings in pgvector columns with HNSW/ivfflat indexes for similarity search.
  • Log and event workloads use TimescaleDB hypertables for time-series partitioning and retention.
Guidelines

Best practices

  • Right-size shared_buffers (typically 25% of RAM) and let the OS cache the rest; do not starve work_mem.
  • Match fillfactor (e.g., 70) on frequently-updated tables to slow page-level bloat.
  • Set max_connections honestly and use a connection pooler (PgBouncer) to reduce per-connection overhead.
  • Monitor vacuum lag with pg_stat_user_tables and never disable autovacuum on production tables.
  • Test config changes on staging and measure with EXPLAIN (ANALYZE, BUFFERS) before rolling out.
Watch out

Common mistakes

  • Maxing out shared_buffers at 75% of RAM, starving the OS page cache and hurting cold reads.
  • Running with synchronous_commit=off everywhere to gain latency, then losing committed transactions in a power failure.
  • Ignoring autovacuum errors and letting dead tuples balloon until tables bloat to gigabytes.
  • Tuning work_mem globally to a large value and multiplying it by max_connections for a huge total.
Root cause analysis

Common issues

Error
ERROR: out of memory
Root cause

work_mem is too small for a sort or hash, or memory is exhausted.

Fix

Raise work_mem for the query, reduce max_connections, and check for swap thrashing.

Error
database is not accepting commands to avoid wraparound data loss
Root cause

Transaction ID wraparound is imminent because autovacuum fell behind.

Fix

Run VACUUM immediately; lower autovacuum_freeze_max_age and monitor via pg_stat_user_tables.

Error
PANIC: could not locate a valid checkpoint record
Root cause

WAL files are missing or the data directory was corrupted.

Fix

Restore from the last base backup plus WAL archive; verify archive_command and replication slots.

When things break

Debugging tips

  • Watch pg_stat_activity for long-running queries and use pg_stat_statements to rank total query time.
  • Track bloat with pg_total_relation_size and n_dead_tup; run VACUUM (VERBOSE) to confirm reclamation.
  • Use pg_locks to resolve lock contention and blocking chains.
  • Scan the Postgres log for 'checkpoints are occurring too frequently' to tune checkpoint settings.
Interviews

Interview checkpoint

Practice

Hands-on lab

Operating a Production Postgres
45 min · advanced
In progress
Objectives
  • Generate churn and measure dead tuples
  • Observe MVCC in action with snapshots
  • Run and verify VACUUM reclamation
  • Analyze a hot query with EXPLAIN (ANALYZE, BUFFERS)

Operating a Production Postgres

45 min · advanced

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/3
1

Which background process reclaims dead tuples in PostgreSQL?

2

A PostgreSQL reader blocks a writer when both touch the same row.

3

pg_stat_user_tables shows 50 million dead tuples on a heavily-updated table. What is the immediate priority?

Quick reference

Cheat sheet

Memory and I/O settings
shared_buffersIn-server cache, ~25% of RAM
work_memPer sort/hash operation
effective_cache_sizeOS cache estimate for planner
synchronous_commiton = durable, off = faster
checkpoint_timeoutHow often dirty buffers flush
Introspection views
pg_stat_activityRunning queries and waits
pg_stat_user_tablesLive/dead tuple counts
pg_stat_statementsTop queries by total time
pg_locksLock holders and waiters
pg_relation_size()Table/index byte sizes
Go deeper

Official documentation

PostgreSQL Documentation
docs
MVCC documentation
docs
Write-Ahead Logging
docs
Resource consumption (memory) settings
docs