Interview guides · BC Public Service

BC Public Service Software Engineer Interview Questions & Guide 2026

Concurrency drills for BC Public Service software engineer prep: races, lock ordering, and what a check-then-act sequence assumes.

The 60-second brief

As a Software Engineer at the BC Public Service, you are at the forefront of digital transformation for the province. Your work directly impacts how citizens access essential government services, ranging from health and education to environmental and social programs. This role is not just about writing code; it is about building scalable, secure, and accessible software solutions that serve millions of British Columbians.

Treat capacity estimation as a conversion skill rather than a table to memorise: turn a user count and an action rate into requests per second and bytes per day, then name the component that number breaks first. The figure only matters if it changes the design.

Focus areas

Choose indexes from the query's access path
Paginate large result sets with keyset cursors
Bound every outbound call with a timeout

Overview

The environment is uniquely challenging because it balances the rigors of public sector accountability with the need for modern, agile development practices. You will often work on high-stakes projects that require integrating complex legacy systems with modern cloud infrastructure. For a developer, this offers the rare opportunity to see your work have a tangible, positive influence on the day-to-day lives of people in your community while tackling large-scale architectural problems.

The BC Public Service interview process

BC Public Service candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Resume 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

Technical Assessment

Input bounds are the part of the prompt most often skimmed, and they usually contain the answer. They tell you which complexity class is admissible, which narrows the search before you have thought about the problem itself. As a rough planning figure, a compiled language does on the order of 10^8 simple operations per second and an interpreted one roughly an order of magnitude less. So n up to about twenty admits enumerating subsets, a few thousand admits a quadratic pass, and a million admits neither: you need near-linear, or linear with a log factor. If the bounds are missing, ask for them.

What to demonstrate

Whether the approach is justified by the stated input size rather than by whichever pattern you recognised first
Whether you ask about the properties that change the algorithm: whether the input arrives sorted, whether duplicates occur, whether values are bounded integers, whether it all fits in memory
Whether you can name the bottleneck in your own solution and what would remove it, even when you deliberately leave it in place
Whether a claimed speedup is real, since memoising a recursion only helps when subproblems genuinely overlap and the state can be keyed cheaply

How to prepare

For each algorithm you rely on, write down the largest n it handles in roughly a second, then check two of those figures by timing them in the language you will actually type in
For two weeks, write one line naming your target complexity and the bound that justifies it before you write any code, then compare that line with what you ended up submitting
Practise the conversion backwards: given a required O(n log n), list the mechanisms that get you there (sorting, a heap, an ordered map, divide and conquer) and choose by what the problem needs to query, not by what you used last

Behavioral Interview

Your first answer is not really what is scored. It buys the follow-up questions, and those decide the round. An interviewer with fifteen minutes takes one thread and pushes on it four or five times, so a story you can only tell at a single level of detail collapses under the third why. That is an argument for fewer stories known deeply rather than one prepared per prompt. Four or five pieces of work you can still explain down to the code you changed and the argument you had about it will cover nearly anything asked in this round.

What to demonstrate

Whether a story holds as the questioning moves from what you did to why that instead of the alternative, and then to what you would change knowing what you know now
Whether you can re-cut a project to answer the question actually asked rather than delivering a rehearsed block that answers an adjacent one
Whether your level of detail is chosen rather than habitual: going down to the schema when the question is about the data model, staying out of it when the question is about the person who disagreed with you

How to prepare

Pick four projects and write the chain out four levels deep for each: what you did, why that, why not the alternative, and what would have to be true for the alternative to have won. Where you cannot reach the fourth level, you have a placeholder rather than a story
Have someone ask why three times in a row on a single thread with nothing else added, and mark the point where you start repeating a sentence you already said. That point is where the interviewer stops learning anything
Build a one-page index instead of an answer bank: the common prompts in this round (disagreement, a failure that was yours, thin requirements, a deadline you missed, work you inherited) mapped to which of your four projects you would use for each, so the choosing is done now rather than while an interviewer waits

Where candidates lose points

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.

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.

Reading the constraints as preamble rather than as part of the problem

The bounds are usually there to eliminate the obvious approach: n up to 10^5 makes an O(n^2) scan roughly 10^10 operations, far outside any per-test time budget, and an input larger than memory rules out loading it at all. When a bound is not given, ask for it, then say out loud which approach it kills.

