MVCC & VACUUM

-- 1) Every row has hidden columns: xmin (insert tx) + xmax (delete tx)
SELECT id, name, xmin, xmax FROM users LIMIT 5;
--  id | name | xmin  | xmax
-- ----+------+-------+------
--   1 | Mara | 12345 |    0
--   2 | Sam  | 12345 |    0

-- xmin = transaction that INSERTED the row
-- xmax = transaction that DELETED/UPDATED the row (0 = current)

-- 2) Snapshot isolation — read your own writes, ignore others'
-- Transaction A:
BEGIN;
SELECT count(*) FROM orders;  -- sees committed state at this moment

-- Meanwhile, transaction B inserts 1000 orders and commits.

-- Transaction A still in same txn:
SELECT count(*) FROM orders;  -- SAME count as before — snapshot frozen
COMMIT;
-- Now reading sees the new orders

-- 3) Updates are 'delete + insert'
UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- Old row: xmax = current_tx_id (marked deleted)
-- New row: xmin = current_tx_id (newly inserted)
-- The OLD row stays until vacuum cleans it up.

-- This means: an UPDATE-heavy table accumulates dead tuples (bloat).

-- 4) Visibility rules — what each transaction sees
-- A row is visible to a transaction T if:
--   • xmin committed before T started AND
--   • xmax is null OR uncommitted OR committed after T started

-- Each connection has a 'transaction snapshot' = view of committed state at start.

-- 5) Inspecting bloat
SELECT schemaname, relname, n_live_tup, n_dead_tup,
             round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY dead_pct DESC LIMIT 10;

-- Heavy bloat (50%+) means VACUUM hasn't kept up. Tune autovacuum or run manually.

-- 6) VACUUM — reclaim dead tuples
VACUUM users;                  -- non-blocking; reclaim space for reuse
VACUUM (VERBOSE) users;        -- show progress
VACUUM ANALYZE users;          -- reclaim + update statistics
VACUUM FULL users;             -- REWRITES table; holds EXCLUSIVE lock; reclaims to disk

-- VACUUM FULL is DANGEROUS in production (locks for the entire duration).
-- For online compaction, use pg_repack extension.

-- 7) Autovacuum — runs automatically
SHOW autovacuum;                              -- on by default
SHOW autovacuum_vacuum_scale_factor;          -- default 0.2 (20% dead)
SHOW autovacuum_naptime;                       -- default 60s

-- Per-table tuning for hot tables:
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);  -- vacuum at 5%
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 1000);     -- absolute min

-- 8) Long-running transactions = bloat risk
-- VACUUM can only clean rows that no LIVE transaction needs.
-- A transaction left open for hours blocks cleanup of recent changes.

-- Find long-running txns:
SELECT pid, usename, application_name, state,
             clock_timestamp() - xact_start AS xact_duration,
             query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND state != 'idle'
ORDER BY xact_duration DESC;

-- Idle transactions are the worst:
SELECT pid, usename, application_name, state, xact_start, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

-- Kill if needed (carefully):
SELECT pg_cancel_backend(12345);    -- ask politely
SELECT pg_terminate_backend(12345); -- force

-- 9) Transaction ID (XID) wraparound
-- Postgres uses 32-bit XIDs that wrap around after ~2 billion transactions.
-- VACUUM 'freezes' old rows by replacing xmin with a special FrozenXID.
-- If wraparound approaches without freezing, Postgres SHUTS DOWN to prevent data loss.

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;
-- Tune autovacuum_freeze_max_age if approaching 200 million.

-- 10) Isolation levels + MVCC
-- READ COMMITTED (default)
--   Each STATEMENT sees a fresh snapshot. Repeatable issues possible.
--
-- REPEATABLE READ
--   Entire TRANSACTION sees same snapshot. Detects serialisation anomalies as errors.
--
-- SERIALIZABLE
--   Like REPEATABLE READ + ensures serial-equivalent ordering. May abort with 40001.

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- run queries
COMMIT;  -- may fail with 'could not serialize access'; retry the transaction

-- 11) Avoiding bloat
--   • Short transactions — commit often
--   • Avoid 'idle in transaction' for hours
--   • Bulk DELETE → consider partitioning + DROP partition instead
--   • HOT updates (heap-only tuple) when index fields not changed → less bloat
--   • Fillfactor < 100 leaves space in pages for HOT updates
ALTER TABLE orders SET (fillfactor = 80);

-- 12) pg_visibility — see which rows are visible to autovacuum
CREATE EXTENSION pg_visibility;
SELECT * FROM pg_visibility_map('users') LIMIT 10;

-- 13) Hot-standby + MVCC
-- Replicas read at lag; queries hold snapshot. Long queries on replica delay vacuum on primary
-- (replication slot keeps WAL alive). Watch hot_standby_feedback.

-- 14) Common bugs
-- • Long-running txn keeps bloat from being reclaimed → vacuum stuck; close transactions
-- • 'idle in transaction' connections from connection pool → set idle_in_transaction_session_timeout
-- • VACUUM FULL during peak → locks table; outage
-- • Bulk DELETE then SELECT immediately → dead tuples still visible to query plan
-- • Autovacuum starved by hot tables — increase autovacuum_max_workers
-- • Forgetting that UPDATE creates a NEW row — every column update on hot table doubles bloat
-- • Heavily-updated wide table — fillfactor + HOT updates required
-- • Transaction ID wraparound warnings ignored → emergency single-user mode
-- • Replicas blocking primary vacuum — adjust hot_standby_feedback / max_standby_streaming_delay
-- • Mistaking 'idle' for 'idle in transaction' — they're different; idle is benign

-- Each row has xmin/xmax. Writers don't block readers.
VACUUM (VERBOSE, ANALYZE) orders;
VACUUM FULL orders;  -- rebuilds table; locks it