5 exercises — choose the best-structured answer to DBRE interview questions. Focus on PostgreSQL replication, failover, query performance, connection pooling, and live migrations.
What separates good from great DBRE answers
Name the lock type: "AccessExclusiveLock" beats "it locks the table"
Explain the failure mode: what specifically breaks and why
Multi-root-cause thinking: slowness can be plan, statistics, or contention
PostgreSQL internals: process-per-connection, WAL, catalog storage all matter
0 / 5 completed
1 / 5
The interviewer asks: "How do you set up and validate PostgreSQL streaming replication?" Which answer demonstrates hands-on experience?
Option B is the strongest: names specific configuration parameters (wal_level, max_wal_senders, replication slots), explains each component's purpose (slots prevent premature WAL recycling), gives the exact monitoring view (pg_stat_replication) with the specific columns to watch, and warns about a real production failure mode (stuck slot exhausting disk). This is the answer of someone who has debugged replication in production. Option A is technically accurate but a textbook summary. Option C is vague about validation. Option D mentions pg_stat_replication but gives no insight into what to look for or why.
2 / 5
The interviewer asks: "Walk me through a PostgreSQL failover. What can go wrong?" Choose the most complete answer.
Option B is the strongest: introduces a memorable three-phase structure (fence → promote → redirect), explains split-brain precisely and why fencing comes first, gives the specific WAL check commands (replay_lag, pg_last_wal_replay_lsn), names the exact promotion command (pg_ctl promote or pg_promote()), and lists three concrete failure modes with their mechanisms. This answer shows someone who has planned and executed real failovers. Option A describes the happy path without any failure modes. Option C mentions Patroni but does not explain the underlying mechanics. Option D is correct but shallow — naming "replication lag" as a problem without explaining the data loss mechanism misses the key insight.
3 / 5
The interviewer asks: "How do you diagnose a slow query in PostgreSQL?" Which answer shows the most systematic approach?
Option B is the strongest: establishes a top-down funnel (aggregate visibility first via pg_stat_statements, then drill down with EXPLAIN), names the exact EXPLAIN flags and what each reveals (BUFFERS for disk I/O, ANALYZE for actual vs estimated rows), correctly identifies stale statistics as the cause of plan divergence, and adds the crucial dimension of wait events — showing that slowness can be contention-based not plan-based. This multi-root-cause thinking is what separates DBREs from developers. Option A is the most common answer but too narrow. Option C adds autovacuum but as a checklist, not a framework. Option D mentions the right tools but without the diagnostic reasoning chain.
4 / 5
The interviewer asks: "What is connection pooling and why is it critical for PostgreSQL at scale?" Choose the strongest explanation.
Option B is the strongest: explains the PostgreSQL-specific mechanism (process-per-connection vs thread-per-connection), quantifies the memory cost (~5-10 MB per backend), explains how PgBouncer's transaction mode works mechanically, names the real trade-off (transaction mode breaks LISTEN/NOTIFY and prepared statements), and states the practical target (max_connections 100-300 regardless of application thread count). This is a genuinely expert answer. Option A is correct but has no depth. Option C mentions the three modes and names the trade-off correctly but does not explain the PostgreSQL process model that makes pooling necessary. Option D is vague. Strong DBRE answers explain PostgreSQL's internals, not just the tool.
5 / 5
The interviewer asks: "How do you approach zero-downtime schema migrations on a large table?" Which answer is the most operationally mature?
Option B is the strongest: names the exact lock type to avoid (AccessExclusiveLock), explains the PostgreSQL 11+ optimisation for NOT NULL with defaults (catalog storage, no rewrite), describes CREATE INDEX CONCURRENTLY with its trade-offs, and most importantly explains the expand/migrate/contract pattern with the specific NOT VALID + VALIDATE CONSTRAINT technique — a genuinely advanced approach that minimises lock duration. The governing principle at the end (never hold AccessExclusiveLock for more than milliseconds) is a memorable rule. Option A is naive — transactions don't help for DDL on live tables. Option C lists tools and practices correctly but has no understanding of lock types or the expand/contract pattern. Option D is correct on CREATE INDEX CONCURRENTLY but "PostgreSQL is usually fast" for columns reveals a misunderstanding of when table rewrites occur.