PostgreSQL · Advanced interview

EXPLAIN (ANALYZE, BUFFERS) shows the plan tree with node types (Seq Scan, Index Scan, Nested Loop, Hash Join), estimated vs actual rows, timing, and buffer hits. You look for sequential scans on large tables, row-estimate mismatches (stale stats — run ANALYZE), expensive sorts spilling to disk (raise work_mem), and nested loops over large sets. The fix is usually a better index, rewritten predicate (sargable), or updated statistics — verified by re-running the plan.

The planner is cost-based: it estimates row counts and costs using statistics gathered by ANALYZE (value distributions, most-common-values, histograms, n_distinct). If stats are stale or a column is skewed/correlated, estimates go wrong and it picks bad plans (e.g. a nested loop over millions of rows). You fix this by running ANALYZE, increasing the statistics target for skewed columns, or adding extended statistics for correlated columns.

An index-only scan answers a query from the index alone when all needed columns are present and the visibility map shows the pages are all-visible, avoiding heap fetches. Bloat (dead tuples from MVCC) and an out-of-date visibility map force heap visits, undermining the optimization. Keeping autovacuum healthy maintains the visibility map so index-only scans stay effective — another reason bloat hurts read performance.

Postgres uses row-level locks (acquired by UPDATE/DELETE/SELECT FOR UPDATE) and table-level locks (e.g. ACCESS EXCLUSIVE taken by some DDL). Reads don't block writes thanks to MVCC, but conflicting writes to the same rows serialize, and DDL like ALTER TABLE can block all access briefly. Contention shows up as queries waiting on locks (pg_locks, pg_stat_activity); you reduce it with short transactions and careful, concurrent-friendly migrations (e.g. CREATE INDEX CONCURRENTLY).

Postgres uses 32-bit transaction ids to determine row visibility; if the oldest un-vacuumed transactions aren't frozen before the id counter wraps, data could appear to vanish — so Postgres forces aggressive anti-wraparound vacuums and ultimately refuses new transactions to protect data. You prevent it by ensuring autovacuum keeps up (especially on large/old tables), avoiding extremely long-running transactions that hold back the freeze horizon, and monitoring age(datfrozenxid).

A deadlock arises when two transactions each hold a lock the other needs; Postgres detects the cycle and aborts one with a deadlock error. SELECT ... FOR UPDATE takes row locks to implement safe read-modify-write (preventing lost updates), with NOWAIT or SKIP LOCKED variants for queue-like patterns. You avoid deadlocks by locking rows in a consistent order, keeping transactions short, and adding retry logic for the aborted one.

The primary streams its write-ahead log (WAL) to one or more standbys that replay it, giving near-real-time replicas for read scaling and failover. Asynchronous replication is fast but a standby can lag (potential data loss on failover); synchronous replication waits for a standby to confirm, guaranteeing durability at the cost of write latency. You route read-only traffic to replicas while accepting eventual consistency from lag.

Declarative partitioning splits a big table by range/list/hash into child partitions; the planner prunes to only relevant partitions, speeding queries and making bulk data lifecycle (dropping an old time partition) cheap. Gotchas: queries must include the partition key to benefit from pruning, indexes and constraints are per-partition, and too many partitions add planning overhead. It's most valuable for large time-series or clearly-partitionable data.

Each Postgres connection is a backend process with real memory overhead, so thousands of direct app connections exhaust resources and degrade performance. PgBouncer sits in front and multiplexes many client connections onto a small pool of server connections. Its modes (session, transaction, statement) trade feature compatibility for efficiency — transaction pooling is common and very efficient but disallows session-level features like prepared statements without care.

Monitor for bloat and lagging vacuums (dead tuple counts, last_autovacuum). On high-write tables, lower the autovacuum_vacuum_scale_factor (so it triggers sooner), increase autovacuum workers and cost limits so it runs faster, and set per-table storage parameters for hot tables. The goal is to reclaim dead tuples before bloat and wraparound become problems, balanced against the I/O autovacuum itself consumes.

A base backup (pg_basebackup) plus continuously archived WAL lets you restore to the base and replay WAL up to any moment — point-in-time recovery (PITR) — which is invaluable for recovering from an accidental delete or corruption. Logical backups (pg_dump) are portable, per-database/table snapshots good for migrations but slower to restore at scale. A solid strategy combines regular base backups, WAL archiving, and tested restores.

Run one or more standbys via streaming replication and use a tool like Patroni (with etcd/Consul) or repmgr to automate leader election and failover, promoting a standby when the primary fails, fronted by a virtual IP or a proxy/HAProxy so clients reconnect transparently. Synchronous standbys bound data loss (RPO); automated failover bounds downtime (RTO). You also fence the old primary to avoid split-brain and test failover regularly.