POSTGRESQL · HARD CODEX

PostgreSQL Interview Questions — Hard

Deep runtime internals, memory models, distributed design, concurrency failure modes, and architectural decisions.

25 Theory Questions5 Free Model Answers

THEORY QUESTIONS & SOLUTIONSShowing 5 of 25 questions

Q1Detail the visibility check: how does a tuple decide if it's visible to a snapshot?

Tuple headers store xmin/xmax with hint bits; snapshot holds xmin-horizon, xmax, in-progress xid array.
Visible when inserting txn committed before snapshot AND (no deleter OR deleter uncommitted/not-visible).
Hint bits cache commit status avoiding clog lookups; clog slru pages matter for cold reads.
Frozen rows (xid=FrozenTransactionId semantics via heap_new_infomask bits) visible to ALL snapshots enabling wraparound safety.

Q2What is HOT update and which conditions disable it?

Heap-Only Tuple updates skip index insertion when NO indexed column changed AND page has free space — following versions found via chain from old index entries.
Disablers: touching indexed columns, full pages (fillfactor exhausted) forcing new page.
Consequences: HOT-heavy workloads dramatically reduce index write amplification — fillfactor tuning per update-pattern table is the lever. Diagnose: n_tup_hot_upd vs total ratio in pg_stat_user_tables.

Q3Explain SSI internals: SIREAD locks and dangerous structures.

Serializable transactions take predicate-level SIREAD locks (coarse: page/table granularity) recording READS without blocking writers; writers acquiring tuples check rw-conflicts against concurrent SIREAD holders.
Dangerous structure = two consecutive rw-antidependencies in the dependency graph forming potential pivot cycle → one participant aborted (serialization_failure) even without physical conflict.
False positives possible (conservative); retry discipline mandatory application-side. Contrast vs SQL Server range locks: abort-based optimism vs blocking pessimism.

Q4What does WAL contain at record level and why is full_page_writes essential?

Records: page references, per-change payloads (insert tuple bytes, delete xmax), commit/abort markers with timestamps; sequence-ordered by LSN.
full_page_writes: first touch of a page post-checkpoint logs ENTIRE page image — guards torn-page partial writes making crash recovery deterministic.
wal_compression shrinks FPW records; checkpoint tuning (completion spread) smooths FPW bursts. Recovery = redo from last checkpoint replaying WAL — durability's mechanical truth.

Q5How does logical decoding produce changes, and what are slot lifecycle hazards?

Background walsenders decode WAL via output plugins (pgoutput/test_decoding) reordering by transaction; replication slots pin restart_lsn preventing WAL recycling for disconnected consumers.
Hazards: abandoned slots → disk-full outage; invalid slots after max_slot_wal_keep_size exceed; catalog xmin horizon affecting autovacuum on catalogs.
Ops guardrails: lag byte alerts, heartbeat tables keeping idle consumers advancing, scheduled slot audits.

Unlock the remaining 20 PostgreSQL (Hard) questions

You've completed the 5 free sample questions. Get unrestricted lifetime access to every question, model answer, implementation challenge, and all 27+ technologies for a single payment.

Unlock Everything — Already Enrolled? Sign In

₹399 India / $9 International · One-time settlement · Zero subscription

CROSS-DIFFICULTY NAVIGATION

Continue Preparing PostgreSQL

EASY LEVEL

Foundations & Core Concepts

MEDIUM LEVEL

Mechanisms & Practical Patterns