← Back to experiences
[ OK ] Loaded —
[ INFO ]
$ cd
$ ls -lt
01
02
03
04
05
$ ls -lt
01
02
03
04
05
user@intervues:~/experiences/…$
Level: Staff-Level
Round: Full Journey · Type: System Design · Difficulty: 7/10 · Duration: 60 min · Interviewer: Unfriendly
Topics: System Design, Concurrency, Database Sharding, Transactions, API Design, Consistency, Replication, Caching
Location: San Francisco Bay Area
Interview date: 2025-12-25
Question: Design a system to track and limit resource usage across many services, such as Google's storage quota shared between Drive, Photos, and Gmail.
The system should enforce strict consistency to prevent users from exceeding their quota, while maintaining low latency and high availability.
Functional Requirements:
Non-Functional Requirements:
Database Schema:
API Endpoints:
Architecture Overview:
Reserve Flow (Atomic):
` BEGIN;
-- Check for duplicate requests (Idempotency) SELECT reservation_id, status, amount FROM reservations WHERE service_id = $service_id AND idempotency_key = $idempotency_key FOR UPDATE; -- If found, return the existing result.
WITH reserved_row AS ( UPDATE user_quota SET reserved = reserved + $amount, updated_at = NOW() WHERE user_id = $user_id AND (quota_limit - used - reserved) >= $amount RETURNING user_id ) INSERT INTO reservations ( reservation_id, user_id, service_id, idempotency_key, amount, status, created_at, expires_at ) SELECT $reservation_id, user_id, $service_id, $idempotency_key, $amount, 'pending', NOW(), NOW() + INTERVAL '30 minutes' FROM reserved_row;
COMMIT; `
Confirm Flow:
` BEGIN;
WITH target AS ( SELECT reservation_id, user_id, amount, status FROM reservations WHERE reservation_id = $reservation_id FOR UPDATE ) UPDATE user_quota q SET used = q.used + t.amount, reserved = q.reserved - t.amount, updated_at = NOW() FROM target t WHERE q.user_id = t.user_id AND t.status = 'pending';
UPDATE reservations SET status = 'confirmed' WHERE reservation_id = $reservation_id AND status = 'pending'; COMMIT; `
Cancel Flow:
BEGIN; WITH target AS ( UPDATE reservations SET status = 'released' WHERE reservation_id = $reservation_id AND status = 'pending' RETURNING user_id, amount ) UPDATE user_quota q SET reserved = q.reserved - t.amount, updated_at = NOW() FROM target t WHERE q.user_id = t.user_id; COMMIT;
Cleaning Up (Expiration Worker):
-- Worker loop: find old 'pending' reservations and cancel them WITH expired AS ( UPDATE reservations SET status = 'expired' WHERE reservation_id IN ( SELECT reservation_id FROM reservations WHERE status = 'pending' AND expires_at < NOW() ORDER BY expires_at LIMIT 1000 FOR UPDATE SKIP LOCKED ) RETURNING user_id, amount ) UPDATE user_quota q SET reserved = q.reserved - e.amount, updated_at = NOW() FROM expired e WHERE q.user_id = e.user_id;
Handling Heavy Traffic:
Common Mistakes: