Skip to content
Tech Interview Prep
Technical interview guide

Database Scaling (Sharding & Replication)

Splitting data across machines (sharding) and copying it across machines (replication): solving two different scaling problems.

Read
24 min
Practice MCQs
25
Interview Q&A
25
Edition
v3
Editorial status
Reviewed

Scope: Vendor-neutral distributed-systems principles; current AWS architecture guidance.

Curated: · Written: · Reviewed:

25 questions

MCQ-15Which workload is best moved to a read replica?
MCQ-5A service writes to the primary and serves reads from three async replicas behind a load balancer. Lag is 200–800 ms. A user saves a profile, gets a 200 from the primary, and the next page load is served by a replica. Which bug does this produce?
MCQ-1A primary runs PostgreSQL 16 and sits at about 90 percent CPU, nearly all of it write work: 1,000 commits/s and 8 MB/s of WAL, with no reporting load of its own. The team adds three asynchronous read replicas, one per availability zone, and moves all reporting queries onto them. What happens to the primary's sustained write throughput?
MCQ-9What does functional partitioning mean?
MCQ-7What does partitioning a table by time make dramatically cheaper?
MCQ-24A PostgreSQL table is range-partitioned by created_at. A unique constraint on email alone is rejected. What does PostgreSQL require of the constraint?
MCQ-16`events` is RANGE-partitioned by month on `created_at`, one partition per month, plus a `DEFAULT` partition added so inserts never fail. A job attaches the next month's partition on the 25th: ```sql ALTER TABLE events ATTACH PARTITION events_2025_03 FOR VALUES FROM ('2025-03-01') TO ('2025-04-01'); ``` For six months this finished in under a second. This month it ran for 38 minutes and blocked all reads and writes on `events`. A partition was missed last month, so 400M rows landed in the `DEFAULT` partition; `events_2025_03` is empty. What explains the 38 minutes?
MCQ-8When evaluating range-based versus hash-based horizontal sharding on a high-cardinality key, which operational trade-off is inherent to the key-to-shard mapping itself, rather than to the routing or rebalancing layer built on top of it?
MCQ-18What is the main risk of sharding by time?
MCQ-3A 2 TB `events` table is range-sharded across 16 shards by `event_id`, a 64-bit value that increases with every insert, so each shard owns a contiguous ID range. The table takes 8,000 inserts/s. Shard 16, which owns the highest range, absorbs 7,900 of those inserts per second and sits at 92% CPU; the other 15 shards sit at 6% CPU. 93% of reads filter by `user_id` and currently fan out to all 16 shards. Which single change fixes both the write concentration and the read fan-out?
MCQ-14A 12-shard cluster is sharded by hash of `user_id`, accepts queries that omit the shard key, and has no secondary index on `status`. A client sends a query filtering on `status = 'active'`. What does the router do?
MCQ-12A Postgres 16 service has pg_stat_statements enabled. A one-hour snapshot shows the four highest-load statements: | statement | calls | mean_exec_time | | --- | --- | --- | | Q1 | 2,000,000 | 0.4 ms | | Q2 | 1,200 | 250 ms | | Q3 | 40,000 | 12 ms | | Q4 | 300 | 900 ms | One engineer-week is available and the goal is the largest reduction in total database time. Which statement should be tuned first?
MCQ-21An endpoint runs `SELECT id, payload FROM events WHERE tenant_id = $1 ORDER BY created_at DESC` against a Postgres 16 primary (16 vCPU, 64 GB RAM, shared_buffers 16 GB). The `events` table holds 60M rows in 9 GB and the buffer cache hit ratio is 99.98%. At peak the endpoint is called 1,500 times/sec. The plan is an Index Scan using `idx_events_tenant_created` on (tenant_id, created_at DESC), returning 48,000 rows per call and consuming 62 ms of CPU. The primary is pinned at 100% CPU and the endpoint's p99 is 12 s. The endpoint renders only the 50 most recent events, and the provider's largest instance for this engine is 64 vCPU. Which change should be made first?
MCQ-4A Postgres 15 primary runs on 8 vCPU / 32 GB with gp3 storage provisioned at 3,000 IOPS and 125 MiB/s. Over a week, p99 for the top read query rose from 35 ms to 410 ms. At peak: CPU 24%, shared_buffers 8 GB, buffer cache hit ratio 91% (down from 99.6%), disk read throughput pinned at 125 MiB/s, 38 of 200 connections in use. Which change is most likely to bring p99 back down?
MCQ-25A Postgres 15 service runs on an 8-vCPU instance. A balance-lookup query executes 30,000 times a minute, and each execution consumes 6 ms of CPU: about 3 of the 8 vCPUs. The lookup must reflect the latest committed write, so its results cannot be cached. Traffic is growing 15% per month. An index would cut the query to 0.4 ms of CPU; a 16-vCPU instance costs twice as much to run. Which choice is justified?
MCQ-20A Postgres primary is configured with max_connections = 200. A service runs 50 pods, each opening a pool of 10 connections, so 500 client connections are open at peak. Under load, pods fail with 'FATAL: sorry, too many clients already'. The team puts PgBouncer in front in transaction pooling mode with default_pool_size = 30 and max_client_conn = 1000. The service uses two databases on that primary, app_a and app_b, both through the same PgBouncer and the same database user. At peak, how many backend connections does the primary hold?
MCQ-6A 16-vCPU Postgres 16 primary (max_connections=500) serves a read-only API through a connection pool. With the pool sized at 20 the API sustains 9,000 queries/s at 100% CPU. An engineer raises the pool to 200; throughput falls to 5,400 queries/s, still at 100% CPU, with ~190 backends in state active. What best explains the drop?
MCQ-19An events service runs PostgreSQL 16 on a single 16-vCPU, 128 GB primary. The events table holds 900 GB and grows about 20 GB/month, but every query filters on the last 7 days, roughly 45 GB, and that range stays resident in the OS page cache. At peak the primary serves 1,200 writes/s and 6,000 reads/s, with CPU at 35% and p99 read latency of 1.5 ms. The team proposes sharding. Which conclusion do the measurements support?
MCQ-13A payments API runs PostgreSQL 16 on a single primary: 16 vCPU, 128 GB RAM. The hot working set is 60 GB and fully cached (99.4% buffer hit ratio). About 70% of statements are writes; at 3,000 TPS the primary sits at 40% CPU, p99 read is 2 ms and p99 commit is 6 ms. CPU has scaled close to linearly with TPS, and a load test at 6,000 TPS on the same instance peaked at 82% CPU. Product forecasts 9,000 TPS within two quarters, and the hot set will grow to about 80 GB. The team will make exactly one infrastructure change now. Which change is right?
MCQ-2An audit-log service runs on one Postgres 16 primary (16 vCPU, 64 GB) whose gp3 volume is already provisioned at the 1,000 MiB/s maximum. Rows average about 85 KiB, and the service already commits 500 rows per transaction. It sustains 12,000 inserts/s with CPU at 35%, and adding clients does not push it past ~12,000 inserts/s. The team must reach 40,000 inserts/s next quarter. Every row carries a tenant_id, and no transaction touches more than one tenant. Which step reaches the target?
MCQ-11A team runs PostgreSQL 16 on one primary: `orders` is declaratively partitioned by month, data older than 24 months is purged by dropping the month partition, the checkout path writes the new order and the customer's updated `lifetime_value` in one commit, and a nightly report reads `orders` and `customers` together. They are moving to 8 shards keyed by `order_id`, with `customers` sharded by `customer_id`. Which capability does the move remove?
MCQ-22A 40-shard ledger uses a directory service that can switch an account's shard mapping atomically. One account's shard has reached 92% disk, while an empty shard is available. The team must move that account with no downtime. Which property of the move makes it hard?
MCQ-23Which observation suggests the problem is contention rather than capacity?
MCQ-10A profile service writes to a single primary and serves reads from asynchronous replicas. The application reaches the database through a proxy that binds each connection to a replica at connect time; the proxy exposes no per-replica replication lag or applied log position to the application. Operators measure lag to the user's region at p50 30 ms and p99 1.8 s. A user edits their profile and immediately reloads it. The write path must return within 50 ms, and the primary sits 80 ms from the user's region, where the nearest replica runs. Which mechanism guarantees the user sees their own edit while maximizing replica offloading?
MCQ-17A comments service writes to a primary and serves all reads, the comment list and the feed, from three async replicas. Replication lag runs 20 ms at p50 and 400 ms at p99. A user posts a comment, the page reloads immediately, and the reload fetches the comment list from a replica; about 6% of these reloads do not show the user's own comment. Feed reads are 90% of read traffic and must stay on replicas, and the write path is already at 45 ms p99 with no room for more latency. Which change fixes the missing comment?