PostgreSQL

MVCC allows concurrent reads and writes without blocking by keeping multiple versions of every row. When a row is updated, PostgreSQL: 1. Writes a new tuple with xmin = current transaction ID. 2. Sets xmax on the old tuple = current transaction ID (marks it deleted). Readers use a snapshot — a list of transaction IDs visible to them. The snapshot rules: a tuple is visible if its xmin committed before the snapshot and its xmax is either not yet committed or not in the snapshot. This means old and new versions coexist on disk — readers see the version that was current when their transaction started. Dead tuples are old versions no longer visible to any active snapshot. They waste space and slow down index scans (they appear in indexes but require a heap fetch to confirm visibility). VACUUM scans the table, identifies dead tuples (those with xmax below the oldest active transaction's snapshot), marks their space as reusable, and removes their index entries. It does not compact the file by default (that's VACUUM FULL). Regular VACUUM runs concurrently with reads and writes.

PostgreSQL implements three distinct levels (READ UNCOMMITTED maps to READ COMMITTED): READ COMMITTED (default): each SQL statement sees a fresh snapshot as of when that statement started. Within a single transaction, two reads of the same row can return different values if a concurrent transaction committed between them (non-repeatable read). Safe for most OLTP operations where each statement is self-contained. REPEATABLE READ: snapshot taken at the first statement of the transaction. All subsequent reads within that transaction see the same data. Prevents non-repeatable reads and phantom reads (PostgreSQL's implementation goes beyond the SQL standard here). Serialization failures possible on write conflicts (ERROR 40001). SERIALIZABLE: uses Serializable Snapshot Isolation (SSI). Detects read-write dependencies between concurrent transactions that would produce a non-serializable execution. Aborts one transaction with ERROR 40001 — serialization_failure rather than allowing write skew. Application must retry on this error. Overhead is higher but correctness is guaranteed for any concurrent access pattern. Choosing: READ COMMITTED for most web OLTP; REPEATABLE READ for long transactions reading a consistent view (reports, exports); SERIALIZABLE for financial transactions, inventory deductions, or any "check then act" pattern.

The planner estimates the cost of each candidate plan and picks the lowest. Cost has two components: I/O cost (proportional to pages read) and CPU cost (proportional to rows processed). Key parameters: - seq_page_cost = 1.0 (baseline) - random_page_cost = 4.0 (default) — random I/O costs 4× sequential - cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost When the planner chooses Seq Scan over an existing index: it estimates that the fraction of rows returned is large enough that fetching them via random index lookups (each requiring a heap page fetch) costs more than scanning the whole table sequentially. Wrong plan causes: - Stale statistics: n_distinct, MCV list, histogram are outdated — run ANALYZE. - Wrong random_page_cost: on SSDs, random I/O is nearly as cheap as sequential. Set random_page_cost = 1.1 to make the planner correctly prefer index scans. - Correlation mismatch: data inserted in random order won't benefit from a BRIN index or have good correlation for index-friendly access patterns. - Missing ANALYZE after bulk load: newly loaded tables have no statistics. - High work_mem for the query causing a hash join to be preferred over a nested loop.

Use GIN when you need to search within a composite value — the opposite of B-tree, which searches by the whole value. Primary GIN use cases: - tsvector full-text search (@@ operator) - JSONB containment (@>) and key existence (?, ?|, ?&) - Array overlap/containment (&&, @>, <@) Full-text search setup: ```sql -- Add a tsvector column (or use a generated column in PG12+) ALTER TABLE articles ADD COLUMN fts tsvector GENERATED ALWAYS AS ( to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')) ) STORED;

CREATE INDEX articles_fts_idx ON articles USING gin(fts); -- Query SELECT id, title FROM articles WHERE fts @@ plainto_tsquery('english', 'postgresql indexing') ORDER BY ts_rank(fts, plainto_tsquery('english', 'postgresql indexing')) DESC LIMIT 20; `` GIN builds an inverted index: for each lexeme, it stores all row IDs containing it. Lookups are fast; writes are slower than B-tree (batched via pending list, flushed atgin_pending_list_limit). For write-heavy tables, considerfastupdate=off` if GIN pending list is causing I/O spikes.

WAL (Write-Ahead Log) is an append-only sequential log of every change to the database. The WAL guarantee: a change is never applied to a data page until its WAL record is durably written to disk first (fsync). This means: Crash recovery: on restart after a crash, PostgreSQL replays WAL from the last checkpoint forward. Any change that has a WAL record is guaranteed to be recoverable, even if the data page was never flushed to disk. The database reaches a consistent state without any data loss for committed transactions. Physical (streaming) replication: the primary ships WAL segments to standby servers. Each standby replays the WAL, maintaining a byte-for-byte copy of the primary. Standbys can serve read queries (hot standby). Replication lag = WAL on primary not yet replayed on standby. Logical replication: WAL Decoder (wal_level = logical) reads the WAL and converts it into row-level changes (INSERT/UPDATE/DELETE). Enables selective table replication, cross-major-version replication, and CDC (Change Data Capture) for downstream systems (Debezium reads PostgreSQL's logical replication slot).

Autovacuum is PostgreSQL's background daemon that runs VACUUM and ANALYZE on tables automatically when they accumulate enough dead tuples (autovacuum_vacuum_scale_factor × row count + autovacuum_vacuum_threshold dead tuples). It also triggers ANALYZE for statistics refresh on modified tables. What happens when it falls behind: - Dead tuples accumulate → table bloat → larger sequential scans, more I/O - Indexes accumulate dead entries → index bloat → slower index scans - xmin horizon stalls → transaction ID wraparound risk (the most dangerous outcome) - PostgreSQL runs emergency autovacuum (to prevent wraparound) which is more aggressive and has higher priority but can still be outrun on extremely write-heavy tables

Tuning for high-write tables: sql ALTER TABLE orders SET ( autovacuum_vacuum_scale_factor = 0.01, -- vacuum after 1% dead tuples (not default 20%) autovacuum_vacuum_cost_delay = 2, -- less I/O throttling (ms, default 20) autovacuum_vacuum_cost_limit = 800 -- more work per cycle (default 200) ); Also increase autovacuum_max_workers (default 3) for databases with many large tables.

PostgreSQL's transaction IDs (XIDs) are 32-bit integers cycling from 0 to ~2.1 billion. Each new transaction gets a new XID. MVCC visibility is determined by XID comparison — whether a tuple's xmin is "in the past" relative to the current transaction. The problem: after ~2.1 billion transactions, XIDs wrap around. A row with xmin that was in the past becomes "in the future" — it becomes invisible. The entire database would appear empty. PostgreSQL prevents this by freezing old tuples: replacing their xmin with a special FrozenTransactionId that is always considered "in the past." VACUUM does this when a row is old enough (vacuum_freeze_min_age transactions old). Emergency threshold: when the oldest unfrozen XID is within 40M transactions of wraparound, PostgreSQL runs autovacuum in emergency mode. If it can't finish in time, PostgreSQL shuts down with the message "database is not accepting commands to avoid wraparound data loss." Prevention: monitor age(datfrozenxid) in pg_database. Alert above 1.5 billion. Ensure autovacuum runs regularly on all tables. Don't suppress autovacuum on large tables without an alternative.

Start from the queries, not the columns. For each query pattern: 1. Identify the predicate columns (WHERE, JOIN ON) — these must be in the index. 2. Identify the sort columns (ORDER BY) — B-tree indexes can satisfy ORDER BY, eliminating a sort step. 3. Identify the output columns (SELECT) — if they fit in the index, you get an Index Only Scan (no heap fetch).

Multi-column index column order matters: - Equality predicates first, range predicates last (B-tree can use a range only on the last column in a multi-column index). - (status, created_at) serves WHERE status = 'pending' ORDER BY created_at well. Reverse order serves neither well. - A query using only created_at cannot use (status, created_at) — but can use (created_at, status). Design for your most selective queries first.

Covering indexes for hot queries: Add INCLUDE (col1, col2) to include non-predicate output columns in index leaf pages. Index Only Scans avoid heap entirely — massive speedup for queries selecting a few columns from large tables. Partial indexes for selective data: CREATE INDEX ON orders (user_id) WHERE status = 'active' is tiny and fast for an orders table where 99% of rows are closed. The query must include WHERE status = 'active'. Avoid over-indexing: each index is updated on every write. A table with 12 indexes pays a significant write penalty. Remove indexes with zero scans (pg_stat_user_indexes where idx_scan = 0) after a representative period.

When to partition: - Table size makes VACUUM, REINDEX, or backup/restore operationally painful (rough threshold: >100 GB or >500M rows) - You need to drop old data efficiently — DROP or DETACH a partition is instant, DELETE on a huge table is slow and bloating - Queries reliably filter on the partition key, enabling partition pruning - You want separate storage, autovacuum tuning, or tablespaces per partition Range partitioning for time-series data is the most common and valuable use case: sql CREATE TABLE events ( id bigint, created_at timestamptz, payload jsonb ) PARTITION BY RANGE (created_at); CREATE TABLE events_2024_01 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); Use pg_partman to automate partition creation and retention. Operational trade-offs: - Pruning requires the partition key in the WHERE clause — queries without it scan all partitions (worse than a single large table). - Cross-partition queries (aggregations, sorts) are more expensive — each partition is scanned separately and results merged. - Foreign keys referencing the parent table are not supported in PostgreSQL. - Global unique constraints are not supported — unique constraints must include the partition key. - Index maintenance: each partition has its own indexes. REINDEX CONCURRENTLY must be run per partition.

Streaming replication (physical): the primary continuously sends WAL records to standby servers. Standbys replay WAL, maintaining a byte-for-byte identical copy. Standbys can serve read queries (hot_standby = on). Failover promotes a standby to primary. Works at the storage level — same PostgreSQL major version required. Replication slots: guarantee WAL is retained on the primary until the standby has consumed it. Prevents standby falling too far behind and being unable to catch up. Risk: if the standby disconnects long-term, WAL accumulates and fills the disk. Monitor pg_replication_slots.confirmed_flush_lsn and the lag in bytes. Logical replication: decodes WAL into row-level changes (INSERT/UPDATE/DELETE) and sends only selected tables to a subscriber. Subscriber can be a different major version, a different database, or a downstream analytics system. Used for: zero-downtime major version upgrades, selective replication, CDC with Debezium. Choosing: - Primary + read replicas for load distribution → streaming - Failover / HA → streaming with a standby promoted on failure (Patroni manages this) - Major version upgrade (PG14 → PG16) without downtime → logical (replicate to new version, switchover) - CDC to Kafka or a data warehouse → logical (via Debezium or pg_logical) - Cross-cloud or cross-region async replica → streaming

Step 1 — Identify the query: pg_stat_statements (extension) aggregates queries by normalized form. Query by total_exec_time (total time across all calls) to find high-impact queries, not just the slowest individual call. sql SELECT query, calls, mean_exec_time, total_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; Step 2 — Get the plan: run EXPLAIN (ANALYZE, BUFFERS) against the query on a replica or during low traffic. Look for: - Seq Scan on large tables (missing index) - Estimated rows far from actual rows (stale statistics → run ANALYZE) - High Buffers: read (reading from disk rather than cache — I/O bound) - Sort step that could be eliminated by an index Step 3 — Check for locks: if the query is fast in isolation but slow in production, lock contention may be the cause. sql SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event IS NOT NULL; Step 4 — Fix without downtime: - Add an index: CREATE INDEX CONCURRENTLY — does not lock the table. - Update statistics: ANALYZE tablename — fast, concurrent. - Rewrite the query: avoid NOT IN with subqueries, use EXISTS or JOIN instead. - Fix random_page_cost if index scans are being avoided on SSD. Step 5 — Verify: compare EXPLAIN ANALYZE before and after. Check pg_stat_statements for reduction in mean_exec_time and total_exec_time.

RPO = 0 requires synchronous replication — the primary waits for at least one standby to acknowledge each WAL write before returning to the client. No committed transaction can be lost even if the primary fails. RTO = 30s requires automated failover with health checking and promotion. Architecture: - 3-node cluster: 1 primary + 2 synchronous standbys (or 1 sync + 1 async as a witness). With 2 sync standbys, the primary can commit as long as 1 acknowledges — tolerates 1 standby failure without blocking writes. - Patroni with etcd (3-node) as the DCS. Patroni runs on each PostgreSQL node, holds a leader lock in etcd, and automatically promotes the most up-to-date standby when the primary fails or becomes unhealthy. etcd provides fencing — the old primary loses the lock and stops accepting writes (STONITH via pg_ctl or watchdog). - HAProxy / PgBouncer in front for connection routing: writes to port 5000 (primary), reads to port 5001 (any standby). Patroni updates HAProxy health check endpoints automatically. - PgBouncer in transaction mode on each application server — keeps connection count manageable. Failover sequence: 1. Patroni detects primary is down (health check TTL ~10 s). 2. etcd leader lock expires, candidate standbys race to acquire it. 3. Most advanced standby (highest LSN) wins and promotes itself. 4. Patroni notifies HAProxy to reroute traffic. 5. Old primary rejoins as standby when it recovers (rewinds with pg_rewind). Monitoring: replication lag in bytes and seconds, etcd health, Patroni API status, checkpoint frequency, lock wait events.

Naive approach is wrong: ALTER TABLE ADD COLUMN col type NOT NULL DEFAULT value in PostgreSQL < 11 rewrites the entire table (ACCESS EXCLUSIVE lock for hours). In PG11+ it's instant for constant defaults — but only for simple scalar constants, not function calls. Safe approach (all versions): Step 1 — Add nullable column, no default, no lock: sql ALTER TABLE orders ADD COLUMN metadata jsonb; -- ACCESS EXCLUSIVE lock held for milliseconds, then released Step 2 — Backfill in small batches (background job, not a single UPDATE): sql UPDATE orders SET metadata = '{}' WHERE id BETWEEN :lo AND :hi AND metadata IS NULL; -- Commit each batch. Keep batches small (1000–10000 rows). -- Pause between batches to let autovacuum keep up and avoid replication lag. A single UPDATE of 500M rows generates enormous WAL, pins replication lag, bloats the table, and may take hours while holding a transaction. Batching avoids all of this. Step 3 — Add NOT NULL constraint as NOT VALID (PG12+): sql ALTER TABLE orders ADD CONSTRAINT orders_metadata_not_null CHECK (metadata IS NOT NULL) NOT VALID; -- Instant — does not scan existing rows. Step 4 — Validate the constraint (concurrent, no write lock): sql ALTER TABLE orders VALIDATE CONSTRAINT orders_metadata_not_null; -- Scans the table, but holds only SHARE UPDATE EXCLUSIVE (concurrent reads/writes OK). Step 5 — (Optional) If you truly need a NOT NULL column (PG12+), after validation you can drop the CHECK constraint and convert — or live with the check constraint, which the query planner understands as equivalent.