Scaling the database
🏠The everyday picture
A busy library first speeds up the catalogue, then adds reading rooms for people who only read, and only much later opens a second branch with half the books. Each step is more work than the last.
📘What it really is
Steps in rough order, simplest first:
- Fix queries and indexes: most database problems are a missing index or an N+1 query (Module 57). Check
EXPLAIN ANALYZEand the slow query log. - Connection pooling (PgBouncer or the platform's pooler, Module 61): many app copies can otherwise exhaust connections.
- Bigger server (vertical scaling): simple and often enough for a long time.
- Caching (Lesson 3) and materialised views for heavy reads and reports.
- Read replicas: copies of the database that receive changes from the primary and serve reads (search, reports). Writes still go to the primary. Replicas lag slightly (eventual consistency, Module 94): send reads that must be fresh (just after a booking) to the primary.
- Partitioning large tables (for example, by month for old bookings or logs), so queries and maintenance touch less data.
- Archiving: move old data to cheaper storage (Module 79's retention rules help).
-
Sharding: splitting data across several databases (for example, by region or tutor). Very powerful, very complex: queries and transactions across shards become hard. Most applications never need it.
-
Separate workloads: run analytics and reports on a replica or a separate analytics database, not on the primary that serves bookings.
-
Measure at each step and go only as far as needed.
🖱️Try it
Load the booking database with realistic volumes (say 2 million bookings and 50,000 students of generated test data). Run your load
test and use EXPLAIN ANALYZE to fix the slowest three queries. Add PgBouncer. If your platform allows, add a read replica and route
search queries to it, keeping booking reads on the primary. Record the improvement at each step.
✅Quick check
What's usually the first fix for a slow database?
Show the answer
Fixing queries and indexes, found with the slow query log and EXPLAIN ANALYZE.
Why send some reads to the primary even with read replicas?
Show the answer
Replicas lag slightly; reads that must reflect a just-made change need the primary.