Quoting amortised or average cost as if it were a worst-case guarantee

Appending to a dynamic array is amortised O(1), but the append that triggers a resize copies every element, and hash lookup is constant only while the hash spreads the actual keys. Say which guarantee you are offering when the caller cares about the latency of one call rather than the total over many.

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?

Identify the heaviest tenants in a five-minute window under memory pressure

Medium · top-k · heavy hitters · streaming

The edge service handles about 3,000 requests per second across roughly 50,000 tenants, peaking near 9,000. Expose the 50 heaviest tenants by request count over the trailing five minutes so limits can be tightened before one tenant's backfill starves the fleet. You may not retain five minutes of raw records. Give the exact solution and its memory, then the bounded-memory approximation with its error stated as a formula, and say which you would ship and at what tenant cardinality that choice changes.

Approach

Do the exact version first, because it is affordable at this cardinality: a ring of 300 one-second counters per tenant, advanced lazily, is 1,200 bytes of counters per tenant and roughly 60 to 90 MB for 50,000 tenants with overhead. Carry a running total and subtract the bucket you overwrite so a window read is O(1) rather than 300 adds.
Extract the top 50 with a size-k min-heap over the tenant sums: O(d log k) for d tenants, against O(d log d) to sort them all. Maintaining the heap continuously instead of on query requires a tenant-to-heap-index map, because incrementing a count already inside the heap means sifting from a known position, and without that map you rebuild the heap on every request.
State the approximation precisely rather than gesturing at sketches. Misra-Gries with m counters retains every item whose true count exceeds N/(m+1), and each retained count underestimates the truth by at most N/(m+1). With m = 1,000 and N = 900,000 requests in the window the error is roughly 900 requests, which is fine for spotting a tenant sending 50,000 and useless for ranking two tenants 200 apart.
Say what breaks when the window slides: Misra-Gries and Space-Saving are insert-only and cannot be decremented as records age out. The workable construction is one summary per sub-window, say ten seconds, with 30 summaries merged at query time, and the merged error is the sum of the per-summary errors, so the bound degrades linearly in the number of sub-windows.
Choose and defend it: at 50,000 tenants the exact rings cost under 100 MB in a process that already holds more, so ship exact. Keep the sketch for the case that actually motivates it, a per-principal or per-IP key where cardinality runs to millions and is not bounded by anything you control.
Raise the fleet problem before it is asked: each of 20 to 40 instances sees only its share, and the top 50 of one shard is not the top 50 of the fleet. Either aggregate counts centrally or accept that a per-instance threshold multiplied by instance count is the limit you are really enforcing.

Follow-up

The heaviest tenant is heavy because of one export job rather than user traffic. Should the limiter treat those as the same tenant?
Two tenants sit tied at the boundary of the top 50. Does your answer flap, and does the flapping matter?
You switch to per-principal keys and cardinality goes to 10 million. Walk through what changes.

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?

Find version gaps and relay lag with window functions

Medium · window functions · event tables · frame semantics

outbox_event holds event_id, aggregate_type, aggregate_id, aggregate_version, event_type, payload, status ('pending','published','dead'), attempts, created_at, published_at. A projection is missing rows and you must decide whether the relay skipped events or the consumer dropped them. Write three queries over the last seven days: one listing every aggregate_id whose published aggregate_version sequence has a hole, one giving per-day counts with a running total, and one returning the newest published event per aggregate. For each, say where the window function is evaluated relative to WHERE and LIMIT. PostgreSQL 16.

Approach

