Database and distributed-systems interview questions

Status: Complete for the backend data and distributed-systems core (Wave 3). Last reviewed 2026-08-28.

This canonical bank grows with the data and distributed-systems bundles. Example answers model credible spoken responses and should name the chosen engine and invariant.

Relational guarantees, indexes, and isolation

DATA-RELATIONAL-01 — Protect inventory under concurrency

Design inventory reservation without a read-check-write race, including retries and release.

DATA-RELATIONAL-02 — Explain isolation without a generic table

How would you explain transaction anomalies and choose an isolation strategy without assuming all database engines behave identically?

DATA-RELATIONAL-03 — Choose a composite index

Choose and justify an index for a tenant-scoped, status-filtered feed ordered by creation time.

DATA-RELATIONAL-04 — Diagnose a query plan

A query returns few rows but becomes slow for one large tenant. Give a plan-led diagnosis.

DATA-RELATIONAL-05 — Choose a concurrency mechanism

Contrast a unique constraint, conditional update, row lock, advisory lock, and optimistic version check.

DATA-RELATIONAL-06 — Deploy a compatible schema change

Add a required derived column to a large live table while old and new application versions coexist.

Cache and Redis guarantees

DATA-CACHE-01 — Define a cache contract

Define cache-aside for tenant-scoped product data, including key, freshness, invalidation, and failure behavior.

DATA-CACHE-02 — Prevent a cache stampede

A popular expensive report expires at once for many callers. Design refresh and overload behavior.

DATA-CACHE-03 — Explain Redis durability and failover

What can be lost after an acknowledged Redis write, and how do persistence, replication, and failover affect the answer?

DATA-CACHE-04 — Design a safe lease

Why can two clients holding successive expiring Redis locks both act, and when do fencing tokens help?

DATA-CACHE-05 — Diagnose a hot key and eviction

A Redis cluster has a high hit ratio but one endpoint has rising latency and source load. Diagnose it.

DATA-CACHE-06 — Degrade during cache failure

Redis becomes slow and intermittently unavailable. How should a cache-dependent service avoid cascading failure?

Distributed delivery and workflows

DATA-DELIVERY-01 — Make a payment consumer retry-safe

A payment worker can crash after the provider succeeds but before broker acknowledgement. Design safe redelivery.

DATA-DELIVERY-02 — Compare after-commit dispatch and outbox

What failure does dispatch-after-commit prevent, what gap remains, and how does an outbox change it?

DATA-DELIVERY-03 — Define the required ordering

When is global ordering unnecessary, and how would you preserve only the ordering a domain needs?

DATA-DELIVERY-04 — Recover an ambiguous external outcome

An external provider timed out, local outcome recording failed, and a retry is due. Decide the next action.

DATA-DELIVERY-05 — Design a saga

Coordinate reservation, payment, and fulfilment across services, including compensation and stuck workflows.

DATA-DELIVERY-06 — Build reconciliation

Design a reconciliation process for local payments versus provider settlements.

Example answers

DATA-RELATIONAL-01 — Example answer

Solid response: I make the database own “available cannot go below zero.” One option is an atomic conditional update that decrements only when available quantity is sufficient, then checks affected rows. In the same short transaction I insert a reservation with a unique operation ID and expiry. A retry with the same ID returns the same reservation.

Release is another idempotent state transition that increments once, not an unguarded write.

Senior extension: For multiple SKUs I lock/update in stable order or use a retryable serializable strategy and define all-or-nothing versus partial reservation. Expiry is processed with concurrency-safe claiming and reconciliation. I avoid holding locks during payment calls; an outbox advances the workflow after commit. Tests force interleavings against the production engine and assert both stock and ledger invariants.

DATA-RELATIONAL-02 — Example answer

Solid response: I define dirty/non-repeatable reads, phantoms, lost updates, and write skew, then name the engine. Isolation labels differ: PostgreSQL and InnoDB implement snapshots and locking differently. I start from the invariant and statements—what rows/predicate are read, what is written, and whether conflicts block, abort, or pass.

I use atomic statements/constraints first, then the weakest verified isolation that preserves correctness.

Senior extension: Serializable execution may abort and requires whole-transaction retries. Repeatable reads do not universally prevent write skew, and a transaction wrapper alone does not protect read-check-write. I inspect vendor docs/configuration and create a controlled concurrent test. External effects stay outside retryable transaction bodies or carry idempotency.

DATA-RELATIONAL-03 — Example answer

Solid response: For WHERE tenant_id=? AND status=? ORDER BY created_at DESC, id DESC LIMIT ?, I would test a B-tree such as (tenant_id, status, created_at DESC, id DESC). Equality columns narrow the range and the remaining keys provide deterministic order without a separate sort.

I verify with the real plan and representative tenant/status distributions rather than relying only on column selectivity.

Senior extension: I check whether queries omit status, whether a partial index fits, and whether covering selected columns is worth size/write cost. Skew, stale statistics, pagination direction, null semantics, and engine capabilities can change the choice. I compare rows examined, heap/table fetches, sort work, write latency, and redundant indexes before and after.

DATA-RELATIONAL-04 — Example answer

