Interview guides · 5th Axis

5th Axis Software Engineer Interview Questions & Guide 2026

5th Axis software engineer prep on operations: decide what you would need to see to know a release is going wrong.

The 60-second brief

The Software Engineer role at 5th Axis is a pivotal position focused on bridging the gap between high-precision manufacturing and software-driven efficiency. You will be instrumental in developing systems that support the company’s core mission of engineering excellence, directly influencing how the team scales its operations and maintains its competitive edge in the machining industry.

Find out what you will be typing into. A shared plain-text editor with no autocomplete, compiler or test runner changes what you have to hold in your head, and practising inside your own configured environment hides exactly that gap.

Focus areas

Bound every outbound call with a timeout
Paginate large result sets with keyset cursors
Detect concurrent edits instead of losing writes

Overview

This role is unique because it operates at the intersection of software development and physical engineering. You will not just be writing code; you will be solving real-world problems that directly impact the daily operations of a sophisticated machine shop. Success in this role requires a blend of technical proficiency and a genuine curiosity about how software can optimize industrial workflows.

The 5th Axis interview process

5th Axis candidates report 2 rounds · ≈ 2-4 weeks. The stages below are what candidates describe, not a published process.

Initial Screening

The person on this call usually cannot evaluate your code and does not need to. They write a short paragraph, and that paragraph is what a hiring manager skims when deciding who to put on your loop. So the test is not whether your work was hard, it is whether a non-engineer can repeat it correctly. Name systems by what they did rather than by their internal codename, give each project a shape (what was breaking, what you changed, what happened after), and keep the whole walkthrough near ninety seconds. Depth that cannot survive a paraphrase reads as vagueness.

What to demonstrate

Whether a non-engineer can restate your projects without distorting them, since their paraphrase is what travels to the hiring manager, not your sentences
Whether each project has a shape rather than a stack list: the failure or constraint, the change you made, the result and how it was measured
Whether you can say what was yours inside a team project without either inflating it or disappearing into the plural

How to prepare

Rewrite each headline project as two sentences with no internal system names and no acronyms outside your company, then say them to someone outside engineering and have them repeat them back. Fix whatever came back wrong
Attach one measured number to each project: the baseline, the change, and the window it was measured over. Where nothing was ever measured, say that plainly rather than reaching for a plausible percentage
Time the background walkthrough against a clock. If it runs past two minutes, compress the earliest role to a single clause and spend the recovered time on the most recent one

In-Person Engagement

An unlabelled round is first an information problem, and the cheapest information is free. Whoever schedules it can usually tell you how long it runs, who will be in the room and what they work on, whether you will be writing code and in what environment, and whether anything is being sent beforehand. Ask in writing so the answer is on record, then prepare for the two or three formats those answers still leave open instead of betting on one. What separates a strong candidate is not guessing right; it is having an opening that works whichever one it turns out to be.

What to demonstrate

Whether you can start work from an ambiguous brief, since tolerating a vague scope without stalling is the same thing the job asks for
Whether the questions you asked beforehand were ones that change your preparation, such as duration, medium and who is joining, rather than ones whose answers you could not have acted on
Whether you adapt when the round turns out to be something other than what you were told, instead of spending the first ten minutes visibly recalibrating

How to prepare

Send one short scheduling message asking four things: how long, who is joining and what they work on, whether you will be writing code and where, and whether to prepare anything in advance. Treat a vague reply as real information, since it means the round is loosely structured and you will be shaping it yourself.
Write one opening that works in any of the formats still open: restate in your own words what you have been asked to do, then ask which of two directions is more useful to them. Say it aloud until it stops sounding recited.
Set up for the two most likely formats before the call starts, with a blank editor in the language you would choose and a shared document you can type into, so a format surprise costs you nothing in the first minutes

Where candidates lose points

Shipping a migration and the code that depends on it as a single change

During any rolling deploy, and for as long as a rollback remains possible, old and new code execute against the same schema at the same time. A migration that drops or renames a column breaks every instance that has not restarted yet, and code that requires a column the migration has not applied breaks every instance that restarted early. The discipline is expand then contract: add the new column nullable, write both shapes, backfill in batches, move reads across once the backfill is verified, and only then stop writing the old shape and drop it - four deploys, usually spread over days. It feels disproportionate until the first rollback, at which point it is the only reason the previous version still runs.

Running a schema change as though the lock lasts as long as the statement

