Database Scaling, Replication, Pooling & Consistency Models — Interview Questions

How to use this lesson

Scaling answers should follow the scaling ladder, and you should say why each step is needed, not jump straight to sharding:

1. Optimise (queries, indexes, pooling).
2. Cache.
3. Read replicas.
4. Partition.
5. Archive.
6. Shard, or use distributed SQL.

Pair every technique with its consistency cost.

Q1. How do you handle very large datasets, and queries over them?

Short answer:

• Access paths: the right indexes (composite, covering, partial), with partition pruning on time or tenant keys.
• Don't load everything: keyset pagination and streaming cursors (fetch size), projections, and server-side aggregation.
• Partition tables (by time or tenant) for pruning, and cheap retention (drop old partitions).
• Archive cold data to cheaper storage (Q16).
• Pre-aggregate: materialised views or rollup tables for dashboards.
• Offload analytics to a warehouse or lake (CDC or ETL): columnar stores for scans.
• Batch processing for bulk jobs (Spring Batch chunking, and set-based SQL).
• Scale out reads (replicas), and eventually writes (sharding) once a single node is saturated.

Learn it in depth → Database Scaling Decision Framework

Q2. Partitioning vs sharding? What is database partitioning, and time-based partitioning?

Short answer:

• Partitioning: splitting one logical table into several physical pieces within the same database server:

  • range (by date);
  • list (by region);
  • hash.

  The benefits are pruning (queries touch only the relevant partitions), smaller indexes, parallelism, and cheap retention (detach or drop a partition instead of a huge DELETE). It's transparent to the application (Postgres declarative partitioning, MySQL partitioning).

• Time-based partitioning: range partitions per day, week or month, on a timestamp. It's ideal for append-mostly data (orders, events, logs, time series). New partitions are created ahead of time (pg_partman), and old ones are archived or dropped.

• Sharding: splitting data across multiple database servers (nodes) by a shard key (customer ID, tenant ID), so write capacity and storage scale horizontally. The costs:

  • cross-shard queries and joins become hard or impossible;
  • there are no cross-shard ACID transactions (without distributed SQL);
  • resharding and rebalancing are complex (consistent hashing, or many logical shards on fewer nodes);
  • the operational complexity grows.

Order of preference: partition first (one node), and shard only when a single node's writes or storage are exhausted. Consider distributed SQL (Citus, Vitess, CockroachDB, Spanner) before hand-rolling shards.

Learn it in depth → Sharding vs Partitioning vs Replication

Q3. What's the hot-partition problem? How do you avoid hotspots in a database?

Short answer: A hot partition or shard receives a disproportionate share of the traffic:

• a monotonically increasing key (timestamp or sequence) means all writes hit the latest partition or shard;
• a celebrity tenant or product;
• a skewed hash key.

The hot node saturates, while the others sit idle. Avoidance:

