1. Authentication mechanism — API keys for simplicity, OAuth 2.0 if delegated access is needed.
2. Versioning strategy — URL-based (/v1/) for discoverability, with a deprecation policy from day one.
3. Pagination — cursor-based (not offset) for consistency at scale; every list endpoint returns pagination metadata.
4. Error format — consistent error response schema across all endpoints (error code, message, details, request_id for support).
5. Rate limiting — per API key, with 429 Too Many Requests and Retry-After header.

• Weak: “I would use REST with JSON and add authentication.” — Too vague, no specifics on versioning, pagination, or error handling. Shows no awareness of the decisions that cause real pain later.
• Strong: “My first five decisions would be versioning, auth, pagination, error format, and rate limiting — because those are the five things that are hardest to change after launch and cause the most partner friction when done wrong.” — Shows prioritization and understanding of irreversibility.

• Failure mode: “What happens if you skip the idempotency decision at launch?” — Every retry creates duplicates. Retrofitting idempotency keys onto an existing API is a breaking change (new required header). Partners who integrated without it will resist.
• Rollout: “How do you roll out rate limiting to existing consumers without breaking them?” — Announce 30 days before enforcement, start in monitoring-only mode (log but do not reject), then enforce with generous initial limits. Tighten over time.
• Measurement: “How do you know your API design is working?” — Track time-to-first-successful-call for new integrators (target: <5 minutes with docs), support ticket rate per 1,000 API calls, and error rate breakdown by status code.
• Cost: “What is the cost of getting versioning wrong?” — Stripe maintains hundreds of API versions through a transformation chain. This is viable at $14B revenue. For most companies, rearchitecting versioning after launch costs 3-6 months of engineering time.
• Security: “What auth decision would you avoid for a public API?” — Never use API keys alone without HTTPS. Never embed secrets in URLs (they appear in logs, browser history, referer headers). Always support key rotation without downtime.
• Governance: “Who owns the API design decisions?” — A single API design lead or working group, not each team independently. Inconsistency across endpoints is the number-one developer complaint.

1. Decouple with an outbound queue. Instead of calling the partner synchronously as events occur, publish events to a message queue (SQS, Kafka, or Redis Streams) dedicated to this partner.
2. Rate-limited consumer. A dedicated worker consumes from the queue at the partner’s rate — 100 RPS. Use a token bucket or leaky bucket rate limiter on the consumer side. The queue absorbs the burst; the consumer drains it steadily.
3. Batching. If the partner supports batch callbacks, aggregate events into batches of 50-100 per request. This delivers 5,000-10,000 events per second within their 100 RPS limit. This is the highest-leverage optimization.
4. Backpressure and overflow. Set a queue depth limit. If the partner falls behind (their system is down), the queue grows. Add dead-letter queue (DLQ) for events that fail after N retries. Alert when queue depth exceeds a threshold — this means the partner cannot keep up even at steady state.
5. Webhook with retry and exponential backoff. For the actual callback, use exponential backoff with jitter (1s, 2s, 4s, …, max 5 minutes). Include an Idempotency-Key header so the partner can safely deduplicate retries.
6. Pull alternative. Offer the partner a poll-based API (GET /events?since=cursor) as an alternative to push. This lets them control their own consumption rate. Many partners prefer this because it puts them in control of backpressure.

POST /uploads
Content-Type: application/json
{ "filename": "dataset.parquet", "size_bytes": 5368709120, "content_type": "application/octet-stream" }

Response 201:
{ "upload_id": "upl_abc123", "chunk_size": 5242880, "total_chunks": 1024, "upload_url": "/uploads/upl_abc123" }

PUT /uploads/upl_abc123/chunks/0
Content-Type: application/octet-stream
Content-Range: bytes 0-5242879/5368709120
[binary data]

Response 200: { "chunk_index": 0, "status": "received", "checksum": "sha256:abc..." }

GET /uploads/upl_abc123/status
Response: { "upload_id": "upl_abc123", "received_chunks": [0, 1, 2, 3, 4], "missing_chunks": [5, 6, ..., 1023], "progress_pct": 0.49 }

POST /uploads/upl_abc123/complete
Response 200: { "file_id": "file_xyz789", "size_bytes": 5368709120, "checksum": "sha256:final...", "url": "/files/file_xyz789" }

• Chunk size: 5MB is a good default — small enough that a failed chunk is cheap to retry, large enough that 1024 chunks is manageable. Let the server decide (clients with slow connections could request smaller chunks).
• Idempotency: Uploading the same chunk index twice is idempotent (server overwrites or ignores if checksum matches). This makes retries safe.
• Progress: The client knows received_chunks / total_chunks. For real-time progress, the client can calculate bytes uploaded per second from the chunk upload times.
• Expiry: Incomplete uploads expire after 24 hours. The client can extend with a PATCH /uploads/{id} to refresh the TTL.
• Parallel uploads: Chunks are independent, so the client can upload 3-5 chunks in parallel for better throughput on high-bandwidth connections.

• Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) on the slow queries. This is non-negotiable — you cannot optimize what you have not measured. Look at the actual execution plan, not the estimated one.
• Check if the planner is using your indexes at all. If you see Seq Scan on a 500M-row table, either the index is missing for that query pattern, the statistics are stale, or the planner decided a sequential scan was cheaper (which can happen if the query returns a large fraction of the table).

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

Seq Scan on orders  (cost=0.00..12847623.00 rows=4821 width=196)
                    (actual time=287401.22..287892.55 rows=4793 loops=1)
  Filter: ((customer_id = 42) AND (status = 'shipped'::text))
  Rows Removed by Filter: 499995207
  Buffers: shared hit=1024 read=8472981
Planning Time: 0.12 ms
Execution Time: 287893.01 ms

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

Index Scan using idx_orders_customer_status on orders
    (cost=0.57..5241.33 rows=4821 width=196)
    (actual time=0.03..4.87 rows=4793 loops=1)
  Index Cond: ((customer_id = 42) AND (status = 'shipped'::text))
  Buffers: shared hit=4821
Planning Time: 0.15 ms
Execution Time: 5.12 ms

• Run ANALYZE on the table. PostgreSQL’s query planner uses statistics about data distribution to choose execution plans. On a 500M-row table, the default statistics target (100) may be too low — increase it with ALTER TABLE SET STATISTICS 1000 on high-cardinality columns used in WHERE clauses.
• Check for bloat. On a heavily-updated table, dead tuples accumulate (MVCC). Run SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = 'your_table'. If dead tuples are a significant fraction of live tuples, VACUUM FULL or pg_repack (non-locking) to reclaim space and reduce I/O.
• Check index bloat. B-tree indexes on frequently-updated columns become bloated over time. REINDEX CONCURRENTLY rebuilds without locking.

• Verify composite index column order matches query patterns (leftmost prefix rule). An index on (status, created_at) helps WHERE status = 'active' AND created_at > '2025-01-01' but NOT WHERE created_at > '2025-01-01' alone.
• Consider partial indexes if queries consistently filter a subset: CREATE INDEX idx_active ON orders(created_at) WHERE status = 'active' — if only 5% of rows are active, this index is 20x smaller and 20x faster to scan.
• Consider covering indexes (INCLUDE) to enable index-only scans — if the query only needs id and amount, CREATE INDEX idx ON orders(status, created_at) INCLUDE (id, amount) avoids touching the heap entirely.

