Databases and Scaling
Data outlives servers and is the hardest part to scale. Choose storage by access pattern, then scale reads with replicas and writes with shards.
- Several TBdata size where sharding becomes worth it
- ~80%keys moved, 4 to 5 shards, hash % N
- Tens of secondsRTO with automated failover
Show what to say
“I take the cheapest move first: indexes, a bigger machine, a cache, then read replicas, and I shard only when one primary can't keep up with writes or data size. Replicas scale reads but lag, so right after a write I read the user's own data from the primary. If I do shard, I pick a high-cardinality key that matches the main query, like user_id.”
Trade-offs
- Async vs sync replication: async commits fastest but can lose the last few writes; sync loses nothing but one slow replica stalls every write.
- Hash vs range sharding: hash spreads evenly but range scans hit every shard; range scans cleanly but sequential keys make one hot shard.
- Indexes vs write speed: an index makes reads fast, but every INSERT updates it and it takes memory, so index the queries you run.
Bring it up before they ask
Read-your-writes breaks on a lagging replica: a user saves, reads, and thinks the change was lost, so route their reads to the primary for a few seconds.
New to the words? Read the one-minute primer
Pick a store by access pattern, index the queries you run, and scale reads with . last, on a key that matches your main query.
Why it matters
Lost orders and double bookings happen in the database. Copies of data must agree, so it is the hardest part to scale.
Read more · 315 words
Take the cheapest move first: indexes → a bigger machine → a → → . Each step costs more code and more ways to fail than the one before, so climb only as far as the numbers force you.
| You see | Try first |
|---|---|
| A page takes 2 s at 300 queries/s | An index: EXPLAIN shows a Seq Scan |
| 10,000 reads/s, 95% of them reads | A cache, then replicas |
| Several TB, or writes one primary can't take | Shard |
Work the arithmetic before you choose. At 10,000 queries/s with 95% reads, the sees 9,500 reads and 500 writes. A cache at a 90% leaves 950 reads, so the primary now handles 1,450 queries/s. Two instead would leave it with only the 500 writes. Neither move touches the write rate: only sharding does.
Play the three scenarios: a Write streams to replicas, a Read is spread across them, and the primary stays the only node that accepts writes.
The decision tree walks the same ladder as a set of questions.
Tap any box to see what it is and what it talks to.
- Service
- Database
Text description
- UPDATE users SET bio = … goes to the primary, the only node that accepts writes.
- The primary appends the change to its write-ahead log, commits and acknowledges. With async replication it doesn't wait for the replicas.
- A few milliseconds later the change streams to both replicas, which apply it in the same order.
- Both replicas have caught up. Every node now holds the same data.
Is one query slow, and does EXPLAIN show a Seq Scan on a big table?
Storage engines and indexes
Two designs, opposite bets. A keeps rows in sorted 8 KB pages and edits a page in place, so reads stay fast and predictable.
Read more · 538 words
An only appends, so writes are fast; background compaction merges its files. You rarely pick an engine on its own: you pick a database and inherit its engine. Knowing the bet explains why Postgres is a good default and why Cassandra eats a firehose of writes.
| B-tree (the default) | LSM tree | |
|---|---|---|
| How it works | A lookup walks root, branch, leaf: 3 or 4 pages even for hundreds of millions of rows, so read latency barely moves as data grows | A write goes to a commit log and an in-memory sorted table; when full it is flushed as one immutable sorted file; compaction merges files |
| Best at | Read-heavy and mixed workloads, range scans: orders, users, payments | Write-heavy ingest read mostly by key: chat messages, event streams, metrics, time series, tens of thousands of writes per second per node and up |
| Costs | Every write is a random write somewhere on disk, plus a log write; a full page must be split in two when it overflows | A read may check several files (softened by Bloom filters); compaction rewrites the same bytes several times (write amplification); a compaction that falls behind slows everything down |
| Used by | Postgres, MySQL InnoDB, SQL Server | Cassandra, RocksDB, ScyllaDB, HBase |
Indexes. An index buys fast reads with slower writes. It is a sorted B-tree beside the table, so the database seeks to the matching rows instead of reading all of them. Play No index, then With an index: with 10 million rows and 20 matches, the scan reads about 500,000 times more rows than the seek needs. Index the queries you run, not the columns you have:
- Leftmost prefix. A composite index is sorted by its first column, then the second inside it, like a phone book sorted by last name then first name.
(user_id, created_at)servesWHERE user_id = ?but notcreated_atalone. Equality columns first, then range or sort. - Covering and partial. A covering index stores extra columns (
INCLUDE (total)) so the query never visits the table. A partial index covers only the rows you query (WHERE status = 'pending'), which can be 1% of the table: small, cheap to write, and fast. - When to add one. A query you run often shows a
Seq Scanon a table over a few thousand rows. Do not index a column with two or three values (is_active): the planner will scan anyway. Use a partial index on the rare value instead. - Every index costs writes. A table with 8 indexes does 9 writes per insert (the table and each index), so a 5,000-inserts-per-second table is really 45,000 page writes a second. Check
pg_stat_user_indexesfor indexes with zero scans and drop them. - An index that exists but is not used: a function on the column (
lower(email)), a type cast, or a leading%inLIKE. Play Index ignored. Always confirm withEXPLAIN ANALYZEand watch for aSeq Scanon a big table. - Build online with
CREATE INDEX CONCURRENTLY, which doesn't block writes. If it fails, drop the invalid index and retry.
Tap any box to see what it is and what it talks to.
- Service
- Search index
- Database
Text description
- There is no index on user_id, so EXPLAIN shows a Seq Scan. The engine reads all 10 million rows to find 20.
- It returns the right rows, but only after reading about 500,000 times more than it needed. At 300 queries a second this is a 2 s page.
Does the WHERE clause test the index's first column, as is (no function, no cast)?
-- A composite index follows the leftmost-prefix rule
CREATE INDEX CONCURRENTLY orders_user_created ON orders (user_id, created_at DESC);
-- uses it fully: WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20
-- uses the prefix: WHERE user_id = $1 AND status = 'paid'
-- generally can't use it: WHERE created_at > now() - interval '1 day' (no user_id)
EXPLAIN ANALYZE SELECT id, total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;
-- Covering (replaces the first index): id and total are stored in the index,
-- so the query above can run as an index-only scan without reading the table
CREATE INDEX CONCURRENTLY orders_user_created_cov ON orders (user_id, created_at DESC) INCLUDE (id, total);
-- Partial: index only the rows you actually query (small, cheap to maintain)
CREATE INDEX CONCURRENTLY orders_pending ON orders (created_at) WHERE status = 'pending';Transactions and isolation levels
Two doctors on call each check "is anyone else on call?", see yes, and both go off call.
Read more · 437 words
That is an isolation bug.
Isolation decides what concurrent see of each other. Weaker levels allow:
- Dirty read: seeing an uncommitted change.
- Non-repeatable read: a row changes between two reads.
- Phantom: a range query finds new rows on the second run.
- : two transactions write different rows and together break a rule (the doctors).
| Level | Prevents | Still possible |
|---|---|---|
| Read committed (Postgres default) | Dirty reads | Non-repeatable reads, phantoms, write skew |
| Repeatable read (MySQL InnoDB default) | Dirty and non-repeatable reads | Phantoms (per the SQL standard; Postgres's snapshot prevents them), write skew |
| Serializable | All four | Nothing |
For rules across rows, use SERIALIZABLE or SELECT … FOR UPDATE, and retry failures. Often a constraint is simpler: UNIQUE (room_id, slot). Pick the cheapest tool that closes the gap: a constraint if the rule is "no duplicates" or "stay in range", an atomic UPDATE for counters, a row lock (FOR UPDATE) when you know which rows to protect, SERIALIZABLE when the rule spans rows you cannot name in advance. Default to read committed and add exactly one of these.
Write skew is two transactions that each read a rule, see it hold, and then update different rows. No row is written twice, so row locks never fire and read committed lets both through. Open the serializable tab: the database notices that each transaction read what the other one wrote, aborts the second with error 40001, and the retry sees the new count. Use SERIALIZABLE for rules like "at least one on call" or "no overlapping bookings", and always retry on 40001 and 40P01.
The lost update is the commonest bug here: two requests read stock 10, each subtract one, each write 9. Two sales, one decrement, and an oversell at 3 a.m. when a flash sale starts. Fix it in the database, not in the app. UPDATE stock = stock - 1 WHERE stock > 0 is atomic and needs no isolation upgrade. When the new value depends on app logic, add a version column and update WHERE version = 3: zero rows changed means someone beat you, so re-read and retry. Deadlocks are the other classic: two transactions lock rows in opposite order. Lock rows in a fixed order (by id) and retry on 40P01.
- Client
- Database
Text description
- Dr. Alice → Database: BEGIN; SELECT count(*) on call
- Database replies to Dr. Alice: 2
- Dr. Bob → Database: BEGIN; SELECT count(*) on call
- Database replies to Dr. Bob: 2
- Dr. Alice → Database: UPDATE alice SET on_call = false; COMMIT
- Dr. Bob → Database: UPDATE bob SET on_call = false; COMMIT
// Postgres aborts conflicting transactions (40001) and deadlocks (40P01);
// the fix is to retry the whole transaction
export async function withSerializable(pool, work, attempts = 5) {
for (let i = 1; ; i++) {
const c = await pool.connect();
try {
await c.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
const result = await work(c);
await c.query('COMMIT');
return result;
} catch (e) {
await c.query('ROLLBACK').catch(() => {});
if (!['40001', '40P01'].includes(e.code) || i === attempts) throw e;
await new Promise(r => setTimeout(r, Math.random() * 20 * 2 ** i)); // jittered backoff
} finally {
c.release();
}
}
}Modeling, SQL vs NoSQL and connection pooling
Modeling: store each fact once, then copy the hot ones on purpose. first, so copies can't disagree.
Read more · 860 words
Denormalize for hot reads, only after a measured read is too slow, and copy the smallest thing that fixes it: a comment_count on posts replaces a COUNT(*) over millions of comments on every feed load with a single column read. Write the comment and the counter in one so they cannot disagree. The cost is real: every write now touches two places, and a missed update means the copy lies. Run a periodic job that recounts and repairs. For copies in another store (a , a search index), see Caching.
The commonest read bug is the N+1 query: a loop issues a query per row. 20 posts (1 query), then each author (20 more) is 21 queries; at 2 ms a round trip that is 42 ms of waiting, against 4 ms for the two-query version, and it gets worse with page size. Spot it by logging the query count per request. Fix it with a join, WHERE id = ANY(ids), or a DataLoader that batches the lookups made in one tick. ORMs hide this: turn on eager loading deliberately.
vs : pick by access pattern. SQL gives joins and transactions. NoSQL gives flexible and easier horizontal scale. In an interview, start relational and say why you would leave.
| Store | How it works | Pick it for | Watch out for |
|---|---|---|---|
| Relational (Postgres, MySQL) | Rows in tables with constraints, joins and multi-row transactions. Handles most products to a very large scale | Orders, payments, anything with rules (an order needs a user; a payment is unique per order); ad hoc queries; unsure how data will be read | No horizontal write scaling: one primary takes all writes until you shard |
| Key-value (Redis, DynamoDB) | Key hashed to a partition: one hop per lookup, capacity grows by adding partitions | Sessions, carts, feature flags, counters, anything read by one known id | Cannot do anything but key lookups (play Not a key lookup). Redis does about 100K operations a second per node; DynamoDB documents a per-partition limit of about 1,000 writes a second, so a hot key is the failure to plan for |
| Wide-column (Cassandra, Bigtable) | Rows partitioned by a key and sorted inside the partition, copies on several nodes, no single leader | Huge append-heavy data read by partition: chat history, activity feeds, time series, device telemetry | Design one table per question; no joins or ad hoc filters; consistency is tunable (write to 2 of 3 nodes and read from 2 of 3 and you get the latest write) |
| Document (MongoDB) | A nested record kept as one JSON-like document | Profiles, catalog items, CMS pages that vary in shape and are read whole | Copies: embed what changes together, reference what is shared, because joins are weaker than SQL. Multi-document transactions exist but are slower and less central, so money still belongs in a relational store |
Bad fits: a money transfer on a store with no multi-row transactions, or a sensor firehose of 100K writes/s on one Postgres . Real systems mix them: Postgres for orders, for sessions, a search index for the catalog. Start from the question you must answer, then walk the tree.
. A database holds a few hundred connections well, not thousands, so many app servers need a pooler between them and it. In Postgres each connection is a separate server process, and max_connections defaults to 100. 50 app servers each keeping a pool of 20 is 1,000 connections against a limit of about 100. Play No pooler, then With a pooler.
Use a pooler (PgBouncer, RDS Proxy) as soon as app servers or you run serverless functions, which open a new connection per invocation. A database works best with roughly 2 × its CPU cores of active queries, so an 8-core machine wants about 16 busy connections, not 1,000. A pool that is too big hurts the database, and one that is too small queues requests. Size from the database outward. The full treatment is in connections and pooling.
// Fixes N+1 by batching lookups made in the same tick
import DataLoader from 'dataloader';
const authors = new DataLoader(async ids => {
const { rows } = await pool.query('SELECT * FROM users WHERE id = ANY($1)', [ids]);
const byId = new Map(rows.map(r => [r.id, r]));
return ids.map(id => byId.get(id) ?? null); // results must match the order of ids
});
// 1 query for 20 posts' authors instead of 20:
const postAuthors = await Promise.all(posts.map(p => authors.load(p.author_id)));Do you need multi-row transactions, joins or ad hoc queries?
Tap any box to see what it is and what it talks to.
- Service
- Load balancer
- Database
Text description
- 50 app servers each open a pool of 20 connections straight to Postgres: 1,000 connections.
- Each connection is a separate server process with its own memory, and the default limit is 100. New connections fail with too many clients.
Quick check50 servers each open 20 connections. Postgres allows about 100. Fix?
Replication, failover and backups
A is a copy that only answers reads.
Read more · 1149 words
It adds read capacity and a standby. The catch is lag: it trails the , and how long a commit waits decides what you can lose. Replicas scale reads only. If writes are the bottleneck, more replicas make it worse, since each one replays every write. Most setups have one leader that takes all writes; multi-leader and leaderless setups add conflicts to resolve (see Consistency and CAP).
Work the load. At 10,000 queries/s with 95% reads, one primary takes all 10,000. Add two replicas and it takes only the 500 writes, while each replica serves 4,750 reads and still replays the same 500 writes.
Play Async, Semi-sync and Sync to see where each commit waits.
| Mode | A commit waits for | If the primary dies | Use it for |
|---|---|---|---|
| Async (default in Postgres and MySQL) | Only the primary's disk | The last few writes can be lost: an acknowledged write that was not yet shipped dies with the primary | Data where losing the last second is acceptable or that is rebuildable: feeds, profiles, most product data |
| Semi-sync | One replica to receive it | Nothing lost, if that replica survives; only one round trip added | Orders and payments. Keep the waiting replica in the same region: one tens of milliseconds away adds that to every commit |
| Sync | All replicas to confirm | Nothing lost, but commit latency equals the slowest replica and one failure stops all writes | Rarely right for a general database. Prefer semi-sync (or a quorum of replicas) so one slow node cannot stall the system |
Replica lag breaks two promises:
- : you save, read a lagging replica, and your change seems lost. Read from the primary for a few seconds after a write.
- Monotonic reads: two reads by one user hit different replicas and the second is older, so data goes back in time (a comment appears and vanishes on refresh). Pin each user to one replica by hashing the user id, or read from the primary for sessions that just wrote. It costs a little balance and gives a consistent timeline.
Normal lag is milliseconds. A bulk UPDATE of 10 million rows, or a long query on the replica, can push it to minutes, because a replica applies the change log mostly in order. Alert on replica lag in seconds, and route a lagging replica out of the read pool. Raise the lag in the simulator:
. The primary dies. Four steps:
- Detect: several missed , so a blip doesn't trigger it.
- Promote the replica with the newest log.
- Reconfigure: point or a proxy at the new primary.
- Fence the old primary. If it returns believing it is primary, two nodes take writes: . Cut its access.
(recovery point objective) is the data you lose: with async , writes that hadn't shipped. (recovery time objective) is time down: tens of seconds with automated failover. RTO is mostly waiting. With a heartbeat every 5 s and three misses to declare death, detection alone costs 15 s. Add 5 s to pick and promote a replica and about 10 s for DNS or a proxy to repoint, and writes fail for roughly 30 s. These are example numbers; yours depend on your timeouts.
- Tune the trade-off, not just the number. Faster detection (fewer missed beats) shortens downtime but triggers more false failovers on a network blip.
- Use a proxy or a managed endpoint rather than raw DNS, because clients DNS answers and keep writing to the dead address.
- Split brain is two nodes that both believe they are the primary. A network blip hides the old primary; the manager promotes a new one; app servers with a cached address keep writing to the old one. Now the same keys hold different data and nothing can merge them. Fence before you redirect: power the old node off, revoke its storage or make it read-only, and let a majority of observers decide who is dead so a single flaky link cannot trigger a promotion. The managers that do this (Patroni, RDS, Orchestrator) exist because doing it by hand at 3 a.m. goes wrong.
Backups. Replicas are not backups: a DROP TABLE reaches every replica within milliseconds. A replica protects against a dead machine; a backup protects against a mistake. At 14:32 a bad migration drops orders; the replica copies the drop at once.
- Point-in-time recovery: base backups plus every segment. Restore a nightly base backup, then replay archived log up to the target second: "restore to 14:31:59, before the bad deploy". You lose at most the log not yet archived, a few minutes of changes or less, so the RPO is far better than a nightly dump. Use it on every production database; managed services (RDS, Cloud ) switch it on with one setting.
- Restore drills: an untested backup is a hope. A restore of a 2 TB database at 100 MB/s takes 2,000,000 MB / 100 = 20,000 s, about 5.5 hours, before replaying log. Time it before you need it, because the number goes into your RTO. Restore into a scratch instance on a schedule and run a query against it.
- Keep one copy in another region and another account, so a leaked credential or a regional outage cannot take both the data and its backup.
Show data table
| queries/s | |
|---|---|
| One primary | 10,000 |
| Primary + 2 replicas: primary | 500 |
| Primary + 2 replicas: each replica | 4,750 |
Tap any box to see what it is and what it talks to.
- Service
- Database
Text description
- A commit arrives at the primary.
- The primary flushes its own log and acknowledges at once. Fastest commit.
- Shipping to the replicas happens afterwards, a few milliseconds later.
- If the primary dies in that gap, the acknowledged write was never shipped. It is lost: RPO is not zero.
// After a user writes, their reads go to the primary for a short window
const STICKY_MS = 5_000; // longer than typical replica lag
export async function query(sql, params, { userId, write = false } = {}) {
if (write) {
const result = await primary.query(sql, params);
if (userId) await redis.set(`rw:${userId}`, Date.now(), 'PX', STICKY_MS);
return result;
}
const recentlyWrote = userId && (await redis.exists(`rw:${userId}`));
return (recentlyWrote ? primary : pickReplica()).query(sql, params);
}Example numbers: a 5 s heartbeat, three misses to declare death, then promotion and repointing. Writes fail for about 30 s.
Text description
- Failover manager: 3 missed heartbeats (0–15 s), Pick newest log (15–20 s), Repoint DNS or proxy (20–30 s)
- Replica 1: Promote (15–20 s)
- App writes: Writes fail or time out (0–30 s)
Sharding
Split the data over several databases, each owning a slice (a ).
Read more · 849 words
It is a one-way door: every query must now know its shard. Shard last, when one can't keep up with writes or data size: several terabytes, or tens of thousands of writes a second.
| Strategy | How it works | Use it for | Watch out for |
|---|---|---|---|
Hash: hash(key) % N (the default) | Hash the key, take it modulo the shard count. Even spread; a lookup by key goes to one shard | Access by id (users, orders, chats) with few range scans | Range scans hit every shard: neighbouring ids are scattered. Going from 4 to 5 shards moves about 80% of keys |
| Range: each shard owns a key range | A contiguous slice per shard: a scan reads one shard in order, and a shard can be split where it grows | Range scans (a customer's orders A to F, a time window) on a non-monotonic key | Sequential keys make one hot shard. Fix: lead with an id that spreads writes, then sort by time inside it |
| Directory: a lookup table | A table says which shard holds each key (often each tenant); place and move data however you like | Multi-tenant products where a few big customers need their own shard | The directory sits on the path of every request: cache it and replicate it |
Play Range scan for the hash cost and Rising key for the range trap.
Pick the key. The is the decision you cannot undo. A good key has high cardinality (user_id, not country) and matches your main query, so the common request is a one-shard request. Shard by country and one shard holds most users while the rest sit idle. A key with few values (country, status) or a rising value (created_at, an auto-increment id) makes one shard hot: never range-shard on a rising key, because every new write hits the last shard. Test it: for the busiest hour, count requests per shard. If the hottest shard has more than about twice the average, change the key now. Walk the questions with your main query in mind.
Size it with arithmetic. Say one primary tops out near 10,000 writes a second (an assumption; measure yours) and you expect 35,000. That needs at least 4 shards (35,000 / 10,000 = 3.5). Plan 8, so each takes about 4,400 writes a second and has room to grow.
What gets harder afterwards: cross-shard joins and , , . See the sharding deep dive.
- Queries that cross shards. A query without the shard key must ask every shard () and wait for the slowest. With 16 shards where each is slow 1% of the time, the chance that at least one is slow is 1 - 0.99 to the power 16, about 15%, so the of the whole query is far worse than any shard's. Fix it with a lookup table (email to user_id) so the common request hits one shard. Cross-shard joins move to the app or an analytics copy, and cross-shard transactions are best designed away: keep rows that change together on one shard (the order and its items share an
order_idoruser_id). - Resharding. Adding a shard to
hash % Nis painful: going from 4 to 5 shards, only 20% of keys keep their shard, so 80% must move. Decouple keys from machines: hash into many virtual shards (say 1,024) and map those to machines. Adding a fifth machine then moves about 1,024 / 5 = 205 whole virtual shards, around 20%, never individual rows. Plan the shard count up front, move one virtual shard at a time, and keep a dual-write or a copy-then-switch step so reads stay correct during the move. Try the ring and add a node.
One node is hidden in the first diagram: the one the app reads from. Place it.
Tap any box to see what it is and what it talks to.
- Service
- Load balancer
- Database
Text description
- Load user 42. The router computes hash(42) % 3 = 1.
- One shard answers. Users spread evenly, so no shard is busier than the rest.
- One hop, one shard.
Does most traffic arrive keyed by one id, like user or tenant?
// Hash a user id to one of N shards
import { createHash } from 'node:crypto';
export const SHARDS = ['db0', 'db1', 'db2', 'db3'];
export function shardFor(userId) {
const h = createHash('md5').update(String(userId)).digest().readUInt32BE(0);
return SHARDS[h % SHARDS.length]; // one user's rows stay on one shard
}
// Simple, but adding a 5th shard changes h % N for ~80% of users: see consistent hashing.Databases and scaling in the interview
Bring the database up as soon as you list the entities, then choose the store, the indexes and the scaling move in that order.
Read more · 609 words
Naming the cheap moves before is what interviewers listen for.
Choose the store by access pattern
Say what you read and write: "Orders need , so Postgres. Sessions are lookups by id, so ." Start relational unless a number or a shape says otherwise.
Name the indexes for the main queries
Pick the two or three queries that matter and give each an index: "(user_id, created_at) for the order history page." Say what you will not index.
Scale reads before writes
Put numbers on it. A first, then , with handled by reading the right after a write.
Shard only when the numbers force you
Say the trigger (several TB, or writes beyond one primary) and the key: "hash on user_id, which matches the main query". Name the cost: cross-shard queries and .
Cover failure and recovery
State the mode (semi-sync for money), the plan with fencing, and backups with point-in-time recovery and a restore drill.
| If the interviewer says | You raise |
|---|---|
| "10x more reads than writes" | Cache, then read replicas with read-your-writes |
| "Two users book the same seat" | A unique constraint, or SERIALIZABLE with retry |
| "The table has 2 billion rows" | Indexes first, then shard by a high-cardinality key |
| "What if the database dies?" | Semi-sync replica, fenced failover, RPO and RTO |
| "Someone ran a bad migration" | Point-in-time recovery from a backup, not a replica |
Quick checkReads are ten times writes. What do you add before sharding?
Interview questions
How would you shard a users table, and what can go wrong?
Hash user_id. Watch , cross-shard lookups like "find by email", and cost.
How do you handle replication lag?
Read your own writes from the for a few seconds. Pin a session to one .
SQL or NoSQL for orders vs an activity feed?
Orders need : . A feed is huge, append-heavy and read by key and time: wide-column or key-value.
How do you stop two people booking the same slot?
A unique constraint, or SERIALIZABLE with retry. Check-then-insert races.
How do you add an index to a 500M-row table without downtime?
CREATE INDEX CONCURRENTLY. If it fails, drop the invalid index and retry.
Your app opens 1,000 connections and Postgres refuses new ones. Now what?
Put a pooler such as PgBouncer in front, so about 40 real connections serve all clients.
Check yourself
Question 1 of 5Score 2 out of 3 or better to mark this page complete.