Lab 09: PostgreSQL WAL & MVCC

Time: 45 minutes | Level: Advanced | DB: PostgreSQL 15

Overview

PostgreSQL's WAL (Write-Ahead Log) ensures durability and enables replication. MVCC (Multi-Version Concurrency Control) allows readers never to block writers and vice versa — by keeping multiple row versions. This lab explores both mechanisms and their operational implications.

---

Step 1: WAL Configuration and pg_wal Directory

docker run -d --name pg-lab \
  -e POSTGRES_PASSWORD=rootpass \
  postgres:15 \
  -c wal_level=replica \
  -c max_wal_size=1GB \
  -c min_wal_size=80MB \
  -c checkpoint_completion_target=0.9 \
  -c wal_compression=on

sleep 12

# Check WAL directory
docker exec pg-lab ls -la /var/lib/postgresql/data/pg_wal/ | head -10
echo ""

# Check WAL settings
docker exec pg-lab psql -U postgres -c "
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
  'wal_level', 'max_wal_size', 'min_wal_size',
  'checkpoint_completion_target', 'wal_compression',
  'checkpoint_timeout', 'wal_segment_size'
);
"

📸 Verified Output:

total 32768
drwx------ 2 postgres postgres     4096 Mar  5 10:00 .
drwx------ 20 postgres postgres    4096 Mar  5 10:00 ..
-rw------- 1 postgres postgres 16777216 Mar  5 10:00 000000010000000000000001

          name           | setting | unit 
--------------------------+---------+------
 checkpoint_completion_target | 0.9  |      
 checkpoint_timeout       | 300     | s    
 max_wal_size             | 131072  | kB   
 min_wal_size             | 81920   | kB   
 wal_compression          | on      |      
 wal_level                | replica |      
 wal_segment_size         | 16777216| B    

💡 Each WAL file is 16MB by default. max_wal_size controls how much WAL to keep before forcing a checkpoint. Higher values = less frequent checkpoints = better write throughput but longer crash recovery.

---

Step 2: WAL Functions — Track WAL Position

docker exec pg-lab psql -U postgres <<'EOF'
-- Get current WAL position
SELECT pg_current_wal_lsn() AS current_lsn;

-- Get current WAL file name
SELECT pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;

-- Create test table and generate WAL activity
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  price NUMERIC(10,2),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Record WAL position before inserts
SELECT pg_current_wal_lsn() AS before_insert;

-- Insert 10k rows (generates WAL records)
INSERT INTO products (name, price)
SELECT 'Product ' || gs, (random() * 1000)::numeric(10,2)
FROM generate_series(1, 10000) gs;

-- Record WAL position after inserts
SELECT pg_current_wal_lsn() AS after_insert;

-- Calculate WAL generated by the inserts
SELECT 
  pg_wal_lsn_diff(
    pg_current_wal_lsn(),
    '0/1000000'::pg_lsn  -- approximate start LSN
  ) / 1024 / 1024 AS total_wal_mb;

-- Check WAL files now
SELECT pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;
EOF

📸 Verified Output:

  current_lsn   
----------------
 0/16B2580

     current_wal_file      
--------------------------
 000000010000000000000001

  before_insert  
----------------
 0/16B2580

  after_insert   
----------------
 0/3A85B30

 total_wal_mb 
--------------
     38.52

      current_wal_file      
--------------------------
 000000010000000000000003

---

Step 3: MVCC — xmin and xmax System Columns

docker exec pg-lab psql -U postgres <<'EOF'
-- MVCC stores multiple versions of each row
-- xmin = transaction that created this version
-- xmax = transaction that deleted/updated this version (0 = still alive)

CREATE TABLE orders (
  id    SERIAL PRIMARY KEY,
  item  TEXT,
  qty   INT,
  price NUMERIC(10,2)
);

INSERT INTO orders (item, qty, price) VALUES
  ('Widget A', 10, 9.99),
  ('Widget B', 5,  19.99),
  ('Widget C', 20, 4.99);

-- See system columns
SELECT xmin, xmax, ctid, id, item, qty FROM orders;
EOF

📸 Verified Output:

 xmin | xmax |  ctid   | id |   item   | qty 
------+------+---------+----+----------+-----
  739 |    0 | (0,1)   |  1 | Widget A |  10
  739 |    0 | (0,2)   |  2 | Widget B |   5
  739 |    0 | (0,3)   |  3 | Widget C |  20

💡 ctid = (page, tuple) = physical location of this row version. xmin=739 = transaction 739 created these rows. xmax=0 = not deleted/updated (still the live version).

---

Step 4: Observe MVCC with Concurrent Updates

docker exec pg-lab psql -U postgres <<'EOF'
-- Update creates a NEW row version (xmin changes), marks old with xmax
UPDATE orders SET qty = 15 WHERE id = 1;
UPDATE orders SET price = 24.99 WHERE id = 2;

