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?
Model A fails with a syntax error because PostgreSQL does not support descending sort order inside standard B-tree indexes.
Model A acquires an ACCESS EXCLUSIVE lock that halts all SELECT queries, whereas Model B permits reads but still blocks writes.
Model B builds the index significantly faster than Model A by parallelizing disk writes inside a single atomic transaction block.
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).
Assume Order.customer uses SQLAlchemy's default lazy="select". What performance regression does this one-line diff introduce?
It loads every Customer row across all organizations into Python memory before filtering by org_id in the list comprehension.
Accessing o.customer.tier inside a list comprehension forces SQLAlchemy to open a separate database connection per order.
SQLAlchemy throws DetachedInstanceError immediately on the first item because .all() closes the active session.
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.
Reject as a breaking API change: removing total_cents breaks live /v1 clients instead of additively returning both fields.
Approve with a minor nit: renaming a JSON property on a GET endpoint is backwards-compatible because HTTP status codes stay 200.
Reject solely because invoice.totalCents uses camelCase internally while the JSON payload mixes snake_case field names.
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