• Partitioning. If queries consistently filter by a time range (and they usually do), partition by month or quarter using PostgreSQL declarative partitioning. A query for “last 30 days” now scans one partition (maybe 15M rows) instead of 500M. Partition pruning is automatic. The numbers are dramatic: scanning 15M rows takes ~2-3 seconds vs ~8-10 minutes for 500M. With an index on the partition, point lookups drop to under 1 ms because the index is 30x smaller and fits entirely in memory.
• Read replicas. Route analytics and reporting queries to a read replica. Keep the primary for OLTP.
• Materialized views. For expensive aggregation queries that do not need real-time data, create a materialized view refreshed every hour. A dashboard that aggregates 500M rows every page load is a bug, not a feature.

• Add pagination. No query should return 500M rows. Cursor-based pagination with WHERE id > last_seen_id ORDER BY id LIMIT 100 is O(1) regardless of offset. At 500M rows, an OFFSET 10000000 LIMIT 20 query still scans and discards 10 million rows — cursor-based pagination avoids this entirely.
• Cache hot queries in Redis with a TTL. If the same aggregation is requested 1000 times per minute, compute it once. A Redis GET takes ~0.1 ms vs a complex PostgreSQL aggregation at 50-500 ms — that is a 500-5000x improvement for repeat queries. See the Caching chapter for cache invalidation strategies (write-through, write-behind, cache-aside) and how to avoid the thundering herd problem on cache expiry.
• Review the ORM. ORMs often generate suboptimal queries — N+1 problems, unnecessary SELECT *, missing WHERE clauses. EXPLAIN ANALYZE the actual SQL the ORM produces, not what you think it produces. A classic N+1 on a 500M-row table turns a single 5 ms indexed query into 100 separate queries taking 500 ms total — plus the connection overhead.

1. Measure the breakdown. Instrument the request lifecycle: DNS resolution, TCP/TLS handshake, time-to-first-byte (TTFB), and content transfer time. On a 50KB JSON response over a typical 4G connection (~10 Mbps), the transfer itself is only about 40ms. If the total latency is 2 seconds, the transfer is not the bottleneck — the server processing time is. But on a 3G connection in a developing market (~1 Mbps), that 50KB takes 400ms just to transfer, and now payload size actually matters.
2. Enable gzip/brotli compression. JSON compresses extremely well — typically 5-10x. A 50KB response compresses to roughly 5-8KB. This is the single highest-leverage, lowest-risk change. Configure it at the API gateway or reverse proxy (nginx, CloudFront). This alone might cut perceived latency by 30-50% for bandwidth-constrained clients.
3. Analyze the payload. Are mobile clients actually using all 50KB of that nested data? In my experience, the answer is almost always no. A mobile feed view might need 5 fields from a response that includes 40. This is the classic REST over-fetching problem. Solutions: (a) Add a ?fields=id,name,thumbnail sparse fieldset parameter so clients request only what they need. (b) Create a dedicated mobile-optimized endpoint or a Backend-for-Frontend (BFF) that returns a slim payload. (c) If the problem is widespread across many endpoints, this is where GraphQL earns its keep — the client specifies exactly the shape it needs.
4. Check the query layer. Run EXPLAIN ANALYZE on the queries backing the endpoint. A deeply nested JSON response often means the server is doing multiple JOINs or N+1 queries to assemble the object graph. If the endpoint returns an order with line items, each with a product, each with a category — that could be 4 sequential queries or a massive JOIN. Use eager loading (include the joins upfront) or DataLoader-style batching if the backend is GraphQL.
5. Cache aggressively. If this endpoint serves data that does not change every second (product catalog, user profiles), add HTTP cache headers (Cache-Control: max-age=300, stale-while-revalidate=60) and consider a CDN (CloudFront, Fastly) in front of the API. For authenticated endpoints where CDN caching is not appropriate, use server-side caching with Redis — cache the serialized response for 30-60 seconds.

​

Follow-up: The team proposes moving to GraphQL to solve the over-fetching. What concerns would you raise?

1. HTTP caching breaks. Every GraphQL request is a POST with a unique body. You cannot use a CDN to cache responses without persisted queries (pre-registered query strings with an ID, so GET /graphql?id=abc123 becomes cacheable). That is additional infrastructure.
2. Query cost is unpredictable. A single GraphQL query can be { user(id: 1) { name } } (trivial) or { users { orders { items { product { reviews { author { orders { ... } } } } } } } } (a denial-of-service attack disguised as a query). You need query depth limits, complexity analysis, and cost-based rate limiting from day one. This is non-trivial to implement correctly.
3. N+1 problems move to the server. In REST, the server controls what data is fetched. In GraphQL, the client’s query determines the resolver call graph. Without DataLoader (or equivalent batching), resolving users { orders { ... } } for 100 users triggers 100 separate getOrders calls. This is the most common GraphQL performance trap.
4. Organizational cost. Every frontend developer now needs to understand the schema, write queries, and manage a client-side cache (Apollo Client’s normalized cache is powerful but has a steep learning curve). The backend team needs to maintain the schema, handle schema evolution without breaking existing queries, and monitor query performance.

​

Follow-up: How would you implement sparse fieldsets in a REST API without it becoming a maintenance nightmare?

• The client sends GET /orders/123?fields=id,total,customer.name,items.product_name
• The server parses the fields parameter into a projection tree
• The ORM generates a SELECT with only the requested columns (and the necessary JOINs for nested fields)
• The serializer filters the response to include only the requested fields

1. Network retries create duplicates. The client sends a POST to create an order. The server processes it, inserts the row, but the response is lost (TCP connection reset, load balancer timeout, anything). The client retries. Without idempotency, a second order is created. At Stripe’s scale — processing billions of dollars — even a 0.01% duplicate rate means millions in double charges.
2. Message queue redelivery. Most message brokers provide at-least-once delivery, not exactly-once. If a consumer processes a message, crashes before acknowledging it, the broker redelivers it. The consumer must be idempotent or it processes the same event twice — double-sending an email, double-crediting an account, double-triggering a webhook.
3. Load balancer failover. The client sends a request to Server A. Server A processes it but crashes before responding. The load balancer retries to Server B. Without a shared idempotency store, Server B has no idea Server A already processed it. Both servers execute the operation. This is why the idempotency store must be shared (Redis or the primary database), not in-process memory.
4. Distributed transactions get messy. In a saga pattern where creating an order involves charging a payment, reserving inventory, and sending a notification — if the payment step succeeds but inventory reservation fails and you need to compensate, the compensation (refund) must also be idempotent. Otherwise, retrying a compensation can issue multiple refunds.

​

Follow-up: How would you handle idempotency in a system where the primary database and Redis (your idempotency store) might have different states after a crash?