In PostgreSQL an ALTER TABLE that needs an ACCESS EXCLUSIVE lock must first wait for every open transaction touching that table, and while it waits, later queries needing a conflicting lock queue behind it rather than overtaking it. A DDL statement that would execute in milliseconds, issued while a thirty-second analytics query is open, therefore stalls all traffic on that table for thirty seconds: the outage length is set by the longest open transaction, not by the change. The defences are specific and worth knowing by name - set lock_timeout low and retry rather than queue, add columns without a volatile default so no table rewrite occurs (from version 11 a non-volatile default is a metadata-only change), build indexes with CREATE INDEX CONCURRENTLY while accepting that it cannot run inside a transaction block and leaves an invalid index behind if it fails, and add constraints as NOT VALID followed by a separate VALIDATE CONSTRAINT, which takes a weaker lock.

Finishing a solution without stating its complexity

Give time and space in the same breath as the code, and define n explicitly when there are two sizes, since n nodes and m edges are not interchangeable. Space is the half that gets skipped: count the auxiliary structures you allocate and the recursion stack at its deepest, not only the answer you hand back.

Tests that assert on the implementation rather than the behaviour

Assert on what a caller can observe, not on the number of internal calls or the shape of a private field. A test that breaks on every refactor but still passes when the answer is wrong costs more than it protects.

Questions & practice

Archive a resource graph without breaking live references or recursing

Medium · graph traversal · topological order · tenant isolation

Resources reference other resources within a tenant; for the largest tenant the reference table holds up to 2,000,000 nodes and 8,000,000 edges. Archiving a resource must archive everything reachable from it that nothing outside the set still references, refuse when a live external referrer exists, and terminate when references form cycles, which they legitimately do. Produce the archive order and the refusal list, targeting O(V+E). Say what stops the traversal crossing a tenant boundary, and why recursion is the wrong control structure at this size.

Approach

Load the subgraph with the tenant predicate on both endpoints of the edge, not only on the side you started from. Scoping the left table alone is the classic cross-tenant leak: one mis-entered edge then pulls another tenant's resources into the traversal and, worse, into the archive.
Traverse iteratively with an explicit stack. A 2,000,000-node graph can hold a chain deep enough to exhaust a native stack in the low tens of thousands of frames, and that failure is a process crash rather than an error you can return.
Treat cycles as data rather than corruption: compute strongly connected components with Tarjan in O(V+E) using its own explicit stack, then condense. The condensation is a DAG, so a topological order over it gives the archive order, and every member of a component archives in one transaction because no order within a cycle is valid.
Decide refusals with reverse edges. A candidate is archivable only if every in-edge originates inside the candidate set, so build the transpose or count in-degrees restricted to the visited set, and emit each blocked resource with the id of the external referrer, which is the only part of the answer an operator can act on.
Store the graph as CSR rather than a map of lists: an offsets array of V+1 8-byte entries plus E 8-byte targets is about 80 MB at this size, where boxed adjacency lists cost several times that and lose cache locality on every hop.
Run Kahn over the condensation for the order in O(V+E). If the emitted count is short of the component count the condensation step itself is wrong, since a condensation cannot contain a cycle, which makes the check free.

Follow-up

The graph is read in one query and the archive writes a minute later. What can change in between, and how do you make the write safe?
The candidate set is 400,000 resources. Is that one transaction, and if not, what does a half-finished archive look like to a reader?
An edge points at a resource in another tenant. Is that a refusal, an error, or an alert?

Find overlapping job attempts and peak concurrency from lease records

Medium · sweep line · intervals · leases

A day of job_run history yields about 50,000,000 attempt records: (job_run_id, job_type, attempt, started_at, finished_at which is NULL when the worker died, lease_expires_at). Leases expire on a clock, so a job that outran its lease ran twice. Produce (a) every job_run_id whose attempts overlapped in wall-clock time and (b) the peak number of simultaneously running attempts per job_type with the minute it occurred. Target O(n log n). State how you treat a NULL finished_at and what clock skew does to your answer.

Approach

Define the interval before sorting anything: an attempt occupies [started_at, COALESCE(finished_at, lease_expires_at)). finished_at is observed and lease_expires_at is only a promise, so every attempt without a finish contributes an estimate and the whole result is a lower bound on overlap rather than an exact count.
For peak concurrency, sweep: emit 2n endpoints, sort by (timestamp, kind) with ends ordered before starts at equal timestamps, then walk the sequence maintaining a counter per job_type and record each type's maximum with its timestamp. O(n log n) dominated by the sort, O(n) space, or O(1) extra if the sort is external and the walk streams.
For overlap detection, do not compare attempts pairwise. A single global sort by (job_run_id, started_at) gives both the grouping and the order; within a group, keep the maximum end seen so far and report an overlap exactly when the next start is less than that running maximum, which is one linear pass after the sort.
Half-open intervals matter and are easy to get wrong: with closed intervals an attempt ending at the same millisecond another begins reads as concurrency two, and across 50,000,000 records that artefact swamps the real signal.
State the clock caveat: started_at and finished_at are written by different workers, so under skew of a few hundred milliseconds an apparent overlap shorter than that bound is not evidence. Filter reported overlaps by a minimum duration, or prefer timestamps written by whichever component heartbeats the lease.
Scale the sort rather than assuming it fits: the sweep emits two endpoints per attempt, so 50,000,000 records become 100,000,000 endpoints, and at roughly 24 bytes each, an 8-byte timestamp plus a 4-byte job_type plus a kind flag padded to alignment, that is about 2.4 GB of sort keys before any scratch space. Either push the ordering into the database behind an index on (job_type, started_at) or run an external merge sort in chunks; the overlap pass sorts n records rather than 2n, so it is the cheaper of the two.

