Databases
Intermediate 18 min read Databases

PostgreSQL In Depth

Why Postgres is the de-facto warehouse and OLTP engine

PostgreSQL is an extensible, MVCC-based relational database. Its snapshots, unlogged indexes, WAL archiving, TOASTed values, and background VACUUM define how it behaves under load.

MVCCVACUUMTOASTWALcheckpointextensionpg dumper
Prerequisites: Relational Database Internals
Before we start

What you will learn

  • Explain MVCC snapshots and snapshot age in a few sentences
  • Describe the role of TOAST, WAL, and VACUUM
  • Install a useful extension and run psql routines
Introduction

What is this?

PostgreSQL gives each transaction a snapshot, stores row versions so readers and writers do not block each other, keeps large values out of line with TOAST, and journals every change to the WAL before touching data files. Background VACUUM reclaims obsolete row versions and keeps the engine honest.

Motivation

Why do you need it?

  • It is the default engine for warehouses, backend services, and self-managed analytics in the modern stack.
  • Extensibility lets you turn a relational store into geospatial, JSON, or analytics infrastructure.
  • Batch workloads die on bloat and checkpoint storms; knowing VACUUM and WAL keeps them steady.
Think about it

Mental model

Imagine

A git repo where every commit keeps a full snapshot

Think of

Postgres MVCC

Reading at an old snapshot is like checking out an old commit: it never sees later commits (uncommitted writes) yet stays perfectly consistent. Obsolete blobs are garbage-collected by VACUUM like git gc.

Mechanics

How it works

  • Every query reads under a snapshot; dirty rows are ignored based on their xmin/xmax visibility flags.
  • Each row's old versions accumulate until VACUUM removes ones no transaction can see anymore.
  • Values over ~2KB are stored inline in TOAST tables and only fetched when selected, so big JSON payloads stay cheap.
  • WAL is appended before page changes; checkpoints flush dirty buffers and recycle WAL segments.
  • extensions (pg_stat_statements, postgis, timescaledb) hook into catalogs and planner hooks without forking.

MVCC visibility and the VACUUM lifecycle.

Row inserted
Row updated
Transaction ends
VACUUM
Space reusable
Reference

Key commands

SELECT pid, age(clock_timestamp(), state_change), query FROM pg_stat_activity;

List open transactions and their age — find long-running snapshots.

SELECT pg_relation_size('orders') AS bytes, pg_total_relation_size('orders') AS with_indexes;

Check table vs total size including indexes and TOAST.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Enable top-query statistics, the fastest way to find hot SQL.

VACUUM ANALYZE orders;

Reclaim dead tuples and refresh planner statistics.

Hands-on

Examples

Seeing the versioning in action

Seeing the versioning in action
BEGIN;
CREATE TABLE t(id int);
INSERT INTO t VALUES (1);
-- in another session:
-- SELECT count(*) FROM t;  -> 0 (uncommitted invisible)
COMMIT;
-- now count(*) -> 1
 
SELECT relname, n_live_tup, n_dead_tup, last_vacuum
FROM pg_stat_user_tables WHERE relname = 't';

Uncommitted rows stay invisible to other sessions; the stats view shows how many dead versions are waiting for VACUUM.

Real world

In production

  • A team hooks pg_stat_statements to its APM to catch 'top ten slow queries' weekly.
  • A CDC pipeline relies on logical replication slots; unused slots are detected via pg_stat_replication.
  • Long-running analytics queries that touch JSONB benefit because TOAST keeps the compact columns in cache.
Guidelines

Best practices

  • Set autovacuum thresholds generously and watch n_dead_tup growth on hot tables.
  • Use prepared statements and bind parameters so constant-inlining does not defeat cached plans.
  • Drop unused replication slots after reading them from pg_stat_replication.
Watch out

Common mistakes

  • Never running VACUUM and letting bloat slow every scan or, worse, let XID wraparound threaten the cluster.
  • Treating the checkpoint as a spare 'backup': it is a crash-recovery boundary, not a copy of your data.
  • Using lowercase or per-message connection churn, which piles connection setup and planner startup onto every request.
Root cause analysis

Common issues

Error
Table grows 10% daily but deletes are frequent
Root cause

Dead tuples (old MVCC versions) not yet vacuumed

Fix

Tune autovacuum or run VACUUM ANALYZE manually during a maintenance window.

Error
Every write pauses for 15 seconds before resuming
Root cause

Uncontrolled checkpoint with low max_wal_size

Fix

Raise max_wal_size and check checkpoint_timeout so the flush is smooth instead of a spike.

When things break

Debugging tips

  • When a query is slow, run EXPLAIN (ANALYZE, BUFFERS) and compare reads vs cached hits.
  • When writes stall, inspect pg_stat_activity for 'waiting' and look for lock waits, fsync pressure, or checkpoint spikes.
  • When a replica lags, check the WAL replay position and consumed slots rather than CPU.
Interviews

Interview checkpoint

Practice

Hands-on lab

Prove MVCC Snapshot Semantics in Postgres
35 min · intermediate
In progress
Objectives
  • Run PostgreSQL locally in Docker
  • Demonstrate that uncommitted writes are invisible to other sessions
  • Observe snapshot age and dead tuple counts via pg_stat_*
  • Show VACUUM reclaiming dead tuples

Prove MVCC Snapshot Semantics in Postgres

35 min · intermediate

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

Quick quiz

0/2
1

What does VACUUM remove from a table?

2

A replication slot that is never consumed can stop WAL recycling and fill the disk.

Go deeper

Official documentation

PostgreSQL MVCC docs
docs
VACUUM manual
docs
WAL internals
docs
pg_stat_statements
docs