Database Administrator (DBA) interview questionsTechnical Interview round

Purpose

Not scored. Makes the page a system the candidate answers for, not a quiz on PostgreSQL. If they ask about something the page does not say, give the most ordinary answer and note that they asked.

Purpose

Online schema change, read on a file the candidate did not write. Lock names matter less than what follows from them on this table at 11:00 on a Tuesday; the separator is seeing the transaction around the file, and the session already holding a lock, before reaching for CONCURRENTLY.

Signals to score

• Notices the file is one transaction, so the first ALTER's ACCESS EXCLUSIVE lock on `shipments` lasts to the end
• Spots pid 48211 holding a lock on `shipments` since Friday: the first ALTER queues behind it, and every later query behind the ALTER
• Gives each DDL step a short `lock_timeout` and a retry, so a waiting statement gives up instead of stalling the queue
• Says the constant default on `customs_status` is catalog-only on PostgreSQL 15 and leaves it alone
• Says `gen_random_uuid()` is volatile, so `customs_ref` rewrites the table and its indexes, and adds the column without a default first
• Asks what the index is for and proposes a partial index on `customs_status = 'held'`
• Builds the index CONCURRENTLY outside a transaction, and knows a failed build leaves an invalid index to drop
• Adds the foreign key NOT VALID and validates it separately, after looking for `carrier_id` values with no carrier
• Turns the 58-million-row UPDATE into a batched backfill, run before the index exists and paced against replication lag and vacuum
• Ends with an order of steps, saying which can run at 11:00 and which belong off-peak

Follow-up questions

• `customs_status` and `customs_ref` both add a NOT NULL column with a default. Do they cost the same?
• The first ALTER has been waiting for twenty seconds and has changed nothing. What is the API doing?
• The new screen shows parcels held at customs. What index does it need?
• There has never been a constraint on `carrier_id`. What do you check before adding one?
• Which of your steps would you run at 11:00 on a Tuesday, and which not?

Purpose

For a candidate who knows what "dead but not yet removable" means, that log line is most of the answer. The separator is sorting the page's three possible holders of the horizon by what each can actually hold back, and saying what fixing `db3` costs the analysts.

Signals to score

• Reads "dead but not yet removable" as something holding back the cleanup horizon, not as autovacuum running too rarely
• Names pid 48211's open transaction as blocking cleanup database-wide since Friday, and sets `idle_in_transaction_session_timeout`
• Ties `hot_standby_feedback` and the hours-long reports on `db3` to the growth since April, using the dates on the page
• Names the choices for `db3` — feedback off with a longer `max_standby_streaming_delay`, a cap on report length, or a separate copy — and what each costs
• Rules out `warehouse_cdc` as a cause of this bloat, because a logical slot holds back only the system catalogs
• Works out that the default scale factor waits for about 38 million dead rows here, roughly three days' worth, and sets per-table thresholds
• Gives autovacuum a higher cost limit and more memory so runs over 255 GB finish sooner, and says why each helps
• Says plain VACUUM makes space reusable without shrinking the table, so fixing the cause stops the growth
• Rules out VACUUM FULL on a live table and, if the space must come back, rebuilds online after the horizon is fixed and the disk has room
• Asks which columns the 8 indexes cover, because an update that changes an indexed column cannot be HOT

Follow-up questions

• Autovacuum ran for almost four hours on Sunday. What stopped it removing those 20 million rows?
• `hot_standby_feedback` went on in April. What has it been doing to `db1` since?
• Has `warehouse_cdc` got anything to do with this table?
• With the settings as they are, how many dead rows does `shipments` collect before autovacuum starts?
• Do you need the 88 GB back, and what would getting them back cost?

Purpose

Tests whether the candidate knows what a replication slot promises, what that costs when nobody reads from it, and whether they can make an unwelcome decision together with the team it affects. The arithmetic is on the page.

Signals to score

• Names `warehouse_cdc` as the cause: a slot keeps every WAL segment from its `restart_lsn`, whether anything reads from it or not
• Works out the runway — about 0.8 TB free at about 140 GB a day, under six days — and shortens it for a migration that rewrites a table
• Knows PostgreSQL stops when it cannot write WAL, and that here that means a failover
• Refuses to delete files from `pg_wal` or to restart the server to free space
• Sets out the two real options — resume the connector and let it catch up, or drop the slot and re-snapshot — and what each costs
• Knows a caught-up logical slot still holds the WAL since the oldest open transaction began, so ends 48211 first
• Takes the choice to the warehouse team with the date the disk fills and a deadline, rather than dropping the slot unannounced or waiting indefinitely
• Treats growing the volume as time bought, not as the fix
• Sets `max_slot_wal_keep_size` and says its cost: a consumer or replica further behind than the cap loses its slot
• Adds alerts on WAL retained per slot and on inactive slots, and checks that the `db2` and `db3` slots are healthy

Follow-up questions

• Nobody has read from `warehouse_cdc` since Wednesday. Why is it still costing disk?
• When does the volume fill, and what happens to `db1` at that moment?
• Why not delete the oldest files in `pg_wal`?
• What does dropping the slot cost the warehouse team, and who decides?
• How do you make sure no slot does this again?

Purpose

A candidate who has been through a failover lists what it leaves behind before being asked — the old primary, the other replica, the slots, the commits that never arrived. One who has only configured a failover manager tends to stop at "the application is back".

Signals to score

• Makes sure `db1` is powered off or fenced first, because a server that misses health checks may still accept writes
• Says asynchronous replication means commits `db2` had not received are gone, and sizes them from the lag and the load
• Knows `db2` replays all the WAL it received before accepting writes, so the loss is what it never received, not what it had not replayed
• Finds the lost transactions in `db1`'s WAL past `db2`'s timeline switch point, if `db1`'s disk can be read, and gives the application team the window
• Notices `db3` still follows `db1` and serves a frozen copy, and points it at `db2` with a new slot there
• Knows `db3` can follow `db2` only if it has not gone further along `db1`'s history than `db2` did, and otherwise rebuilds it
• Knows `warehouse_cdc` did not survive the promotion on PostgreSQL 15, so the warehouse must re-snapshot
• Will not start `db1` as it was, and rebuilds it from `db2` because `pg_rewind` needs `wal_log_hints` or data checksums
• Treats the cluster as having no automatic failover target until `db1` is back, and schedules the rebuild's load on `db2`
• Puts synchronous replication to the business as a choice with a cost on every commit, rather than adopting or dismissing it

Follow-up questions

• `db1` missed its health checks. How do you know it is not still taking writes?
• How much did we lose, and how would you find out exactly what?
• What is `db3` doing right now?
• What happened to `warehouse_cdc`?
• How do you get back to three servers, and how long will it take?