Worked solution (30 min)

Write the interval derivation with the COALESCE and state in one line which of the two end sources is observed and which is assumed.
Write the concurrency sweep: the endpoint tuples, the sort key including the end-before-start tie-break, and the per-job_type counter.
Hand-trace four attempts of one job, two disjoint and two overlapping by three seconds, and confirm the overlap detector fires exactly once.
Add the skew filter as a minimum overlap duration, state the value you chose, and justify it from how the timestamps are written.

Expected result: One global sort by (job_run_id, started_at) driving a running-maximum-end check for overlaps, plus a sweep over 2n endpoints with ends ordered before starts for per-job_type peak concurrency, both O(n log n), with half-open intervals, NULL finished_at falling back to lease_expires_at, and overlaps shorter than the clock-skew bound excluded.

Verify it yourself

Two attempts touching at exactly the same instant report concurrency 1, not 2.
A job with a single attempt never appears in the overlap output regardless of its lease.
The reported peak timestamp for a job_type lies inside at least one of that type's intervals.

Follow-up

A handler is not idempotent and you have found 400 overlapping jobs. Which of them actually caused damage, and what would you query to find out?
Peak concurrency for one job_type is 4 against a configured cap of 4. Is the cap working, or is the data hiding attempts that never started?
How would you compute both answers incrementally as records arrive rather than in a daily batch?

Diff a projection against the primary without per-row point reads

Hard · reconciliation · range hashing · throttling

The listing projection has drifted and some rows show a stale version. The primary holds 40,000,000 resource rows across 12,000 tenants while serving 1,200 writes and 14,000 reads per second. The obvious repair, reading each resource row and comparing its version against the projection, is correct and would eventually finish. Explain precisely why it is unacceptable here, then give a diff that finds the differing rows, state its complexity, and make it safe to run against a live primary. Replication lag is usually under 100 ms and is not bounded.

Approach

Quantify the naive cost rather than calling it slow: 40,000,000 point reads at even 0.5 ms each is over five hours serialised, and the only lever is concurrency, which is exactly what you cannot spend. The primary's pool is sized for the write path, and 40,000,000 random reads evict the buffer cache that sustains the 85 percent cache hit rate, so the audit degrades the system it is auditing.
Replace random access with one ordered pass per side. Both sides can be read in (tenant_id, resource_id) order, which is a sequential scan on each and a merge join in O(n) time and O(1) memory. For a dense diff that is the whole answer, and it reads the primary once instead of 40,000,000 times.
For the expected sparse case, compare range hashes instead of rows: partition the key space, compute per range an order-independent aggregate over hash(resource_id, version), compare aggregates, and descend only into ranges that differ. With d differing rows and branching factor B, at most d ranges mismatch per level, so the drill-down examines O(d log_B(n/d)) ranges and reads full rows only in mismatching leaves.
Aggregate with a sum modulo 2^64 or a multiset hash, never XOR. XOR is order-independent but self-cancelling, so two rows wrong in the same way, or a row duplicated on one side, leave the range aggregate matching and the range is declared clean.
Pin the comparison to a point in time or it reports lag as drift: consider only rows whose updated_at is older than now minus a lag margin, and re-check each candidate mismatch individually before repairing. At 1,200 writes per second a diff without this reports thousands of false positives, and an unattended repairer would then overwrite live rows with stale values.
Make the run resumable and throttled: batch by range key, persist the last completed range, and watch a signal such as replica lag or primary CPU, pausing rather than pressing on. A reconciliation that cannot be stopped and resumed gets killed halfway and restarted from zero, which is how a repair becomes an incident.

Follow-up

The diff reports 900 stale rows. How do you decide between patching those rows and rebuilding the projection from resource_revision?
Same job, but the projection lives in a search index that cannot be scanned in key order. What changes?
How would you run this continuously at low cost instead of only as incident response?

