Skip to content

Interview PrepScaling · Advanced

Scaling and Operating Databases ​

34. What is sharding, and when would it be useful? ​

The short answer

Sharding splits one large dataset across several independent databases, called shards, by a shard key: for example, customers A to M on shard 1 and N to Z on shard 2, or more usually hash(customer_id) % 8. Each shard holds only its part, so storage and write load spread across machines. It is useful when one database can no longer handle the writes or the data size, even on the biggest machine. It is a last resort: queries across shards, joins, transactions and resharding all become hard. Try indexes, caching, read replicas and partitioning first.

app or routershard = hash(customer_id) % 4shard 0¼ of customersshard 1¼ of customersshard 2customer 42 lives hereshard 3¼ of customerscustomer 42cross-shard query: ask all
One key decides where the data lives. A query by customer goes to exactly one shard, which is what makes sharding fast. A query that is not by customer must ask every shard and merge, which is slow and complicated.

Choosing the shard key is the whole game:

Good keyWhy
tenant_id in B2B SaaSEvery query is per tenant; tenants never join to each other
customer_id in a shopA customer's orders, cart and addresses live together
user_id in a chat appA user's messages stay on one shard
Bad keyWhy
created_atAll new writes hit the same shard: a hot spot
countryA few countries dominate; very uneven shards
A low-cardinality statusOnly a few possible shards

Do this before sharding: add indexes, cache hot reads, move reads to replicas, archive old data, partition big tables inside one database, and buy a bigger machine. A single modern PostgreSQL server handles a lot. Distributed SQL databases, such as Citus, CockroachDB, Spanner or Vitess for MySQL, can shard for you, but the shard key rules still apply.

35. What is database replication? ​

The short answer

Replication keeps copies of the same data on several servers. In the common primary-replica setup, all writes go to the primary, which streams its write-ahead log to replicas that replay it. It gives high availability, because a replica can be promoted if the primary dies, read scaling, and copies for backups and analytics. Replication can be synchronous, where the primary waits for a replica to confirm before COMMIT returns, losing no data but adding latency, or asynchronous, faster but a crash can lose the last few transactions.

app writesprimaryall writesreplica 1, synchronouspromotion candidatereplica 2, asynchronousreads, reportsWAL, waitWAL, no wait
One writer, several readers. The primary sends its log of changes to each replica. It waits for the synchronous one before confirming a commit, so that replica never misses a transaction and can safely take over.
SynchronousAsynchronous
COMMIT waits for the replicaYesNo
Data lost if the primary diesNoneThe last moments of writes
Write latencyHigher, especially across regionsUnchanged
Replica lagNone for committed dataMilliseconds to seconds

Other replication shapes: logical replication copies row changes for chosen tables, useful for upgrades and feeding other systems. Multi-primary setups accept writes in several places, but must resolve conflicts when two places change the same row.

36. What are read replicas, and when should they be used? ​

The short answer

Read replicas are copies of the primary that serve read-only queries, so heavy reads, such as product listings, search pages, dashboards and reports, stop competing with writes on the primary. Use them when reads far outnumber writes and the primary is busy. The catch is replication lag: a replica can be a fraction of a second, or under load several seconds, behind. A user who just saved something and immediately reads from a replica may not see their own change. So route a user's reads to the primary for a short time after they write, or read from the primary for anything that must be current.

Read-your-own-writes, in code:

ts
const RECENT_WRITE_MS = 5_000

function dbFor(req: Request) {
  const wroteAt = Number(req.session.lastWriteAt ?? 0)
  return Date.now() - wroteAt < RECENT_WRITE_MS ? primary : replica             // (1)
}

app.post('/api/profile', async (req, res) => {
  await primary.query('UPDATE users SET bio = $1 WHERE id = $2', [req.body.bio, req.user.id])
  req.session.lastWriteAt = Date.now()                                          // (2)
  res.status(204).end()
})

app.get('/api/profile', async (req, res) => {
  const { rows } = await dbFor(req).query('SELECT bio FROM users WHERE id = $1', [req.user.id])
  res.json(rows[0])
})
  1. Recent writer? Read from the primary. Everyone else reads from the replica.
  2. Remember when this user last wrote.
Use a replica forKeep on the primary
Product browsing, search, public pagesReading right after writing
Reports, exports, analyticsChecking stock before taking payment
Background jobs that tolerate slight lagAnything inside a write transaction

Monitor lag. Alert when a replica falls more than a few seconds behind, and take it out of the read pool if it falls far behind.

37. How do you manage schema and database migrations safely? ​

The short answer

Keep every schema change as a versioned migration file in git, applied by a tool such as Prisma Migrate, Drizzle Kit, Knex, Flyway or Alembic, and run them automatically in the deploy pipeline, never by hand. Make each change backward compatible, because during a rolling deploy old and new code run at the same time. Use expand and contract: add the new column or table first, deploy code that writes both and reads the new, backfill old rows in batches, switch reads, and only then remove the old column in a later release. Avoid statements that lock big tables for long.

Renaming users.name to full_name without downtime:

  1. Expand. ALTER TABLE users ADD COLUMN full_name text; This is instant: it adds a nullable column with no default.

  2. Write both. Deploy code that writes name and full_name, and still reads name.

  3. Backfill in small batches, so no single statement holds locks for long:

    sql
    UPDATE users SET full_name = name
    WHERE id IN (SELECT id FROM users WHERE full_name IS NULL LIMIT 5000);
  4. Read the new column. Deploy code that reads full_name.

  5. Stop writing the old one. Deploy code that no longer touches name.

  6. Contract. In a later release, ALTER TABLE users DROP COLUMN name;