BEGIN;
-- Try to insert the idempotency key (fails if duplicate)
INSERT INTO idempotency_keys (key, status, request_hash)
VALUES ('uuid-123', 'processing', 'sha256:abc')
ON CONFLICT (key) DO NOTHING;

-- Check if we won the insert (key is new) or lost (key exists)
-- If key already existed, SELECT the stored response and return it
-- If key is new, execute the business logic within this same transaction:
INSERT INTO orders (customer_id, amount) VALUES (42, 99.99);

-- Store the response alongside the key
UPDATE idempotency_keys SET status = 'completed', response_body = '...' WHERE key = 'uuid-123';
COMMIT;

​

Going Deeper: What happens when the idempotency key store itself becomes a scaling bottleneck?

1. TTL and cleanup. Set keys to expire after 24 hours (Stripe’s approach). Run a periodic job to delete expired keys. This keeps the table size bounded. At 1 million API calls per hour with 24-hour TTL, you have roughly 24 million active keys — manageable for PostgreSQL with proper indexing.
2. Partition the idempotency table. Partition by created_at (daily or hourly). Dropping old partitions is O(1) — far faster than DELETE on millions of rows. This also keeps the index small and fast.
3. Use a hash as the primary key. Idempotency keys are UUIDs (128 bits). Store them as uuid type, not text — this saves 20+ bytes per row and makes the B-tree index denser (more keys per page, fewer levels, faster lookups).
4. Separate the hot path. For extremely high-throughput systems, use a dedicated Redis cluster just for idempotency with a TTL. Accept that a Redis failure means you lose deduplication for a brief window — for most operations this is an acceptable trade-off. For payment operations where duplicates are catastrophic, keep the database as the authoritative store and use Redis as a cache layer in front of it.

• Token bucket when bursts are natural and acceptable. A mobile app that syncs 50 items on launch, then makes occasional requests. An API where clients batch operations. Stripe and AWS use token bucket because their clients have bursty access patterns and penalizing bursts would hurt developer experience. The burst size is the bucket capacity — it is a tunable safety valve.
• Sliding window when you need strictly even distribution. Payment processing where 1,000 simultaneous requests could overwhelm a downstream bank API. Rate limiting writes to a database where sustained throughput matters more than peak. Sliding window prevents the “double burst at window boundary” problem that fixed windows have, without allowing the bursty behavior of token bucket.

1. Centralized counter in Redis (most common). Each gateway instance increments a counter in Redis on every request. For token bucket, store {tokens_remaining, last_refill_timestamp} and use a Lua script to atomically check-and-decrement:

-- Token bucket in Redis (atomic Lua script)
local tokens = tonumber(redis.call('get', KEYS[1]) or ARGV[1])  -- max_tokens default
local last_refill = tonumber(redis.call('get', KEYS[2]) or ARGV[3])
local now = tonumber(ARGV[3])
local refill_rate = tonumber(ARGV[2])
local elapsed = now - last_refill
tokens = math.min(tonumber(ARGV[1]), tokens + elapsed * refill_rate)
if tokens >= 1 then
  tokens = tokens - 1
  redis.call('set', KEYS[1], tokens)
  redis.call('set', KEYS[2], now)
  return 1  -- allowed
else
  return 0  -- rejected
end

2. Local counters with periodic sync. Each gateway maintains a local counter and syncs to Redis every N seconds. Faster (no per-request Redis call) but less accurate — during the sync interval, the total across all instances can exceed the limit by up to instances * local_threshold. Acceptable for high-throughput internal APIs where approximate limiting is fine.
3. Redis Cell module. A native Redis module that implements token bucket with a single CL.THROTTLE command. Most operationally simple — one command, atomic, no Lua scripts. But requires loading a Redis module, which some managed Redis services do not support.

​

Follow-up: What happens when your Redis rate limiting cluster goes down? Do you fail open (allow all traffic) or fail closed (reject all traffic)?

​

Follow-up: A client complains they are being rate-limited unfairly — they say they are well under the limit. How do you debug this?

1. Semi-synchronous replication. PostgreSQL supports synchronous_standby_names = 'ANY 1 (standby1, standby2)'. With two standbys, the primary waits for only one to acknowledge. If one standby is fast (same AZ) and one is slow (cross-region), the fast one usually responds first. Write latency drops to the fastest standby’s round-trip. You still get durability — at least one replica has the data.
2. Classify writes by criticality. Not every write needs synchronous replication. For financial transactions, payment records, and audit logs — yes, synchronous is warranted. For session updates, analytics events, and activity logs — asynchronous is fine. PostgreSQL allows setting synchronous_commit at the transaction level:

-- Critical write: wait for standby (default when sync replication is on)
SET LOCAL synchronous_commit = 'on';
INSERT INTO payments (...) VALUES (...);

-- Non-critical write: do not wait for standby
SET LOCAL synchronous_commit = 'local';
INSERT INTO activity_log (...) VALUES (...);

3. Move the standby closer. If the standby is cross-region (which explains the 150ms), consider whether you actually need cross-region replication for your synchronous standby. Put the synchronous standby in the same region (different AZ for fault tolerance — typically 1-3ms round-trip). Add an asynchronous replica cross-region for disaster recovery. Same durability benefit, much lower latency.
4. Batch writes. If the application makes many small writes, batch them into fewer transactions. The sync replication overhead is per-commit, not per-row. Inserting 100 rows in a single transaction pays the round-trip penalty once. Inserting 100 rows in 100 separate transactions pays it 100 times.

​

Follow-up: The standby goes down. What happens to writes on the primary?

• If synchronous_standby_names lists only one standby and it is down, the primary has two choices: (a) block all writes indefinitely (safest for data, catastrophic for availability), or (b) fall back to asynchronous mode (maintain availability, accept that new writes are not replicated until the standby recovers).
• With ANY 1 (standby1, standby2) and two standbys, losing one standby is fine — the primary continues with the other. This is why production setups should always have at least two synchronous standbys configured in an ANY 1 group.

​

Going Deeper: How does PostgreSQL’s WAL-based replication actually work at the byte level?

• synchronous_commit = on (default): primary waits until the standby has flushed WAL to disk. Durable against standby crash, but the standby has not yet replayed the records — queries on the standby might not see the data yet.
• synchronous_commit = remote_apply: primary waits until the standby has replayed the WAL records. Queries on the standby immediately see the committed data. This is strongest consistency but adds replay latency on top of network latency.
• synchronous_commit = remote_write: primary waits until the standby has received and written WAL to OS cache (not necessarily flushed to disk). Faster but vulnerable to standby OS crash.

