I have lost count of the times a slow app turned out to be a database problem. The app code was fine. The query was not. Or the pool was empty. Or the disk was full and nobody had set an alert on it. Postgres is the database behind most of the apps I have worked on, and it rewards the same habit as everything else in this course. Look at the numbers first, then decide. This lesson is the one I wish I had read before my first on-call week with a database. The example is a small shop database I built for this lesson, called shopdb. It holds 5 customers and 10,012 orders. I ran every statement in this lesson on my own machine, Postgres 16.2, and the outputs are the real ones. One order travels through it, hop by hop. It connects, it waits in a pool, it gets planned, it finds its row, it writes to the log, it gets copied to a replica, it gets cleaned up, and it gets backed up. The same trip, three ways. A steady Tuesday, a sale day when the pool runs dry, and the night the primary disk filled up. Lesson 74 taught the Python that talks to the database. Lesson 144 put Postgres in a container with a healthcheck. This lesson opens the container and shows what is inside.
Difficulty: Intermediate · Know this first: Lesson 74, Lesson 144
One write, three copies. The app writes to the primary. The primary writes the change to its WAL diary. The replica replays the diary. The backup takes a snapshot every night. The green arrow is the way back: reads come from the replica, and they can be stale. That staleness is the price of the second copy.
Read it left to right, then bottom to top. The write goes app to primary to WAL. The replica reads the WAL, never the primary. The backup reads the primary once a night. The green arrow only exists because the write already reached the primary: no write, no read back.
The bar below is a teaching example, not a measurement from a live system. The numbers are the ones from the story: one order on a steady Tuesday costs 55 ms end to end, and the user waits for about 27 ms of it. The rest is background work. The replica, the vacuum, and the backup all happen after the user gets their answer. Step through the lesson or switch the scenario and watch the bar change. The sale day is 1,972 ms, and the disk-full day is 30,024 ms, and almost all of that is one commit waiting on a disk that has no room.
The budget bar above shows three fixed stories. This simulator lets you write your own. The pool holds 100 connections. You set how many orders arrive at once, how slow the slowest query is, and how far behind the replica sits. Move a slider and watch the connection wait, the p95 order time, and the verdict change. The pool wait is simple arithmetic: every order past the first 100 waits, and each place in the queue costs about 4 ms. The p95 order time is the wait, plus 3 ms to plan, plus 4 ms for the index, plus the slow query, plus 8 ms for the WAL flush. The replica lag does not change the order time. It changes how stale the reads are. The numbers are a teaching example, not live measurements. At the defaults the wait is 0 ms, the p95 is 27 ms, and the stale-read window is 2 seconds.
How many orders arrive at once. The pool holds 100 connections. Up to 100, nobody waits. Past 100, every extra order joins the queue, and each place in the queue costs about 4 ms.
How slow the slowest query is, at p95. 12 ms is the fast query from this lesson. 1,500 ms is the report query that scans the whole table. This is the number the user feels.
How far behind the replica sits. 2 seconds is a blink. 30 seconds means a customer who just paid may not see their order yet. The lag changes the reads, not the order time.
Try this: (1) Push concurrent orders to 200 at the defaults. 100 orders queue, the wait becomes 400 ms, and the p95 becomes 427 ms. Same queries, same database. Only the queue changed. (2) Now set concurrent orders back to 40 and push the slowest query to 1,500 ms. The p95 becomes 1,527 ms, about 1.5 seconds, and the verdict flips. The pool did not cause this one. The query did. (3) Set the replica lag to 30 seconds. The order time does not move, but the verdict flips anyway, because a customer who just paid cannot see their order for half a minute. Then move the sliders back and watch the budget bar above follow while the Steady Tuesday scenario is picked: the pool hop and the table-read hop change with your numbers.
Three questions. No score is saved anywhere — this is just you against the material.
Postgres is not one thing. It is four levels of the same job, and every team I have worked with sits on one of them. The levels are not about how much SQL you know. They are about how much of the database is written down, watched, and recoverable. Read them in order. Each level fixes the failure that hurt the most at the level below it.
At the basic level, Postgres is one server, one database, and a handful of tables. The app connects straight to it. There is no pool, no index beyond the primary key, and no backup. It works, and it is how every database starts. I use this level to learn, never to serve real users.
Types:
Uses: A demo, a side project, a first version built to answer one question: does this data model work at all.
Scale: One app, one database, a few thousand rows. It breaks the moment a second app connects, because there is no pool and no plan for who waits.
Failure modes: The disk fills and nobody notices. A bad query scans the whole table and nobody can say which one. The server dies and the data dies with it, because there was no backup.
Cost: Almost nothing. One container, one volume. All of the cost is risk, and the risk is invisible until the day it is not.
Trade-offs: Fast to start, impossible to trust. You get a working database in an afternoon, and you pay for it the first time it goes down and there is nothing to restore.
At the intermediate level, the database gets the three habits from this lesson. The slow queries get indexes. EXPLAIN becomes the first check, not the last one. And pg_dump runs on a schedule, with the file checked. This is the level this lesson teaches in full, and it is the level a small production app needs.
Types:
Uses: A small shop, an internal tool, any app where an hour of downtime is painful but not fatal. The database is still one server, but it is a watched server.
Scale: One primary, tens of thousands of rows, a few dozen connections. It holds until the traffic grows past one server or the data outgrows one disk.
Failure modes: The backup exists but the restore was never tested, so the first real restore fails. Autovacuum falls behind on a write-heavy table, and the table bloats. A long transaction blocks vacuum, and dead rows pile up for days.
Cost: A backup volume, a little time to write the restore test, and the discipline to look at the slow queries once a week.
Trade-offs: You gain a database you can reason about, and you pay with process. The habits only help if the team actually runs them, and the first time somebody skips the restore test to save time, the backup becomes a hope again.
At the advanced level, the database stops being one server. A pooler sits in front of it, so a connection storm cannot kill it. A replica takes the reads. WAL archiving is on, so recovery can reach any point in time. And the dashboard watches four numbers: connection count, replication lag, dead tuples, and disk free. This is the level a production database needs before it serves real money.
Types:
Uses: A shop that takes real orders, a service where an hour of downtime costs real money, any app where the data matters more than the code.
Scale: One primary, one or two replicas, millions of rows, hundreds of connections. It holds until one table outgrows one server, and then the answer is sharding, not a bigger box.
Failure modes: The replica lags and the app reads stale data without knowing it. The pooler queues grow because the pool is sized to the traffic, not the database. A failover is attempted by hand at 2 AM, and the runbook is out of date.
Cost: A second server, a pooler, storage for the WAL archive, and real on-call time to keep the replica healthy.
Trade-offs: You gain reads that scale and a recovery story you can prove, and you pay with a second server and a new failure mode: the replica itself. A replica that is never promoted is a server you are paying to watch.
At the master level, the database is a system, not a server. Failover is tested on a schedule, with a measured time to promote. Backups are restored on a schedule, with a measured time to recover. The schema changes through migrations with a rollback. And the slow queries are caught by a review, not by a user complaint. The team does not hope the database survives. It proves it, every month.
Types:
Uses: A company where the database is the business. A regulated setting where the recovery time is a contract, not a guess. Any team where losing the database means losing the company.
Scale: Many primaries, many replicas, billions of rows, thousands of connections. The platform scales by making the database boring: the same pool, the same replica, the same backup, for every service.
Failure modes: The automation itself becomes the risk. A failover triggers on a false alarm and the app writes to two primaries. A bad migration locks the table for an hour because nobody tested it on a copy of the data. The team trusts the dashboard and stops reading the logs.
Cost: A database team, a fleet of servers, and storage for every version of every backup. The per-service cost falls as services share the platform, but the fixed cost is the highest in this lesson.
Trade-offs: You gain a database you can prove, and you pay with platform complexity. The master level is not the advanced level done harder. It is a different job, and a team should only take it on when the data makes the hand-built database more expensive than the platform.
The pool is the first thing I size, and I size it to the database, not to the traffic. Postgres does real work per connection, and past about 100 active connections it spends more time switching between them than running them. The formula I use is simple. Connections equals twice the CPU cores, plus the disk count. A 4-core box with one disk gets about 9 connections doing real work, and the rest wait. That is why the app gets a pooler. The app can hold a thousand connections. The pooler hands a hundred of them to Postgres. The other nine hundred wait in the pooler, which is cheap, instead of in Postgres, which is not.
Connections fail in two ways, and they look the same from the app. The first is the pool running dry, which this lesson shows. The second is idle connections piling up, which is quieter and worse. An app that opens a connection per request and never closes it will hold a thousand idle connections by lunch. Postgres still pays memory for each one. I catch it with one query, the same pg_stat_activity check from the triage section below. If idle outnumbers active ten to one, the app is leaking connections, and the fix is in the app code, not in the database.
Replication has one honest limit, and it is the lag. A replica replays the WAL in order, one change at a time. It cannot replay faster than the primary writes, and a long transaction on the primary blocks the replay behind it. That is why the sale-day scenario shows 900 ms of lag. The fix is not a bigger replica. The fix is shorter transactions on the primary, and reads that can wait. The rule I give the app team is plain. If the user just wrote it, read it from the primary. If the user is browsing, read it from the replica. A read-your-write bug is a replica lag bug, and the fix is routing, not hardware.
Backups fail in one way more than any other. They succeed, and nobody restores them. The pg_dump in this lesson wrote 517,160 bytes and exited 0. That proves the dump ran. It proves nothing about the restore. I restore on a scratch server once a month, and I time it. The restore time is the recovery time, and it goes in the runbook as a number, not a hope. Point-in-time recovery adds one more piece. The dump is the snapshot. The WAL archive is the replay. On my test server archive_mode was off, so the recovery story stopped at last night. The config below turns it on, and the archive_command copies each WAL segment to the backup volume before Postgres is allowed to reuse it.
Vacuum has one enemy, and it is the long transaction. Vacuum cannot remove a dead row that an old transaction might still read. One report that runs for an hour holds back the vacuum for an hour, and dead rows pile up behind it. I find it with pg_stat_activity, looking for the oldest xact_start. A transaction older than a few minutes is a suspect. The fix is usually in the app: a transaction that opens, waits on a user, and closes an hour later. Short transactions, committed fast, are the whole vacuum story.
At 10x the traffic, the pool is the first thing that breaks, and the answer is a pooler, not a bigger pool. At 100x, the table outgrows one server, and the answer is partitioning by time, then sharding by customer, and both are projects, not settings. The shape of the work does not change. One order still travels the same nine hops. The number of orders does, and the pool, the replica, and the backup are what keep that number from becoming an outage.
Save this as the database section of postgresql.conf. The values are the ones from this lesson: a pool of 100, 128 MB of shared buffers, WAL level replica, and archiving on. My test server had archiving off, and that was the gap. Production does not ship with that gap.
# postgresql.conf: production primary, from this lesson
max_connections = 100 # the pool size; the app pooler holds the rest
shared_buffers = 128MB # the cache Postgres manages itself
wal_level = replica # WAL carries enough to feed a replica
archive_mode = on # keep every WAL segment for point-in-time recovery
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'
autovacuum = on # leave it on; dead rows are its job
Run this in psql against a Postgres 16 database. It builds the shop from this lesson: 5 customers, 12 orders, then 10,000 more. I ran it on my own machine. The output below is the real output.
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
city TEXT,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(customer_id),
total NUMERIC(10,2) NOT NULL,
status TEXT NOT NULL DEFAULT 'placed',
placed_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO customers (name, email, city) VALUES
('Asha Rao', 'asha@example.com', 'Irving'),
('Ben Carter', 'ben@example.com', 'Dallas'),
('Priya Nair', 'priya@example.com', 'Plano'),
('Tom Okafor', 'tom@example.com', 'Fort Worth'),
('Mia Chen', 'mia@example.com', 'Irving');
INSERT INTO orders (customer_id, total)
SELECT 1 + (i % 5), (10 + (i % 490))::numeric + 0.99
FROM generate_series(1, 10000) AS i;
Expected output, exactly as it ran:
CREATE TABLE CREATE TABLE INSERT 0 5 INSERT 0 10000 SELECT count(*) FROM orders; count ------- 10012 (1 row)
Check it yourself. Change the bulk load to 100,000 rows and run it again. The table grows to about 8 MB, and the Seq Scan below gets slower. The shape does not change. The scan still reads every row.
Run the same query twice, once before the index and once after. The first run scans the table. The second run uses the index. The outputs below are the real ones from my run. Timings bounce from run to run on a table this small, so read the rows, not the milliseconds. The scan reads 10,012 rows to return 2,003. The index reads only the 2,003, through 74 heap blocks.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 3; CREATE INDEX idx_orders_customer ON orders(customer_id); EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 3;
Expected output, exactly as it ran:
Seq Scan on orders (cost=0.00..155.40 rows=33 width=64) (actual time=0.015..1.054 rows=2003 loops=1)
Filter: (customer_id = 3)
Rows Removed by Filter: 8009
Planning Time: 0.513 ms
Execution Time: 1.207 ms
CREATE INDEX
Bitmap Heap Scan on orders (cost=4.67..75.61 rows=50 width=64) (actual time=0.170..0.645 rows=2003 loops=1)
Recheck Cond: (customer_id = 3)
Heap Blocks: exact=74
-> Bitmap Index Scan on idx_orders_customer (cost=0.00..4.66 rows=50 width=0) (actual time=0.144..0.145 rows=2003 loops=1)
Index Cond: (customer_id = 3)
Planning Time: 0.690 ms
Execution Time: 0.829 ms
Check it yourself. The planner guessed rows=50 before ANALYZE and rows=2003 after it. Run ANALYZE orders; and then plain EXPLAIN SELECT * FROM orders WHERE customer_id = 3;. The estimate moves to 2003. A bulk load without ANALYZE leaves the planner guessing, and the guess was off by 40 times. Then try the unindexed filter: EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 499.00; It scans again, removes 9,992 rows, and returns 20. The customer index cannot help a filter on total.
Open two psql sessions. In the first, run a slow query. In the second, ask Postgres what is running. The slow query below is SELECT pg_sleep(30), a query that sleeps for 30 seconds. I ran it in one session and caught it from the other. The output below is the real one.
-- session 1:
SELECT 'stuck-report' AS job, pg_sleep(30);
-- session 2, while session 1 is still running:
SELECT pid, usename, state, wait_event_type, wait_event,
round(extract(epoch FROM (now() - query_start))) AS secs_running,
left(query, 60) AS query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start;
Expected output, exactly as it ran:
pid | usename | state | wait_event_type | wait_event | secs_running | query -------+----------+--------+-----------------+------------+--------------+--------------------------------------------- 15093 | postgres | active | Timeout | PgSleep | 3 | SELECT 'stuck-report' AS job, pg_sleep(30); (1 row)
Check it yourself. Cancel it from session 2 with SELECT pg_cancel_backend(15093); using the pid from your own output, not mine. It returns t, and session 1 prints ERROR: canceling statement due to user request. Then run the count again. Zero active queries. One honest note: pg_stat_statements is the usual tool for finding slow queries over time, and it was not available in the Postgres build I used for this lesson. The extension control file was missing, so the CREATE EXTENSION failed. pg_stat_activity is the tool that works everywhere, and it is the one I used here.
Update 3,000 orders, count the dead rows, vacuum, count again. The outputs below are the real ones. The row count never changes. Vacuum takes out the trash, not the data.
UPDATE orders SET status = 'cancelled' WHERE order_id BETWEEN 5001 AND 8000; SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders'; VACUUM VERBOSE orders; SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders'; SELECT count(*) FROM orders;
Expected output, exactly as it ran:
UPDATE 3000 relname | n_live_tup | n_dead_tup ---------+------------+------------ orders | 10012 | 3000 (1 row) VACUUM tuples: 3000 removed, 7676 remain, 0 are dead but not yet removable relname | n_live_tup | n_dead_tup ---------+------------+------------ orders | 7676 | 0 (1 row) count ------- 10012 (1 row)
Check it yourself. The n_live_tup number after the vacuum reads 7,676, not 10,012, because the statistics view lags behind the vacuum and the vacuum only scanned the pages with dead rows. The count is the truth: 10,012 orders, before and after. Trust count, not the statistics view, right after a vacuum.
Dump the database, then ask the primary about replication. The dump wrote 517,160 bytes and exited 0. The replication check returned zero rows, because no replica was connected. That empty result is the check.
pg_dump -h /tmp/n7/pgdata -U postgres -d shopdb > shopdb.sql SHOW wal_level; SHOW archive_mode; SELECT pg_is_in_recovery() AS is_replica; SELECT pid, client_addr, state FROM pg_stat_replication;
Expected output, exactly as it ran:
-- PostgreSQL database dump -- Dumped from database version 16.2 -- Dumped by pg_dump version 16.2 -- pg_dump exit code 0, 517,160 bytes wal_level ----------- replica (1 row) archive_mode -------------- off (1 row) is_replica ------------ f (1 row) pid | client_addr | state -----+-------------+------- (0 rows)
Check it yourself. The two gaps in this output are the homework. archive_mode is off, so there is no point-in-time recovery. pg_stat_replication is empty, so there is no replica. Turn archiving on with the config above, attach a replica, and run the same four queries again. The first three answers change, and the fourth returns one row.
This one stayed with me because every check said the database was fine, and the database was dying anyway. The failure was not in the database. It was in the silence around it.
A small shop ran one Postgres primary on one disk. Orders, the WAL, and the backups all lived on the same volume. The WAL archive job had been failing silently for a week, and the old WAL segments were never cleaned up. The disk filled slowly, a few gigabytes a day, and nobody had an alert on it. At 1 AM the disk hit 100 percent. Commits started waiting, then erroring. Orders failed at the checkout. The replica had nothing new to replay, so it served yesterday. The backup failed too, because there was nowhere to write it. The database was up. It just could not take a write.
Three things, and they are the same three things this lesson teaches. First, a disk alert at 80 percent, with a page at 90 percent. A full disk is a slow failure, and a slow failure deserves an early alert. Second, the WAL archive job got an alert of its own. A failing archive job is a failing backup, and it is checked every day now, not when the disk fills. Third, the WAL moved to its own volume. Orders, the WAL, and the backups no longer share one disk, so one of them filling cannot take down the other two. The incident cost one night of orders. The fix cost an afternoon. The lesson is the one from hop 6: WAL first, data second, and a disk with no alert is a disk that will fill on the worst night it can find.
Design the database for a food-delivery app. It must take 50,000 orders a day, answer each order write in under 100 ms at p95, serve order history reads from a replica, and survive the primary disk filling up without losing a single confirmed order. You have one Postgres primary, one replica, a pooler, and a backup volume. Sketch the design: the tables, the indexes, the pool, the replication, and the backup.
Start with the traffic. 50,000 orders a day is about 0.6 orders a second on average. The Friday peak is about 5 times the average, so plan for 3 orders a second at peak, and 10 a second to be safe. The pool math is easy at this size. 10 orders a second against a pool of 100 means the pool is never the problem, and I keep it at 100 anyway, because the pool is sized to the database, not the traffic. Each order is about 200 bytes, plus items, so a day is about 10 MB of orders and a year is about 4 GB. The indexes add about half that again. The WAL grows faster than the data, about 3 times the order volume on a write-heavy day, so the WAL volume gets its own disk with room for a week. The replica lags under 2 seconds at peak, because the primary writes 10 orders a second and the replica replays 10 a second. The backup is a nightly pg_dump plus WAL archiving, and the restore is tested monthly, with the restore time written in the runbook.
Reading history from the replica buys primary capacity and costs freshness, so I read the just-placed order from the primary and the old history from the replica. A pool of 100 buys safety and costs nothing at this traffic, so I keep it small and let the pooler hold the rest. WAL archiving buys point-in-time recovery and costs disk, so the archive gets its own volume and its own alert. A strict p95 target buys user trust and costs index discipline, so every new query ships with its EXPLAIN and a slow query blocks the merge. And the target is p95, not the average. An average of 50 ms with a p95 of 900 ms fails this design, and the p95 is where the bad query lives.
Saved in this browser, so the Lessons index can show your progress across the course.