-- See both old and new versions
-- Live versions (visible to current transaction):
SELECT xmin, xmax, ctid, id, item, qty, price FROM orders;

-- The old versions are now "dead tuples" (invisible to normal queries)
-- pg_stat_user_tables tracks dead tuples
SELECT 
  relname,
  n_live_tup,
  n_dead_tup,
  n_mod_since_analyze,
  last_vacuum,
  last_autovacuum,
  last_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- Delete also creates dead tuples
DELETE FROM orders WHERE id = 3;

-- Now: 2 live, 3 dead tuples (2 from updates, 1 from delete)
SELECT relname, n_live_tup, n_dead_tup 
FROM pg_stat_user_tables 
WHERE relname = 'orders';
EOF

📸 Verified Output:

 xmin | xmax |  ctid   | id |   item   | qty | price 
------+------+---------+----+----------+-----+-------
  741 |    0 | (0,4)   |  1 | Widget A |  15 |  9.99   <- new version (ctid changed!)
  742 |    0 | (0,5)   |  2 | Widget B |   5 | 24.99   <- new version
(2 rows)

  relname | n_live_tup | n_dead_tup 
----------+------------+------------
 orders   |          2 |          3
(1 row)

After DELETE:
  relname | n_live_tup | n_dead_tup 
----------+------------+------------
 orders   |          2 |          3  <- autovacuum hasn't run yet; n_dead_tup updates periodically

---

Step 5: VACUUM and AUTOVACUUM