Gaps: compute lead(aggregate_version) OVER (PARTITION BY aggregate_id ORDER BY aggregate_version) in a subquery, then filter next_version <> aggregate_version + 1 in the outer query. Window functions are evaluated after WHERE, GROUP BY and HAVING and before the outer ORDER BY and LIMIT, so the predicate cannot sit in the same WHERE clause and PostgreSQL 16 has no QUALIFY.
Say what the seven-day filter does to the answer: it truncates every partition, so the first row per aggregate has no predecessor inside the window and a hole spanning the boundary is invisible. Widen the window, or join to resource.version as the authority for the true maximum.
Running total: SELECT date_trunc('day', created_at) AS d, count() AS n, sum(count()) OVER (ORDER BY date_trunc('day', created_at) ROWS UNBOUNDED PRECEDING). An aggregate inside a window call is legal because grouping runs before windowing. The grouping key is unique per row here so ROWS and RANGE agree, but write the frame anyway — over ungrouped rows with tied timestamps the default RANGE frame pulls in every peer row and the total jumps.
Newest per aggregate: DISTINCT ON (aggregate_id) ... ORDER BY aggregate_id, aggregate_version DESC is the cheap PostgreSQL-only form when an index matches that order; row_number() OVER (PARTITION BY aggregate_id ORDER BY aggregate_version DESC) = 1 is the portable form and needs a subquery for the same evaluation-order reason as the gap query.
Interpret rather than report: no gaps plus a normal p95 of published_at - created_at points at the consumer; gaps or a fat lag tail point at the relay; rows still 'pending' with attempts > 0 point at neither, because they never left the database.
Be explicit that the partial index on (created_at, event_id) WHERE status = 'pending' does not serve any of these — they read published rows. Name the index a recurring monitor would need, and say why a query run twice a year may not deserve one.

Worked solution (30 min)

Write the three queries against seven days of data and confirm each returns without error.
In a scratch copy, delete one middle event for a single aggregate and confirm the gap query names that aggregate and the versions either side.
Run a running total over ungrouped rows ordered by date_trunc('second', created_at), once with the default frame and once with ROWS, and record where the two series diverge.
Compare the DISTINCT ON and row_number() plans on the same data and record rows-read for each.

Expected result: The gap query returns one row per hole with the version on each side; the daily query returns seven rows whose running total ends at the seven-day count; the newest-per-aggregate query returns exactly one row per aggregate_id present in the window. The RANGE running total is higher than the ROWS one at every tied second.

Verify it yourself

The per-day counts sum to COUNT(*) over the same predicate.
Re-running the gap query after reinserting the deleted event returns zero rows.
count(*) of the newest-per-aggregate result equals count(DISTINCT aggregate_id) over the same filter.

Follow-up

Relay failover redelivers events. Does a duplicate break the gap query, and how would you detect one from this table alone?
Turn the gap check into a continuous monitor rather than a query someone runs after an incident. What does it watch?
The consumer claims it never received event 4,812,006. What do you look at, in what order?

Tell us about a project you worked on; what tech stacks did you use and why did you choose them?

Medium

Approach

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

Follow-up

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

Please describe your previous full-stack development project.

Medium

Approach

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

Follow-up

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

Relay committed events to the log without gaps or reordering

Medium · transactional outbox · at-least-once · ordering · leases

outbox_event rows are written in the same transaction as the state change and carry aggregate_type, aggregate_id, aggregate_version, payload and status, with a partial index on (created_at, event_id) WHERE status = 'pending'. The relay publishes about 4k events/second to a partitioned append-only log keyed by aggregate_id, with one leader per partition range holding a lease. Consumers must never miss an event; they may see one twice. Design the claim-publish-mark loop, and state exactly what consumers observe when a leader's lease expires while it is mid-batch.

Approach

Claim with SELECT ... WHERE status='pending' ORDER BY created_at, event_id LIMIT $batch FOR UPDATE SKIP LOCKED inside a transaction. SKIP LOCKED lets several relay workers share a range without serialising on each other's rows, and the partial index keeps the claim proportional to the backlog rather than to a table that is overwhelmingly published rows. At 4k/second a batch of 500 is eight claims per second, each an index scan of 500 entries.
Publish before marking, never the reverse, and say why it is a choice. Marking first loses the event outright if the process dies in the gap, and the loss is silent - nothing remains to retry, and it surfaces later as a projection missing a row. Publishing first can repeat the event, and repetition is what every consumer is already built to survive. That single ordering is the whole at-least-once guarantee.
Preserve the only ordering on offer. Partition by aggregate_id and never publish two events for one aggregate concurrently: claim in (created_at, event_id) order and publish sequentially within an aggregate, or hash aggregate_id to a worker slot. Order across aggregates is not available at any price here, which is why the event carries aggregate_version and the full fact rather than a delta - a consumer can then discard what it has already applied without coordinating with anyone.
State the failover behaviour precisely, because it is the consistency-versus-availability decision in this design. A lease expires because the holder is slow, and no mechanism distinguishes that from dead, so for the length of the lease window two leaders can publish the same claimed batch. The system accepts duplicates to avoid stalling publication for every aggregate in the range whenever one worker pauses. Consumers deduplicate on (aggregate_id, aggregate_version) and drop anything at or below what they have applied.
Bound the failure paths and pick the right alarm. A row that fails to publish increments attempts, records last_error, and moves to 'dead' after a limit so one poison payload cannot block the backlog behind it. Alert on the age of the oldest pending row, not on the relay's error rate: the failure worth catching is a relay reporting itself healthy while nothing is being published.

