DEVPREP CODEX · #18

PostgreSQL Interview Questions

75 curated questions graded from Foundations (Easy) to Practical Patterns (Medium) and Internals & Architecture (Hard).

TOTAL QUESTIONS

THEORY QUESTIONS

IMPLEMENTATION FOLIOS

FREE QUESTIONS

5 / Level

Easy Level·25 Questions Total

Foundations & Core Concepts

Open Full Easy List

Q1What is PostgreSQL and what distinguishes it?

Advanced open-source object-relational DB known for standards compliance, extensibility (custom types/extensions), and correctness.
Standouts: rich indexing (B-tree/GIN/GiST/BRIN), full-text search, JSONB, window functions, MVCC without read locks, transactional DDL.
Single license (PostgreSQL License), no paid editions — feature parity everywhere.

Q2What is a primary key and how does Postgres implement it?

Uniquely identifies rows; PRIMARY KEY creates a unique B-tree index + NOT NULL implicitly.
One PK per table; choice of int/bigserial vs UUID trade-offs (size, ordering, fragmentation).
PK index backs fast lookups; clustered tables don't exist like SQL Server — heap storage with index-organized access via the index only.

Q3What are the main differences between CHAR, VARCHAR and TEXT?

CHAR(n): blank-padded fixed length (rarely desired).
VARCHAR(n): variable with limit enforced.
TEXT: unlimited — Postgres performance identical across all three; use TEXT + CHECK constraint for length when needed. Interview nugget: unlike other engines, there's NO performance penalty choosing TEXT.

Q4What does SERIAL vs IDENTITY vs UUID mean for keys?

SERIAL legacy pseudo-type creating sequence default; GENERATED ALWAYS AS IDENTITY SQL-standard preferred (protects against manual id inserts).
Sequences have gaps on rollback by design.
UUIDv4 random → index bloat/fragmentation on big tables; UUIDv7/time-ordered mitigates. Choose bigint identity for internal high-write tables.

Q5What is NULL and how do IS NULL / COALESCE work?

NULL = unknown/absent, not zero/empty string.
Comparisons yield NULL (WHERE col <> 'x' excludes NULLs!) — explicit IS NULL/IS NOT NULL required.
COALESCE(a,b,c) first non-null; NULLIF(a,b) returns null when equal (divide-by-zero guard pattern).

20 More Easy Questions Locked

Unlock the complete PostgreSQL Easy question bank

Get instant access to all 25 questions, in-depth model answers, code sandboxes, and all 27+ technologies for a single one-time payment.

Unlock All — One-time payment · Lifetime access

Medium Level·25 Questions Total

Mechanisms & Practical Patterns

Open Full Medium List

Q1Explain MVCC in Postgres and how it avoids read-write blocking.

Every row version carries xmin/xmax transaction ids; readers see snapshots where versions are committed-visible — writers create NEW versions instead of mutating in place.
Result: readers never block writers, writers never block readers.
Cost: dead tuple accumulation requiring VACUUM; long transactions hold back horizon causing bloat.

Q2What do xmin/xmax/cmin/cmax hide and how are they used practically?

System columns on every row: xmin (creating txid), xmax (deleting/locking txid).
Practical uses: optimistic concurrency checks (WHERE id=$1 AND xmin=$2 detecting concurrent modification without version column), debugging visibility issues.
Interview favorite: "how do you detect a row changed between SELECT and UPDATE" answered via xmin.

Q3What is VACUUM's full job and what is the free space map?

Removes dead tuples (marking space reusable in FSM), freezes old xids preventing wraparound, updates visibility map enabling index-only scans.
Aggressive vs regular vacuum thresholds; autovacuum workers tuned per table.
Long-running transactions pin the xmin horizon → bloat explosion — monitoring oldest-xid/backed-up-tuples essential.

Q4What is transaction ID wraparound and why does it cause outages?

xid is 32-bit comparing modulo wraparound space (~2^31 per epoch); Postgres must freeze rows (mark permanently-committed) before old xids risk future comparison ambiguity.
If wraparound approaches, autovacuum goes aggressive anti-wraparound mode; at limit, system REFUSES writes (single-user mode cleanup) — the famous outage class. Monitoring: datfrozenxid age alerts well before danger.

Q5Compare B-tree, GIN, GiST, BRIN and Hash indexes with use cases.

B-tree: equality/range/sort default.
GIN: inverted index for jsonb containment, arrays, full-text — fast lookups, slower updates.
GiST: extensible R-tree family — ranges (tstzrange overlap exclusion constraints), geometric, kNN distance ordering.
BRIN: block-range summaries for huge append-only time-series — tiny, fast builds.
Hash: equality-only, WAL-logged since v10 — rarely wins over btree. Exclusion constraints (GiST) power booking-no-overlap rules declaratively.

20 More Medium Questions Locked

Unlock the complete PostgreSQL Medium question bank

Get instant access to all 25 questions, in-depth model answers, code sandboxes, and all 27+ technologies for a single one-time payment.

Unlock All — One-time payment · Lifetime access

Hard Level·25 Questions Total

Internals & System Architecture

Open Full Hard List

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.

20 More Hard Questions Locked