• choose high-cardinality, evenly distributed shard keys;
• salt or bucket hot keys (key#0..N), aggregating on read;
• hash-based instead of range-based distribution for write-heavy keys;
• split hot tenants onto dedicated shards;
• write sharding for counters (N counter rows, summed on read);
• caching for read hotspots;
• queueing or batching bursts.

For time-series data, combine time with a hash bucket in the partition key (Cassandra's (sensor_id, day)).

Q4. How do you design a multi-tenant database?

Short answer: There are three models, with trade-offs in isolation, cost and operations:

1. A shared database, shared schema: a tenant_id column on every table. It's the cheapest, and scales to many tenants. Enforce isolation with row-level security (Postgres RLS policies), or ORM filters (Hibernate @TenantId/filters), and include tenant_id in every index and unique constraint. The risks are noisy neighbours, and data leaks from a missing filter.
2. A shared database, schema per tenant: better isolation, and per-tenant customisation. Migrations run N times, and it gets painful beyond hundreds or thousands of tenants.
3. A database per tenant: the strongest isolation (compliance, large enterprise customers), with per-tenant backup, restore and scaling. It's operationally heavy, but works well for a few, large tenants.

Hybrid: pooled for small tenants, and dedicated for large or regulated ones. Route by tenant (a routing DataSource in Spring), with tenant context from the authentication token, tenant-aware caching keys, per-tenant quotas, and per-tenant encryption keys where required.

Q5. What is replication? Master-replica vs multi-master? What is quorum-based replication?

Short answer: Replication keeps copies of data on several nodes, for availability, read scaling and disaster recovery.

• Single-leader (master-replica): all writes go to the primary, which streams its log (WAL or binlog) to replicas. It's simple, with consistent write ordering. Reads from replicas may be stale. Replication is asynchronous (fast, but data loss is possible on failover) or synchronous (safe, but with more latency).
• Multi-leader (multi-master): several nodes accept writes (multi-region active-active), and replicate to each other. It gives write availability and locality, but you need conflict resolution (last-writer-wins, CRDTs, application merge), and ordering anomalies appear. It's used with care (Galera, BDR, Cosmos DB multi-region writes).
• Leaderless, quorum-based (Dynamo-style: Cassandra, Riak): writes go to W of N replicas, and reads query R replicas. R + W > N gives overlap (a strong-ish read). Tunable consistency (QUORUM, ONE, LOCAL_QUORUM), with read repair, hinted handoff and anti-entropy.
• Consensus-based replication (Raft or Paxos: etcd, CockroachDB, Spanner) provides linearisable writes through a majority quorum.

Learn it in depth → Replication & Consistency

Q6. What is a read replica? What are read-your-writes consistency and monotonic reads?

Short answer: A read replica is a copy that serves read-only queries, which offloads the primary (reports, browsing, search pages). Because replication is usually asynchronous, replicas lag, so there are consistency guarantees to manage:

• Read-your-writes: a user must see their own updates immediately after writing (they update their profile, then reload it). Techniques:
  • route reads to the primary for a short window after a write, or for that user's session;
  • pass the write's log position or LSN and wait for the replica to reach it (or fall back to the primary);
  • read from a cache updated on write.
• Monotonic reads: a user never sees data go backwards in time (seeing a new comment, then refreshing and it's gone, because the second request hit a more-lagged replica). Techniques: sticky replica routing per session, or tracking the last-seen LSN.

In Spring, routing data sources (AbstractRoutingDataSource, keyed off @Transactional(readOnly = true)) plus lag-aware policies implement this.

Q7. What is connection pooling? How do you tune HikariCP?

Short answer: Opening a database connection is expensive (TCP, TLS, authentication, a server-side process or thread), so a pool keeps a set of reusable connections. The application borrows and returns them, which gives low latency, and bounds the load on the database. HikariCP is Spring Boot's default. The key settings:

• maximumPoolSize: the most important one. Smaller than you think. A good starting point is about (DB cores × 2) + effective spindles for the whole database, divided across the application instances. Total = instances × pool, and it must stay below the database's max_connections, with headroom. Too big means context switching, lock contention and memory on the database. Too small means threads wait (connectionTimeout).
• minimumIdle: equal to max, for fixed pools (recommended), or lower for bursty or idle workloads.
• connectionTimeout (how long to wait for a connection, for example 2–5 seconds, to fail fast), maxLifetime (below the database or firewall idle timeouts, for example 30 minutes), idleTimeout, keepaliveTime.
• leakDetectionThreshold (for example 30 seconds), for debugging leaks.
• With many instances: use PgBouncer or RDS Proxy (transaction pooling) to multiplex them.
• Monitor: hikaricp_connections_active/pending/timeout, and usage and acquire histograms.

Learn it in depth → Connection Pooling

Q8. What is connection-pool exhaustion? What is a connection leak?

Short answer:

• Pool exhaustion: all the connections are in use, so new requests wait, and then fail with SQLTransientConnectionException: Connection is not available, request timed out. Latency spikes, and requests pile up. Causes:
  • slow queries or long transactions holding connections;
  • remote calls inside transactions;
  • traffic spikes with pools that are too small;
  • REQUIRES_NEW doubling the connections per request;
  • N+1 bursts;
  • leaks.
• Connection leak: a borrowed connection that's never returned (a Connection/Statement/ResultSet not closed on some code path, or a Stream from a repository not closed), so the pool gradually empties.
  • Detect it with Hikari's leakDetectionThreshold (it logs the stack trace of the borrower), with pool metrics trending up, and with database session views (pg_stat_activity, "idle in transaction").
  • Fix it with try-with-resources, JdbcTemplate or JPA (which manage closing), closing streams, and database-side timeouts (idle_in_transaction_session_timeout).

Q9. How do you prevent and handle database deadlocks? What is lock escalation?

Short answer:

• Deadlock prevention:
  • access and lock rows in a consistent order (for example, by primary key);
  • keep transactions short;
  • use indexes, so updates lock fewer rows (missing indexes make updates scan and lock more);
  • avoid "read then update" without locking; use atomic UPDATE … WHERE;
  • use lower isolation where it's safe;
  • SELECT … FOR UPDATE SKIP LOCKED for job queues;
  • batch in a consistent order.
• Handling: the database detects and kills a victim, so retry the whole transaction (idempotently, with jitter). Log and monitor the deadlock rates (Postgres log_lock_waits, and the MySQL SHOW ENGINE INNODB STATUS latest deadlock section).
• Lock escalation (mainly SQL Server, and DB2): when a transaction holds many fine-grained locks (row or page), the engine converts them into a single table lock, to save memory. That can suddenly block the whole table for other sessions. Avoid it by batching large updates into smaller chunks, using appropriate indexes, and table options (LOCK_ESCALATION = DISABLE/AUTO) with care. Postgres and InnoDB don't escalate row locks.

Q10. How do you design for write-heavy systems? What is write amplification?

Short answer:

• Write-heavy design:

  • Batch and buffer writes (JDBC batching, multi-row inserts, COPY).
  • Append-only models (event logs), instead of in-place updates.
  • Async ingestion through Kafka, with consumers writing in batches.
  • LSM-based stores (Cassandra, ScyllaDB, RocksDB) or time-series databases for extreme ingest.
  • Minimise the indexes on hot tables (every index is another write).
  • Partitioning or sharding to spread the writes.
  • Avoid hot rows (shard the counters).
  • Tune the durability trade-offs deliberately (group commit, synchronous_commit for non-critical data).
  • Separate the OLTP writes from analytics.

• Write amplification: the ratio of bytes physically written to bytes logically written:

  • B-trees: WAL plus page writes, full-page images after checkpoints, and index updates;
  • LSM trees: rewriting data repeatedly during compaction;
  • SSDs: internal garbage collection;
  • replication: copies on every node.

  High amplification wears SSDs, and reduces throughput. Mitigate it with fewer indexes, larger batches, compaction strategy tuning (leveled vs size-tiered), and HOT updates in Postgres (by avoiding updates to indexed columns).

Q11. What is a database failover strategy?

Short answer:

• High availability within a region: a primary plus synchronous or semi-synchronous standby replicas across availability zones, with automatic failover (Patroni with etcd for Postgres, orchestrator for MySQL, or managed: RDS Multi-AZ, Aurora, Cloud SQL HA).
• Detection and promotion: health checks, then fencing the old primary (to prevent split-brain), then promoting the standby, then updating the endpoints (DNS or a virtual IP, a proxy like PgBouncer or HAProxy, or cloud cluster endpoints).
• The client side:
  • connection-pool validation, and short DNS TTLs;
  • retries for transient errors during failover, with idempotent operations;
  • multi-host connection strings (targetServerType=primary in PgJDBC).
• Disaster recovery across regions: asynchronous cross-region replicas or backups, and a defined RPO (acceptable data loss) and RTO (recovery time). Drill it regularly.
• Test it: chaos drills (kill the primary), and measure the downtime and data loss.

Q12. What is BASE vs ACID? What is the dual-write problem?

Short answer:

• ACID: atomic, consistent, isolated, durable transactions. Strong guarantees, typically within one database.
• BASE: Basically Available, Soft state, Eventually consistent. Distributed systems favour availability and partition tolerance, allowing temporary inconsistency, which converges later (NoSQL replication, microservices with events).
• The dual-write problem: an application writes to two systems (database + Kafka, or database + cache or search index) without a shared transaction. If one write succeeds and the other fails (or the process crashes in between), the systems diverge permanently: the order saved but the event never sent, or the event sent but the transaction rolled back. Fixes:
  • the transactional outbox: write the event to an outbox table in the same database transaction, and relay it to Kafka (a poller, or Debezium CDC);
  • CDC directly from the database log, instead of writing twice;
  • listen-to-yourself: publish first, and have the service consume its own event to update its database;
  • idempotent consumers and reconciliation jobs.

Learn it in depth → Outbox Pattern

Q13. How do you handle data archival?

Short answer:

• Define the policy: retention by data class (legal and regulatory requirements: finance often needs 7–10 years; GDPR requires deleting personal data when it's no longer needed), hot, warm and cold tiers, and restore SLAs.
• The mechanics:
  • partition by time, and detach or drop old partitions after exporting them;
  • batch-move rows to archive tables or an archive database;
  • export to object storage (Parquet on S3, with lifecycle rules to Glacier) and make it queryable through Athena, Trino or the warehouse;
  • keep tombstones or summaries if the application still needs aggregates.
• Operational care: do it in small batches, off-peak (to avoid lock and replication-lag spikes), make it idempotent and resumable, verify the counts and checksums, and keep the audit trail. Anonymise the personal data that must be retained for analytics.

Follow-up questions this topic invites — and their answers

Q: How does consistent hashing help sharding? A: Keys and nodes are mapped onto a ring, and each key belongs to the next node clockwise. Adding or removing a node moves only about 1/N of the keys, instead of rehashing everything. Virtual nodes smooth the distribution.

Q: How do you choose a shard key? A: It should have high cardinality and even distribution, appear in most queries (to avoid scatter-gather), keep related data together (tenant or customer), and avoid monotonic hot spots. Changing it later is very expensive, so decide carefully.

Q: Why is max_connections in Postgres not "the more the better"? A: Each connection is a process with memory. Beyond the number of cores, active connections compete for CPU and locks, and throughput falls. Use pooling (PgBouncer) to serve many clients with a few hundred server connections.

Q: What are the semi-synchronous replication trade-offs? A: The primary waits for at least one replica to acknowledge receiving the transaction (not necessarily to apply it) before committing. That reduces data loss on failover compared with async replication, at the cost of commit latency. It can fall back to async if replicas are slow.