1. Every query in the application must filter by deleted_at IS NULL. This is the most dangerous part. Miss one query, and deleted records appear in search results, reports, or API responses. In a codebase with 200 queries touching the users table, every single one needs the filter. Global query scopes (Rails default_scope, Django Manager, Hibernate @Where) help but are fragile — they can be accidentally bypassed, they do not apply to raw SQL, and they create confusion when you actually need to see deleted records (admin views, audit, analytics).
2. Unique constraints break. If you have UNIQUE(email) and user A with email alice@example.com soft-deletes their account, user B cannot sign up with that same email because the soft-deleted row still occupies the unique constraint. The fix is a partial unique index: CREATE UNIQUE INDEX idx_unique_email ON users(email) WHERE deleted_at IS NULL. This works but is PostgreSQL-specific and needs to be applied to every unique constraint on every soft-delete table.
3. Foreign keys become complicated. If a user is soft-deleted but their orders still reference user_id, what happens? Cascade soft-delete all orders? Leave them orphaned pointing to a “deleted” user? Neither is clean. In my experience, most teams end up with ad-hoc “if the parent is soft-deleted, treat the child as…” logic scattered throughout the codebase.
4. Table bloat. Soft-deleted records never leave the table. A table that accumulates 10 million “deleted” records but only has 500K active records is 95% dead weight. Indexes include the dead rows (unless you use partial indexes for everything). Full table scans are 20x slower than they need to be. You end up needing a periodic “hard delete” job that archives old soft-deleted records to a separate table or data warehouse — which is basically implementing what you tried to avoid.
5. Performance. A partial index WHERE deleted_at IS NULL helps for queries that filter active records, but the table itself grows unbounded. VACUUM still has to process the full table. Statistics calculations include soft-deleted rows. The query planner might make suboptimal decisions because the row count statistics do not reflect the actual working set.

• “Users want an undo/recycle bin” — Implement a time-limited undo (30 days), then hard delete. Store the deleted record in a separate deleted_users table or an archive. This keeps the main table clean.
• “Legal/compliance requires data retention” — Use an audit log or event sourcing pattern. Every state change is an immutable event. You can hard-delete from the operational database while maintaining a complete history in the audit log. This separates operational data from compliance data.
• “We need to preserve foreign key relationships” — Consider using a status field (status: active | suspended | deactivated) instead of delete semantics. This is more honest about what is happening and avoids the mental model mismatch of “deleted but still there.”
• “We are afraid of accidental deletes” — Use database backups and point-in-time recovery. That is what they are for.

​

Follow-up: The team decides to go with soft delete. Six months later, the table has 50M soft-deleted rows out of 52M total. The API is slow. What do you do?

1. Immediate relief: Create partial indexes for every query that filters active records: CREATE INDEX CONCURRENTLY idx_users_active ON users(id) WHERE deleted_at IS NULL. This gives the query planner a small, efficient index to work with instead of scanning the full 52M-row B-tree. For covering queries, include the needed columns: CREATE INDEX CONCURRENTLY idx_users_active_email ON users(email) WHERE deleted_at IS NULL INCLUDE (name, created_at).
2. Archive the dead rows. Create a users_archive table with identical schema. Move soft-deleted rows older than 90 days in batches:

WITH moved AS (
  DELETE FROM users WHERE deleted_at < now() - INTERVAL '90 days'
  RETURNING *
)
INSERT INTO users_archive SELECT * FROM moved;

3. Table maintenance. After bulk deletion, run VACUUM FULL users (locks the table — do during maintenance window) or pg_repack (no lock, requires extension) to reclaim disk space and rebuild indexes compactly.
4. Prevent recurrence. Implement the archival job as a periodic cron that runs daily, moving records older than the retention period.

​

Follow-up: How does the event sourcing approach compare to soft delete for audit compliance?

1. Partition tolerance is not optional. Network partitions happen in any distributed system — a cable gets cut, a switch fails, a cloud AZ has connectivity issues. You do not get to “pick” partition tolerance. The real choice is: when a partition occurs, do you sacrifice consistency (return potentially stale data) or availability (return an error)?
2. The choice is not binary or permanent. Real databases make different trade-offs for different operations, different configurations, and even different queries. DynamoDB with ConsistentRead: false is AP (eventual consistency, always available). DynamoDB with ConsistentRead: true is CP (strong consistency, may reject reads during a partition if the leader is unreachable). Same database, different behavior per request.
3. CAP says nothing about the common case. Most of the time, there is no partition. During normal operation, the real trade-off is latency vs consistency — which is what PACELC captures. Cassandra is fast because it does not wait for all replicas to agree (low latency, weaker consistency). CockroachDB is slower because it uses distributed consensus on every write (higher latency, strong consistency). The daily engineering experience is dominated by this latency/consistency trade-off, not by partition behavior.

​

Follow-up: A colleague says “we need Serializable consistency for our entire application.” How do you respond?

1. Performance penalty. PostgreSQL’s Serializable Snapshot Isolation (SSI) tracks read and write dependencies across all concurrent transactions. When it detects a potential serialization anomaly, it aborts one of the transactions with a serialization failure. Your application must catch these failures and retry. Under high concurrency, the abort rate can be significant — I have seen 5-15% of transactions aborted in write-heavy workloads. Each abort wastes all the work done by that transaction.
2. Throughput reduction. Because SSI sometimes aborts transactions speculatively (it errs on the side of caution), total throughput drops. Benchmarks typically show 20-40% lower throughput compared to Read Committed for write-heavy workloads.
3. Most operations do not need it. Displaying a user’s dashboard (Read Committed is fine — slight staleness is invisible). Updating a user’s email (Read Committed with a unique constraint is sufficient). Generating a report (Repeatable Read to get a consistent snapshot). The operations that truly need Serializable are specific: double-booking prevention, inventory reservation where two buyers compete for the last item, financial transfers with multi-account balance constraints (write skew).

​

Follow-up: How does CockroachDB achieve strong consistency across regions with acceptable latency?

CREATE TABLE orders (
  id BIGSERIAL,
  tenant_id UUID NOT NULL,
  customer_id BIGINT NOT NULL,
  total NUMERIC(12,2),
  created_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (tenant_id, id)
);

CREATE INDEX idx_orders_tenant_customer ON orders(tenant_id, customer_id);
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at DESC);

1. Row-Level Security (RLS) in PostgreSQL. Enforce tenant isolation at the database level, not just the application level:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant')::UUID);

2. Query-level resource limits. PostgreSQL’s statement_timeout kills queries that run too long. Set it per-role: large enterprise tenants get 30 seconds, small tenants get 5 seconds. This prevents a poorly-written report from a large tenant from holding connections and locks for minutes.
3. Connection pool isolation. Dedicate a portion of the connection pool to each tenant tier. Large enterprise tenants get 20 dedicated connections. Small tenants share a pool of 50 connections. This prevents a single tenant from exhausting all connections during a traffic spike.
4. Partitioning by tenant_id for the largest tenants. When a tenant grows past a threshold (say 10M rows), create a dedicated partition for them:

-- Declarative partitioning by tenant_id
CREATE TABLE orders (
  id BIGSERIAL, tenant_id UUID NOT NULL, ...
) PARTITION BY LIST (tenant_id);

CREATE TABLE orders_tenant_large PARTITION OF orders
  FOR VALUES IN ('large-tenant-uuid');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

5. Monitoring per-tenant query patterns. Track P99 query latency per tenant. If a tenant’s queries degrade, investigate before it affects others. pg_stat_statements with a tenant_id label (via the application, logged as a comment in the query) gives you per-tenant query performance visibility.

​

