After this lesson, you will be able to: Optimize real database performance: read EXPLAIN ANALYZE, fix N+1 queries, size and use connection pooling, and run production migrations without downtime.
The difference between an app that scales and one that falls over is usually the database. This lesson covers reading EXPLAIN ANALYZE to find slow queries, the N+1 problem ORMs cause invisibly, connection pooling and why it matters, and running schema migrations in production without downtime.
EXPLAIN ANALYZE runs a query and shows the actual plan and timing the database used. The key things to spot: a Seq Scan (full table scan) on a large table where an Index Scan should happen usually means a missing or unused index; high row estimates that are wildly off mean stale statistics; and the most expensive node tells you where the time goes. Covering indexes (which include all columns a query needs) let the database answer from the index alone; partial indexes cover only the rows you query. Reading plans is the core query-optimization skill.
A Seq Scan on a filtered column is the classic signal.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;-- Seq Scan on orders (cost=0.00..18334 rows=1 ...) (actual time=...)-- Filter: (customer_id = 42)-- ^ Seq Scan over the whole table to find one customer's orders = slow at scale.CREATE INDEX idx_orders_customer_id ON orders (customer_id);-- Re-run EXPLAIN ANALYZE: now an Index Scan, orders of magnitude faster.
N+1 is the most common ORM performance bug. You fetch a list of N records, then the ORM lazily fires one extra query per record to load a relation, for N+1 total queries instead of 2. It is invisible in code (just a property access in a loop) but devastating at scale. Detect it by logging queries in development and watching for a burst of near-identical queries. Fix it with eager loading (the ORM's include/join feature) or an explicit JOIN, turning N+1 queries into one or two.
Databases allow only a limited number of concurrent connections, and opening one is expensive. A connection pool keeps a set of reusable connections so requests borrow and return them instead of constantly opening new ones. Size the pool to the database's limit divided by the number of app instances; too large and you exhaust the database (especially with serverless functions that each open a pool), too small and requests queue. PgBouncer is the standard external pooler for PostgreSQL, essential in front of serverless or many-instance deployments. When the pool is exhausted, requests wait or error, so pooling is a reliability concern, not just performance.
A schema change on a live database can lock tables and take the app down. The safe pattern is expand/contract (also called parallel change): make only backward-compatible changes, deploy in steps so old and new code both work during the transition. To rename a column: add the new column (expand), backfill it, deploy code that writes both and reads new, then drop the old column (contract) in a later release. Never make a breaking change in one step against production. Add indexes concurrently so they do not lock writes.
Pick one.
Adding indexes blindly without reading EXPLAIN (indexes cost write performance and space). Not noticing N+1 because each query is fast in isolation. Oversizing connection pools on serverless until the database hits its connection limit. Running a breaking migration in one step and locking the table. Creating an index non-concurrently on a large hot table and blocking writes. Optimizing a query that runs once instead of the one that runs a million times.
Sign in and purchase access to unlock this lesson.