Explain why the owner filter ignores the listing index

Medium · composite index · query plans · sort elimination

The only index on resource is (tenant_id, status, updated_at DESC, resource_id DESC). A new endpoint returns one user's resources across all statuses, newest created first: WHERE tenant_id = $1 AND owner_user_id = $2 ORDER BY created_at DESC LIMIT 20. On a tenant with 2M rows it takes 900 ms and EXPLAIN shows a sort above a large scan. Explain precisely why the existing index cannot serve it, give the index that can, and state which of these the new index still will not help: owner_user_id alone across tenants; the same query ordered by updated_at. PostgreSQL 16.

Approach

Separate the two jobs an index does. For filtering, a composite btree is seekable only on a left prefix, so with no predicate on status the scan can at best range over tenant_id and test owner_user_id per row; PostgreSQL 16 has no btree skip scan to jump the unconstrained column.
For ordering, the index is sorted by (status, updated_at) within a tenant and not by created_at, so the LIMIT cannot stop early: every matching row is read and then sorted. That is the 'Sort Method: top-N heapsort' line, and it is why the plan reads 2M rows to answer with 20.
Derive the replacement from the access path — equality, equality, then the ordering column: CREATE INDEX CONCURRENTLY ON resource (tenant_id, owner_user_id, created_at DESC). The scan seeks to the (tenant, owner) range and walks 20 entries in order, so the Sort node disappears along with the row-read.
Treat INCLUDE (title, status) as conditional, not free. An index-only scan still visits the heap for any row whose page is not marked all-visible, so on a table taking 1.2k writes/second the win depends on autovacuum keeping the visibility map current, and the wider index costs more on every insert.
Answer the two negatives explicitly. owner_user_id alone is not a left prefix of the new index, so it degrades to a full scan of the index at best. Ordered by updated_at, the query still seeks on the (tenant, owner) pair but must sort, because only created_at is ordered within that pair.
Measure both sides with EXPLAIN (ANALYZE, BUFFERS) and compare estimated against actual rows at the lowest node — a 2M-versus-200 misestimate there is usually what chose the plan, and adding an index will not fix a statistics problem.

Follow-up

90% of rows are status='active'. Would a partial index WHERE status = 'active' change your answer, and for which of the three queries?
A dashboard runs this for 40 owners in one page load. What changes about the design?
How do you roll this index out on a table taking 1.2k writes/second, and what does it cost on every insert from then on?

Denormalise tenant onto revisions and backfill it live

Hard · denormalisation · online migration · backfill

resource_revision (revision_id, resource_id, version, actor_user_id, change_kind, patch, request_id, created_at) has 400M rows and no tenant column; tenant_id lives only on resource. Two reads need it: a tenant-scoped audit feed ordered by created_at DESC, and an offboarding purge. Both join back to resource today. Justify adding tenant_id to resource_revision against those two reads, name the anomaly the copy introduces and the constraint that prevents it, then give the ordered migration for a live table taking 1.2k writes/second — the lock each step takes, how the backfill is batched, and where each step stops being reversible. PostgreSQL 16.

Approach

