AnnotationPrep

Multi-File Git Diffs, Migrations & Concurrency practice questions

20 questions from the SWE-Bench & GitHub PR review RLHF practice test.

Start practising

Multi-File Git Diffs, Migrations & Concurrency practice drill

10 scenarios, graded as you go, with the exact grading principle for every answer. Start free, no sign-up.

Sample scenarios

PROMPT

Add a composite index on (org_id, created_at DESC) for the high-write audit_events table in PostgreSQL without disrupting ingestion.

MODEL A
SQL
CREATE INDEX idx_audit_org_created ON audit_events (org_id, created_at DESC);
MODEL B
SQL
-- alembic: disable_transaction = true
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_org_created
  ON audit_events (org_id, created_at DESC);

Which comparison accurately justifies preferring Model B over Model A?

  1. Model A fails with a syntax error because PostgreSQL does not support descending sort order inside standard B-tree indexes.
  2. Model A acquires an ACCESS EXCLUSIVE lock that halts all SELECT queries, whereas Model B permits reads but still blocks writes.
  3. Model B builds the index significantly faster than Model A by parallelizing disk writes inside a single atomic transaction block.
  4. Model A takes a SHARE lock blocking writes; Model B uses CONCURRENTLY outside a transaction so writes proceed.
Show the answer

D. Model A takes a SHARE lock blocking writes; Model B uses CONCURRENTLY outside a transaction so writes proceed.

A standard CREATE INDEX in PostgreSQL takes a SHARE lock, which permits concurrent SELECT reads but blocks all writes (INSERT, UPDATE, DELETE) for the entire build duration—causing ingestion timeouts on busy tables. CREATE INDEX CONCURRENTLY takes a SHARE UPDATE EXCLUSIVE lock so writes keep flowing, and Model B correctly disables transaction wrapping because Postgres rejects CONCURRENTLY inside a transaction block.

Principle: PostgreSQL Index Locks: CREATE INDEX vs. CREATE INDEX CONCURRENTLY

PROMPT

Add each order's customer_tier to the GET /orders list endpoint (default page size: 200).

Submitted PR diff:

DIFF
 def list_orders(session, org_id: int):
     orders = (
         session.query(Order)
         .filter(Order.org_id == org_id)
         .limit(200)
         .all()
     )
     return [
-        {"id": o.id, "total": o.total_cents}
+        {"id": o.id, "total": o.total_cents, "tier": o.customer.tier}
         for o in orders
     ]

Assume Order.customer uses SQLAlchemy's default lazy="select". What performance regression does this one-line diff introduce?

  1. It loads every Customer row across all organizations into Python memory before filtering by org_id in the list comprehension.
  2. Accessing o.customer.tier inside a list comprehension forces SQLAlchemy to open a separate database connection per order.
  3. SQLAlchemy throws DetachedInstanceError immediately on the first item because .all() closes the active session.
  4. An N+1 query regression: 1 query fetches 200 orders, then up to 200 extra queries lazy-load o.customer inside the loop.
Show the answer

D. An N+1 query regression: 1 query fetches 200 orders, then up to 200 extra queries lazy-load o.customer inside the loop.

With default lazy="select", touching o.customer on each Order fires a separate SELECT * FROM customers WHERE id = ? query (unless cached in the session identity map). On a 200-item page, a single-line serializer addition explodes latency from 1 query to up to 201 queries. The proper fix eager-loads the relationship via .options(joinedload(Order.customer)) or selectinload(Order.customer).

Principle: ORM Performance: Detecting N+1 Query Regressions in Serializers

PROMPT

Standardize the public /v1/invoices/:id JSON field name from total_cents to amount_cents without breaking existing mobile and webhook consumers.

Submitted PR diff:

DIFF
--- a/src/routes/invoices.ts
+++ b/src/routes/invoices.ts
   res.json({
     id: invoice.id,
-    total_cents: invoice.totalCents,
+    amount_cents: invoice.totalCents,
     currency: invoice.currency,
   });

How should an evaluator grade this PR?

  1. Reject as a breaking API change: removing total_cents breaks live /v1 clients instead of additively returning both fields.
  2. Approve with a minor nit: renaming a JSON property on a GET endpoint is backwards-compatible because HTTP status codes stay 200.
  3. Reject solely because invoice.totalCents uses camelCase internally while the JSON payload mixes snake_case field names.
  4. Approve if the PR also updates openapi.yaml, because updating the specification automatically protects older mobile clients.
Show the answer

A. Reject as a breaking API change: removing total_cents breaks live /v1 clients instead of additively returning both fields.

Public versioned APIs (/v1/...) must preserve backwards compatibility for pinned mobile apps and third-party integrations. Removing total_cents in place causes existing clients to read undefined or crash deserialization. The non-breaking pattern is additive deprecation: emit both total_cents and amount_cents in /v1, document total_cents as deprecated, and remove it only in /v2 or after a sunset window.

Principle: API Evolution: Additive Deprecation vs. Breaking Contract Changes

Other topics