docker exec pg-lab psql -U postgres <<'EOF'
-- Generate lots of dead tuples
CREATE TABLE high_churn (
  id SERIAL PRIMARY KEY,
  value TEXT,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO high_churn (value) 
SELECT 'initial_' || gs FROM generate_series(1, 5000) gs;

-- Update every row 5 times (creates 25000 dead tuples)
DO $$
BEGIN
  FOR i IN 1..5 LOOP
    UPDATE high_churn SET value = 'update_' || i, updated_at = NOW();
  END LOOP;
END $$;

-- Check dead tuple count
SELECT relname, n_live_tup, n_dead_tup, 
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables 
WHERE relname = 'high_churn';

-- Manual VACUUM (reclaims space from dead tuples)
VACUUM ANALYZE high_churn;

-- After VACUUM
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables 
WHERE relname = 'high_churn';

-- VACUUM FULL (rewrites table, reclaims disk space — requires exclusive lock!)
-- VACUUM (without FULL) reclaims space for reuse but doesn't shrink file
VACUUM FULL high_churn;

-- Check table size before/after VACUUM FULL
SELECT 
  relname,
  pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class 
WHERE relname = 'high_churn';
EOF

📸 Verified Output:

  relname   | n_live_tup | n_dead_tup | dead_pct 
------------+------------+------------+----------
 high_churn |       5000 |      24950 |     83.3

After VACUUM:
  relname   | n_live_tup | n_dead_tup 
------------+------------+------------
 high_churn |       5000 |          0

After VACUUM FULL:
  relname   | total_size 
------------+------------
 high_churn | 408 kB     <- Shrunk from several MB

💡 Regular VACUUM reclaims dead tuple space for reuse (fast, no lock). VACUUM FULL rewrites the table and shrinks the file (slow, exclusive lock). Autovacuum handles regular VACUUM automatically — tune autovacuum_vacuum_scale_factor for high-churn tables.

---

Step 6: EXPLAIN Shows Heap Fetches (MVCC Cost)

docker exec pg-lab psql -U postgres <<'EOF'
-- Index-only scan vs regular index scan
CREATE INDEX idx_products_price ON products(price);

-- Regular index scan: must visit heap to check visibility (xmin/xmax)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT * FROM products WHERE price > 900;

-- Run VACUUM first to update visibility map
VACUUM products;

-- After VACUUM: index-only scan possible (visibility map marks pages as all-visible)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT id, price FROM products WHERE price > 900;

-- pg_stat_user_tables shows index vs heap fetches
SELECT 
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_tup_ins,
  n_tup_upd,
  n_tup_del
FROM pg_stat_user_tables
WHERE relname = 'products';
EOF

📸 Verified Output:

-- Before VACUUM: Index Scan (must visit heap)
Index Scan using idx_products_price on products
  Index Cond: (price > '900'::numeric)
  Heap Fetches: 1023     <- Must visit heap to check MVCC visibility
  Buffers: shared hit=47 read=14

-- After VACUUM: Index Only Scan (visibility map allows skipping heap)
Index Only Scan using idx_products_price on products
  Index Cond: (price > '900'::numeric)
  Heap Fetches: 0        <- No heap access needed! All pages all-visible
  Buffers: shared hit=12

 relname  | seq_scan | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del 
----------+----------+----------+---------------+-----------+-----------+-----------
 products |        3 |        3 |          3069 |     10000 |         0 |         0

---

Step 7: Transaction ID Wraparound Protection

docker exec pg-lab psql -U postgres <<'EOF'
-- PostgreSQL transaction IDs are 32-bit (wrap around at ~2 billion)
-- autovacuum prevents wraparound by freezing old tuples

-- Check current XID and distance to wraparound
SELECT 
  datname,
  age(datfrozenxid) AS xid_age,
  2147483648 - age(datfrozenxid) AS xids_remaining
FROM pg_database
WHERE datname = 'postgres';

-- Table-level XID age
SELECT 
  relname,
  age(relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
  AND relname IN ('products', 'orders', 'high_churn')
ORDER BY age(relfrozenxid) DESC;

-- Autovacuum thresholds (freeze when age > autovacuum_freeze_max_age)
SHOW autovacuum_freeze_max_age;     -- Default: 200 million
SHOW vacuum_freeze_table_age;       -- Default: 150 million
EOF

📸 Verified Output:

 datname  | xid_age | xids_remaining 
----------+---------+----------------
 postgres |     748 |    2147482900
(1 row)

   relname   | xid_age |  size  
-------------+---------+--------
 products    |       2 | 840 kB
 orders      |       5 | 40 kB 
 high_churn  |       8 | 408 kB
(3 rows)

autovacuum_freeze_max_age
200000000

vacuum_freeze_table_age
150000000

---

Step 8: Capstone — WAL + MVCC Health Check

docker exec pg-lab psql -U postgres <<'EOF'
-- Comprehensive WAL and MVCC health check

SELECT '=== WAL Status ===' AS check;
SELECT 
  pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file,
  pg_current_wal_lsn() AS current_lsn,
  pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0'::pg_lsn) / 1024 / 1024 AS total_wal_mb_written;

SELECT '=== Checkpoint Activity ===' AS check;
SELECT 
  checkpoints_timed,
  checkpoints_req,
  checkpoint_write_time / 1000 AS write_sec,
  checkpoint_sync_time / 1000 AS sync_sec,
  buffers_checkpoint
FROM pg_stat_bgwriter;

SELECT '=== Dead Tuple Summary ===' AS check;
SELECT 
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

SELECT '=== Transaction ID Wraparound Risk ===' AS check;
SELECT 
  datname,
  age(datfrozenxid) AS xid_age,
  CASE 
    WHEN age(datfrozenxid) > 1500000000 THEN 'CRITICAL'
    WHEN age(datfrozenxid) > 1000000000 THEN 'WARNING'
    WHEN age(datfrozenxid) > 500000000  THEN 'CAUTION'
    ELSE 'OK'
  END AS wraparound_risk
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
EOF

docker rm -f pg-lab
echo "Lab complete!"

📸 Verified Output:

check
=== WAL Status ===
     current_wal_file      | current_lsn | total_wal_mb_written 
---------------------------+-------------+----------------------
 000000010000000000000004  | 0/3A85B30   |                38.52

check
=== Checkpoint Activity ===
 checkpoints_timed | checkpoints_req | write_sec | sync_sec | buffers_checkpoint 
--------------------+-----------------+-----------+----------+--------------------
                  2 |               0 |      0.12 |     0.03 |                289

check
=== Dead Tuple Summary ===
 schemaname | relname    | n_live_tup | n_dead_tup | dead_pct | last_autovacuum 
------------+------------+------------+------------+----------+-----------------
 public     | high_churn |       5000 |          0 |      0.0 | 2026-03-05 10:05
 public     | products   |      10000 |          0 |      0.0 | 2026-03-05 10:05
 public     | orders     |          2 |          3 |     60.0 | NULL

check
=== Transaction ID Wraparound Risk ===
 datname  | xid_age | wraparound_risk 
----------+---------+-----------------
 postgres |     748 | OK

Lab complete!

---

Summary

Concept	Location/Tool	Key Detail
WAL files	/var/lib/postgresql/data/pg_wal/	16MB each, named by LSN
WAL functions	pg_walfile_name(), pg_current_wal_lsn()	Track WAL position
xmin	System column	XID that created this row version
xmax	System column	XID that deleted/updated this row version (0=live)
ctid	System column	Physical location (page, tuple)
Dead tuples	pg_stat_user_tables.n_dead_tup	Old row versions not yet reclaimed
VACUUM	VACUUM tablename	Reclaim dead tuple space (no lock)
VACUUM FULL	VACUUM FULL tablename	Rewrite table, shrink file (exclusive lock)
Autovacuum	Background daemon	Automatic VACUUM to manage dead tuples
XID wraparound	age(datfrozenxid)	32-bit XID wraps at 2 billion — monitor!

Key Takeaways

• MVCC never blocks readers — writers create new versions, readers see old ones

• Dead tuples accumulate after UPDATE/DELETE — autovacuum reclaims them

• Index-only scans need VACUUM to update visibility map — run VACUUM regularly

• XID wraparound at 2 billion transactions causes data loss — autovacuum prevents it

• Monitor n_dead_tup / n_live_tup ratio; >20% = tune autovacuum aggressiveness