Appearance
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.
Choosing the shard key is the whole game:
| Good key | Why |
|---|---|
tenant_id in B2B SaaS | Every query is per tenant; tenants never join to each other |
customer_id in a shop | A customer's orders, cart and addresses live together |
user_id in a chat app | A user's messages stay on one shard |
| Bad key | Why |
|---|---|
created_at | All new writes hit the same shard: a hot spot |
country | A few countries dominate; very uneven shards |
| A low-cardinality status | Only 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.
| Synchronous | Asynchronous | |
|---|---|---|
COMMIT waits for the replica | Yes | No |
| Data lost if the primary dies | None | The last moments of writes |
| Write latency | Higher, especially across regions | Unchanged |
| Replica lag | None for committed data | Milliseconds 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])
})- Recent writer? Read from the primary. Everyone else reads from the replica.
- Remember when this user last wrote.
| Use a replica for | Keep on the primary |
|---|---|
| Product browsing, search, public pages | Reading right after writing |
| Reports, exports, analytics | Checking stock before taking payment |
| Background jobs that tolerate slight lag | Anything 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:
Expand.
ALTER TABLE users ADD COLUMN full_name text;This is instant: it adds a nullable column with no default.Write both. Deploy code that writes
nameandfull_name, and still readsname.Backfill in small batches, so no single statement holds locks for long:
sqlUPDATE users SET full_name = name WHERE id IN (SELECT id FROM users WHERE full_name IS NULL LIMIT 5000);Read the new column. Deploy code that reads
full_name.Stop writing the old one. Deploy code that no longer touches
name.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:
| Risky | Safer |
|---|---|
CREATE INDEX | CREATE INDEX CONCURRENTLY |
Adding NOT NULL to an existing column | Add a CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT |
| Adding a foreign key | Add it NOT VALID, then validate |
| Changing a column's type | New column, backfill, switch |
| Renaming a column used by running code | Expand 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)- A partial unique index: an email must be unique only among active users, so someone can sign up again after deleting their account.
- A view that the application queries by default, so forgetting the filter is harder.
- Deleting is an update.
| Good | Costs |
|---|---|
| Undo and restore | Every query needs the filter |
| Audit history | Unique constraints need partial indexes |
| No broken references | Tables 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 identity | UUIDv4 | UUIDv7 | |
|---|---|---|---|
| Size | 8 bytes | 16 bytes | 16 bytes |
| Generated by | The database | Anyone | Anyone |
| Insert order in the index | Sequential | Random | Roughly sequential |
| Guessable, reveals volume | Yes | No | Reveals creation time |
| Good for | Internal IDs, single database | Public IDs, small tables | Primary 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:
| Layer | Defence |
|---|---|
| Pool | max sized so instances × max stays below the database limit |
| Pool | connectionTimeoutMillis of a second or two: fail fast, do not hang |
| Query | statement_timeout, such as 5 seconds, so one bad query cannot hold a connection forever |
| Code | Release in finally; never hold a connection while calling an external API |
| Between app and database | PgBouncer in transaction mode, for many instances or serverless functions |
| Database | A replica with automatic failover; tested regularly |
| App | Retry idempotent work with backoff; a circuit breaker stops hammering a failed database |
| Users | Serve 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)
})- Log errors on idle connections, such as the database restarting, instead of crashing the process.
- 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. - 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.