Why I stopped fearing the database and learned to love SQL

For most of my twenties I thought of the database as plumbing. Something that lived behind an ORM, spoke a language I half-remembered from a university course, and was best left undisturbed. I wrote User.where(active: true) and whatever happened next was not, in any meaningful sense, my problem.

It became my problem at 2 a.m. on a Tuesday.

The query that changed my mind

A reporting endpoint that had always been slow-but-tolerable crossed some invisible line and started taking ninety seconds. Every request held a connection. The pool drained. Everything else in the application (signups, logins, the health check) queued behind a report nobody was reading.

The ORM call was four lines long and looked completely innocent. So I did the thing I’d been avoiding for years and asked the database what it was actually doing:

EXPLAIN ANALYZE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '30 days'
  AND o.status = 'settled'
ORDER BY o.total DESC
LIMIT 50;

The plan came back with a sequential scan over eleven million rows, a sort that spilled to disk, and (buried at the bottom) the actual row count next to the planner’s estimate. It expected forty rows. It got 1.8 million.

That gap between estimated and actual is the single most useful number in database work, and I had never once looked at it.

Three things I wish I’d learned earlier

The planner is a cost model, not an oracle. It picks a plan based on statistics about your data. When those statistics are stale or the distribution is skewed, it picks confidently and wrongly. Running ANALYZE after a bulk load has fixed more “mysterious” slowdowns for me than any index.

An index is a promise about how you’ll ask questions. A composite index on (status, created_at) serves the query above beautifully. The same two columns in the opposite order barely help at all. Indexes aren’t a general-purpose speed setting; they’re an answer to a specific question, and the order of the columns is the question.

SELECT * is a bandwidth decision. Pulling every column means the database can’t answer from the index alone, so it goes back to the heap for each row. Naming the three columns you need can turn a scan-and-fetch into a single index scan. It’s the least glamorous optimisation available and routinely the largest.

An ORM is a good tool for writing queries and a terrible tool for understanding them. Use it for the first. Never trust it for the second.

The fix, and what it cost

The eventual repair was one index and one rewritten query, deployed the next morning:

CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC)
  INCLUDE (total, customer_id);

Ninety seconds became eleven milliseconds. The index took four minutes to build and cost about 200 MB of disk. That is the whole trade, and once you can read a plan it’s a trade you can evaluate in advance instead of discovering at 2 a.m.

Falling for it

What surprised me most wasn’t the performance. It was that SQL turned out to be pleasant. It’s declarative, it’s been stable for forty years, and it is one of the few technologies where the thing you learned a decade ago is still exactly correct today. Window functions, CTEs, lateral joins: there’s a genuinely expressive language in there, and most of us are using maybe a fifth of it.

The database was never a dumb bucket. It’s the most sophisticated piece of software in the stack, it has spent decades learning how to answer questions efficiently, and it will explain its reasoning to anyone who asks.

I just had to start asking.