On PostgreSQL 18, add a defaulted column and create or rebuild an index on a busy 2 TB table. Name the table and index locks, distinguish scans from rewrites, choose the online path, and define abort and cleanup conditions.
Two slogans get repeated about online schema changes and both are wrong in the same direction. Metadata-only is not lock-free, because a catalog change still needs exclusive access for the instant it takes, and on a busy table acquiring that access is the risk rather than holding it. Concurrently is not impact-free either: it takes a weaker lock, it runs much longer, and it leaves something behind when it fails. Interviewers ask this to hear whether you can name the specific lock for each statement rather than sorting operations into safe and unsafe. The other half is operational: what you watch while it runs, and what makes you stop. This page covers the lock taxonomy and the abort conditions.
What the interviewer is scoring
Weak answer: says CONCURRENTLY means no impact, calls every long validation a rewrite, or forgets that metadata-only ADD COLUMN still needs ACCESS EXCLUSIVE.
In short: Metadata-only is not lock-free, and concurrent is not impact-free. Predict the exact lock, rewrite, scan, wait, and cleanup behavior before running the change.
The spoken answer and the reasoning behind it
- The full 90-second answer, written first person, the way you would actually say it
- How I reason through it: the mechanism, the decision points, and where the claim stops
- Verification steps with a written summary of what a controlled lab run showed, labelled as a summary rather than a transcript.
- Worked responses to the 3 follow-up probes listed above, plus the traps that lose the point
Card required. Cancel before day 7 and you are not charged.
How to reason through it
- Inspect the exact DDL, default volatility, generated/identity/domain semantics, constraints, partitions, dependencies, and transaction context.
- State that ALTER TABLE ... ADD COLUMN takes ACCESS EXCLUSIVE on the table even when no rewrite is required; bound acquisition with lock_timeout.
- State that CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY take SHARE UPDATE EXCLUSIVE on the parent table during their documented phases; plain CREATE INDEX takes SHARE.
- Separate a constraint validation scan from a table rewrite; know which ADD COLUMN forms rewrite the table and indexes.
- Monitor locks and progress views; define stop conditions for lock queue, I/O, WAL, replica lag, and time window.
- Diagnose and clean an invalid concurrent index under the documented transaction, constraint, and partition restrictions.
What I would verify
- Set a short lock timeout so the statement fails rather than queueing behind long transactions.
- Watch the lock queue rather than only the statement; the damage is done by what is waiting behind it.
- Use the progress views to decide whether a long build is advancing or stalled.
- After a failed concurrent build, check for an index left behind in an invalid state.
- Define the abort thresholds for replica lag, WAL volume and wall-clock before starting.
Follow-ups they push on
- Adding a column is instant. Why bound it with a timeout?
- What exactly does a failed concurrent index build leave behind?
- When does adding a defaulted column rewrite the table?
The probes are open. Pro carries the spoken answer, the reasoning behind it, and the verification steps, including a worked response to each of these.
Concepts tested
Learn it, run it, then say it
Three steps, in order. Nothing here is a detour.
1 · Learn the mechanism
Understand it before you try to say it.
2 · Practise it for real
Run it once so the answer describes something you have seen.
3 · Rehearse the next question
Keep going while the mechanism is fresh.
Questions that go with this one
- senior · ProA query became slow after a deploy. Show a safe production triage that separates estimate error, execution or I/O cost, lock waits, and a cleanup horizon. Then explain what VACUUM can reclaim, how freezing protects availability, and how you would prove physical bloat.The other side of a schema change: what it did to the plans afterwards.
- staff · ProYou must cut a write workload from PostgreSQL 14 to 18 with logical replication. Where is the rollback boundary, and how do you prove the subscriber caught the source stop LSN?The same change-safety reasoning at the largest scale it gets asked at.