"Design an online food-ordering backend" is the interview prompt where every database topic gets exercised in one conversation: schema and indexes, transactions, inventory concurrency, caching, replication, sharding, failure handling, and increasingly a "now add AI recommendations" follow-up. Interviewers use it because there is no single right answer; they are listening for whether each component you add is justified by a specific requirement, and whether you can state what it costs. For a Backend / AI engineer, this page is the rehearsal: the other DBMS pages go deep on each mechanism, this one shows where each mechanism earns its place.
Start by pinning down what must never be wrong versus what may be stale. That single split drives every later choice:
MUST be correct (strong consistency) MAY be stale / eventually consistent
- an order is never lost or duplicated - restaurant list, menu text, photos
- a customer is never charged twice - "4.3 stars" rating
- the last item is sold at most once - "12 people ordered this today"
- money state matches payment provider - search / recommendations
=> PostgreSQL transactions => Redis cache, replicas, search index
Access patterns (drive the schema and indexes):
1. Show a restaurant's menu (read-heavy, ~every session)
2. Place an order (write, must be atomic)
3. Customer's order history (read, per user, newest first)
4. Restaurant's live queue of PAID orders (read, per restaurant, by status)
5. Look up a payment by idempotency key (read, exact match, on every retry)Client -> Load Balancer -> API Service -> { Redis (cached restaurant/menu data),
PostgreSQL (orders, users, restaurants, payments),
Queue -> Worker }
Client (mobile / web)
|
v
Load Balancer health checks, TLS, spreads traffic
|
v
API Service stateless, N instances, connection pool to Postgres
|
+-- menu / restaurant reads ---> Redis (cache-aside, TTL)
| | miss
| v
+-- orders, payments, inventory --> PostgreSQL primary --WAL--> read replica(s)
| (users, restaurants, menu_items, inventory,
| orders, order_items, payments, outbox)
|
+-- outbox row committed in the SAME transaction as the order
|
v
relay / CDC ---> Queue ---> Worker
(notify restaurant, receipts, refunds,
re-embed menu changes)Every arrow is a decision with a price. Postgres is the source of truth because orders and payments need multi-row atomic transactions and constraints. Redis exists only because menu reads vastly outnumber writes and can tolerate seconds of staleness; if it disappears the system should slow down, not break. The queue exists because notifying a restaurant or sending a receipt must not sit inside the checkout transaction, and must survive a worker crash. The honest opening line in an interview is: "I would start with one Postgres and a cache, and add replicas, partitioning, or AI retrieval only when a stated requirement forces it."
-- Runs as-is in sqlite3 (tested). In PostgreSQL use BIGINT GENERATED ... AS IDENTITY
-- and TIMESTAMPTZ; money stays as integer cents (never floating point).
CREATE TABLE users (
id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL);
CREATE TABLE restaurants (
id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE menu_items (
id INTEGER PRIMARY KEY,
restaurant_id INTEGER NOT NULL REFERENCES restaurants(id),
name TEXT NOT NULL,
price_cents INTEGER NOT NULL CHECK (price_cents > 0));
CREATE TABLE inventory ( -- hot, tiny rows: kept apart from menu text
item_id INTEGER PRIMARY KEY REFERENCES menu_items(id),
stock INTEGER NOT NULL CHECK (stock >= 0));
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
request_id TEXT NOT NULL UNIQUE, -- client-generated; makes POST /orders retry-safe
user_id INTEGER NOT NULL REFERENCES users(id),
restaurant_id INTEGER NOT NULL REFERENCES restaurants(id),
status TEXT NOT NULL CHECK (status IN ('PENDING','PAID','CANCELLED')),
total_cents INTEGER NOT NULL,
created_at TEXT NOT NULL);
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(id),
item_id INTEGER NOT NULL REFERENCES menu_items(id),
qty INTEGER NOT NULL CHECK (qty > 0),
unit_price_cents INTEGER NOT NULL, -- price SNAPSHOT: later menu edits must not rewrite history
PRIMARY KEY (order_id, item_id));
CREATE TABLE payments (
id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES orders(id),
idempotency_key TEXT NOT NULL UNIQUE,
amount_cents INTEGER NOT NULL,
status TEXT NOT NULL);
CREATE TABLE outbox (
id INTEGER PRIMARY KEY, topic TEXT NOT NULL, payload TEXT NOT NULL, published_at TEXT);
CREATE INDEX idx_menu_restaurant ON menu_items(restaurant_id);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
CREATE INDEX idx_orders_restaurant_status ON orders(restaurant_id, status, created_at);
CREATE INDEX idx_outbox_unpublished ON outbox(id) WHERE published_at IS NULL;Access pattern Query shape Served by
1. Menu for a restaurant WHERE restaurant_id = ? idx_menu_restaurant
2. Customer history, newest first WHERE user_id = ? ORDER BY idx_orders_user_created
created_at DESC LIMIT 20 (no sort step needed)
3. Restaurant live queue WHERE restaurant_id = ? AND idx_orders_restaurant_status
status = 'PAID' ORDER BY created_at (equality, equality, then order)
4. Payment by idempotency key WHERE idempotency_key = ? UNIQUE constraint's own index
5. Duplicate POST /orders INSERT ... request_id UNIQUE constraint's own index
6. Outbox relay: "what is unpublished?" WHERE published_at IS NULL partial index (stays tiny)
Real sqlite output of EXPLAIN QUERY PLAN for queries 1-4 (sqlite 3.51; PostgreSQL's
EXPLAIN format differs, but the index choice is the point):
SEARCH menu_items USING INDEX idx_menu_restaurant (restaurant_id=?)
SEARCH orders USING INDEX idx_orders_user_created (user_id=?)
SEARCH orders USING COVERING INDEX idx_orders_restaurant_status (restaurant_id=? AND status=?)
SEARCH payments USING INDEX sqlite_autoindex_payments_1 (idempotency_key=?)
And a query with no matching index prefix:
SELECT * FROM orders WHERE status = 'PAID' -> SCAN ordersIndexes follow queries, not tables. The composite (restaurant_id, status, created_at) puts the equality columns first and the sort column last, so the restaurant's queue is one contiguous index range already in time order. Column order matters: an index led by status would be nearly useless, because a handful of distinct values means low selectivity, and the same index cannot answer "all orders for this restaurant" efficiently. Each index also taxes every INSERT/UPDATE of an order, which is why the schema stops at the four that a listed query needs. Note that status = 'PAID' alone scans: nothing in the design asks that question across all restaurants, so paying for an index on it would be pure write overhead. Foreign keys give referential integrity, and CHECK constraints (stock >= 0) make the database itself the last line of defence against a buggy service. In PostgreSQL, unlike some other engines, a foreign key does not automatically create an index on the referencing column; add one where you join or delete by it.
POST /orders (request_id = k1)
|
|== TX 1: local, milliseconds, ALL-or-nothing ===============================
| 1. INSERT INTO orders (... status='PENDING', request_id=k1)
| ON CONFLICT (request_id) DO NOTHING -- duplicate click / retry: 0 rows -> return existing
| 2. for each line: UPDATE inventory SET stock = stock - :qty
| WHERE item_id = :id AND stock >= :qty -- rows affected must be 1
| 3. INSERT INTO order_items (... unit_price_cents = price snapshot)
| 4. INSERT INTO outbox ('order.created', {order_id})
| COMMIT (any line with 0 rows affected => ROLLBACK, respond "sold out")
|
|-- call payment provider with Idempotency-Key = order id, NO DB transaction open --
|
|== TX 2: local, milliseconds ===============================================
| 5. INSERT INTO payments (... idempotency_key UNIQUE)
| 6. UPDATE orders SET status='PAID' WHERE id = ? AND status = 'PENDING'
| 7. INSERT INTO outbox ('order.paid', {order_id})
| COMMIT
v
respond 201 to client (everything below happens AFTER the response path)
relay (poll or CDC) -> Queue -> Worker: notify restaurant, push/email receipt, analytics
sweeper: PENDING orders older than N minutes -> CANCELLED + stock returned (compensation)The rule is: keep in one transaction what must be true together, and keep out of it anything slow or external. Reserving stock, creating the order and its lines, and writing the outbox row are all local writes to one database, so one short transaction makes them atomic: either the order exists with stock reserved and an event queued, or none of it happened. The payment call is an HTTP request to a third party that can take seconds or hang; holding row locks on hot inventory rows across it would serialize every buyer of that item behind the slowest payment, so it stays outside any transaction. Notifications are outside too, but not lost: the outbox row is committed atomically with the order, and a relay publishes it to the queue later. That fixes the classic dual-write bug, where "commit to the DB, then publish to the queue" loses the event if the process dies between the two, and "publish, then commit" announces orders that never existed. The price is at-least-once delivery, so workers must be idempotent.
Broken: check-then-act (two sessions, stock = 1)
A: SELECT stock FROM inventory WHERE item_id = 1; -- sees 1
B: SELECT stock FROM inventory WHERE item_id = 1; -- sees 1
A: UPDATE inventory SET stock = 0 WHERE item_id = 1; -- A thinks it bought it
B: UPDATE inventory SET stock = 0 WHERE item_id = 1; -- B thinks it bought it
Two orders, one unit. Each statement was fine; the gap between read and write was the bug.
Option 1: atomic conditional UPDATE (preferred default)
UPDATE inventory SET stock = stock - 1 WHERE item_id = ? AND stock > 0;
-- check the rows-affected count: 1 = you got it, 0 = sold out
Option 2: pessimistic lock -- PostgreSQL
BEGIN;
SELECT stock FROM inventory WHERE item_id = 1 FOR UPDATE; -- lock the row
-- app checks stock > 0
UPDATE inventory SET stock = stock - 1 WHERE item_id = 1;
COMMIT;
Option 3: SERIALIZABLE + retry -- PostgreSQL
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT stock ...; UPDATE inventory ...;
COMMIT; -- may fail with SQLSTATE 40001 (serialization_failure): re-run the whole transaction# Real Python + sqlite3 output (file DB, 2 connections, then 20 threads):
A) both saw stock=1 -> both 'succeeded'; final stock = 0 (one unit, two buyers)
B) 20 buyers, stock 5 -> winners: 5 losers: 15 final stock: 0Option 1 works because the read, the check and the write become one statement, and the database evaluates it while holding the row lock. In PostgreSQL at Read Committed, a second concurrent UPDATE on the same row waits for the first transaction to finish, then re-evaluates its WHERE stock > 0 against the newly committed row version; if the stock is gone it updates zero rows. The application reads the rows-affected count instead of guessing. It needs no explicit locks, no retry loop, and holds the lock for microseconds. Option 2 (SELECT ... FOR UPDATE) is right when the decision needs more than one column or table, such as checking several rows and business rules before writing, but it holds the lock longer, adds a round trip, and invites deadlocks when different requests lock rows in different orders (lock in a consistent order, for example by item id). Option 3 is the most general (the database detects the conflict for you) and the most expensive: under contention many transactions abort, and every caller must be written to retry the entire transaction safely. For a single counter, it is overkill.
The remaining problem is not correctness but throughput: a viral item makes one row a serialized queue, since writers to the same row take turns. Mitigations, in order of complexity: keep the transaction tiny, cap purchase quantity, split stock across several bucket rows and try them in turn, or take reservations in Redis and reconcile to Postgres. Each of the last two adds a second place where stock lives, so justify them with a measured hot row, not a hunch. Also decide what a reservation means: stock is decremented when the order is PENDING, so abandoned checkouts must release it, which is why the sweeper in the previous diagram exists.
Cache-aside for menus (Redis) # pseudo-code
def get_menu(restaurant_id):
key = f"menu:v1:{restaurant_id}"
cached = redis.get(key)
if cached: return cached # hit: no database work
menu = pg_replica.query(MENU_SQL, restaurant_id) # miss: read from DB
redis.set(key, menu, ex=300 + random(0, 60)) # TTL with jitter
return menu
def on_menu_edit(restaurant_id): # writer path
pg.update(...); redis.delete(f"menu:v1:{restaurant_id}")
Read replicas and lag
Client writes order --> primary (commit) ---WAL---> replica (applies later)
Client immediately GETs "my orders" --> replica -> may NOT show the new order yet
Rule of thumb for where each read goes
order confirmation, "my order just placed", stock check -> primary (or read-your-writes)
menu, restaurant list, historical order pages, analytics -> cache or replica
Sharding: what could the shard key be?
by user_id -> "my order history" hits ONE shard; restaurant dashboards fan out to all
by restaurant_id -> restaurant queue + menu + inventory on one shard; user history fans out
risk: a hugely popular restaurant becomes a hot partition
by order_id hash -> even spread; BOTH main queries fan out (scatter-gather)Caching is the first lever because the menu is read far more than written and staleness of seconds is acceptable. Cache-aside keeps Postgres authoritative: on a miss you read and populate, on a write you delete the key. The failure modes are known and cheap to design in: add TTL jitter so keys do not all expire together, and coalesce concurrent misses (a per-key lock or request coalescing) to avoid a cache stampede on a popular restaurant. Replicas scale reads, but asynchronous replication means a replica can trail the primary, so the design must say which reads tolerate lag. Payments, stock checks and "show me the order I just placed" go to the primary or use a read-your-writes rule such as pinning a session to the primary for a short window. Replication is also not a backup: an accidental DELETE replicates in milliseconds, so point-in-time recovery from base backups plus archived WAL is separate.
Sharding is the last lever, not the first. A single well-indexed PostgreSQL primary with replicas, connection pooling and time-partitioned orders (declarative partitioning inside one server, which is not sharding) goes a long way, and it keeps joins, foreign keys and transactions simple. Shard when write volume or dataset size genuinely exceeds one machine and vertical scaling, archiving old orders and moving read load are exhausted. Then the shard key is chosen from the dominant query: per-restaurant sharding keeps menu, inventory, and the live queue and the stock-decrement transaction on one shard, which is the hard consistency need, but hot restaurants skew load; per-user sharding makes order history cheap but pushes restaurant dashboards to scatter-gather. What you give up is cross-shard transactions and joins, so a cross-restaurant order needs a saga or must be forbidden by the product.
Failure Effect Design response
Redis down / evicted more DB reads, higher cache is optional: fall back to DB, protect DB
latency (NOT wrong data) with rate limits, request coalescing, short-lived
in-process cache; never store the only copy in Redis
Postgres primary down writes fail replica promotion (Patroni / managed failover);
async replication => RPO > 0 possible, sync => RPO ~ 0
but higher commit latency; RTO = detection + promotion;
degraded mode: serve menus from cache, reject/queue orders
Replica lag user sees stale data read-your-writes routing, or read primary for own orders
Worker crashes mid-task message redelivered at-least-once + idempotent handlers (dedupe by event id),
(maybe twice) visibility timeout, DLQ for poison messages
Payment OK, order update fails customer charged, order payment idempotency key, outbox, webhook + reconciliation
still PENDING job, refund as compensation (see Q&A)
Connection exhaustion at 100x errors "too many clients" pooler (PgBouncer) in front, sized pool per instance,
timeouts, load shedding
Duplicate client request double order request_id UNIQUE (tested below)The pattern is to ask, for each component, "what if this is down, slow, or delivers the same message twice?" The system should degrade in a predictable direction: losing Redis costs latency, losing a worker delays notifications, losing the primary stops new orders but should not corrupt existing ones. RPO (how much data you can lose) and RTO (how long you can be down) must be stated as requirements before choosing sync versus async replication: for orders and payments a small RPO is usually worth the commit-latency cost, for menu browsing it is not. Failover itself introduces a risk: if the old primary comes back and still accepts writes (split brain), you have two histories, which is why failover tooling fences the old primary.
Menu / restaurant edits (Postgres)
| CDC (logical decoding) or the outbox
v
Queue --> Embedding worker: build text, skip if source_hash unchanged, call embedding model
| upsert vector, delete vector on tombstone
v
PostgreSQL + pgvector (embeddings live next to the data they describe)
|
+--- keyword search (tsvector / BM25-style) -> ranked list A
+--- vector search (embedding <=> query) -> ranked list B
both filtered by: available now, delivers to user, permissions
v
Reciprocal Rank Fusion (RRF) merges A and B -> top-k candidates
v
Re-check price / stock / availability from Postgres (facts change; embeddings do not)
v
LLM: "recommend from THESE items and explain briefly" -> response with item ids-- PostgreSQL + pgvector (not run locally; syntax only, no output claimed)
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE menu_item_embeddings (
item_id BIGINT PRIMARY KEY REFERENCES menu_items(id) ON DELETE CASCADE,
restaurant_id BIGINT NOT NULL, -- filter column: tenant / merchant scope
is_available BOOLEAN NOT NULL,
source_hash TEXT NOT NULL, -- hash of the text that was embedded
model TEXT NOT NULL, -- embedding model + version, needed for re-embedding
embedding vector(1536) NOT NULL, -- dimension must match the model
updated_at TIMESTAMPTZ NOT NULL DEFAULT now());
CREATE INDEX ON menu_item_embeddings USING hnsw (embedding vector_cosine_ops);
-- vector candidates, scoped to restaurants the user may see (<=> = cosine distance)
SELECT item_id FROM menu_item_embeddings
WHERE restaurant_id = ANY(:deliverable_restaurant_ids) AND is_available
ORDER BY embedding <=> :query_embedding
LIMIT 20;-- Reciprocal Rank Fusion: score = sum over lists of 1 / (k + rank), k commonly 60.
-- Real sqlite output for keyword ranks (7,3,9) and vector ranks (3,5,7):
-- item_id | rrf_score
-- 3 | 0.03252 (rank 2 keyword, rank 1 vector)
-- 7 | 0.03227 (rank 1 keyword, rank 3 vector)
-- 5 | 0.01613 (vector only)
-- 9 | 0.01587 (keyword only)
WITH fused AS (
SELECT item_id, 1.0/(60+rnk) AS s FROM keyword_hits
UNION ALL
SELECT item_id, 1.0/(60+rnk) FROM vector_hits)
SELECT item_id, ROUND(SUM(s), 5) AS rrf_score
FROM fused GROUP BY item_id ORDER BY rrf_score DESC, item_id;Why pgvector and not a separate vector database: the menu data, availability flags and permissions already live in Postgres, so the vector search can filter on them in the same query and the embeddings can be updated in the same transaction or pipeline as the source rows. That removes a second system to keep consistent. The trade-off is that a very large corpus or high vector query volume may eventually justify a dedicated engine or a separate replica. Hybrid retrieval matters for food: "paneer tikka" is an exact keyword, while "something light and spicy for a rainy day" needs semantic similarity; RRF fuses the two ranked lists using ranks only, so you do not have to reconcile incomparable scores. Keeping embeddings in sync is a data-pipeline problem: CDC or the outbox emits change events, and the worker re-embeds only when the source text hash changed, deletes vectors when items are removed, and records the model version so a model upgrade can re-embed in the background. Filtering deserves care: approximate indexes such as HNSW may return fewer than k rows when a restrictive filter is applied after the index scan (recent pgvector versions add iterative scans to mitigate this), so test recall with your real filters. Personalisation data, such as a user's order history, must only be retrieved for that same user, and the LLM should only see items the caller is allowed to see; enforce that in the retrieval query or with row-level security, never in the prompt.
(restaurant_id, status, created_at)); every extra index taxes writes.UPDATE ... WHERE stock > 0 and check rows affected; use FOR UPDATE or SERIALIZABLE only when the rule spans multiple rows.request_id, payment idempotency_key) backed by UNIQUE constraints make retries safe; reconciliation catches what retries miss.POST /orders with a request_id through the load balancer to a stateless API instance; menu reads on the way were served from Redis or a replica.request_id), decrement each item's stock with a conditional UPDATE, insert the order lines with price snapshots, and insert an outbox row.payments table and fixes or refunds mismatches.# Save as demo.sql, then: sqlite3 :memory: < demo.sql
CREATE TABLE inventory (item_id INTEGER PRIMARY KEY, stock INTEGER NOT NULL CHECK (stock >= 0));
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, status TEXT NOT NULL, total_cents INTEGER NOT NULL);
CREATE TABLE payments (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, idempotency_key TEXT NOT NULL UNIQUE, amount_cents INTEGER NOT NULL);
CREATE TABLE outbox (id INTEGER PRIMARY KEY, topic TEXT NOT NULL, payload TEXT NOT NULL, published_at TEXT);
INSERT INTO inventory VALUES (1, 1), (2, 100); -- item 1 has exactly ONE unit left
-- Buyer A: reserve + create order + outbox row, one short transaction
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE item_id = 1 AND stock > 0;
SELECT 'A rows affected:', changes();
INSERT INTO orders (user_id, status, total_cents) VALUES (1, 'PENDING', 280);
INSERT INTO outbox (topic, payload) VALUES ('order.created', '{"order_id":1}');
COMMIT;
-- Buyer B: same statement, one millisecond later
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE item_id = 1 AND stock > 0;
SELECT 'B rows affected:', changes();
ROLLBACK; -- 0 rows -> app rolls back, tells B "sold out"
SELECT 'stock now:', stock FROM inventory WHERE item_id = 1;
SELECT 'orders:', count(*) FROM orders;
-- Payment retry: same idempotency key must not create a second payment
INSERT INTO payments (order_id, idempotency_key, amount_cents) VALUES (1, 'order-1-attempt-1', 280) ON CONFLICT (idempotency_key) DO NOTHING;
SELECT 'first attempt inserted:', changes();
INSERT INTO payments (order_id, idempotency_key, amount_cents) VALUES (1, 'order-1-attempt-1', 280) ON CONFLICT (idempotency_key) DO NOTHING;
SELECT 'retry inserted:', changes();
SELECT 'payments:', count(*) FROM payments;
-- Relay: what has not been published yet?
SELECT id, topic FROM outbox WHERE published_at IS NULL ORDER BY id;
UPDATE outbox SET published_at = '2026-09-19T13:00:01' WHERE id = 1;
SELECT 'unpublished left:', count(*) FROM outbox WHERE published_at IS NULL;
# Real output (sqlite 3.51):
# A rows affected:|1
# B rows affected:|0
# stock now:|0
# orders:|1
# first attempt inserted:|1
# retry inserted:|0
# payments:|1
# 1|order.created
# unpublished left:|0
# Try next: remove "AND stock > 0" and observe that the CHECK (stock >= 0) constraint
# is the only thing stopping a negative stock; the constraint is a safety net, not the mechanism.Q: Why PostgreSQL for this system?
A: Orders, order lines, payments and stock are related records that must change together, which is exactly what multi-row ACID transactions, foreign keys, UNIQUE and CHECK constraints are for. Relational modelling also fits the access patterns (joins for order details, range queries for history), and PostgreSQL adds partial and composite indexes, JSONB for flexible attributes, and pgvector later. The trade-off is that a single primary has a write ceiling and scaling writes horizontally is hard; a document store would scale writes more easily but pushes cross-document consistency into application code, which is the wrong place for money.
Q: Why Redis, and what if it goes down?
A: Menus and restaurant data are read far more than written and can be seconds stale, so an in-memory cache with cache-aside removes most read load and gives low-latency responses. Redis must be optional: if it goes down, reads fall back to the database (or a replica), latency rises, and the system should protect itself with request coalescing, rate limits and a short-lived local cache so the database is not stampeded. The trade-off is invalidation complexity and a stale-data window; source-of-truth data such as stock and payment state should not live only in Redis.
Q: Why these indexes?
A: Each one maps to a listed query: (user_id, created_at DESC) for order history, (restaurant_id, status, created_at) for the restaurant's live queue, restaurant_id for menus, and UNIQUE indexes on request_id and idempotency_key for duplicate protection. Column order follows equality first, sort last. The cost is write amplification on every order insert and status change, so I would not add an index on status alone or on columns nobody queries, and I would confirm choices with EXPLAIN on real data before adding more.
Q: How do you prevent two users buying the last item?
A: Make the check and the decrement one atomic statement, UPDATE inventory SET stock = stock - 1 WHERE item_id = ? AND stock > 0, and treat rows-affected 0 as sold out. In PostgreSQL the second writer waits for the first to commit, then re-evaluates the condition against the new row. FOR UPDATE works when the rule spans several rows but holds locks longer; SERIALIZABLE is the most general but requires retry handling and aborts under contention. The remaining limit is a hot row, which you address only if measured (bucketed stock or Redis reservations), because each adds a second copy of stock to reconcile.
Q: What if PostgreSQL becomes unavailable?
A: Run a primary with at least one standby, and promote automatically (Patroni or managed failover) with the old primary fenced. Choose RPO and RTO first: asynchronous replication gives faster commits but a failover can lose the last few transactions; synchronous replication reduces that loss to near zero at the cost of commit latency and the risk that a dead standby stalls writes. In the meantime, degrade deliberately: serve menus from Redis, queue or reject order placement with a clear message, and keep backups plus WAL archiving separately, since replication copies mistakes too.
Q: What if traffic grows 100x?
A: Measure to find the bottleneck, then apply the cheapest fix first: fix slow queries and indexes, cache menu and restaurant reads, put a connection pooler in front of Postgres, add read replicas for lag-tolerant reads, and use the queue to smooth bursts (accept the order, process side effects asynchronously). Partition orders by time for retention and index size. Only shard when writes or data outgrow one primary, choosing the shard key from the dominant access path (often restaurant_id for orders and inventory) and accepting cross-shard limits. Every step adds operational cost and a consistency caveat, so I would state which one I am buying.
Q: What if payment succeeds but order creation fails?
A: Order the steps so the order exists (PENDING, stock reserved) before charging, and send the provider an idempotency key so a retry returns the same charge instead of a second one. If the update to PAID fails after a successful charge, retries or the provider's webhook complete it, because the same key and UNIQUE constraint make the operation repeatable. A reconciliation job compares provider records with local payments and either repairs the order or refunds as a compensating action, which is the saga pattern. The trade-off is eventual consistency: for a short window the customer may see PENDING while money has moved.
Q: How would you add AI recommendations?
A: Derive embeddings from menu and restaurant text, store them in pgvector beside the source data, and combine keyword and vector search with RRF before passing a small, verified candidate set to an LLM to rank and explain. Keep embeddings in sync with CDC or the outbox, re-embed only when the text hash changes, and filter by availability and permission inside the retrieval query. The trade-off is added pipeline complexity, embedding cost, and recall that must be evaluated; start with simple heuristics (popular, reorder) as a baseline before proving that the model adds value.
Follow-up: A stampede hits when a popular restaurant's menu key expires. What do you do?
A: Coalesce so only one request rebuilds the value (per-key lock or single-flight) while others wait or serve the stale copy, and jitter TTLs so keys do not expire together. For known hot keys, refresh in the background before expiry. The trade-off is serving slightly stale data or a little extra complexity, in exchange for protecting the database.
Follow-up: The outbox relay publishes an event twice. What breaks?
A: Nothing should, if consumers are idempotent: each handler records processed event ids (or uses natural idempotency such as "set status to X") so a second delivery is a no-op. Exactly-once across a database and a broker is not realistically available, so at-least-once plus idempotent consumers is the practical design. The cost is a dedupe table or key per consumer.
Follow-up: A customer just placed an order but "My orders" does not show it. Why, and how do you fix it?
A: Likely replica lag: the write went to the primary and the read went to a replica that had not yet applied it. Fix with read-your-writes routing (read the primary for a short window after a write, or compare log positions), or have the client render the just-created order locally. The trade-off is more load on the primary versus a confusing user experience.
This capstone shows the general method for any transactional product: identify the invariants that must never break and give them to the database, using constraints and short transactions; identify what may be stale and give it to caches, replicas and search; and connect the two with asynchronous, idempotent messaging through an outbox instead of distributed transactions. The same skeleton applies to e-commerce checkout, ticket booking or ride dispatch: a stateless service tier, one authoritative relational store with carefully chosen indexes, a cache in front of hot reads, a queue behind for side effects, and retries made safe by idempotency keys. In an interview, walk the requirement list, draw the simple version first, then let the interviewer's "what if" questions (a crash, 100x traffic, a race) pull each additional component in, naming its price when it arrives. That sequence shows judgment, which is what senior-level system design interviews are testing.
The biggest risk in this question is over-engineering: proposing microservices, sharding, Kafka, a vector database and multi-region failover for a system that a single PostgreSQL instance with good indexes, a cache and a small worker would serve for a long time. Start simple: one primary with a standby, backups and WAL archiving, PgBouncer, Redis for menus, an outbox with one worker. Add replicas when read load shows up in metrics, partition orders by time when the table and its indexes grow unwieldy, bucket or reserve hot inventory when a single row is measurably contended, add pgvector search when there is a product requirement and an evaluation set to prove it helps, and shard only when one machine truly cannot hold the writes or data. Each addition buys capacity or availability at the price of consistency caveats (stale reads, at-least-once delivery, eventual reconciliation), operational burden, and more failure modes to reason about. The wrong choice is any mechanism added without being able to state the requirement and the measurement that justify it.