Meta Site Reliability Engineer (Senior Level) Interview Preparation Guide

Direct answer

The tight worst-case bound here is $\Theta(n)$ time and $O(1)$ auxiliary space, not the $\Theta(n \log n)$ that the doubling inner loop might suggest at first glance: for a fixed outer value i, the inner loop runs only about $\log_2(n/i)$ times, and summing that quantity over all i turns out to telescope to a linear total, not a linearithmic one, once carried through carefully. If the outer loop body itself did $O(n)$ work instead of $O(1)$, the total becomes $O(n^2)$, since that O(n) cost is now paid once per outer iteration, n times, dominating the inner loop's own (still linear) total cost.

Structured elaboration

Step 1: count inner-loop executions for a fixed i

Starting from j = i, each inner iteration replaces j with 2j + 1. In closed form, after m iterations, $j_m = 2^m(i+1) - 1$. The loop stops as soon as j >= n, so the number of executions $t(i)$ for a given i is the smallest m with $2^m(i+1) - 1 \ge n$, which gives
$t(i) = \left\lceil \log_2\!\left(\frac{n+1}{i+1}\right) \right\rceil$

Step 2: sum across all outer iterations

$T(n) = \sum_{i=0}^{n-1} t(i) = \Theta\!\left(n + \sum_{i=0}^{n-1} \log_2\!\left(\frac{n}{i+1}\right)\right)$
(the added $n$ term accounts for the ceiling and the outer loop's own O(1) per-iteration bookkeeping). The sum splits cleanly:
$\sum_{i=0}^{n-1} \log_2\!\left(\frac{n}{i+1}\right) = \sum_{k=1}^{n} \left(\log_2 n - \log_2 k\right) = n\log_2 n - \log_2(n!)$

Step 3: this is where the naive intuition goes wrong, and Stirling's approximation resolves it

It is tempting to stop at "a sum of n logarithmic terms is $\Theta(n \log n)$" without simplifying $\log_2(n!)$ further, but $\log_2(n!)$ is itself $\Theta(n \log n)$, and the two $n \log_2 n$ terms above very nearly cancel. Using Stirling's approximation in natural-log form,
$\ln(n!) = n\ln n - n + O(\ln n)$
and converting to base 2 ($\log_2 x = \ln x / \ln 2$):
$\log_2(n!) = n\log_2 n - \frac{n}{\ln 2} + O(\log n)$
Substituting back:
$n\log_2 n - \log_2(n!) = n\log_2 n - \left(n\log_2 n - \frac{n}{\ln 2} + O(\log n)\right) = \frac{n}{\ln 2} + O(\log n) = \Theta(n)$
so the total is
$T(n) = \Theta(n)$
Intuitively: the inner loop runs many times only for the small handful of i near the very start (i close to 0 needs close to $\log_2 n$ iterations), and that count drops off so quickly as i grows that the sum across all i stays linear in n rather than growing to n times the average log factor.

Step 4: verifying the derivation against a direct operation count

Since this is a derived claim about growth rate, it is worth checking numerically before trusting it, by literally counting how many times the inner loop body executes:

import math


def count_inner_iterations(n: int) -> int:
    """
    Counts total O(1)-work executions of the inner while loop across all
    outer iterations, for direct comparison against the analytic bound.
    This counts operations, not wall-clock time.
    """
    total = 0
    for i in range(n):
        j = i
        while j < n:
            total += 1
            j = j * 2 + 1
    return total


if __name__ == "__main__":
    for n in [1_000, 10_000, 100_000, 1_000_000]:
        counted = count_inner_iterations(n)
        predicted = n * math.log2(n) if n > 1 else 0
        ratio = counted / predicted if predicted else float("nan")
        print(f"n={n:>8}  counted={counted:>9}  n*log2(n)={predicted:>12.1f}  ratio={ratio:.3f}")

Running this prints:

n=    1000  counted=     1994  n*log2(n)=      9965.8  ratio=0.200
n=   10000  counted=    19995  n*log2(n)=    132877.1  ratio=0.150
n=  100000  counted=   199994  n*log2(n)=   1660964.0  ratio=0.120
n= 1000000  counted=  1999993  n*log2(n)=  19931568.6  ratio=0.100

The counted total divided by n converges to almost exactly 2 as n grows (1.994, 1.9995, 1.99994, 1.999993), while the counted total divided by $n\log_2 n$ keeps shrinking toward 0 rather than settling at a constant. A quantity that is truly $\Theta(n \log n)$ would have a roughly constant ratio against $n \log_2 n$; a quantity that is truly $\Theta(n)$ has a ratio against $n \log_2 n$ that shrinks toward 0 as n grows, which is exactly the pattern above, confirming the $\Theta(n)$ derivation (the limiting ratio against n itself, about 2, is consistent with $1/\ln 2 \approx 1.44$ plus the O(1) per-outer-iteration bookkeeping folded in).

Complexity

Time: $\Theta(n)$, tight (both upper and lower bound, not just an upper bound). Space: $O(1)$ auxiliary, since only i and j are tracked regardless of n.

Edge cases

• n=0: the outer loop body never runs, so the total work is trivially $\Theta(1)$ (or 0, depending on how the base case is counted), consistent with the formula's leading term.
• i=0 is the single most expensive outer iteration, taking close to $\log_2 n$ steps; i near n-1 costs only 1 step (j starts already close to n).
• If the doubling step were instead j = j * 2 (without the +1), the same derivation applies with a one-off adjustment to the closed form for $j_m$, and the asymptotic result is unchanged.

Trade-offs & pitfalls

The single biggest pitfall on this exact problem is stopping the derivation one step early: summing n terms that are each individually $O(\log n)$ and concluding $O(n \log n)$ overall, without carrying through what $\sum \log_2(n/i)$ actually simplifies to via Stirling's approximation. That intuition is wrong here specifically because the terms in the sum shrink rapidly (as $\log_2(n/i)$ for growing i), rather than staying near their largest value the way they would if the inner loop's iteration count did not depend on i at all. This is exactly the kind of derivation the reproducibility standard requires showing step by step, and confirming numerically, rather than asserting from a memorized shape ("doubling inside a loop looks like $\log n$, so nested with an outer loop must be $n \log n$") that does not actually hold once the per-i cost is summed out. If the outer loop body itself does $O(n)$ work in addition to the inner while loop, that new cost is paid once per outer iteration regardless of the inner loop's behavior, adding $n \times O(n) = O(n^2)$ to the total, which now dominates the inner loop's own $\Theta(n)$ contribution; the overall complexity becomes $O(n^2)$.

Direct answer

Alert fatigue is what happens when on-call engineers get so many low-value, noisy, or duplicate pages that they start treating all alerts as probably-not-real, including the ones that matter. It's a trust problem as much as a technical one: once someone has been paged repeatedly in a night for something that turned out to be nothing, the next page, which might be the real incident, gets a slower, more skeptical response.

How I'd prevent it on a team I'm leading

• Deduplication and grouping: alerts that share a root cause (same service, same error type) should collapse into a single incident with a count, not fire a separate page per occurrence. This is usually a config change in the alerting tool (fingerprinting by service and error signature) rather than a code change.
• Severity tuning tied to required response time: not every alert deserves a page. A three-tier split (page now, notify during business hours, dashboard-only) forces every new alert to justify why it needs to interrupt someone's sleep.
• Actionable-by-default policy: no new paging alert ships without a linked runbook and a clear "what to check first." An alert with no next step is a dashboard panel that accidentally has a pager attached.
• Automated remediation for known, safe, repeatable fixes: if the same alert reliably resolves by restarting a stuck worker or clearing a queue, and that action is safe and idempotent, automate it and only page if the automated fix fails.
• A regular noise review: periodically look at which alerts fired most often and whether they led to real action; alerts that never lead to action get tuned or removed, not left running indefinitely out of habit.

Worked example

Suppose a team's on-call rotation is getting paged for "queue depth over 100" on a background job processor, firing several times a week, always self-resolving within a few minutes without anyone doing anything. Applying the framework above: first, check whether these spikes line up with a predictable traffic pattern (a nightly batch job, say) and if so, either raise the threshold above that expected peak or add a time-of-day exception. Second, if the queue really can back up unpredictably but always self-resolves within a known window without intervention, the alert should require a longer sustain window (e.g. "queue depth over 100 for 15 minutes") so it only fires when it isn't going to resolve on its own. Third, if manual intervention when it does page is always the same action (scale up worker count), that's a strong automated-remediation candidate: auto-scale on the same threshold, and only page if depth is still elevated after the auto-scale has had time to take effect.

Trade-offs and pitfalls

• Automated remediation without an audit trail or human confirmation for higher-severity cases can turn a noisy-alert problem into a silent-failure problem: the system "fixes" itself repeatedly while masking a root cause that's getting worse.
• Tuning thresholds purely to reduce page volume, without checking against real past incidents, risks quietly increasing false negatives; the goal is signal-to-noise, not just fewer pages.
• Alert fatigue prevention is not a one-time project. It needs an ongoing review cadence, because new alerts get added faster than old noisy ones get cleaned up if nobody owns the process.

Direct answer

Build trust before you need anything, by being reliable on small things, transparent about your constraints and capacity, and by giving the other person visibility into your world so they aren't surprised later. Waiting to invest in the relationship until you need a favor makes the ask feel transactional.

Framework

Lead with reliability on small things. Deliver on small, early commitments, answer a question promptly, show up to their planning session, so your word has a track record before there's a high-stakes ask on either side.

Be transparent about constraints. Proactively share capacity, risk, and known limitations rather than letting the other person find out the hard way, mid-project.

Give visibility into your world. Invite them into a review or share a roadmap or dashboard, so they understand your constraints without needing you to explain from scratch every time.

Make it reciprocal early. Ask what they need and what's on their plate too. Trust runs both directions, not just from you demonstrating value to them.

Worked example

Situation: a new product manager joins and will depend on your team, for example a platform or infrastructure team, for their roadmap.
Action: in the first couple of weeks, gave the PM read access to the team's capacity and roadmap view along with a short walkthrough, rather than waiting for them to ask. Proactively flagged one known constraint, a piece of infrastructure that was close to capacity, before it affected their planning. Followed through quickly and visibly on a small early request, answering a scoping question the same day, to establish reliability before anything high-stakes came up.
Result: by the time the PM had a genuinely high-stakes ask, an accelerated timeline, there was already a working relationship and a shared understanding of constraints. The conversation started from what's actually possible given what you already know, instead of starting from zero.

Trade-offs and pitfalls

• Trust-building gestures can look like busywork if they aren't tied to something concrete. Keep them small and genuinely useful, not performative.
• Over-sharing every constraint upfront can read as excuse-making before there's even a request. Calibrate to what's actually relevant to their planning.
• The senior differentiator is doing this proactively, before there's a need, rather than scrambling to build rapport only once you need something from the other person, which reads as transactional.

Direct answer
Catch an exception locally only when you can actually do something useful with it there (retry, fall back, translate it into a domain-specific error); otherwise let it propagate. Create a custom exception type when the caller needs to distinguish this failure from others programmatically; reuse a generic one when nobody downstream will branch on it.

Structured elaboration

• The 'can I do something' test: at each layer, ask whether you have enough context AND enough authority to resolve the failure. A repository layer that hits a broken connection can retry once; it usually can't decide whether the caller should show an error page or silently use cached data, so it should propagate (possibly wrapped) rather than swallow.
• Custom vs generic exceptions: define a custom type when a caller three layers up needs to branch (except PaymentDeclinedError: offer another card vs except PaymentGatewayTimeout: retry). A generic RuntimeError or bare Exception is fine for truly unexpected states nobody is meant to handle specially, but never use a bare except Exception: as a catch-all that discards the distinction.
• Avoiding pitfalls: never catch-and-discard (except Exception: pass) without at minimum logging with full context; never catch a broad type just to re-raise the same thing unchanged (that's dead code that hides which specific failures the layer actually expects); when you DO catch to translate, preserve the original exception as the cause (raise DatabaseError(...) from e in Python, wrapping in Java) so a debugger can still walk to the root cause. Also avoid duplicating log lines at every layer: if every layer that catches-and-rethrows also logs the same exception on its way up, one real failure produces several near-identical log entries by the time it reaches the top, which pages on-call with noisy duplicates and makes the true error rate impossible to read off log volume. The fix is to log ONCE, at the layer that either fully handles the error or is the outermost boundary (the request handler, the job runner), and let every intermediate layer that only wraps-and-rethrows do so silently, relying on the exception chain (from e) rather than a fresh log line to preserve context.

Worked example
Service-to-service call: a UserService.get_profile(id) calls an HTTP client that can raise ConnectionError or HTTPStatusError. UserService catches both, and translates HTTPStatusError with status 404 into its own UserNotFoundError (a real domain concept the caller branches on), but lets a 500 or ConnectionError propagate wrapped as UserServiceUnavailableError from e (preserving the cause) since it has no way to fix an unreachable dependency itself; it does NOT log at this layer, since it's not the final decision point.
Database operation: a repository's save(record) catches a unique-constraint violation and translates it to DuplicateRecordError (a business-meaningful case the caller likely wants to show the user), but lets a connection-pool-exhausted error propagate unmodified up to the request-handling layer, which is the only layer with enough context to decide 'shed load' vs 'retry', and is also the only layer that logs it.

Trade-offs and pitfalls
Over-translating (wrapping every low-level exception in a new custom type at every layer) produces an exception-type explosion that is as hard to reason about as no typing at all, and it can silently lose the original stack trace if you forget to chain the cause. Under-translating (letting raw library exceptions like ConnectionError leak all the way to a UI layer) forces every caller to know implementation details of a dependency two layers down. The right amount of translation happens at trust/ownership boundaries (crossing into a new bounded context, crossing a network call, crossing into user-facing code), not at every function call, and the same discipline applies to logging: log at the boundary that owns the decision, not reflexively at every catch block.

Direct answer

The database's own error text, "duplicate key value violates unique constraint," looks identical whether the cause is a genuine two-writer race, a re-run migration script, or real pre-existing duplicate data, because the constraint's whole job is to make all three collisions look the same at the SQL layer. Distinguishing them means bringing in evidence the database itself doesn't retain: an independent request log correlated against the failing key, a direct query against the source data for pre-existing duplicates, and a read of the migration script for idempotency. Mitigate the live impact by treating the race case as an expected, catchable outcome rather than an error, without touching the constraint itself.

Structured elaboration

Race condition check

Correlate an application-level log of every write attempt (not just successful writes) against the failing key. That log must be committed in its own transaction, separate from the risky write attempt; logging it inside the same transaction as the write means a failed write rolls the log entry back too, destroying the exact evidence the investigation needs. If two different client or session identifiers attempted the same key within a tight window (milliseconds to low tens of milliseconds), that's the race signature. One client retrying after a long gap and hitting an already-committed row is a normal, correct idempotent retry, not a bug, and should be treated as success by the caller.

Migration script bug check

Read the migration for whether reruns are idempotent (INSERT ... ON CONFLICT DO NOTHING or MERGE, versus a plain INSERT that fails loudly, or worse silently double-inserts, on a partial rerun after a timeout), whether it deduplicates the source data before inserting rather than assuming it's already unique, and whether its own execution log shows any sign it ran twice (two rows in a migration ledger, or overlapping time ranges in its own job log).

Genuinely inconsistent data check

Query the source (legacy or pre-migration) table directly for existing duplicates on the column the new constraint enforces: SELECT key_col, count(*) FROM legacy GROUP BY key_col HAVING count(*) > 1. If duplicates already existed upstream, the constraint is correctly surfacing a pre-existing data-quality problem the old system silently tolerated, not a migration bug or a race.

Immediate mitigation while investigating

For the race case specifically, catch the unique-violation and treat it as expected: re-select the row that already exists, and if its content matches what you intended to write, return success rather than an error to the caller. This is the standard idempotent-upsert pattern (INSERT ... ON CONFLICT (key) DO NOTHING followed by a SELECT, or DO UPDATE if a merge is genuinely safe). It doesn't fix the underlying cause, but it stops user-visible errors for the likely-majority-benign race case while the migration-bug and bad-data hypotheses, which need a human decision, get investigated.

Worked example

Real output from Postgres 16. First, the genuinely-inconsistent-data check against a legacy table that already has a duplicate before any migration touches it:

SELECT idempotency_key, count(*) AS dup_count, max(created_at) - min(created_at) AS time_spread
FROM legacy_payments
GROUP BY idempotency_key
HAVING count(*) > 1;

 idempotency_key | dup_count | time_spread
------------------+-----------+-------------
 idem-7001        |         2 | 00:00:04

Then, a real unique constraint and two genuinely concurrent sessions racing to insert the same new key:

CREATE TABLE payments (
  id bigserial PRIMARY KEY,
  idempotency_key text NOT NULL,
  amount numeric NOT NULL,
  CONSTRAINT payments_idempotency_key_uq UNIQUE (idempotency_key)
);

Session A and session B, started 20 milliseconds apart, both attempt INSERT INTO payments (idempotency_key, amount) VALUES ('idem-9002', 25.00);. Session B wins; session A gets:

ERROR:  duplicate key value violates unique constraint "payments_idempotency_key_uq"
DETAIL:  Key (idempotency_key)=(idem-9002) already exists.

The exact same error text as the pre-existing-duplicate case above; nothing in it says which cause produced it. The request-log correlation query, run against attempts logged in their own committed transactions before each insert was tried, is what actually distinguishes them:

SELECT idempotency_key, count(*) AS attempts, count(DISTINCT client_request_id) AS distinct_clients,
       max(received_at) - min(received_at) AS spread
FROM request_log GROUP BY idempotency_key HAVING count(*) > 1 ORDER BY spread;

 idempotency_key | attempts | distinct_clients |     spread
------------------+----------+-------------------+-----------------
 idem-9002        |        2 |                 2 | 00:00:00.022678

Two distinct clients, 22.6 milliseconds apart: the race signature, versus the legacy duplicate's 4-second spread above, which is far too slow to be a race and instead reflects a real pre-existing double-submit the old system accepted. The order-of-magnitude difference in spread is itself diagnostic.

While building this, logging the request attempt inside the same transaction as the risky insert made the log entry for the losing session vanish entirely when that transaction rolled back on the constraint violation, taking the very evidence needed for this investigation with it. That's exactly the pitfall stated above, discovered by hitting it directly: log attempts in their own committed transaction, always, before the risky write is attempted.

Trade-offs and pitfalls

• Don't disable or drop the constraint to stop the errors; that removes the only thing currently preventing real duplicate business records, even while it's noisy.
• The idempotent-catch mitigation is for the race case only. Applying it blindly to the inconsistent-data case would silently paper over real duplicate business records that need a human decision about which of the conflicting rows is actually correct.
• The transaction-boundary mistake above (logging inside the same transaction as the write it's meant to explain) is easy to make and destroys exactly the evidence a race-versus-bug investigation depends on.

Direct answer
TCP's retransmission timeout (RTO) is computed from a smoothed estimate of round-trip time (SRTT) plus a term for how MUCH the round-trip time has been varying (RTTVAR), not from RTT alone, so the timer stays reasonably tight on a stable path and automatically widens on a noisy one.

Structured elaboration
The standard algorithm (RFC 6298) updates two running estimates on every new RTT sample:
$\text{RTTVAR} = (1-\beta)\cdot \text{RTTVAR} + \beta \cdot |\text{SRTT} - \text{RTT}_{\text{sample}}|$
$\text{SRTT} = (1-\alpha)\cdot \text{SRTT} + \alpha \cdot \text{RTT}_{\text{sample}}$
with the standard constants $\alpha = 1/8$ and $\beta = 1/4$. The retransmission timeout itself is then:
$\text{RTO} = \text{SRTT} + \max(\text{clock granularity}, 4 \cdot \text{RTTVAR})$
The intuition: if round-trip times are steady (RTTVAR is small), RTO sits close to the smoothed RTT, so genuine loss is detected quickly. If round-trip times are jittery (RTTVAR is large, common on congested or highly variable paths), RTO widens automatically, so ordinary jitter isn't mistaken for loss and doesn't trigger a storm of unnecessary retransmissions.

Retransmissions triggered purely by the RTO timer firing (as opposed to fast retransmit via duplicate ACKs) are treated as a MUCH stronger signal of serious congestion, since it means not even a single later segment's ACK arrived to trigger a duplicate-ACK-based fast retransmit; the response is to collapse all the way back to slow start, not the gentler fast-recovery halving.

Worked example
Suppose a connection's SRTT has settled around 50ms with RTTVAR around 10ms. RTO would be approximately $50 + 4\times10 = 90$ ms. If a burst of network jitter pushes several samples up to 120ms, RTTVAR grows to reflect that variability (say to 25ms), and RTO widens to roughly $70 + 4\times25 = 170$ ms (SRTT itself also shifts upward, more slowly, toward the new samples). This is why an environment full of many short-lived connections (each starting from scratch with no RTT history) behaves differently from one with few long-lived connections (which have had time to build a stable, well-calibrated SRTT/RTTVAR estimate): short-lived connections are stuck using a generic initial RTO (commonly 1 second per RFC 6298) until they've collected enough samples to calibrate, making them systematically slower to detect a REAL loss early in their life.

Trade-offs & pitfalls
A common mistake is assuming a single fixed RTO value would be simpler and just as effective; a fixed timeout either fires too eagerly on a naturally variable path (spurious retransmissions that waste bandwidth and can trigger unnecessary congestion-window collapses) or too slowly on a stable path (wasted time before a real loss is detected), the adaptive SRTT/RTTVAR scheme exists specifically to avoid both failure modes at once.

Was it worth it

I'd put both sides of the trade in the same currency, dollars, rather than comparing a cost saving to a latency number, which can't be compared directly. Say the consolidation saves $50,000/month in infrastructure. If tail latency crept up enough to cost a 0.3% relative dip in conversion (a small relative decline in the share of visitors who convert, not 0.3 percentage points of the conversion rate itself, which would be a much bigger relative hit unless baseline conversion were near 100%) on a $2M/month revenue base, that's $2,000,000 x 0.003, roughly $6,000/month in estimated lost revenue. Net, the trade is still strongly positive, but only because the loss is small relative to the gain; a bigger relative dip, on the order of a few percent, would already erase most of the $50,000 saved, and on a higher-value funnel the sign could flip even sooner.

Watching the trend, not just the snapshot

I'd check whether the conversion dip is transient, users adjusting to a slightly different experience, and recovering, or a persistent tax that compounds as traffic grows. A one-time 0.3-point dip that stabilizes is a different situation than one that keeps widening.

Getting product and finance to agree on guardrails

I'd propose a joint, pre-agreed threshold before any further consolidation happens: for example, "we will not let p99 (99th-percentile) latency exceed a stated ceiling, or conversion drop below a stated floor, without triggering an automatic review." That threshold gets monitored continuously with a named owner, rather than living as a one-time conversation that gets forgotten.

Process

I'd get explicit sign-off from both product, who owns conversion, and finance, who owns cost, on the specific numbers and the escalation path if a guardrail trips, and set a recurring review, say monthly, so the trade-off is being actively managed rather than just approved once and never revisited.

Direct answer

Investigate before touching anything: recompute the summary from the source table and
compare, because the shape of the drift (a clean offset versus scattered small
differences) tells you what kind of bug caused it. Reconcile the data by fixing it toward
the recomputed truth and verifying zero drift remains with the same comparison query. Then
close the loop structurally, make it impossible for the source to change without the
summary changing too, rather than trusting a convention that already failed once.

Investigate, reconcile, prevent

Investigate. Run a read-only comparison: recompute the aggregate directly from the
source table and diff it against the stored summary, before writing anything. The
pattern of the difference is diagnostic: a consistent offset across many rows suggests
a bulk operation (a backfill, a data migration, a manual correction) that bypassed the
code path responsible for keeping the summary in sync. Scattered, small, inconsistent
differences suggest a race condition between concurrent updates to the same row.

Reconcile. Once you understand the scope (how many rows, how large a gap), apply a
targeted update setting the drifted rows to the recomputed truth, inside a transaction,
during low-traffic hours if it touches a large number of rows. Re-run the exact same
comparison query afterward and confirm zero rows remain drifted, not a weaker or
different check that might miss the same class of bug.

Prevent recurrence. Find every code path that can change the source data, not only
the one you expect, and make the summary update either impossible to skip or independent
of application code entirely: a database trigger that fires in the same transaction as
the source write (so there is no path that can write source data without touching the
summary, by construction), or a change-data-capture (CDC) pipeline that reads the
database's write-ahead log and reliably emits every change regardless of which code path
made it, rather than relying on an application convention of "remember to also update
this," which is precisely the convention that failed here. Add a standing, scheduled
reconciliation job that alerts rather than silently auto-fixes, so a new drift is caught
in hours, not discovered again in six months.

Worked example (executed against a real PostgreSQL 16 instance)

CREATE TABLE orders (order_id bigserial PRIMARY KEY, user_id bigint NOT NULL, status text NOT NULL);
INSERT INTO orders (user_id, status)
  SELECT (g % 1000) + 1, 'completed' FROM generate_series(1, 50000) g;

CREATE TABLE user_order_counts (user_id bigint PRIMARY KEY, completed_orders int NOT NULL);
INSERT INTO user_order_counts
  SELECT user_id, count(*) FROM orders WHERE status='completed' GROUP BY user_id;

-- simulate the missed-update bug: a bulk correction that bypassed the
-- code path responsible for keeping user_order_counts in sync
UPDATE orders SET status = 'refunded' WHERE order_id % 37 = 0;   -- UPDATE 1351

Reconciliation query, reusing the summary's own definition exactly (status='completed')
so the comparison can't manufacture false drift:

SELECT count(*) AS drifted_users
FROM user_order_counts c
JOIN (SELECT user_id, count(*) AS actual FROM orders
      WHERE status='completed' GROUP BY user_id) t
  ON t.user_id = c.user_id
WHERE c.completed_orders <> t.actual;
-- drifted_users = 1000   (every user in this 1,000-user demo was touched)

The fix, and re-verification with the identical query:

UPDATE user_order_counts c
SET completed_orders = t.actual
FROM (SELECT user_id, count(*) AS actual FROM orders
      WHERE status='completed' GROUP BY user_id) t
WHERE t.user_id = c.user_id AND c.completed_orders <> t.actual;
-- UPDATE 1000

-- re-run the same comparison query:
-- drifted_users_after_fix = 0

Zero drift confirmed after the fix, not assumed.

Trade-offs and pitfalls

• Fixing today's numbers without also fixing the code path guarantees the same bulk
  operation drifts the numbers again the next time anyone runs something similar.
• A synchronous trigger is the most airtight prevention, no code path can skip it, but
  adds write latency and lock contention directly on the source table's hot path. CDC-
  based materialization decouples that cost (it runs asynchronously) at the price of a
  short, bounded staleness window and the operational overhead of running a CDC pipeline.
• Calling it done once the counts are fixed, without adding a standing monitor, means the
  next drift again goes unnoticed for months.
• A reconciliation query whose filter doesn't exactly match the summary's own definition,
  for example forgetting a soft-delete condition the summary already accounts for, can
  manufacture false drift or, worse, mask real drift; reuse the exact same definition, as
  the example above does.

Starvation means a task or class of work is ready to run but is never granted the resource it needs, because other work keeps getting it first. The system is busy and not deadlocked (nobody is waiting in a cycle), yet one party makes no progress. In a livelock everyone is busy and none progresses; in starvation most work progresses and a victim does not.

A concrete service scenario

A backend worker pulls jobs from two queues: interactive API requests (high priority) and batch report exports (low priority). The rule is "always take interactive first". On a quiet day batch jobs drain in the gaps. During a busy hour interactive requests arrive as fast as the worker can serve them, the interactive queue is never empty, and the batch queue only grows. Exports time out at the client, support tickets arrive, yet CPU is high and the throughput graph looks healthy. The same shape appears with a reader-preferring reader-writer lock (readers keep entering, a writer waits forever), an unfair mutex, and a thread pool where a long job class holds every thread.

A small model

One worker, 10 ms per job, interactive arrivals every 10 ms (the worker's whole capacity), batch arrivals every 100 ms, simulated for 10 s of virtual time. The simulation is deterministic, so its output is the same on every run:

from collections import deque

def simulate(reserve=0, cap=None):
    """One worker, one job per 10 ms slot, 1000 slots (10 s).
    Interactive job arrives every 10 ms, batch job every 100 ms.
    reserve=N: every Nth slot goes to batch if a batch job is waiting.
    cap=C: refuse an interactive arrival when C interactive jobs already wait."""
    hi, lo = deque(), deque()
    hi_served = lo_served = shed = 0
    max_lo_wait = None
    for slot in range(1000):
        t = slot * 10
        if cap is not None and len(hi) >= cap:
            shed += 1
        else:
            hi.append(t)
        if t % 100 == 0:
            lo.append(t)
        if reserve and slot % reserve == reserve - 1 and lo:
            wait = t - lo.popleft()
            lo_served += 1
            max_lo_wait = wait if max_lo_wait is None else max(max_lo_wait, wait)
        elif hi:
            hi.popleft()
            hi_served += 1
        elif lo:
            wait = t - lo.popleft()
            lo_served += 1
            max_lo_wait = wait if max_lo_wait is None else max(max_lo_wait, wait)
    return hi_served, lo_served, shed, len(hi), len(lo), max_lo_wait

rows = [("strict priority", simulate()),
        ("1 slot in 5 reserved", simulate(reserve=5)),
        ("reserved + queue cap 50", simulate(reserve=5, cap=50))]
print(f"{'policy':26}{'hi served':>10}{'lo served':>10}{'shed':>6}{'hi queued':>10}{'lo queued':>10}{'max lo wait ms':>16}")
for name, (h, l, s, hq, lq, w) in rows:
    print(f"{name:26}{h:>10}{l:>10}{s:>6}{hq:>10}{lq:>10}{str(w):>16}")

Run in a python:3.12-slim container it prints:

Column key: hi served and lo served are the interactive and batch jobs finished in the 10 s; shed is jobs refused outright; hi queued and lo queued are jobs still waiting at the end; max lo wait ms is the longest a batch job waited before a worker took it (None means no batch job was ever served, so there is no wait to measure).

policy                     hi served lo served  shed hi queued lo queued  max lo wait ms
strict priority                 1000         0     0         0       100            None
1 slot in 5 reserved             900       100     0       100         0              40
reserved + queue cap 50          900       100    51        49         0              40

Strict priority served all 1000 interactive jobs and none of the 100 batch jobs. Reserving one dispatch in five for batch served every batch job (waiting at most 40 ms), at the price of 100 interactive jobs left queued: the interactive class alone needed 100% of capacity, so giving batch any slots must push a backlog somewhere. The reserve is a guarantee of up to 20% of slots, not a quota that is always spent: only 100 batch jobs arrived against 1,000 dispatch slots (10 s at 10 ms each), so batch used 10% of them and interactive got the other 900. The 100 interactive jobs left queued are exactly the 100 slots batch consumed. Capping the interactive queue at 50 turns that backlog into explicit shedding (51 jobs refused) instead of unbounded latency. The model is idealised (fixed job size, one worker), so read the pattern, not the exact counts.

How I would mitigate, in order

1. Detect it with the right metric. Throughput and CPU look fine. Alert on age of the oldest item per class and per-class completion rate; starvation is invisible in an average.
2. Guarantee a floor. Reserve a share (a slot in N, a separate small pool, or a minimum weight in a weighted fair queue, which hands each class a share of dispatches in proportion to its weight) for each class. This is the fix I would ship first, because it bounds a victim's wait regardless of load.
3. Age priorities. For many classes, raise a job's effective priority the longer it waits, so any job eventually outranks new arrivals.
4. Bound the dominant class. Cap its queue or concurrency and shed or defer its excess (as in the third row), so that high priority cannot consume 100% of a shared worker.
5. Use fair primitives. Choose a fair lock (one that hands the lock to waiters in arrival order, a FIFO hand-off lock) or a writer-preferring or fair reader-writer lock where writers must not wait indefinitely, and know its throughput cost. A reader-preferring reader-writer lock, by contrast, lets new readers in while a writer waits, which is the starvation case described earlier.
6. Fix capacity. Starvation under saturation is a capacity signal. Priorities choose who suffers; they do not create capacity.

I would recommend 2 plus 4 plus the age metric: a floor protects the low class, the cap protects the system, and the metric tells you when the real fix is more workers.

A 90-day, hourly-granularity burn-rate series needs an efficient way to compute a 30-day TRAILING sum at every hour, which is a classic windowed-aggregation problem, not a per-row calculation.

Structured elaboration

Rather than recomputing a 30-day sum from scratch for every output hour (an O(n * window) approach), a window function computes each trailing sum in a single pass. First pre-aggregate raw requests into hourly buckets (service, hour_bucket, total, bad), converting each bucket's timestamp into an integer hour-index (hour_num) so the frame can use a numeric RANGE (dates directly are not portable across engines for RANGE frames). Then a SUM(...) OVER (PARTITION BY service ORDER BY hour_num RANGE BETWEEN 719 PRECEDING AND CURRENT ROW) (719 = 30 days x 24 hours - 1) gives the true trailing 30-day total and bad count at every hour in a single pass; the burn rate at each hour is the ratio of the trailing bad-to-total requests, normalized by the SLO's allowed error fraction.

Worked example (executed; sqlite3, hourly aggregation and 30-day trailing window confirmed against a small synthetic dataset)

WITH hourly AS (
  SELECT service,
         strftime('%Y-%m-%d %H:00:00', timestamp) AS hour_bucket,
         CAST(strftime('%s', strftime('%Y-%m-%d %H:00:00', timestamp)) / 3600 AS INTEGER) AS hour_num,
         COUNT(*) AS total,
         SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS bad
  FROM requests
  WHERE timestamp >= datetime('now', '-90 days')
  GROUP BY service, hour_bucket
),
trailing AS (
  SELECT service, hour_bucket, hour_num, total, bad,
         SUM(total) OVER (PARTITION BY service ORDER BY hour_num
             RANGE BETWEEN 719 PRECEDING AND CURRENT ROW) AS total_30d,
         SUM(bad) OVER (PARTITION BY service ORDER BY hour_num
             RANGE BETWEEN 719 PRECEDING AND CURRENT ROW) AS bad_30d
  FROM hourly
)
SELECT service, hour_bucket, total_30d, bad_30d,
       ROUND(bad_30d * 1.0 / total_30d, 4) AS observed_error_rate_30d,
       ROUND((bad_30d * 1.0 / total_30d) / 0.001, 2) AS burn_rate_30d_vs_999_slo
FROM trailing
ORDER BY hour_bucket;

Against a small synthetic requests table (three consecutive hourly buckets with own error counts of 1/3, 0/2, and 2/3 respectively), the trailing 30-day totals accumulate correctly across hours: hour 1 (3 total, 1 bad, own rate 33.33%, no prior history so trailing = own = 333.33x); hour 2 (cumulative 5 total, 1 bad, trailing rate 20% -> 200.00x, which is LOWER than hour 2's own instantaneous 0% would suggest in isolation only because it is blended with the still-elevated hour 1 inside the trailing window); hour 3 (cumulative 8 total, 3 bad, trailing rate 37.5% -> 375.00x). These trailing figures were confirmed by running the query and differ meaningfully from a naive per-hour-only calculation, which is exactly the bug in a non-trailing implementation: a single bad hour would appear to "clear" the alert the moment the next hour is healthy, when in reality 30 days of accumulated bad requests are still being carried forward.

Trade-offs and pitfalls

SQLite's window RANGE frame only supports numeric offsets, not native date/time intervals, which is why the hour timestamp is first cast to an integer hour-index; PostgreSQL supports RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW directly on a timestamp column, which is the more natural production choice if available. At 90 days of hourly data per service this is a modest row count for the window function, but if this pattern is applied at per-minute granularity across many services, a windowed aggregate over raw (rather than pre-aggregated hourly) rows becomes expensive fast; pre-aggregating to hourly rollups (as done here) before applying the window is the standard mitigation, and the window itself should be computed on the much smaller hourly table, never re-scanning raw per-request rows for every output hour.