Module 57: Transactions and Indexes
Performance habits
🏠The everyday picture
A busy restaurant speeds up by fixing what's actually slow (the one overloaded station), not by buying a bigger building on a hunch.
📘What it really is
-
The N+1 query problem, the most common database slowdown in apps: loading 50 orders, then running one more query for each order's customer (51 queries). Fix with a join, or one query for all the needed customers (
WHERE id IN (...)). ORMs can cause this silently (Module 61). -
Select only what you need:
SELECT *on wide tables sends unnecessary data, especially large text or JSON columns. - Paginate every list (Module 47). Cursor pagination stays fast on huge tables.
-
Keep transactions short: long transactions hold locks and block others. Never wait for a person or an outside API inside a transaction.
-
Connection pooling: opening a database connection is slow; apps keep a pool of open connections to reuse (Module 61).
-
Cache results that are expensive and rarely change (Module 38 on the client; Redis on the server).
-
Keep statistics fresh: databases plan queries using statistics about the data (
ANALYZEin both PostgreSQL and SQLite). PostgreSQL usually does this automatically. -
Order of fixing: the right index → better query → less data → caching → bigger server. Most problems are solved by the first two.
🖱️Try it
Write two versions of "show the 50 latest orders with customer names": one with a loop doing a query per order (you can
simulate it by running the customer query 50 times), and one with a join. Compare the number of queries and time. Add
the finding to your queries.sql notes.
✅Quick check
What's the N+1 query problem?
Show the answer
One query to load a list, then one more query per item, instead of fetching everything needed in one or two queries.
Why keep transactions short?
Show the answer
Long transactions hold locks that block other users' changes.