Module 6: PostgreSQL Internals
Understanding how PostgreSQL works internally transforms you from someone who uses databases to someone who truly understands them. This knowledge helps you make better design decisions and debug complex issues.Estimated Time: 12-15 hours
Depth Level: Advanced
Key Skill: Understanding MVCC, storage, and memory management
Depth Level: Advanced
Key Skill: Understanding MVCC, storage, and memory management
Lock Manager
Spinlocks, LWLocks, heavyweight locks
Buffer Manager
Clock sweep, ring buffers, page eviction
Memory Management
Memory contexts, palloc, shared memory
Catalog System
System catalogs and caching
Extension Development
Building PostgreSQL extensions
6.1 Architecture Overview
Process Model
PostgreSQL uses a multi-process architecture (not multi-threaded). Each client connection gets its own backend process.Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ PROCESS ARCHITECTURE │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ POSTMASTER (pid=1) │ │
│ │ • Parent of all PostgreSQL processes │ │
│ │ • Listens on TCP port 5432 (configurable) │ │
│ │ • fork()s backend processes for each connection │ │
│ │ • Monitors children, restarts if crash │ │
│ │ • Never touches database data directly │ │
│ └───────────────────────────────┬───────────────────────────────────────┘ │
│ │ fork() │
│ ┌─────────────────────────────┼─────────────────────────────┐ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ Backend 1 │ │ Backend 2 │ │ Backend 3 │ │
│ │ pid=1001 │ │ pid=1002 │ │ pid=1003 │ │
│ │ user=alice │ │ user=bob │ │ user=alice │ │
│ │ db=myapp │ │ db=analytics│ │ db=myapp │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │ │ │ │
│ └─────────────────────────┼─────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌───────────────────────────────────────────────────┐ │
│ │ SHARED MEMORY │ │
│ │ • Shared Buffer Pool (shared_buffers) │ │
│ │ • WAL Buffers (wal_buffers) │ │
│ │ • Lock Tables │ │
│ │ • Proc Array (backend state) │ │
│ │ • CLOG (commit status) │ │
│ │ • Background worker state │ │
│ └───────────────────────────────────────────────────┘ │
│ │ │
│ ┌──────────────────────────────┴──────────────────────────────┐ │
│ │ AUXILIARY PROCESSES │ │
│ │ │ │
│ │ ┌────────────┐ ┌────────────┐ ┌────────────┐ │ │
│ │ │ Checkpointer│ │ BG Writer │ │ WAL Writer │ │ │
│ │ │ Flushes all │ │ Writes │ │ Flushes WAL│ │ │
│ │ │ dirty pages │ │ dirty │ │ buffers to │ │ │
│ │ │ periodically│ │ pages │ │ disk │ │ │
│ │ └────────────┘ └────────────┘ └────────────┘ │ │
│ │ │ │
│ │ ┌────────────┐ ┌────────────┐ ┌────────────┐ │ │
│ │ │ Autovacuum │ │ Stats │ │ Archiver │ │ │
│ │ │ Launcher │ │ Collector │ │ Archives │ │ │
│ │ │ + Workers │ │ Aggregates │ │ WAL files │ │ │
│ │ └────────────┘ └────────────┘ └────────────┘ │ │
│ │ │ │
│ │ ┌────────────┐ ┌────────────┐ │ │
│ │ │ WAL Sender │ │ WAL │ (Replication) │ │
│ │ │ (primary) │ │ Receiver │ │ │
│ │ └────────────┘ └────────────┘ │ │
│ └──────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Signal Handling
PostgreSQL uses Unix signals for inter-process communication:Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ SIGNAL HANDLING │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Signal │ Sender │ Effect on Backend │
│ ────────────┼───────────────┼───────────────────────────────────────────── │
│ SIGTERM │ Postmaster │ Smart shutdown (finish current txn, exit) │
│ SIGINT │ Postmaster │ Fast shutdown (rollback current txn, exit) │
│ SIGQUIT │ Postmaster │ Immediate shutdown (crash recovery needed) │
│ SIGHUP │ pg_reload_conf│ Reload configuration files │
│ SIGUSR1 │ Postmaster │ Latch wakeup (check for work) │
│ SIGUSR2 │ Various │ Recovery-related │
│ SIGALRM │ Self │ Statement timeout, lock timeout │
│ │
│ Postmaster signal handling: │
│ • SIGTERM/SIGINT → Initiate shutdown sequence │
│ • SIGCHLD → Child process died, check if restart needed │
│ • SIGHUP → Signal all children to reload config │
│ │
│ Example: Graceful shutdown │
│ ┌────────────────────────────────────────────────────────────────────────┐ │
│ │ 1. Admin: pg_ctl stop -m smart │ │
│ │ 2. Postmaster receives SIGTERM │ │
│ │ 3. Postmaster sends SIGTERM to all backends │ │
│ │ 4. Backends finish current transaction │ │
│ │ 5. Backends exit │ │
│ │ 6. Postmaster waits for all children │ │
│ │ 7. Postmaster performs checkpoint │ │
│ │ 8. Postmaster exits │ │
│ └────────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Copy
-- View all PostgreSQL processes
SELECT pid, usename, application_name, client_addr,
state, query, backend_type
FROM pg_stat_activity;
-- Send signals to backends
SELECT pg_terminate_backend(pid); -- SIGTERM to specific backend
SELECT pg_cancel_backend(pid); -- SIGINT to cancel current query
-- Reload configuration without restart
SELECT pg_reload_conf(); -- SIGHUP to postmaster
-- View postmaster start time
SELECT pg_postmaster_start_time();
Shared Memory Layout
Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ SHARED MEMORY LAYOUT │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ FIXED-SIZE STRUCTURES │ │
│ │ ┌─────────────────────────────────────────────────────────────────┐ │ │
│ │ │ ShmemIndex: Hash table mapping names to shared memory regions │ │ │
│ │ └─────────────────────────────────────────────────────────────────┘ │ │
│ │ ┌─────────────────────────────────────────────────────────────────┐ │ │
│ │ │ ProcGlobal: Array of PGPROC structures (one per max_connections)│ │ │
│ │ │ • PGPROC: Backend state, transaction info, lock info │ │ │
│ │ │ • size = max_connections * sizeof(PGPROC) │ │ │
│ │ └─────────────────────────────────────────────────────────────────┘ │ │
│ │ ┌─────────────────────────────────────────────────────────────────┐ │ │
│ │ │ XLOG Control: WAL write position, flush position, etc. │ │ │
│ │ └─────────────────────────────────────────────────────────────────┘ │ │
│ │ ┌─────────────────────────────────────────────────────────────────┐ │ │
│ │ │ Control File Data: Database system state │ │ │
│ │ └─────────────────────────────────────────────────────────────────┘ │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ BUFFER POOL (largest region) │ │
│ │ size = shared_buffers (typically 25-40% of RAM) │ │
│ │ ┌─────────────────────────────────────────────────────────────────┐ │ │
│ │ │ Buffer Descriptors: Metadata for each buffer │ │ │
│ │ │ Array[NBuffers] of { tag, flags, usage_count, refcount, ... } │ │ │
│ │ └─────────────────────────────────────────────────────────────────┘ │ │
│ │ ┌─────────────────────────────────────────────────────────────────┐ │ │
│ │ │ Buffer Blocks: Actual 8KB page data │ │ │
│ │ │ Array[NBuffers] of char[BLCKSZ] │ │ │
│ │ └─────────────────────────────────────────────────────────────────┘ │ │
│ │ ┌─────────────────────────────────────────────────────────────────┐ │ │
│ │ │ Buffer Hash Table: Maps (relfilenode, forknum, blknum) → bufid │ │ │
│ │ └─────────────────────────────────────────────────────────────────┘ │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ WAL BUFFERS │ │
│ │ size = wal_buffers (default: 1/32 of shared_buffers) │ │
│ │ Ring buffer of WAL pages pending write to disk │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ CLOG (Commit Log / pg_xact) │ │
│ │ 2 bits per transaction: committed, aborted, in-progress, sub-commit │ │
│ │ Cached pages for fast commit status lookup │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ LOCK TABLES │ │
│ │ • Main Lock Table: Heavyweight locks (max_locks_per_transaction) │ │
│ │ • Predicate Lock Table: SSI serialization │ │
│ │ • Fast-path locks: Per-backend array for common cases │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ OTHER SUBSYSTEMS │ │
│ │ • Subtransaction cache │ │
│ │ • Multixact state │ │
│ │ • Two-phase commit state │ │
│ │ • Async notification state │ │
│ │ • Background worker state │ │
│ │ • Replication slots │ │
│ │ • Logical replication state │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Copy
-- View shared memory settings
SHOW shared_buffers;
SHOW wal_buffers;
SHOW max_connections;
-- Estimate shared memory size
-- Approximately: shared_buffers + (max_connections * ~10MB) + overhead
-- On Linux, check actual shared memory usage
-- $ ipcs -m | grep postgres
-- View proc array (backend slots)
SELECT count(*) as backends_used FROM pg_stat_activity;
SELECT setting::int as max_backends FROM pg_settings WHERE name = 'max_connections';
Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ POSTGRESQL ARCHITECTURE │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Client Applications │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ App 1 │ │ App 2 │ │ App 3 │ │
│ └────┬────┘ └────┬────┘ └────┬────┘ │
│ │ │ │ │
│ └───────────┼───────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ POSTMASTER │ │
│ │ (Main PostgreSQL Process) │ │
│ │ Listens for connections, spawns backend processes │ │
│ └────────────────────────────────┬────────────────────────────────────┘ │
│ │ │
│ ┌───────────────────────────┼───────────────────────────┐ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ Backend 1 │ │ Backend 2 │ │ Backend 3 │ │
│ │ (Session) │ │ (Session) │ │ (Session) │ │
│ └──────┬──────┘ └──────┬──────┘ └──────┬──────┘ │
│ │ │ │ │
│ └─────────────────────────┼─────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ SHARED MEMORY │ │
│ │ ┌───────────────────────────────────────────────────────────────┐ │ │
│ │ │ Shared Buffer Pool │ │ │
│ │ │ ┌────────┬────────┬────────┬────────┬────────┬────────┐ │ │ │
│ │ │ │ Buffer │ Buffer │ Buffer │ Buffer │ Buffer │ ... │ │ │ │
│ │ │ │ 1 │ 2 │ 3 │ 4 │ 5 │ │ │ │ │
│ │ │ └────────┴────────┴────────┴────────┴────────┴────────┘ │ │ │
│ │ └───────────────────────────────────────────────────────────────┘ │ │
│ │ ┌────────────────┐ ┌────────────────┐ ┌────────────────────────┐ │ │
│ │ │ WAL Buffers │ │ Clog Buffer │ │ Lock Tables/Semaphores│ │ │
│ │ └────────────────┘ └────────────────┘ └────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ┌────────────────────────────────┼────────────────────────────────────┐ │
│ │ BACKGROUND PROCESSES │ │
│ │ │ │
│ │ ┌────────────┐ ┌────────────┐ ┌────────────┐ ┌────────────────┐ │ │
│ │ │ WAL │ │ Background │ │ Checkpointer│ │ Autovacuum │ │ │
│ │ │ Writer │ │ Writer │ │ │ │ Launcher │ │ │
│ │ └────────────┘ └────────────┘ └────────────┘ └────────────────┘ │ │
│ │ ┌────────────┐ ┌────────────┐ ┌────────────┐ │ │
│ │ │ Stats │ │ Archiver │ │ Logical │ │ │
│ │ │ Collector │ │ │ │ Replication│ │ │
│ │ └────────────┘ └────────────┘ └────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ DISK STORAGE │ │
│ │ ┌──────────────┐ ┌──────────────┐ ┌────────────────────────────┐ │ │
│ │ │ Data Files │ │ WAL Files │ │ Configuration & Logs │ │ │
│ │ │ (Tables, │ │ (pg_wal/) │ │ (postgresql.conf, pg_log/) │ │ │
│ │ │ Indexes) │ │ │ │ │ │ │
│ │ │ (base/) │ │ │ │ │ │ │
│ │ └──────────────┘ └──────────────┘ └────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
6.2 Query Processing Pipeline
Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ QUERY PROCESSING STAGES │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ 1. PARSER │
│ ┌───────────────────────────────────────────────────────────────────┐ │
│ │ Input: "SELECT name FROM users WHERE id = 1" │ │
│ │ │ │
│ │ Lexer → Tokens: [SELECT] [name] [FROM] [users] [WHERE] [id] [=] │ │
│ │ Parser → Parse Tree (AST) │ │
│ └───────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 2. ANALYZER │
│ ┌───────────────────────────────────────────────────────────────────┐ │
│ │ Semantic Analysis: │ │
│ │ • Does table 'users' exist? │ │
│ │ • Does column 'name' exist in 'users'? │ │
│ │ • What are the data types? │ │
│ │ • Does user have SELECT permission? │ │
│ │ │ │
│ │ Output: Query Tree (with resolved names and types) │ │
│ └───────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 3. REWRITER │
│ ┌───────────────────────────────────────────────────────────────────┐ │
│ │ Apply transformation rules: │ │
│ │ • Expand views to their underlying queries │ │
│ │ • Apply row-level security policies │ │
│ │ • Handle rules (CREATE RULE) │ │
│ └───────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 4. PLANNER / OPTIMIZER │
│ ┌───────────────────────────────────────────────────────────────────┐ │
│ │ Generate possible execution plans: │ │
│ │ • Plan A: Seq Scan + Filter │ │
│ │ • Plan B: Index Scan on idx_users_id │ │
│ │ • Plan C: Bitmap Index Scan + Bitmap Heap Scan │ │
│ │ │ │
│ │ Cost estimation using statistics (pg_statistic) │ │
│ │ Choose lowest-cost plan │ │
│ │ │ │
│ │ Output: Execution Plan (tree of plan nodes) │ │
│ └───────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 5. EXECUTOR │
│ ┌───────────────────────────────────────────────────────────────────┐ │
│ │ Execute the plan: │ │
│ │ • Access data pages via buffer manager │ │
│ │ • Apply filters, joins, aggregations │ │
│ │ • Handle transactions (visibility checks) │ │
│ │ • Return results to client │ │
│ └───────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
6.3 Storage Architecture
Page Structure
Everything in PostgreSQL is stored in 8KB pages (blocks).Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ PAGE STRUCTURE (8KB) │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Offset 0 │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ PAGE HEADER (24 bytes) │ │
│ │ • pd_lsn: Last WAL position that modified this page │ │
│ │ • pd_checksum: Data checksum │ │
│ │ • pd_lower: Offset to start of free space │ │
│ │ • pd_upper: Offset to end of free space │ │
│ │ • pd_special: Offset to special space (for indexes) │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ LINE POINTERS (4 bytes each) │ │
│ │ Array of (offset, length, flags) pointing to tuples │ │
│ │ [LP1] [LP2] [LP3] [LP4] [LP5] ... │ │
│ │ │ │ │ │ │
│ └────┼─────┼─────┼──────────────────────────────────────────────────────┘ │
│ │ │ │ │
│ ┌────┼─────┼─────┼──────────────────────────────────────────────────────┐ │
│ │ │ │ │ FREE SPACE │ │
│ │ │ │ │ │ │
│ │ │ │ │ (pd_lower) (pd_upper) │ │
│ │ │ │ │ ↓ ↓ │ │
│ │ │ │ │ ████████████████████████ │ │
│ └────┼─────┼─────┼──────────────────────────────────────────────────────┘ │
│ │ │ │ │
│ ┌────┼─────┼─────┼──────────────────────────────────────────────────────┐ │
│ │ │ │ └───────────────────────────────────┐ │ │
│ │ │ └─────────────────────────┐ │ │ │
│ │ └────────────────┐ │ │ │ │
│ │ ▼ ▼ ▼ │ │
│ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ │
│ │ │ Tuple 3 │ │ Tuple 2 │ │ Tuple 1 │ ← Grows DOWN │ │
│ │ │ (Row Data) │ │ (Row Data) │ │ (Row Data) │ │ │
│ │ └──────────────┘ └──────────────┘ └──────────────┘ │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ │
│ Offset 8192 │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Tuple (Row) Structure
Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ TUPLE HEADER (23+ bytes) │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ t_xmin (4 bytes) │ Transaction ID that inserted this tuple │ │
│ │ t_xmax (4 bytes) │ Transaction ID that deleted/updated │ │
│ │ t_cid (4 bytes) │ Command ID within transaction │ │
│ │ t_ctid (6 bytes) │ Current tuple ID (page, offset) │ │
│ │ t_infomask (2 bytes) │ Status flags (committed, aborted, etc.) │ │
│ │ t_infomask2 (2 bytes)│ More flags + number of attributes │ │
│ │ t_hoff (1 byte) │ Offset to user data │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ NULL BITMAP (variable) │ │
│ │ One bit per column, indicates if value is NULL │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ USER DATA │ │
│ │ [Column 1 Value] [Column 2 Value] [Column 3 Value] ... │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
View Hidden Columns
Copy
-- See system columns for any row
SELECT
ctid, -- Physical location (page, offset)
xmin, -- Transaction that created
xmax, -- Transaction that deleted (0 if live)
cmin, -- Command ID for insert
cmax, -- Command ID for delete
tableoid, -- OID of the table
*
FROM users
WHERE id = 1;
-- Result:
-- ctid | (0,1) -- Page 0, tuple 1
-- xmin | 12345 -- Created by txn 12345
-- xmax | 0 -- Not deleted
-- cmin | 0 -- First command in its txn
-- cmax | 0
6.4 MVCC (Multi-Version Concurrency Control)
PostgreSQL’s MVCC allows readers and writers to work without blocking each other.How MVCC Works
Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ MVCC IN ACTION │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Initial State: Row with id=1, name='Alice' │
│ ┌────────────────────────────────────────────────────────────────────┐ │
│ │ xmin=100 │ xmax=0 │ id=1 │ name='Alice' │ │ │
│ └────────────────────────────────────────────────────────────────────┘ │
│ │
│ Transaction 200: UPDATE users SET name='Bob' WHERE id=1 │
│ │
│ After UPDATE: TWO tuple versions exist! │
│ ┌────────────────────────────────────────────────────────────────────┐ │
│ │ xmin=100 │ xmax=200 │ id=1 │ name='Alice' │ ← Old version (dead) │ │
│ └────────────────────────────────────────────────────────────────────┘ │
│ ┌────────────────────────────────────────────────────────────────────┐ │
│ │ xmin=200 │ xmax=0 │ id=1 │ name='Bob' │ ← New version (live) │ │
│ └────────────────────────────────────────────────────────────────────┘ │
│ │
│ Visibility Rules: │
│ ───────────────── │
│ Transaction 150 (started before 200): │
│ → Sees 'Alice' (xmin=100 committed, xmax=200 not visible) │
│ │
│ Transaction 250 (started after 200 committed): │
│ → Sees 'Bob' (xmin=200 committed, xmax=0 means live) │
│ │
│ Transaction 200 (the updater itself): │
│ → Sees 'Bob' (its own changes are visible) │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Tuple Visibility Check
Copy
-- Simplified visibility logic (actual code is more complex)
-- A tuple is visible if:
-- 1. xmin is committed AND
-- 2. xmin started before our snapshot AND
-- 3. (xmax is 0 OR xmax is aborted OR xmax started after our snapshot)
-- Check transaction status
SELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid();
-- View commit log status
SELECT
pg_xact_status(xmin) as xmin_status,
pg_xact_status(xmax) as xmax_status
FROM users WHERE id = 1;
-- Returns: 'committed', 'aborted', 'in progress', or NULL
Snapshot Isolation
Copy
-- REPEATABLE READ takes a snapshot at first query
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1; -- Snapshot taken HERE
-- Returns balance = 1000
-- Another transaction updates and commits...
SELECT * FROM accounts WHERE id = 1;
-- Still returns balance = 1000! (same snapshot)
COMMIT;
-- New transaction sees the update
SELECT * FROM accounts WHERE id = 1;
-- Returns balance = 1200
6.5 VACUUM and Dead Tuples
Why VACUUM is Necessary
Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ THE DEAD TUPLE PROBLEM │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ After many updates, pages fill with dead tuples: │
│ │
│ Page: │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ [DEAD] [LIVE] [DEAD] [DEAD] [LIVE] [DEAD] [LIVE] [DEAD] [DEAD] │ │
│ │ │ │
│ │ 70% dead tuples = wasted space + slow scans! │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
│ Problems: │
│ • Table bloat: 10GB of data uses 100GB on disk │
│ • Slow sequential scans: must read dead tuples too │
│ • Index bloat: indexes point to dead tuples │
│ • Transaction ID wraparound risk │
│ │
│ After VACUUM: │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ [FREE] [LIVE] [FREE] [FREE] [LIVE] [FREE] [LIVE] [FREE] [FREE] │ │
│ │ │ │
│ │ Space marked as free for reuse (but not returned to OS) │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
│ After VACUUM FULL (rewrites table): │
│ ┌────────────────────────────────────────────────────────────────────┐ │
│ │ [LIVE] [LIVE] [LIVE] │ │
│ │ │ │
│ │ Compacted! Space returned to OS. But table is locked during. │ │
│ └────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
VACUUM Operations
Copy
-- Regular VACUUM: Mark dead tuples as reusable
VACUUM users;
-- VACUUM with analysis: Also update statistics
VACUUM ANALYZE users;
-- VACUUM VERBOSE: Show what it's doing
VACUUM VERBOSE users;
-- VACUUM FULL: Rewrite table (locks table exclusively!)
VACUUM FULL users; -- Use sparingly!
-- Check vacuum stats
SELECT
relname,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_pct,
last_vacuum,
last_autovacuum,
last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
Autovacuum Configuration
Copy
-- View current settings
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE '%autovacuum%';
-- Per-table settings for high-traffic tables
ALTER TABLE orders SET (
autovacuum_vacuum_threshold = 1000, -- Min dead tuples
autovacuum_vacuum_scale_factor = 0.01, -- + 1% of table
autovacuum_analyze_threshold = 500,
autovacuum_analyze_scale_factor = 0.005,
autovacuum_vacuum_cost_delay = 2 -- Less aggressive
);
-- Check if autovacuum is running
SELECT pid, datname, usename, state, query,
backend_start, xact_start
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';
6.6 Write-Ahead Logging (WAL)
How WAL Works
Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ WRITE-AHEAD LOGGING │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ The Golden Rule: "Write the log before the data" │
│ │
│ Transaction Flow: │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ 1. Application: INSERT INTO orders VALUES (...) │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ 2. PostgreSQL: Create WAL record in WAL Buffer │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ 3. Modify page in Shared Buffer (in memory) │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ 4. Application: COMMIT │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ 5. Flush WAL Buffer to disk (fsync) ← Durability guaranteed here! │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ 6. Return success to application │ │
│ │ │ │ │
│ │ ▼ (Later, asynchronously) │ │
│ │ 7. Background Writer: Write dirty pages to data files │ │
│ │ │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
│ Recovery After Crash: │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ 1. Start PostgreSQL │ │
│ │ 2. Read WAL from last checkpoint │ │
│ │ 3. Replay all WAL records │ │
│ │ 4. Database is consistent! No data loss. │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
WAL Configuration
Copy
-- View WAL settings
SHOW wal_level; -- minimal, replica, logical
SHOW max_wal_size; -- Maximum WAL size before checkpoint
SHOW min_wal_size; -- Minimum WAL to keep
SHOW wal_buffers; -- WAL buffer size
-- View current WAL position
SELECT pg_current_wal_lsn();
-- View WAL file info
SELECT * FROM pg_stat_wal;
-- Force a checkpoint (flushes all data to disk)
CHECKPOINT;
-- View checkpoint stats
SELECT * FROM pg_stat_bgwriter;
6.7 Buffer Management
Shared Buffer Pool
Copy
┌─────────────────────────────────────────────────────────────────────────────┐
│ BUFFER POOL MANAGEMENT │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Shared Buffer Pool (shared_buffers): │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ │ │
│ │ │ Buf │ │ Buf │ │ Buf │ │ Buf │ │ Buf │ │ Buf │ │ Buf │ │ Buf │ │ │
│ │ │ 1 │ │ 2 │ │ 3 │ │ 4 │ │ 5 │ │ 6 │ │ 7 │ │ 8 │ │ │
│ │ └──┬──┘ └──┬──┘ └──┬──┘ └──┬──┘ └──┬──┘ └──┬──┘ └──┬──┘ └──┬──┘ │ │
│ │ │ │ │ │ │ │ │ │ │ │
│ │ │ │ │ │ │ │ │ │ │ │
│ │ ┌──┴──────┴──────┴──────┴──────┴──────┴──────┴──────┴──┐ │ │
│ │ │ Buffer Descriptor Array │ │ │
│ │ │ • Page ID (table OID, block number) │ │ │
│ │ │ • Usage count (for clock sweep eviction) │ │ │
│ │ │ • Dirty flag │ │ │
│ │ │ • Pin count (number of backends using it) │ │ │
│ │ └──────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────┐ │ │
│ │ │ Hash Table │ │ │
│ │ │ Maps (table_oid, block_num) → buffer_id │ │ │
│ │ └──────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
│ When page is needed: │
│ 1. Check hash table for (table, block) │
│ 2. If found: buffer hit! Increment usage count. │
│ 3. If not found: buffer miss │
│ a. Find victim buffer (clock sweep: usage count = 0) │
│ b. If victim is dirty, write to disk first │
│ c. Read requested page from disk │
│ d. Insert into hash table │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Monitoring Buffer Usage
Copy
-- Buffer cache hit ratio
SELECT
sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) as cache_hit_ratio
FROM pg_statio_user_tables;
-- Should be > 0.99 for well-tuned system
-- Per-table buffer stats
SELECT
relname,
heap_blks_read, -- Blocks read from disk
heap_blks_hit, -- Blocks found in cache
round(100.0 * heap_blks_hit / nullif(heap_blks_hit + heap_blks_read, 0), 2) as hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 20;
-- What's in the buffer cache? (requires pg_buffercache extension)
CREATE EXTENSION pg_buffercache;
SELECT
c.relname,
count(*) AS buffers,
pg_size_pretty(count(*) * 8192) AS size_in_cache
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname
ORDER BY count(*) DESC
LIMIT 20;
6.8 Practice Exercises
Exercise 1: Investigate Tuple Visibility
Copy
-- Setup: Create a test scenario
CREATE TABLE visibility_test (id SERIAL PRIMARY KEY, value TEXT);
-- Session 1
BEGIN;
INSERT INTO visibility_test (value) VALUES ('first');
-- Note the xmin
-- Session 2 (before Session 1 commits)
SELECT ctid, xmin, xmax, * FROM visibility_test;
-- What do you see?
-- Session 1
COMMIT;
-- Session 2
SELECT ctid, xmin, xmax, * FROM visibility_test;
-- What changed?
Exercise 2: Analyze Table Bloat
Copy
-- Create and bloat a table
CREATE TABLE bloat_test AS
SELECT generate_series(1, 100000) AS id, 'data' AS value;
-- Update every row multiple times
UPDATE bloat_test SET value = 'updated1';
UPDATE bloat_test SET value = 'updated2';
UPDATE bloat_test SET value = 'updated3';
-- Check bloat
SELECT
pg_size_pretty(pg_table_size('bloat_test')) AS table_size,
n_live_tup,
n_dead_tup,
last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'bloat_test';
-- Run vacuum and compare
VACUUM bloat_test;
-- Check sizes again
Next Module
Module 7: Replication & High Availability
Build systems that survive failures