Justify from the access path rather than from taste. Without the column, the audit feed either scans resource_revision by created_at and discards other tenants' rows, or resolves the tenant's resource_ids first and probes with them — both proportional to the tenant's whole history rather than to one page. With (tenant_id, created_at DESC, revision_id DESC) it is a seek that stops at 50 rows, and the purge becomes a ranged delete instead of a join.
Name the cost exactly: a second copy of a fact can disagree with the first. Make the disagreement unwritable rather than documented — add UNIQUE (resource_id, tenant_id) on resource so it can serve as a foreign-key target, then FOREIGN KEY (resource_id, tenant_id) REFERENCES resource (resource_id, tenant_id) on the revision table. A revision can then only ever carry its parent's tenant.
Step one, expand: ALTER TABLE resource_revision ADD COLUMN tenant_id BIGINT NULL, with no default, so it is a catalogue change and no rewrite. It still needs ACCESS EXCLUSIVE for an instant, and that instant queues behind the longest open transaction on the table while every later query queues behind it — set lock_timeout to 2s and retry rather than wait.
Step two, dual-write: deploy the writer that populates tenant_id on every new revision while reads still use the join. Reversible by redeploying the previous build, because nothing reads the column yet.
Step three, backfill: batch by primary key rather than by created_at so the cursor is dense and resumable — UPDATE resource_revision rr SET tenant_id = r.tenant_id FROM resource r WHERE r.resource_id = rr.resource_id AND rr.revision_id > $1 AND rr.revision_id <= $1 + 5000 AND rr.tenant_id IS NULL — committing per batch and persisting the cursor. Throttle on replica replay lag and on dead-tuple count, since each batch writes 5,000 new row versions. Run the backfill before the index exists so those updates can stay HOT.
Step four, index then enforce then contract: CREATE INDEX CONCURRENTLY (cannot run inside a transaction block, scans the table twice, waits on open transactions, and leaves an INVALID index to drop concurrently if it fails); ADD CONSTRAINT ... CHECK (tenant_id IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT, which takes only SHARE UPDATE EXCLUSIVE, after which SET NOT NULL uses the validated check instead of re-scanning on PostgreSQL 12 and later. Only then move the audit reads onto the column and, in a later deploy, delete the join path.

Worked solution (40 min)

Write the five steps as separate scripts and state, for each, the lock mode it acquires and the deploy it pairs with.
On a 20M-row copy, run the ADD COLUMN while a 30-second transaction holds a lock on the table, and record how long unrelated queries queue behind it.
Run the batched backfill at 5,000 rows, kill it mid-run, restart from the persisted cursor, and confirm no row is processed twice and none is skipped.
Build the index concurrently under concurrent write load, then add the CHECK ... NOT VALID, VALIDATE it and SET NOT NULL, timing each.
Compare the audit-feed plan before and after: join-and-filter versus an index seek with no Sort.

Expected result: ADD COLUMN completes in milliseconds once it holds the lock, but with a 30-second transaction open it stalls all traffic on that table for the full 30 seconds unless lock_timeout cuts it off. The backfill resumes exactly at the persisted revision_id. After VALIDATE, SET NOT NULL returns without a table scan. The audit feed drops from scanning the tenant's history to reading about 50 rows.

Verify it yourself

SELECT count(*) FROM resource_revision WHERE tenant_id IS NULL returns 0, re-checked after the dual-writer has been live for a full deploy cycle.
A sampled join confirms rr.tenant_id = r.tenant_id across 100k random revisions; the composite foreign key makes a mismatch unwritable from then on.
pg_stat_activity shows no transaction older than one batch's duration while the backfill runs.
EXPLAIN of the audit feed shows an Index Scan with no Sort node.

Follow-up

The backfill is half finished and a rollback is required. What state is the table in, and what does the previous build do with a half-populated column?
How do you verify the backfill actually finished, given rows are still being inserted while it runs?
A resource must now be movable between tenants. What does that do to the composite foreign key and to the revisions already written?

How have you applied software solutions to solve complex physical or operational problems?

Medium

Approach

State your assumptions explicitly before working the problem.
Work from the requirement backwards to the design.
Say what you would check first and why it is the highest-information step.

Follow-up

What assumption would you test first?
How would you know your answer was wrong?

What is your perspective on scaling software systems within a hardware-heavy environment?

Medium

Approach

Work from the requirement backwards to the design.
State your assumptions explicitly before working the problem.
Say what you would check first and why it is the highest-information step.

Follow-up

How would you know your answer was wrong?
What assumption would you test first?

How would you approach identifying bottlenecks in a production-oriented software pipeline?

Medium

Approach

Clarify what is being asked and what a complete answer contains.
Work from the requirement backwards to the design.
Say what you would check first and why it is the highest-information step.

Follow-up

How would you know your answer was wrong?
What assumption would you test first?

Discuss your background in engineering.

Medium

Approach

State your assumptions explicitly before working the problem.
Clarify what is being asked and what a complete answer contains.
Work from the requirement backwards to the design.

Follow-up

How would you know your answer was wrong?
What assumption would you test first?

Design the bulk write endpoint a migration script retries blindly

Medium · batch api · partial failure · idempotency

A customer's migration script pushes 2 million resources through POST /v1/resources:batch, up to 500 items per call, and retries any call that errors or times out. Within a call some items fail validation, some collide with rows that already exist, and some succeed. Specify the request and response shape, whether a batch is atomic or per-item, how idempotency works for the call and for each item, the status code for a mixed outcome, the size and item-count limits with their error codes, and exactly what the script does after a timeout mid-batch.

Approach

Choose atomicity deliberately and price it. All-or-nothing means one transaction holding locks for the whole batch on a primary already absorbing about 1.2k writes/second, which bounds batch size by lock duration, and it turns one bad row into 499 rejections the script must resend. Per-item partial success is the right default for a migration, and the contract's job is then to make a partial outcome impossible to miss.
Require a client-supplied id on every item and echo it in every result. Deriving a per-item key from the array index breaks the first time the script resends a batch with the failures removed: the indices shift, previously-succeeded items acquire new keys, and they are created a second time.
Key the effects at two levels. The call's Idempotency-Key covers an exact resend of the same bytes; per-item keys of (tenant_id, client_item_id) make a partially-applied batch safe to resend whole. Resending an identical batch must reproduce the same per-item results, not 500 conflicts the script has to interpret.
Answer a mixed outcome with one status plus per-item detail: 200, or 207 borrowed from WebDAV if you prefer it - document whichever you pick - carrying an array of client_item_id, per-item status, and either resource_id or an error code from the same taxonomy the single-item endpoint uses. Reserve 4xx for the request as a whole: unparseable body, too many items, payload over the limit (413). Put a failed count at the top level so that even a script checking only the cheapest thing cannot conclude success while rows were dropped.
Cap the request before doing any of it: item count, total bytes, and concurrent batches per tenant, since one tenant's migration otherwise consumes write capacity everyone shares. Anything that cannot finish inside the request deadline belongs on job_run behind a 202, not in a synchronous call that will time out halfway.
Script behaviour after a timeout: the outcome is unknown and no results were received, so resend the identical batch with the same keys and read the results. Never resend 'only the items I have no result for' - a timeout yields no results at all, and that rule silently means resend everything anyway.

Worked solution (30 min)

Write the request schema with the per-item client id, and the response schema with per-item status and a top-level failed count.
Write the atomicity decision and the sentence of justification that names the lock cost or the resend cost.
Define both key levels and trace a resend of a half-applied batch through them, item by item.
List the whole-request rejections with their status codes and limits.
Write the script's timeout rule and the pacing it should apply between calls.

Expected result: A per-item batch with client-supplied item ids, per-item idempotency plus a call-level key, a mixed-outcome response that a status-only check cannot misread, explicit request caps, and a resend rule that is correct when no results were received.

Verify it yourself

Resending an identical batch twice leaves exactly the same set of resource rows as sending it once.
Resending a batch with the successful items removed does not create duplicates of anything.
A caller that reads only the top-level fields can still tell that some items failed.

Follow-up

At 500 items per call, what is the wall-clock time for 2 million rows, and what pacing do you publish so the migration does not become an incident?
One item in every batch fails with the same code. How does the script discover that without a human reading logs?

Every query on one table stalls for forty seconds mid-deploy

Medium · lock contention · ddl · migrations · postgres

During a release on PostgreSQL, every query touching resource times out for about 40 seconds and then recovers with no intervention. The release ran one migration, ALTER TABLE resource ADD COLUMN archived_reason TEXT, and the migration log shows it completing in 6 ms. Unrelated tables showed no change in error rate. Explain how a 6 ms statement caused a 40-second stall, give the ordered checks you would run on a live system to confirm it, and give the migration procedure that prevents a repeat.

Approach

Separate the statement's duration from the lock's duration. ADD COLUMN with no default is a catalogue-only change and genuinely runs in milliseconds, but it requires ACCESS EXCLUSIVE, and it cannot acquire that until every transaction already touching the table has finished.
Account for the queueing, which is the part that surprises people. A lock request that is waiting blocks later requests for conflicting modes behind it rather than letting them overtake, so one long-open transaction holds the DDL and the DDL holds all the traffic. The stall length is set by the longest open transaction, not by the size of the change.
Confirm on a live system in this order: pg_stat_activity for that table ordered by xact_start, looking for the oldest transaction and specifically for state = idle in transaction; then pg_locks where granted = false to find the waiter; then join them on pid to name blocker and blocked. pg_blocking_pids() does that join for you and is the fastest single call.
Prevent rather than merely time it better. Set lock_timeout to a second or two on the migration session so the DDL abandons the queue after a bounded wait and is retried, instead of holding it for as long as the oldest transaction lives. Be exact about what that buys: queries arriving during the wait still queue behind the pending ACCESS EXCLUSIVE request, so each attempt costs them up to one lock_timeout of added latency. The outage goes from 40 seconds to about one second per attempt, not to zero. Also run migrations away from deploy-time peaks, and put a statement timeout and an idle-in-transaction timeout on the analytics role that opens the long transactions.
Know the lock each change takes, since the mitigation differs by change. A column with a non-volatile default is a metadata-only change from PostgreSQL 11 and still needs the brief ACCESS EXCLUSIVE; an index needs CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block and leaves an INVALID index to drop if it fails; a check or foreign key is added NOT VALID and then VALIDATE CONSTRAINT as a separate statement under a weaker lock.

Follow-up

The same release also wants NOT NULL on that column. What is the sequence that gets there without a long lock?
Your lock_timeout retry fails ten times in a row because the analytics transaction is always open. What do you change?
How does this differ on MySQL with InnoDB online DDL, and what is the equivalent of the waiting-lock queue there?

Suggested practice checklist

Four days sample coding, design, fundamentals and the practical rounds at deliberately shallow depth, which is enough to surface the topics you did not know were in scope. That map, rather than a guess made on day one, decides where the last three days go.

Day 1 — Coding, one pass at shallow depth

Solve one problem from each of six families, an array with two pointers, hash counting, binary search, a tree traversal, a graph traversal and one dynamic program, under a hard twenty-minute cap with no extensions, marking each finished, late, or stalled.
For every stall, write the exact move you could not make rather than the subject, so the note reads could not turn the recurrence into a loop rather than bad at dynamic programming.
Fix nothing today. The value of the pass is the unfixed record.

Deliverable: Six timed attempts marked finished, late or stalled, each stall carrying a named blocking move.

Day 2 — Design, one pass at shallow depth

Spend twenty minutes each on three different shapes, a read-heavy feed, a write-heavy ingest path, and something needing a transaction across two entities, stopping each at requirements, interface and data model.
After each, write the first question you could not answer, which is usually a number you could not estimate or a failure mode you had no vocabulary for.
Mark which of the three you would be most relieved not to be asked, and treat that as data rather than as a preference.

Deliverable: Three shallow designs, each with the first unanswerable question written at the bottom.

Day 3 — Fundamentals and the practical rounds

Answer eight short questions in writing at four minutes each, covering the material that fills the gaps between the big rounds: what happens between a URL and a rendered page, what an index costs on write, when a process is preferable to a thread, and what conditions a deadlock requires.
Do one thirty-minute practical task of the kind a take-home compresses: read an unfamiliar two-hundred-line file and write what it does, what you would change, and the one thing you remain unsure of.
Score every answer fluent, correct but slow, or absent, and keep the absent ones visible.

Deliverable: Eight scored short answers and one written reading of unfamiliar code.

Day 4 — The rounds that are about you, and the map

Deliver three behavioural answers aloud against a timer, a conflict, a failure you owned, and a decision made without enough information, marking any that ran past three minutes or contained no number.
Assemble the map: every marked item from days one to three on a single page, sorted by how likely it is to appear in your loop rather than by how uncomfortable it felt.
Choose exactly two areas for the remaining three days and write down what you are deliberately abandoning.

Deliverable: A one-page scored map of the whole surface area with two areas chosen and the rest explicitly abandoned.

Day 5 — First chosen area, to the depth you skipped

Work the higher-ranked area in four focused blocks, choosing items one level above where you stalled rather than repeating what already works.
After each block write the rule you extracted in one sentence with its precondition attached, since a rule carrying no precondition is exactly what fails under a variation.
Re-attempt the day-one or day-two item that exposed this area and compare against the original timing.

Deliverable: Four worked blocks, a timed re-attempt against the original, and three one-sentence rules with preconditions.

Day 6 — Second chosen area, where the gap is coverage rather than speed

Treat the second area differently from the first. Day five drilled something you could already half-do; this one is usually a topic you had simply never met, so build one worked reference example end to end and keep it, rather than attempting six problems badly.
Write down the vocabulary you were missing on day two or three, five terms at most, each with the one sentence that makes it usable in an answer rather than the textbook definition.
Redo the shallow attempt that exposed this area and note whether you now fail later in the problem, because moving the failure point is the realistic gain from a single day and is worth more than a score that did not change.

Deliverable: One worked reference example for the newly covered area, a five-term vocabulary list, and a note on where the failure point moved.

Day 7 — Reassemble the loop

Sit two rounds back to back with no gap, ordering them so the area you chose second comes last, because the map was built from rested, isolated attempts and the loop will reach your weaker area when you are already spent.
Write where the second round suffered from the first, which is normally the point at which structure collapses into narration.
Reduce the week to one page holding only the rules you can state without reading them.

Deliverable: Mock notes on cross-round carryover plus a one-page card of rules you can recite from memory.

The bug you decided not to fix

Engineers over-index on what they repaired. A stronger answer covers something you knowingly left broken: the alert you tuned down, the data inconsistency you documented instead of chasing, the cleanup you deferred past two quarters. Give the reasoning and the condition that would have reopened it, so it reads as a decision and not as neglect.

Turn a code review disagreement into a decision

Easy · code review · optimistic concurrency · communication

A colleague's change updates a row with UPDATE resource SET version = version + 1 WHERE resource_id = $1 AND version = $2 and treats an affected-row count of zero as a successful no-op. You read that as a silently lost update; they think returning 200 is friendlier to clients than returning a conflict. Describe how you have handled a review disagreement of this shape: what goes in the comment, when you leave the thread, and who decides. Then write the comment you would leave here, in under 80 words.

Approach

Sort the disagreement before writing anything. A silently discarded write is a correctness claim about data; the choice between 409 and 412 is taste. Only the first justifies blocking a merge, and saying which one you are doing is most of the value of the comment.
Make the claim reproducible in the comment itself with an interleaving rather than a principle: A reads version 7, B reads version 7, B commits version 8, A's predicate matches zero rows, A is told it succeeded and A's edit is gone.
Offer the alternative with its cost attached: return 409 carrying the current version and the revision that won, so the client can re-read and re-apply. Note that automatic retry is not the fix, because a retry re-reads the winner's state and reapplies an intent formed against data that no longer exists.
Apply an escalation rule you can state: two round trips on the thread, then a call, and the service's owner decides rather than the reviewer. A reviewer who cannot be overruled is a bottleneck with extra steps.
Close in writing wherever the decision lands, so the next reader finds the reasoning in the code or the ticket instead of in a collapsed review thread.

Follow-up

Where would you put the test that fails if someone reintroduces the swallowed zero rowcount?
The author says clients cannot handle a 409. How do you check whether that is true?
How do you handle the same review comment when the author is more senior than you and in a hurry?

Argue against a design, lose, and commit anyway

Medium · disagreement · service boundaries · decision records

Describe a design you argued against and lost. State the failure you predicted as a named mechanism, not a feeling about complexity: two services that would need one transaction, a projection with no rebuild path, a write path with no idempotency key. Say what evidence you brought, what the decision maker weighed instead, and what you did after the decision was made: what you instrumented, what you wrote down, and whether the prediction came true. Five minutes.

Approach

State the prediction in falsifiable form up front: the mechanism, the condition that triggers it, and the observable outcome. A prediction that cannot be checked also cannot be credited to you later.
Show the evidence you had at the time and label each piece honestly as measured, analogous, or intuition. Keeping the intuition is fine; disguising it as data is the thing that erodes your standing in the next argument.
Represent the opposing case at full strength, including the constraint you did not control: a fixed date, a team boundary, or the fact that the decision was cheap to reverse and yours was not.
Make disagree-and-commit concrete. Name the artefact you left behind so the prediction could be settled without you: the alert and its threshold, the counter on the dashboard, the decision note that recorded the trade-off and the condition that would revisit it.
Report the outcome without editing it. If the design held and your predicted mechanism never fired, say so and say what you had mis-weighted, which is more persuasive than a vindication story.

Follow-up

What threshold on that alert would have proved you right, and did anyone ever look at it?
If the same proposal arrived tomorrow with the same deadline, would you argue it the same way?
How did you behave toward the design once it shipped and started failing in a different way than you predicted?

Estimate work you have never done and defend the range

Hard · estimation · backfills · expand-contract

You are asked to estimate a change you have never attempted: add a column to a 100-million-row table, populate it, move reads across, and drop the old shape. Give a range with the assumptions that generate it, including batch size, the signal your backfill throttles on, and wall-clock hours, and name the three unknowns that would move the number most. Then describe a real estimate you gave under comparable ignorance: how you expressed its uncertainty, what you committed to, and how wrong you turned out to be.

Approach

Decompose into independently deployable steps before estimating anything: add the column nullable, write both shapes, backfill in batches, verify, move reads, stop writing the old shape, drop it. That is four deploys spread over days, and the calendar estimate is dominated by them rather than by the loop's runtime.
Do the arithmetic aloud for the part that has arithmetic in it: batch size times number of batches times per-batch duration, at a write rate the primary can absorb alongside roughly 1.2k writes per second of production traffic. The loop is throttled by replication lag and lock waits, not by how fast it can issue statements.
Price the schema step by its lock rather than its statement duration. In PostgreSQL an ALTER TABLE taking ACCESS EXCLUSIVE waits for every open transaction on that table while later queries queue behind it, so a millisecond change issued during a thirty-second analytics query stalls that table for thirty seconds. Adding a nullable column with a non-volatile default avoids a rewrite from version 11; a new index wants CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block and leaves an invalid index behind if it fails.
Express the answer as a range whose endpoints each trace to a stated assumption, then name the cheapest experiment that collapses it, which is almost always running one real batch against the real table and multiplying.
Commit to a checkpoint rather than a completion date: the day you report a measured number from that first batch. That is a promise you can keep under uncertainty, and it is what the asker actually needs in order to plan.

Follow-up

How do you verify the backfill genuinely finished, given rows written by production traffic while it ran?
Where does the backfill resume from after a worker is killed mid-batch, and what makes that resume point trustworthy?
Your first batch comes back ten times slower than assumed. What do you tell the person waiting on the estimate, and when?