Follow-up: A tenant with 50M rows wants to run heavy analytics queries. This is destroying performance for other tenants. What do you do?

1. Immediate mitigation: Route the tenant’s analytics queries to a read replica. This takes the load off the primary immediately. The replica might have 1-10 seconds of replication lag, which is acceptable for analytics.
2. Medium-term solution: Set up a dedicated analytics pipeline. Replicate the tenant’s data to a columnar data warehouse (BigQuery, Redshift, Snowflake, or even DuckDB for smaller datasets). Analytics queries run there, completely isolated from the OLTP database. The cost of running Redshift for one tenant’s analytics is far less than the cost of degraded performance for all tenants.
3. Product-level solution: Offer “advanced analytics” as a premium tier feature. Enterprise tenants who need heavy analytics get a dedicated read replica or data warehouse access. This aligns the cost (infrastructure) with the value (revenue).

​

Going Deeper: How does DynamoDB handle multi-tenancy differently from PostgreSQL, and when would you prefer it?

1. Phase 1 — Initial scan. PostgreSQL scans the entire table and builds the index from the current snapshot. During this scan, new writes continue normally — they modify the table but the in-progress index does not see them yet. This phase takes roughly the same time as a regular index build (10-60 minutes on 500M rows) but the table is fully writable. PostgreSQL acquires only a ShareUpdateExclusiveLock, which blocks other DDL (you cannot run two concurrent index builds simultaneously) but allows all DML (INSERT/UPDATE/DELETE).
2. Phase 2 — Second scan. PostgreSQL performs a second pass to pick up any rows that were inserted, updated, or deleted during Phase 1. This catches the “delta” between the snapshot used in Phase 1 and the current state. For a table with moderate write volume, this phase is fast — it only processes the changes, not the full table.
3. Phase 3 — Validation. PostgreSQL validates that the index is consistent with the table. For unique indexes, it checks uniqueness constraints against the final state. Once validated, the index is marked as valid and the query planner starts using it.

• Takes longer overall. Because it does two table scans instead of one, CREATE INDEX CONCURRENTLY takes roughly 1.5-2x as long as a regular CREATE INDEX. On a 500M-row table, expect 15-90 minutes.
• Uses more I/O. The double scan means double the disk reads. On an I/O-constrained system, this can impact query performance during the build. Monitor disk utilization and consider running it during off-peak hours.
• Cannot run in a transaction block. CREATE INDEX CONCURRENTLY cannot be wrapped in a BEGIN ... COMMIT block. If it fails partway through (out of disk space, unique violation), it leaves an invalid index. You need to check pg_index.indisvalid and clean up with DROP INDEX CONCURRENTLY if it failed.
• maintenance_work_mem matters. The sort phase of index building uses maintenance_work_mem (default 64MB). For a 500M-row index build, increasing it to 1-2GB dramatically reduces sort time because more of the sort can happen in memory instead of spilling to disk.

-- Increase sort memory for this session
SET maintenance_work_mem = '2GB';

-- Build the index concurrently
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders(customer_id, status);

-- Verify it was built successfully
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indexrelid = 'idx_orders_customer_status'::regclass;

​

Follow-up: The concurrent index build fails halfway through because of a unique constraint violation. What state is the database in? How do you recover?

SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
FROM pg_index WHERE NOT indisvalid;

​

Follow-up: How does MySQL handle this differently? Can you create indexes without blocking writes in MySQL?

1. Wire efficiency. Protocol Buffers are 3-10x smaller than JSON. At 15,000 RPS, if the average JSON payload is 2KB, that is 30MB/second of serialization overhead. Protobuf reduces that to 3-6MB/second. More importantly, serialization/deserialization CPU cost drops significantly — protobuf serialization is 5-20x faster than JSON parsing because it is a simple binary format with no parsing ambiguity (no string escaping, no whitespace handling, no number type inference).
2. HTTP/2 multiplexing. REST over HTTP/1.1 uses one TCP connection per concurrent request (or connection pooling with head-of-line blocking). At 15,000 RPS with 10ms average latency, you need ~150 concurrent connections. gRPC runs over HTTP/2, which multiplexes all 15,000 RPS over a handful of TCP connections (typically 1-4 per client instance). This reduces TCP overhead, reduces connection management complexity, and plays better with load balancers.
3. Code-generated clients and servers. The .proto file is the contract. Both teams generate strongly-typed client and server stubs from it. No hand-written HTTP clients, no JSON parsing bugs, no argument about field naming conventions. At 15,000 RPS, even a 0.01% error rate from a parsing bug is 1.5 errors per second — enough to fire alerts.
4. Streaming capability. If the use case evolves to need server-side streaming (one service pushing updates to the other), gRPC supports it natively. Adding WebSocket or SSE to a REST API is a fundamentally different protocol.

1. Operational complexity. gRPC requires proto file management, code generation pipelines, and proto file versioning. If the team has no gRPC experience, the ramp-up cost is real — 2-4 weeks before the team is productive. REST with JSON is universally understood.
2. Debugging difficulty. You cannot curl a gRPC endpoint. You need grpcurl or Postman with gRPC support. Reading a protobuf response in a network trace requires decoding tools. When something goes wrong at 3 AM, the ability to curl an endpoint and read the JSON response is genuinely valuable.
3. Load balancer compatibility. gRPC requires HTTP/2-aware load balancing. Traditional L4 load balancers work, but L7 load balancing (routing based on gRPC method, header-based routing) requires specific support (Envoy, AWS ALB with gRPC support, nginx with grpc_pass). If your infrastructure is not already HTTP/2 ready, this is a prerequisite.
4. Is 15,000 RPS actually stressing REST? A well-implemented REST API with connection pooling, HTTP/2 (yes, REST can use HTTP/2 too), and efficient JSON serialization (simdjson, orjson) can handle 15,000 RPS without breaking a sweat. The serialization overhead at 15K RPS is probably 2-5% of total CPU. If the service is doing meaningful work (database queries, business logic), the JSON vs protobuf difference is noise.

​

Follow-up: The team picks gRPC. Six months later, a new frontend team wants to call this service from the browser. What is your approach?

1. gRPC-Web. A protocol adaptation that works over HTTP/1.1. The browser sends gRPC-Web requests to an Envoy proxy (or any gRPC-Web compatible proxy), which translates them to standard gRPC calls to the backend. The frontend team uses a generated gRPC-Web client. This preserves strong typing and the proto contract but requires the proxy layer.
2. REST gateway. Add a thin REST API that translates HTTP/JSON to gRPC calls. Tools like grpc-gateway (Go) or Envoy’s gRPC-JSON transcoding can auto-generate REST endpoints from proto definitions. The frontend team uses a standard REST client. This is the lowest friction for the frontend team but adds a translation layer.
3. Connect protocol (Buf). A newer protocol from the Buf team (creators of protobuf tooling) that is wire-compatible with gRPC but also supports standard HTTP/1.1 with JSON. A single service implementation serves both gRPC clients (binary, HTTP/2) and browser clients (JSON, HTTP/1.1). This is increasingly the recommended approach for new projects that need both internal gRPC and browser access.

