Ep 9 | Why Is Your Database Slow? Debug It In 6 Steps - Bits To Billions | System Design

Database debugging, connection pools, N+1 queries, EXPLAIN plans and lock contention, taught from zero with no prior knowledge assumed. In September 2025 a company called Clerk had a four-day outage because their database got faster. That is not a typo. A routine minor-version update made one operation quicker - handing out new connections. While that was slow it had acted like a queue, spreading connection expiries out over time. The moment it got fast they stopped drifting and lined up: every container asking for all its connections at once, every fifteen minutes, for four days. Nothing was wrong with the database. No slow query, no missing index, healthy hardware. The fix was three lines in the application, adding a little randomness. That situation is the most common one in production engineering and almost nobody teaches it. Somebody says "the database is slow" - and the database is not what is slow. By the end of this you will be able to take an endpoint that takes three seconds and find out why. Not by guessing. By elimination, in a fixed order, one thing at a time. We assume nothing. Not what a connection is, an index is, or a query plan is. WHAT YOU'LL LEARN What "slow" means - latency vs throughput, and why the average lies to you The ten places a query can lose time - only three of them are "the database" What a connection really costs - it is an entire OS process in PostgreSQL Why a SMALL pool beats a big one - a real 2,048 --- 96 change that got 50x faster Pool exhaustion: every endpoint timing out while the database sits idle The one check that separates pool exhaustion from a genuinely slow database GitHub 2023 vs GitLab 2021 - same symptoms, opposite causes, opposite fixes The N+1 problem, its arithmetic, and why every query in it is perfect Why your ORM does this, and the switch to flip in Hibernate / SQLAlchemy GraphQL's structural N+1, and how DataLoader turns 101 queries into 2 Join or batch? The cartesian explosion, and the rule worth memorising EXPLAIN properly: cost is not milliseconds, and ALWAYS multiply by loops Sequential, index, index-only and bitmap scans - a seq scan is not always bad Nested loop, hash join, merge join - and why the algorithm is never the bug Where estimates come from: a 30,000-row sample and a broken independence assumption CREATE STATISTICS - the one command almost nobody knows Six reasons your index is not being used, and how to tell which one you have One sleeping transaction + one migration = total outage (the FIFO lock queue) Dead rows, autovacuum, bloat - and why vacuum can succeed and free nothing Sort by TOTAL time, never average - plus wait event analysis Retry storms: the AWS October 2025 cascade, and the three rules for retries A six-step procedure with an exit at every step, worked end to end Seven traps that cost people offers, and a checklist for 3am CHAPTERS 00:00 Intro 01:47 What "slow" actually means 03:01 Where the time can hide 04:12 What a connection actually costs 05:14 The pool, and why small beats big 07:04 When the pool runs out 09:01 The N plus one problem 10:38 Why your framework does this to you 12:01 Join, or batch? The row explosion 13:30 How to see an N plus one 14:22 EXPLAIN: reading the plan 16:09 The ways to read a table 17:37 Three ways to join 18:55 Why the planner guesses wrong 20:52 Teaching the planner 22:03 Your index exists. It is not being used. 24:03 One sleeping transaction, total outage 26:13 Dead rows, and why cleanup stalls 27:54 Total time, not average time 28:57 What is it waiting for? 30:23 When the fix is not the database 31:49 Retry storms 33:14 The procedure 35:23 One slow endpoint, start to finish 37:44 What do we notice? 39:04 The traps 40:25 Your checklist 41:32 Recap SOURCES Clerk, incident report on the September 2025 four-day outage GitHub availability report, May 2023 | GitLab incident write-up, 2021 AWS post-event summary, DynamoDB / DNS incident, October 2025 PostgreSQL docs: Using EXPLAIN, Planner Statistics, Extended Statistics PostgreSQL docs: Routine Vacuuming, Explicit Locking, pg_stat_activity; the wiki on Number Of Database Connections Google SRE Book, Handling Overload | HikariCP wiki, About Pool Sizing AWS RDS / Aurora docs on wait events, including ClientWrite Notion engineering on the vacuum wall | Kleppmann, Designing Data-Intensive Apps THE SERIES A complete system design course, from zero to senior-interview level. Every episode assumes no prior knowledge. #1 What Is System Design · #2 The 5-Step Framework #3 Back-of-the-Envelope Estimation · #4 Scaling to 10 Million Users #5 Load Balancers & API Gateways · #6 Caching Deep Dive #7 What Happens When You Type a URL? · #8 SQL vs NoSQL #9 Why Is Your Database Slow? - you are here Subscribe so you don't miss the rest of the series. New episodes regularly. #systemdesign #database #postgresql #performance #backend #softwareengineering #techinterview