Worked solution (25 min)

Write the claim statement and check it against the partial index: which columns it seeks on, how many entries it touches, and what two concurrent workers do to each other.
Write both orderings of publish and mark, and for each state what exists after a crash at every point in the loop.
Write the consumer's dedupe rule on (aggregate_id, aggregate_version) and test it against a replayed batch of 500.
Compute the backlog after a 40-minute outage and the batch rate needed to drain it while 4k/second continues to arrive.

Expected result: A claim using FOR UPDATE SKIP LOCKED over the partial index in (created_at, event_id) order, publish to the aggregate_id partition, then mark published - in that order, accepting duplicates on lease expiry. Per-aggregate order is preserved by never publishing an aggregate concurrently; cross-aggregate order is explicitly not guaranteed. Failures increment attempts and terminate at 'dead'. The alarm is the age of the oldest pending row.

Verify it yourself

Killing the relay mid-batch produces duplicates on restart and no gap.
The claim query's plan uses the partial index and reads at most batch-size entries regardless of table size.
Two relay workers on the same range never block on each other's rows.
Events for one aggregate_id arrive in aggregate_version order, and no consumer relies on order across aggregates.

Follow-up

The relay is down 40 minutes and 9.6 million rows are pending. What does catch-up do to the primary, and what changes in the claim loop to survive it?
A consumer insists it never received an event. Which single query settles whether the relay lost it, and what does each answer look like?
Delivery is at-least-once. What would exactly-once require end to end, and why is that a property of the consumer rather than of the relay?

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

For someone fluent in a dynamic language who has shipped real work but has never had to say what the runtime is doing underneath. The week is built on measuring and deliberately breaking things, because the questions that expose this background are the ones where the interviewer asks why a second time.

Day 1 — Measure before reasoning

Take a slow piece of your own code, write down in advance where you believe the time goes, then profile it and record how wrong the guess was. The cost is usually an allocation you did not notice or an accidental quadratic membership test.
Replace one list membership test inside a loop with a set and measure at a thousand, ten thousand and a hundred thousand elements, confirming the shape of the curve rather than only that it got faster.
Write down the three quantities you can now measure instead of assert: wall time, peak memory, and call count for the function you suspected.

Deliverable: A before-and-after profile of real code plus a written note on the size of the gap between the guess and the measurement.

Day 2 — References, copies, and the bugs they produce

Write the function with a mutable default argument, call it three times, and explain the accumulating result: the default is evaluated once when the function is defined, so every call shares one object.
Build a nested structure, take a shallow copy, mutate an inner element, and show that both views changed, because a shallow copy duplicates the container and not the elements. Then fix it with a deep copy and state the cost you just accepted.
Write two functions, one mutating its argument in place and one rebinding the local name, and predict the caller's view of each before running it. That single distinction produces most of the bugs that pass their tests.

Deliverable: Three small programs whose output you predicted correctly before running, each with a one-line statement of the rule underneath.

Day 3 — Types, once, in a language that checks them

Port one module you have already written, roughly a hundred lines, into a statically typed language, and record every place the compiler demanded an answer your original had left implicit: a value that can be absent, a numeric width, a case never handled.
Write the same signature in both languages and state what the static one guarantees before the program runs and what it does not, since it will not save you from a wrong algorithm or an index out of range.
Write the difference between an interface satisfied by declaration and one satisfied structurally, with one case each where the other approach would miss the mistake.

Deliverable: One module in two languages plus a list of the questions the type checker forced you to answer.