Operations that lock or rewrite a big table, and the safe alternative in PostgreSQL:

RiskySafer
CREATE INDEXCREATE INDEX CONCURRENTLY
Adding NOT NULL to an existing columnAdd a CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT
Adding a foreign keyAdd it NOT VALID, then validate
Changing a column's typeNew column, backfill, switch
Renaming a column used by running codeExpand and contract, as above

Set lock_timeout in migrations, for example to 5 seconds, so a migration that cannot get its lock fails instead of queueing and blocking every query behind it.

38. What is soft deletion, and what are its trade-offs? ​

The short answer

Soft deletion means marking a row as deleted, usually with a deleted_at timestamp, instead of removing it. Deleted rows are hidden from normal queries but can be restored, kept for audit, and keep foreign keys from breaking. The costs: every query must remember WHERE deleted_at IS NULL, or deleted data leaks; unique constraints stop working naturally, because a deleted user's email still blocks a new signup; tables grow forever; and privacy laws may require a real delete. Use partial indexes and views to make it safer, and purge on a schedule.

sql
ALTER TABLE users ADD COLUMN deleted_at timestamptz;

CREATE UNIQUE INDEX users_email_active ON users (lower(email)) WHERE deleted_at IS NULL;   -- (1)

CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;                   -- (2)

UPDATE users SET deleted_at = now() WHERE id = $1;                                          -- (3)
  1. A partial unique index: an email must be unique only among active users, so someone can sign up again after deleting their account.
  2. A view that the application queries by default, so forgetting the filter is harder.
  3. Deleting is an update.
GoodCosts
Undo and restoreEvery query needs the filter
Audit historyUnique constraints need partial indexes
No broken referencesTables and indexes keep growing
Privacy erasure requests need a real delete

Alternatives: move deleted rows to an archive table, or keep an audit log of changes and delete for real.

39. What are the trade-offs between UUIDs and auto-incrementing IDs? ​

The short answer

Auto-increment IDs, 1, 2, 3, are small, 4 or 8 bytes, fast to index because new rows always go to the end, and easy to read. But they need the database to issue them, reveal counts such as "we have 1,043 orders", and are guessable. UUIDs are 16-byte random-looking IDs that any server or client can generate without coordination, are safe to expose, and simplify merging data. But random UUIDv4 values scatter inserts across the index, which slows big tables. UUIDv7, which starts with a timestamp, keeps inserts in order and is now the usual best of both.

bigint identityUUIDv4UUIDv7
Size8 bytes16 bytes16 bytes
Generated byThe databaseAnyoneAnyone
Insert order in the indexSequentialRandomRoughly sequential
Guessable, reveals volumeYesNoReveals creation time
Good forInternal IDs, single databasePublic IDs, small tablesPrimary keys in distributed systems

Why random inserts hurt. A B-tree index is sorted. Sequential IDs always land on the last page, which stays in memory. Random UUIDs land on any page, so on a large table each insert may touch a different page, causing more disk reads, page splits and a bigger index.

In PostgreSQL 18 and later, uuidv7() is built in: id uuid PRIMARY KEY DEFAULT uuidv7(). On older versions, generate UUIDv7 in the application with a library.

A common compromise: a bigint primary key for joins inside the database, plus a separate random public ID, such as a UUID or a short random string, in URLs and APIs.

40. How do you handle database failures and connection exhaustion? ​

The short answer

Connection exhaustion happens when apps open more connections than the database allows, often because of too many instances, oversized pools, leaked connections that are never released, or slow queries holding connections. Fix it with small pools per instance, a connection pooler such as PgBouncer, timeouts on acquiring connections and on statements, and always releasing connections in finally. For database failures, run a replica with automatic failover, make the app reconnect and retry idempotent operations with backoff, fail fast with clear errors, use circuit breakers, and degrade gracefully, such as serving cached data.

Defences, layer by layer:

LayerDefence
Poolmax sized so instances × max stays below the database limit
PoolconnectionTimeoutMillis of a second or two: fail fast, do not hang
Querystatement_timeout, such as 5 seconds, so one bad query cannot hold a connection forever
CodeRelease in finally; never hold a connection while calling an external API
Between app and databasePgBouncer in transaction mode, for many instances or serverless functions
DatabaseA replica with automatic failover; tested regularly
AppRetry idempotent work with backoff; a circuit breaker stops hammering a failed database
UsersServe cached pages, show "try again shortly", queue writes if possible
ts
pool.on('error', (err) => logger.error({ err }, 'idle database client error'))   // (1)

await pool.query("SET statement_timeout = '5s'")                                  // (2)

app.get('/health/ready', async (req, res) => {
  try { await pool.query('SELECT 1'); res.status(200).send('ok') }
  catch { res.status(503).send('database unavailable') }                          // (3)
})
  1. Log errors on idle connections, such as the database restarting, instead of crashing the process.
  2. Cap query time. In practice, set it per role in the database, ALTER ROLE app SET statement_timeout = '5s', so it applies to every pooled connection.
  3. A readiness check, so the load balancer stops sending traffic to an instance that cannot reach the database (health checks).

Seeing connection exhaustion happen: errors such as sorry, too many clients already from PostgreSQL, or a pool timeout error in the app. Check SELECT count(*), state FROM pg_stat_activity GROUP BY state;. Many connections in idle in transaction mean code that began a transaction and never finished it.

Combine chapters into one PDF

Pick the chapters you want. They print in the order you pick them.

Interview Prep
Databases & Caching
Foundations
SQL
Performance
Concurrency
Scaling
Caching
Other topics
Nothing picked yet