Database Scaling
How a data store scales and distributes — replication, sharding and shard-key choice, partitioning vs sharding, read replicas, denormalization, connection pooling, and CAP for databases.
9 questions
JuniorTheoryVery commonWhat is the difference between vertical and horizontal scaling, and where does vertical scaling stop?
What is the difference between vertical and horizontal scaling, and where does vertical scaling stop?
Vertical scaling (scale up) adds CPU, RAM, or disk to one machine; horizontal scaling (scale out) adds machines and spreads load across them. Vertical is simplest but stops at the largest available box, grows costly, and leaves a single point of failure. Past that ceiling you scale out with replicas or shards.
Common mistakes
- ✗Swapping the definitions of scale up and scale out
- ✗Believing vertical scaling has no hardware ceiling
- ✗Forgetting a single scaled-up box is still one point of failure
Follow-up questions
- →Why is scaling out harder for writes than for reads?
- →How does a single point of failure motivate scaling out?
JuniorTheoryCommonWhat is a database connection pool, and what happens when it is exhausted?
What is a database connection pool, and what happens when it is exhausted?
A connection pool keeps open DB connections and lends them out, avoiding a TCP and auth handshake. When all are checked out, new requests queue; if none frees within the timeout they fail with a pool-timeout. Relieve by raising pool size within the DB limit, shortening hold time, or adding a pooler like PgBouncer.
Common mistakes
- ✗Thinking a pool opens a fresh connection per request instead of reusing them
- ✗Expecting exhaustion to drop queries silently rather than time out
- ✗Assuming the pool can grow without any database-side connection limit
Follow-up questions
- →How do you size a pool against the database's max-connection limit?
- →How does a long-held transaction starve a connection pool?
JuniorTheoryCommonHow does partitioning differ from sharding, and when is partitioning alone enough?
How does partitioning differ from sharding, and when is partitioning alone enough?
Partitioning splits one table into parts within one instance (by range, list, or hash); sharding spreads parts across separate nodes. Partitioning helps pruning but shares that server's CPU, memory, and disk. It suffices while one node has capacity and you only prune scans or drop partitions — not to scale writes past one machine.
Common mistakes
- ✗Treating partitioning and sharding as identical rather than same-node vs cross-node
- ✗Thinking partitioning adds CPU or memory rather than sharing one server's
- ✗Expecting partitioning to scale writes beyond a single machine
Follow-up questions
- →How does partition pruning speed up a range query?
- →What signal tells you it is time to move from partitioning to sharding?
JuniorTheoryCommonWhat is database replication, and how do master-slave and master-master setups differ?
What is database replication, and how do master-slave and master-master setups differ?
Replication keeps copies of the same data on several nodes. In master-slave one master takes all writes and streams them to read-only slaves that serve reads. In master-master several nodes accept writes and sync to each other — higher write availability, but a risk of write-write conflicts.
Common mistakes
- ✗Confusing replication (full copies) with sharding (disjoint slices)
- ✗Inverting the roles — thinking slaves accept writes in master-slave
- ✗Ignoring write-write conflicts inherent to master-master
Follow-up questions
- →How does a read replica help scale a read-heavy workload?
- →How are write-write conflicts resolved in master-master?
JuniorTheoryCommonWhat is sharding, and what makes a good shard key?
What is sharding, and what makes a good shard key?
Sharding splits one logical dataset horizontally across several nodes (shards); each shard holds a disjoint subset of rows. A good shard key spreads rows and traffic evenly, has high cardinality, matches the common query filter so most queries hit one shard, and rarely changes.
Common mistakes
- ✗Confusing sharding (disjoint horizontal slices) with replication (full copies)
- ✗Choosing a low-cardinality key that packs rows onto few shards
- ✗Picking a monotonic id that hotspots the newest shard on inserts
Follow-up questions
- →Why does a monotonically increasing shard key create a write hotspot?
- →How does cross-shard querying complicate JOINs and aggregation?
MiddleTheoryCommonHow does the CAP theorem apply to a replicated database — what do you give up during a network partition?
How does the CAP theorem apply to a replicated database — what do you give up during a network partition?
During a partition, CAP lets a replicated store keep only consistency or availability, not both. A consistent (CP) setup blocks or rejects writes on the minority side until the split heals, so no one reads divergent data; an available (AP) one serves but may return stale, conflicting data. The real choice is C versus A.
Common mistakes
- ✗Thinking all three CAP properties are attainable at once during a partition
- ✗Treating partition tolerance as optional you can engineer away
- ✗Swapping the CP and AP behaviours during the split
Follow-up questions
- →How does an AP store reconcile conflicting writes after the partition heals?
- →Where does the consistency-latency model
PACELCextend the CAP trade-off?
MiddleTheoryCommonWhat is denormalization, what does it cost, and how do you keep the duplicated data consistent?
What is denormalization, what does it cost, and how do you keep the duplicated data consistent?
Denormalization stores redundant, pre-joined data so reads avoid JOINs — trading write cost for read speed. It costs storage and consistency: every duplicate must update on write or the copies drift. Keep them consistent by updating all copies in one transaction, via triggers, or asynchronously through events.
Common mistakes
- ✗Confusing denormalization with normalization (removing rather than adding redundancy)
- ✗Believing the database keeps duplicated fields in sync for free
- ✗Forgetting that every duplicate must be updated on each write
Follow-up questions
- →When is asynchronous propagation preferable to a single write transaction?
- →How do you detect and repair drift between denormalized copies?
SeniorTheoryOccasionalA read on a replica returns stale data right after a write to the master. Why does this happen, and how do you avoid it?
A read on a replica returns stale data right after a write to the master. Why does this happen, and how do you avoid it?
Replication is asynchronous: the master answers before the change reaches the replica, so a read there sees an older version — replication lag. Fixes: read your own writes from the master, use synchronous replication, pin a session to one node, or wait for the replica to reach the write's log position.
Common mistakes
- ✗Blaming the client's read order instead of asynchronous replication lag
- ✗Thinking a replica never converges rather than catching up after a delay
- ✗Believing stronger master isolation removes replication lag
Follow-up questions
- →How does read-your-writes consistency differ from full strong consistency?
- →What throughput cost does synchronous replication add to writes?
SeniorDesignOccasionalA large multi-tenant service shards its main table by client_id, and the shard assignment is a plain hash of that key. Analytics shows the load is heavily skewed: about 5% of clients generate roughly 80% of all reads and writes. The busiest clients keep landing on a handful of shards while most shards sit nearly idle, and simply adding more shards has stopped helping. Explain what actually breaks under this skew, why adding shards does not fix it, and which changes to the sharding scheme you would specify to spread the load — while keeping single-shard lookups fast for ordinary clients.
A large multi-tenant service shards its main table by client_id, and the shard assignment is a plain hash of that key. Analytics shows the load is heavily skewed: about 5% of clients generate roughly 80% of all reads and writes. The busiest clients keep landing on a handful of shards while most shards sit nearly idle, and simply adding more shards has stopped helping. Explain what actually breaks under this skew, why adding shards does not fix it, and which changes to the sharding scheme you would specify to spread the load — while keeping single-shard lookups fast for ordinary clients.
Heavy clients land on a few shards that become hotspots: they saturate CPU and IO and cap throughput. Adding shards barely helps, since a whole client still maps to one shard. Fixes: a composite key (client_id plus a second dimension) or sub-sharding big tenants, dedicated shards for the biggest clients, and caching hot reads.
Common mistakes
- ✗Assuming a hash key rules out skew when tenant traffic itself is skewed
- ✗Expecting more shards to help when a whole tenant maps to one shard
- ✗Ignoring dedicated shards or caching for the few whale tenants
Follow-up questions
- →How would a composite shard key spread a single heavy tenant?
- →What operational cost comes with isolating whale tenants on dedicated shards?