​

Going Deeper: How does protobuf backward compatibility work, and what are the rules for evolving a proto file without breaking clients?

• Adding new fields (with a new field number). Old clients ignore unknown fields. New clients use default values for missing fields.
• Adding new enum values. Old clients treat unknown enum values as the default (0 in proto3).
• Adding new RPC methods to a service. Old clients never call them.

• Changing a field’s type (int32 to string).
• Changing a field number (the field number is the wire identifier, not the name).
• Removing a field (old clients that set this field will silently lose data; old clients that read it will get the default value, which may violate assumptions).
• Renaming a field (safe on the wire since protobuf uses numbers, but breaks generated code that references the old name).

• Pessimistic: High contention — multiple users frequently modifying the same resource. Example: seat reservation for a popular concert (thousands of people trying to grab the same seats simultaneously). Inventory decrements for a flash sale. Account balance updates in a banking system. The cost of contention is high, and you would rather wait briefly than retry repeatedly.
• Optimistic: Low contention — conflicts are rare. Example: editing a user profile (how often do two people edit the same profile simultaneously?). Updating configuration settings. Modifying a shopping cart. Most of the time, there is no conflict, and the version check is just a cheap WHERE clause addition.

​

Follow-up: How does SELECT ... FOR UPDATE SKIP LOCKED actually work, and what is the difference from NOWAIT?

• FOR UPDATE (plain): Blocks until the lock is available. If another transaction holds it, you wait. Simplest, but under high contention, you get long wait queues and potential deadlocks.
• FOR UPDATE NOWAIT: Tries to acquire the lock. If it is already held, immediately throws an error (ERROR: could not obtain lock on row). Useful when you want to fail fast rather than wait — the application catches the error and retries or returns an error to the user.
• FOR UPDATE SKIP LOCKED: Tries to acquire the lock. If the row is already locked, skips that row entirely (as if it did not exist in the result set). This is designed for “grab the next available item” patterns — worker queue processing, ticket reservation, any scenario where you want to grab any available row, not a specific row.