Solid response: I capture exact SQL and parameters and run the engine’s execution plan tools with production-like data. I compare estimated and actual rows, loops, access method, filters, sort/hash work, and buffers/I/O. A large tenant may expose skew or correlation that global statistics miss, causing a wrong join order or broad scan.

I also check query count and lock/pool wait so execution is not confused with queueing.

Senior extension: Potential fixes include updated/extended statistics, a tenant-aligned composite/partial index, a sargable predicate, changed pagination, or query decomposition. I test other tenant sizes and write impact; forcing a plan is a last resort with ownership. Parameterized plan caching and replica differences are included in the reproduction.

DATA-RELATIONAL-05 — Example answer

Solid response: A unique constraint is the final owner of uniqueness. A conditional update atomically changes state only when a predicate holds. A row lock reserves selected rows for a short decision under contention. An optimistic version rejects stale writers without waiting. An advisory lock coordinates cooperating sessions around an application key but does not protect against code that ignores it.

I choose from the invariant, contention, rows involved, and retry behavior.

Senior extension: Lock predicates must be indexed and acquisition ordered to limit deadlocks. Session-scoped advisory locks interact with pools. Optimistic conflict retries must recompute intent, not blindly replay stale output. Cache/distributed locks can assist work coordination but do not replace durable constraints when lease expiry or failover admits two holders.

DATA-RELATIONAL-06 — Example answer

Solid response: I use expand/migrate/contract. Add the nullable column without breaking old code, deploy new code that writes both or can derive/fallback, backfill in bounded resumable primary-key batches, validate completeness, then switch reads. Only after all old processes are gone do I enforce not-null and remove compatibility paths.

Each step has metrics and a pause/roll-forward plan.

Senior extension: I verify whether the exact DDL rewrites or locks the table for the deployed engine/version and use online/concurrent facilities where appropriate. Backfill throttles on lock, replica, and application pressure and handles concurrent writes. Constraint validation may be separated from creation. Mixed-version queue workers and rollback data compatibility are tested, not assumed.

DATA-CACHE-01 — Example answer

Solid response: The database owns products. The key includes tenant, product ID, locale/currency if they select output, and schema version. Cache-aside loads on a miss and stores for a TTL chosen from acceptable staleness. Product writes invalidate or publish a version change, while readers still tolerate stale/missing entries.

Redis failure falls back to the database with bounded concurrency; it never returns another tenant’s key.

Senior extension: I define negative-cache TTL, serialization compatibility, stale-on-error policy, warmup, and who owns invalidation failures. I measure hit ratio per workload, miss latency, evictions, value size, and source load. Authorization-sensitive projections either include every policy dimension with reliable invalidation or are not shared-cached.

DATA-CACHE-02 — Example answer

Solid response: I keep an acceptable stale copy and let one caller acquire a short refresh lease; others serve stale or wait briefly according to the contract. The refresher regenerates with a source concurrency limit and atomically publishes the new value. TTL jitter or probabilistic early refresh reduces synchronized expiry.

If refresh fails, the lease expires and stale serving is bounded by a maximum age.

Senior extension: The lease can expire before refresh finishes, so duplicate regeneration remains possible and must be safe. If stale is forbidden, I shed/reject or use the source through strict admission control rather than create an avalanche. I load-test cold cache and mass expiry and observe in-flight regenerations, source saturation, queueing, and tails—not only hit ratio.

DATA-CACHE-03 — Example answer

Solid response: The answer depends on configuration. RDB snapshots have a snapshot interval loss window; AOF durability depends on fsync policy. Replication is normally asynchronous, so a primary can acknowledge before a replica receives the write, and failover can promote a replica missing it. WAIT improves replica acknowledgement probability but is not a consensus or disk-durability guarantee.

Therefore I do not call Redis durable without explicit recovery objectives and tested configuration.

Senior extension: I include Sentinel/Cluster partition and failover behavior, replica-read staleness, AOF rewrite, backups, restore tests, and client retry ambiguity. If Redis is source-of-truth state, the application needs identifiers, reconciliation/conflict rules, and topology-aware tests. For a disposable cache, source protection and rebuild behavior matter more than preserving every key.

DATA-CACHE-04 — Example answer

Solid response: An expiring lock is a lease. Client A can pause past expiry, client B acquires a new lease, and A resumes believing it still owns the resource. Release must compare an ownership token so A cannot delete B’s lease, but that does not stop A’s stale external write.

A monotonically increasing fencing token lets the protected resource reject operations older than the latest accepted token.

Senior extension: Fencing works only when the downstream resource stores and compares the token. I evaluate pauses, partitions, failover, clocks, and ambiguous acquisition. For a database invariant I prefer constraints or database concurrency controls. A lease alone is acceptable for duplicate-work reduction only when overlap is harmless or another durable mechanism preserves correctness.

DATA-CACHE-05 — Example answer

Solid response: Global hit ratio can hide one hot key, shard, or large value. I inspect per-command/key-pattern latency, shard CPU/network, hot-key distribution, value bytes/serialization, evictions versus expirations, client pools, and miss source latency. Eviction can remove the key before TTL and produce repeated regeneration.