Day 4 — Concurrency, starting with what actually runs at the same time

Run the same CPU-bound function across four threads and four processes and measure both. Under the default CPython build the threaded version will not speed up, because only one thread executes bytecode at a time; the process version will. Check which build you are on first, since free-threaded builds remove that lock and change the result.
Then run a blocking I/O workload across four threads and measure it speeding up, because the interpreter releases that lock around blocking calls, which is why treating threads as useless is wrong as a general claim.
Build the lost update: two threads each incrementing a shared counter a hundred thousand times, and show a final value below the expected sum, because an increment is a load, an add and a store and the thread can be suspended between them. Fix it with a lock and then measure what the lock costs.

Deliverable: Three measurements, threads against processes on CPU work, threads on I/O work, and a demonstrated lost update, each with the mechanism written underneath.

Day 5 — Debugging as a procedure rather than an instinct

Work one real failure as a bisection: find a revision or an input size where it is good and one where it is bad, halve repeatedly, and state the two assumptions bisection needs, that the property changes exactly once across the range and that the test is reliable.
Minimise one failing input to the smallest version that still fails, and record how many rounds it took.
Keep a hypothesis log for one bug in three columns, what I believe, what would disprove it, what I observed, and stop yourself the first time you are about to change two things at once.

Deliverable: One bug worked to root cause with a written hypothesis log and a minimised reproducing input.

Day 6 — Tests that catch the bug you are about to write

Implement an LRU cache with a capacity bound, then write the three test cases that would catch an off-by-one in eviction: insert exactly capacity items and assert nothing was evicted, insert one more and assert the least recently used key is the one gone, and read an old key just before that insert so the eviction victim changes.
Add a property test comparing your implementation against a deliberately slow reference, an ordered list scanned linearly, over a few thousand random operation sequences, because a slow reference finds the cases you would not have thought to write.
Write one numeric test that fails under exact equality and passes with a tolerance, and state why the tolerance has to be relative rather than absolute once the magnitudes grow.

Deliverable: An LRU implementation with three boundary tests, one property test against a slow reference, and one tolerance-based numeric test.

Day 7 — Debug something broken, out loud

Have someone plant three defects in a two-hundred-line program, an off-by-one, a shared mutable state bug, and a wrong error-handling path, then find them while narrating, under a fixed rule: state the hypothesis before touching anything.
Time each one and record which tool found it, reading, a printed value, a debugger, or a test, because the question asked in interviews is how you would find it rather than what it was.
Write the sentence you will use when you do not yet know the cause, one that names the next measurement instead of offering a guess.

Deliverable: A recorded debugging session with time-to-find per defect and the method that found each.

'We rewrote it in a better framework' is not a story

A migration is a cost you chose to pay, not an achievement. The story is what the old system made expensive, what you measured before committing, what kept serving traffic during the cutover, and what you would have done if the numbers had come back flat. Without those, a rewrite reads as taste.

Tell us about a time you had to solve a complex problem.

Medium · behavioural and engineering judgement

Approach

Give the blast radius: what could have broken, and what you measured.
State the situation in two sentences and spend the rest on the reasoning.
Name the disagreement and how you resolved it with evidence.

Follow-up

What would you do differently if you ran that again?
What did you decide not to do, and why?

Explain a past project you worked on.

Medium · behavioural and engineering judgement

Approach

Give the blast radius: what could have broken, and what you measured.
Name the disagreement and how you resolved it with evidence.
Pick a story where you made the decision, not one where you watched it.

Follow-up

What would you do differently if you ran that again?
How did you know your change caused the improvement?

Describe a scenario relevant to the job duties.

Medium · behavioural and engineering judgement

Approach

Name the disagreement and how you resolved it with evidence.
Pick a story where you made the decision, not one where you watched it.
State the situation in two sentences and spend the rest on the reasoning.

Follow-up

How did you know your change caused the improvement?
What did you decide not to do, and why?

What can you contribute to the public service?

Medium · behavioural and engineering judgement

Approach

Give the blast radius: what could have broken, and what you measured.
State the situation in two sentences and spend the rest on the reasoning.
Name the disagreement and how you resolved it with evidence.

Follow-up

What would you do differently if you ran that again?
How did you know your change caused the improvement?