-- Worker grabs the next unprocessed job, skipping any locked by other workers
WITH next_job AS (
  SELECT id, payload FROM jobs
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'processing', locked_by = 'worker-1'
WHERE id = (SELECT id FROM next_job)
RETURNING *;

​

Going Deeper: How does MVCC relate to locking in PostgreSQL? Do readers block writers?

1. Deploy v2 alongside v1. Both versions run simultaneously. The v2 endpoints live at /v2/resources. The v1 endpoints remain untouched at /v1/resources. This is the “expand” phase of expand-and-contract.
2. Internally, both versions share the same backend logic. Do not maintain two separate codebases. Implement a version transformation layer — v1 requests are transformed to the internal canonical format, processed, and the response is transformed back to v1 format. v2 requests use the canonical format directly (or a different transformation). Stripe does exactly this — they maintain hundreds of API versions through a chain of version-specific request/response transforms, all running against a single backend.
3. Use a version router. The API gateway or a middleware layer inspects the version (URL path, header, or the consumer’s pinned version) and applies the appropriate transformations. This means the business logic does not need to know about versions at all.

1. Publish a migration guide for every breaking change. Not just “field X was renamed to Y” — but working code examples showing the v1 call, the equivalent v2 call, and how to handle the migration in common languages (Python, Node, Go, Java). Stripe’s migration guides are the gold standard here.
2. Add deprecation headers to v1 responses:

Deprecation: true
Sunset: 2027-06-01
Link: <https://docs.example.com/migration-v2>; rel="successor-version"

3. Email all 200 integrators with a timeline: v2 available now, v1 deprecated on date X, v1 sunset on date Y. Give at least 12 months between deprecation and sunset for public APIs.
4. Provide a migration testing sandbox. Let integrators test their updated code against v2 in a staging environment before committing. Offer to review their migration code if they are a large partner.

1. Track per-consumer version usage. Dashboard showing which consumers are still on v1, their traffic volume, and last request timestamp. Reach out to high-volume v1 consumers individually.
2. Default new consumers to v2. New API keys get v2 by default. New documentation defaults to v2. Move the v1 docs to a “legacy” section but keep them accessible.
3. Progressive rate limit tightening on v1. Optional and aggressive, but effective: reduce v1 rate limits by 10% per quarter after the deprecation date. This creates gentle pressure without breaking anyone. Communicate this in advance.

1. Sunset v1. After the announced date, v1 endpoints return 410 Gone with a response body containing the migration guide URL. Do not return 404 — 410 explicitly means “this existed but has been intentionally removed.”
2. Keep the v1 transformation code for at least 6 more months after sunset. If a critical partner missed the migration, you can temporarily re-enable it.

​

Follow-up: How do you handle a breaking change in the database schema that affects both v1 and v2 responses?

1. Expand the schema: Add first_name and last_name columns alongside the existing name column. Backfill by parsing the existing name field. New writes populate all three columns.
2. Version transforms: v1 response continues to read from name (or concatenate first_name + last_name if name has been deprecated). v2 response reads from first_name and last_name. Both versions work against the same database.
3. Contract: After v1 is sunset and all code paths reading name are removed, drop the name column. Do this with a non-locking migration (on large tables, use ALTER TABLE ... DROP COLUMN which in PostgreSQL is just a catalog update — the column is hidden, not physically removed, so it is instant).

​

Follow-up: A partner refuses to migrate and threatens to leave the platform. What do you do?

• Narrow table: ~6,700 pages, ~52 MB of I/O
• Wide table: ~250,000 pages, ~1.95 GB of I/O

• SELECT id, name, email at ~100 bytes per row: 20 MB/second
• SELECT * with a 2KB row: 400 MB/second

​

Follow-up: When IS it acceptable to use SELECT *?

1. Ad-hoc exploration. When debugging in a psql terminal, SELECT * FROM users WHERE id = 42 LIMIT 1 is fine. You are looking at data, not running production queries.
2. EXISTS subqueries. SELECT 1 FROM ... WHERE EXISTS (SELECT * FROM orders WHERE ...) — the SELECT * inside EXISTS is never materialized. The database just checks for row existence. Practically identical to SELECT 1.
3. Very early prototyping. When you are sketching a feature and the schema is changing hourly. But this should be replaced with explicit column lists before merging to main.
4. When you genuinely need every column and the table is narrow. A 5-column config table where every query needs all columns — SELECT * is fine. But even here, explicit columns protect you against future schema changes.

​

The trap: “Additive changes are always safe” is the textbook answer. It is also wrong in practice.

1. Strict deserialization. Many clients use statically typed languages with strict JSON parsers. Java’s Jackson with FAIL_ON_UNKNOWN_PROPERTIES = true (the default in some frameworks) throws an exception when it encounters a field not defined in the model class. .NET’s System.Text.Json behaves similarly by default. The partner integrated six months ago, generated their DTOs, and never updated them. Your new field is “unknown” to their parser, and their code throws. I have personally seen this break a $2M/year partner integration at a fintech company — a single new metadata field on an invoice response crashed their nightly reconciliation pipeline for 14 hours.
2. Response size increase triggering limits. If the new field is a nested object or array, the response payload grows. Partners with a 64KB response body limit in their HTTP client configuration, or a Lambda function with a 6MB response payload limit, suddenly start failing. At one company, adding a line_items array to an order response pushed the payload for large orders from 45KB to 180KB, exceeding a partner’s nginx proxy_buffer_size of 128KB. The response was silently truncated, producing invalid JSON.
3. Database/cache key changes. If the partner hashes the response body for caching or deduplication, a new field changes the hash. Their cache hit rate drops to zero overnight. Their “is this a duplicate webhook?” check starts treating every callback as new because the signature of the payload changed.
4. Snapshot testing and CI pipelines. The partner’s integration test suite does exact-match comparison against a stored response fixture. The new field causes every test to fail, blocking their deployments. They cannot ship any code until they update all fixtures — which for a large integration could be hundreds of test files.
5. Field name collisions. The new field name collides with a field the partner’s application uses internally. If they flatten the API response into a flat namespace (common in ETL pipelines), status from your API collides with their internal status field.

​

Follow-up: How do you prevent this class of incident going forward?

​

Follow-up: A partner demands you never add fields to existing endpoints. How do you respond?

​

This is a debugging war story question. Candidates who have never been on-call will flounder.

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 12346.
         Process 12346 waits for ShareLock on transaction 67891; blocked by process 12345.
HINT: See server log for query details.
STATEMENT: UPDATE orders SET status = 'shipped' WHERE id = 4567

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       blocked.wait_event_type,
       blocked.state
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks bk ON bk.locktype = bl.locktype
  AND bk.database IS NOT DISTINCT FROM bl.database
  AND bk.relation IS NOT DISTINCT FROM bl.relation
  AND bk.page IS NOT DISTINCT FROM bl.page
  AND bk.tuple IS NOT DISTINCT FROM bl.tuple
  AND bk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = bk.pid
WHERE NOT bl.granted;

# WRONG: order depends on business logic, inconsistent across requests
for order_id in batch_of_orders:
    update_order(order_id, status='shipped')

# RIGHT: sort by ID to guarantee consistent lock ordering
for order_id in sorted(batch_of_orders):
    update_order(order_id, status='shipped')

​

Follow-up: How do deadlocks differ between PostgreSQL and MySQL InnoDB?

​

Follow-up: When are deadlocks actually acceptable?

​

The “obvious answer is wrong” question. Caching everything is a common anti-pattern.

-- Find the most expensive queries by total time
SELECT query, calls, total_exec_time, mean_exec_time,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

1. Cache invalidation becomes your new primary problem. Every write now needs to invalidate the right cache keys. Miss one, and users see stale data. With 50 endpoints, you need 50 invalidation paths. The joke “there are only two hard things in computer science: cache invalidation and naming things” exists because cache invalidation in a complex system is genuinely harder than the database queries it replaces.
2. Thundering herd on cache expiry. If a hot cache key expires and 1,000 requests simultaneously hit the database, you get a load spike worse than having no cache. With TTL-based caching on every endpoint, you get mini thundering herds constantly as different keys expire. Solutions exist (cache stampede locks, stale-while-revalidate), but they add complexity to every endpoint.
3. Memory cost is non-trivial. A Redis instance with 50GB of cached API responses is expensive and operationally complex (Redis cluster, failover, persistence, monitoring). If most of those responses are accessed once per TTL window, you are paying for memory to store data nobody will read before it expires.
4. You now have two data stores to keep consistent. Every bug investigation now requires checking both the database and the cache. “Is the user seeing stale data because of a cache issue or a replication lag issue?” Debugging complexity increases quadratically with the number of data stores.

​

Follow-up: When IS “add Redis everywhere” the right answer?

​

Follow-up: How do you handle cache invalidation for data that changes unpredictably?

​

This question tests whether you recognize when NOT to do something, and how you manage up when a technical decision is being driven by unrealistic timelines.

1. Shard key selection is an irreversible architectural decision. Choose the wrong shard key and you get hot shards, cross-shard queries for common operations, and eventual data migration that is harder than the original sharding. You need to analyze every query pattern, every JOIN, every foreign key relationship. For a 3B-row database that has accumulated years of access patterns, this analysis alone takes weeks.
2. Cross-shard queries. Any query that does not include the shard key becomes a scatter-gather across all shards. If you shard orders by customer_id, the query SELECT * FROM orders WHERE created_at > '2025-01-01' must hit every shard. If 40% of your queries are date-range queries, sharding by customer_id makes 40% of your queries slower, not faster. You need to audit every query path before choosing the shard key.
3. Application changes are pervasive. Every database query in the application needs to be shard-aware — either the application routes to the correct shard, or a proxy (like Vitess or Citus) handles routing. If you use application-level sharding, every ORM query, every raw SQL call, every migration script, every background job needs to know about shards. This is months of engineering.
4. Data migration without downtime. Moving 2TB of data from one database to multiple shards while the application continues to serve traffic requires dual-writing, backfilling, verification, and cutover. At 3B rows and typical migration speeds (50K-100K rows/second with backfill), the migration itself takes 8-16 hours. During that window, any write to the old database must be replicated to the new shards.
5. Operational complexity permanently increases. Instead of one database to monitor, back up, upgrade, and troubleshoot, you now have N databases. Schema migrations run across all shards (Vitess handles this; DIY sharding does not). Backup and restore procedures multiply. On-call complexity increases.

• Run pg_stat_statements analysis. Find and fix the top 10 worst queries (missing indexes, sequential scans, N+1 patterns). On a 3B-row database, a single missing index can account for 30-40% of CPU.
• Add read replicas. Route all reporting, analytics, and read-heavy API endpoints to replicas. This can cut primary load by 60-70%.
• Implement connection pooling with PgBouncer if not already in place.
• Partition the largest tables by date range (declarative partitioning). If the 3B rows are time-series-ish (orders, events, logs), partitioning by month means queries for “last 30 days” scan 1 partition instead of 36 months of data. This is a massive win with far less risk than sharding.

• Add Redis caching for the top 5 read-heavy endpoints.
• Begin the shard key analysis: catalog all query patterns, JOIN paths, and access patterns. This is the prerequisite for sharding, and starting it now means the eventual sharding project starts from a position of knowledge rather than guessing.
• Present a realistic sharding timeline (3-6 months) with the query analysis as evidence for why it cannot be rushed.

​

Follow-up: How do you choose a shard key? What is the worst shard key you have seen?

​

Follow-up: What is the difference between sharding and partitioning?

​

This tests API design instinct — the ability to spot violations of REST conventions and think through the consequences.

POST /users/:id/notifications
Body: { "type": "welcome", "channel": "email" }
Response 202: { "notification_id": "ntf_abc123", "status": "queued" }

​

Follow-up: When IS it acceptable to use verbs in REST URLs?

​

Follow-up: How would you design the notification endpoint to support multiple channels (email, SMS, push)?

POST /users/:id/notifications
{
  "template": "order_shipped",
  "channels": ["email", "push"],
  "data": { "order_id": "ord_123", "tracking_url": "..." }
}

{
  "notification_id": "ntf_abc123",
  "status": "queued",
  "channels": [
    { "channel": "email", "status": "queued" },
    { "channel": "push", "status": "queued" }
  ]
}

​

This is a “common misconception with real money on the line” question. The obvious answer — “Kafka has exactly-once” — is dangerously incomplete.

• Idempotent producer (enable.idempotence=true): the producer can retry sends without creating duplicate messages in the topic. Kafka deduplicates by producer ID and sequence number.
• Transactional producer/consumer: a consume-transform-produce pipeline can atomically read from topic A, process, and write to topic B, with consumer offsets committed in the same transaction. If the process crashes mid-transaction, Kafka rolls back both the output message and the offset commit. No message is double-processed within Kafka.

1. Consumer reads order event from Kafka.
2. Consumer calls the payment API — payment succeeds, customer is charged $50.
3. Consumer attempts to commit the Kafka offset.
4. Consumer crashes (or the network blips) before the offset commit succeeds.
5. Kafka redelivers the message (offset was not committed).
6. Consumer processes it again — calls payment API again — customer is charged another $50.

def process_order(event):
    idempotency_key = f"{event.order_id}:{event.event_type}"

    # Atomic check-and-insert in the same DB transaction as the business logic
    with db.transaction():
        if db.exists("processed_events", key=idempotency_key):
            log.info(f"Skipping duplicate: {idempotency_key}")
            return  # Already processed

        # Process the order
        charge_payment(event.order_id, event.amount)

        # Record that we processed it
        db.insert("processed_events", key=idempotency_key, processed_at=now())

​

Follow-up: Why not just use manual offset commits after processing?

​

Follow-up: How does the idempotency table perform at high throughput?

​

This tests distributed database internals knowledge. Candidates who only know SQL will struggle. Candidates who have operated DynamoDB in production will nail it.

1. Bad partition key design. Using date as the partition key means all of today’s writes hit one partition. Using status (with 3 possible values) means 3 partitions max, with the most common status being hot.
2. One big tenant in a multi-tenant table. If tenant_id is the partition key and one tenant has 100x the traffic of others, their partition is permanently hot.
3. Time-based access patterns. An IoT system where device_id is the partition key, but all devices report at the top of every minute. The write pattern is bursty, concentrating on a few partitions simultaneously.

1. Write sharding (adding randomness). Append a random suffix to the partition key: instead of tenant_id = "big_customer", use tenant_id = "big_customer#3" where the suffix is a random number from 0-9. This spreads the big customer’s writes across 10 partitions. Reads require a scatter-gather across all 10 suffixed keys, but writes are distributed. This is DynamoDB’s official recommended pattern for hot partitions.

import random
# Write: spread across 10 logical partitions
suffix = random.randint(0, 9)
table.put_item(Item={
    'pk': f'TENANT#{tenant_id}#{suffix}',
    'sk': f'ORDER#{order_id}',
    ...
})

# Read all orders for a tenant: query all 10 partitions
for suffix in range(10):
    results = table.query(
        KeyConditionExpression=Key('pk').eq(f'TENANT#{tenant_id}#{suffix}')
    )

2. Switch to on-demand capacity mode. On-demand mode provisions capacity automatically based on traffic and does not have per-partition limits as strict as provisioned mode (it still has limits, but they are higher and auto-adjust). For bursty workloads, on-demand eliminates the capacity planning problem entirely. The trade-off: on-demand is 5-7x more expensive per request than provisioned at steady-state traffic. But if you are throttling, the lost requests cost more than the price premium.
3. Redesign the partition key. If the hot partition is caused by a fundamental design flaw (using date or status as the partition key), no amount of capacity will fix it. Redesign with a high-cardinality key. For time-series data, use device_id (high cardinality) as the partition key and timestamp as the sort key — not the reverse.
4. Use a GSI with a different partition key. If the hot path is a specific query pattern, create a Global Secondary Index with a partition key that distributes more evenly. The GSI has its own partitions and throughput.

​

Follow-up: How does DynamoDB’s on-demand mode handle sudden traffic spikes?

​

Follow-up: When would you use DynamoDB over PostgreSQL?

​

This is a “numbers that sound good but are not” question. It tests whether candidates think in absolute numbers, not just percentages.

• 99.9% success = 0.1% failure = 10,000 failed requests per day
• That is 416 failures per hour, or 7 per minute

• 0.01% failure = 1,000 failed requests per day
• 42 per hour, less than 1 per minute

• 10,000 failures per day — you are right back where you started

1. Categorize the failures. Not all 10,000 daily failures have the same cause. Pull the error logs and categorize:
   • Timeouts to downstream services (often 40-50% of failures): add circuit breakers, increase timeout budgets for critical paths, add fallback responses for non-critical dependencies.
   • Database connection exhaustion (often 15-25%): add PgBouncer, tune pool sizes, add connection timeout with retry.
   • Rate limiting / load shedding (often 10-20%): if you are rate-limiting legitimate traffic, your limits are too low or your capacity is too small.
   • Application bugs (often 10-15%): null pointer exceptions, unhandled edge cases in business logic. Each one is a unique fix.
   • Infrastructure failures (often 5-10%): node crashes, deployment rollouts, cloud provider transient errors.
2. Implement retries with backoff for transient failures. Many of the timeouts and connection errors are transient — the downstream service was busy for 200ms but would succeed on retry. A single retry with 100ms backoff converts a transient failure into a success, reducing the failure count by 30-50%. But retries must be idempotent (see Q2) and must have a retry budget (max 1 retry per request, max 10% of traffic as retries) to avoid amplification.
3. Add graceful degradation. If the recommendation engine is down, show popular products instead of a 500 error. If the analytics service is slow, skip analytics tracking and serve the page. Not every dependency failure needs to be a user-visible error. This alone can improve the user-perceived success rate from 99.9% to 99.95%.
4. Reduce blast radius. A single bad deployment that crashes 1 of 10 servers causes a 10% error spike for the duration of the rollout. Canary deployments (route 5% of traffic to the new version, monitor for 10 minutes, then proceed) limit the blast radius. A bad deploy now affects 5% of traffic for 10 minutes instead of 100% for 5 minutes.

​

Follow-up: How do you define and measure SLOs for an API?

• SLI (Service Level Indicator): The metric you measure. For an API: (successful responses / total responses) * 100 where “successful” means HTTP 2xx/3xx within the latency threshold (e.g., <500ms). Exclude 4xx client errors (those are the client’s fault, not yours) unless they indicate a server-side issue (429s that should not happen).
• SLO (Service Level Objective): The target for the SLI. “99.95% of requests succeed within 500ms over a 30-day rolling window.”
• Error budget: 100% - SLO = error budget. At 99.95% SLO with 50M requests/day, your error budget is 25,000 failures/day. If you burn through the budget, freeze deployments and focus on reliability.

​

Follow-up: What is the difference between SLO and SLA?

​

This is a “both sides have merit and the context determines the answer” question. It tests architectural judgment, not religious preference.