I correlate endpoint tails with Redis and origin spans.

Senior extension: Remedies might be request coalescing, local bounded caching, replicated reads where staleness fits, splitting/precomputing the value, or changing the read model. Key sharding can spread reads but complicates writes and consistency. I verify memory policy and maxmemory headroom and test failover/cold-cache behavior so the fix does not merely move the hotspot to the database.

DATA-CACHE-06 — Example answer

Solid response: Cache calls get short bounded timeouts inside the request deadline. On failure I use a documented mode: bounded direct source reads, acceptable stale/local data, shedding optional traffic, or fail-closed for security state. A semaphore/rate limit protects the source; retries are capped and jittered so clients do not synchronize.

Connection and request queues are bounded to avoid memory growth.

Senior extension: I distinguish cache from authoritative Redis roles such as sessions or queues, which need different continuity plans. Circuit/open-loop behavior is measured rather than blindly toggled. Recovery ramps traffic, reconnects, and warming gradually. I test latency injection, failover, eviction, and cold start while watching source saturation, error budget, and stale age.

DATA-DELIVERY-01 — Example answer

Solid response: The message carries a stable payment operation ID. The consumer records that ID uniquely with local payment state and calls the provider using the same idempotency key. It acknowledges only after the provider outcome and local state are durably safe. On redelivery it loads the operation and returns the recorded result or queries the provider instead of creating a second charge.

Timeout means unknown, not failed.

Senior extension: I model pending, succeeded, failed, and unknown/reconciling states and prevent two consumers from advancing the same operation concurrently. Retries are bounded and classified; poison messages have owned dead-letter handling. A scheduled reconciler compares provider and local ledgers. External effect, local commit, and ack crash points are tested deliberately.

DATA-DELIVERY-02 — Example answer

Solid response: Publishing only after commit prevents a consumer seeing business data that later rolls back. It still has a crash gap: the process can commit and die before publishing. An outbox inserts business state and an outgoing-message row in the same database transaction, so a relay can discover committed unpublished work later.

The relay may publish twice if it crashes before marking sent, so consumers remain idempotent.

Senior extension: I define relay claiming, partition/aggregate key, schema version, retries, retention, and monitoring of oldest unpublished age. CDC and polling are implementation choices with lag and ordering trade-offs. “Sent” means broker acknowledgement under a stated contract, not that every consumer applied the event; consumer inbox state and reconciliation own that boundary.

DATA-DELIVERY-03 — Example answer

Solid response: Independent orders do not need a single global sequence. I partition by the aggregate whose changes must be ordered, such as order ID, and the authoritative writer assigns a monotonically increasing aggregate version. The consumer stores the latest applied version, ignores duplicates, and detects gaps or late events.

This preserves per-order order while allowing orders to process concurrently.

Senior extension: Broker partition order is insufficient with conflicting producers or replay paths. On a gap I either pause/fetch missing history or refresh current state depending on whether events are a complete log or notifications. I define poison-message behavior so one entity does not block unrelated work, and test resharding and consumer-group changes.

DATA-DELIVERY-04 — Example answer

Solid response: I do not blindly retry because the timeout leaves the provider result unknown. Using the stable operation key/provider reference, I query the provider or wait for its webhook/report. If it succeeded, I idempotently record success; if definitively absent/failed, policy may allow a retry with the same key.

The operation remains pending/unknown until evidence resolves it.

Senior extension: A reconciliation job owns outcomes that cannot be resolved inline and escalates aged ambiguity. Local state transitions are conditional so webhook, query, and retry races converge. Amount, currency, account, and operation identity are verified before repair. Manual corrections are ledgered and audited rather than overwriting evidence.

DATA-DELIVERY-05 — Example answer

Solid response: I use a durable workflow state machine. Reserve inventory, authorize/capture payment, then request fulfilment through idempotent commands with stable step IDs. Each service commits locally and publishes its result. Failures transition to retry, compensation, or manual review; cancellation releases inventory and voids/refunds payment where business rules permit.

Compensation is a new fallible action, not rollback.

Senior extension: An orchestrator makes deadlines, retries, and stuck states observable, while choreography might fit simpler independent reactions. I prevent concurrent workflow advancement with versions, record causation, and define irreversible points. Watchdogs reconcile timed-out steps with participants before compensating. Every command, result, and compensation tolerates replay and out-of-order arrival.

DATA-DELIVERY-06 — Example answer

Solid response: I ingest provider settlement/report rows by stable provider operation ID and compare them with the local payment ledger over a bounded time window. The job checkpoints pagination, classifies missing, duplicate, amount/currency mismatch, and status mismatch, and writes idempotent findings. Safe repairs update through normal guarded transitions; ambiguous items go to manual review.

The reconciliation itself can restart without duplicating corrections.

Senior extension: I account for report delay, timezone/cutoff, reversals, partial captures/refunds, and provider pagination consistency. Control totals detect missing pages. Findings and repairs are auditable, least-privileged, and monitored by unresolved count/age and monetary exposure. Reconciliation is a permanent correctness control, not a temporary substitute for fixing delivery paths.