Three queries were using 98% of the database

Loyallist has an analytics side. Customer websites run a small tracking script, and dashboards show who visited and what they did. In October those dashboards were taking up to 18 seconds to load, and the database server was busy day and night.

I was sure the dashboards were the problem, until I read the query logs. Three small queries from the tracking script were using 98% of all database time. Every dashboard together used less than 1%.

After the fix, the busiest query went from 1.6 seconds to 29 milliseconds. Those three queries now cost the database about 3 minutes of work a day, down from about 3 hours. No data changed, and every API response stayed exactly the same. Flip the switch:

How the tracking works

Every customer website loads the tracking script. On each page view it asks for its settings and records the view. When the visitor leaves, it sends how long they stayed. The dashboards read the same tables to draw their charts.

The script runs on every page view of every customer site. A dashboard query only runs when someone opens a dashboard. So a slow script call hurts a lot more than a slow chart.

Where the time went

I took three days of production query logs, about 47,000 requests, and added up the database time for each operation. Tap or hover a slice to see what it does.

The cause was boring. The database had 21 indexes, and almost all of them were primary keys. Not a single foreign key had an index. So every lookup by tracking code, session or page read the whole table, on every page view, for every customer.

If that sounds abstract, try it. Pick a row and search for it both ways, then drag the slider to see what happens as the table grows.

Ground rules before touching prod

This is a live product with real customers, so I wrote down a few rules first. Prod stays read-only unless I approve a specific step, and saying yes to one step doesn't cover the next one. Only read speed changes, so every API response keeps the same shape and the same values. Indexes get built with CONCURRENTLY, which lets reads and writes keep going while they build. And every change has a rollback script that I tested locally first.

Then I set up a test bench. I restored a prod snapshot into Docker on my machine and capped it to the same CPU and memory as the real server. I replayed all 58 read operations from both apps through the API and timed each one with EXPLAIN ANALYZE. Every task also got an independent review.

The most useful part was a parity check. It runs the old query and the new query on a pile of awkward cases and fails on any difference at all.

Step 1: fourteen indexes

An index is a sorted lookup list. With one, the database jumps straight to the rows it needs instead of reading the whole table. I added 14, mostly on foreign keys and on the columns the tracking script filters by.

I tested each one before it went in. I ran all 58 reads with and without every candidate index, and two of them made a query slower, so I left those out. The build took 2 minutes with no downtime. It used about 690 MB of extra disk and made each insert a tiny bit more expensive.

Step 2: tuning Postgres did nothing

Every tuning guide says to give Postgres more memory per query and to make random reads cheaper on SSDs. So I tried four setups on the local copy, twice each, after the indexes were in.

The full set made the new visitor count 3× slower. Raising work_mem to 16 MB pushed Postgres into a plan that spilled to disk. Giving it more shared memory alone changed the total by 1.1%, and two runs with no changes at all were already 3.3% apart, so that's noise. Turning off JIT on top of it gave 6.5%, a small win I parked for later. Any of these also needs a database restart, so prod kept its defaults.

Pick a setup to see where it lands:

Step 3: ask once, get only the answer

Some dashboard queries were slow for another reason: the question itself was clumsy. The KPI cards ran one query with seven parts, and each part repeated the same "skip these visitors" filter. The session pages downloaded every single session row, 6.3 MB of it, just to count them in JavaScript.

I moved that work into three SQL functions. Now the app sends one short request and gets back a handful of numbers.

Every function returns the same values as the query it replaced. That held on up to 40 test cases each, and I checked it again on live prod data during the rollout.

Two of these never shipped, and the numbers made both calls for me.

Pre-computed page stats

These made the page tables about 800× faster. The catch is that the stored copy has to be refreshed every 10 minutes, and each refresh costs about 14 seconds of database time. That's 35 to 60 minutes a day. The logs showed people opened those pages 25 times in 2.6 days, which is about 7 seconds of database time a day. On a small 2-CPU server the refresh would cost way more than it saves, so it waits until traffic grows. Play with the numbers:

The view cleanup

The plan had a simple rule for this one: keep it only if the target query gets at least 2× faster. It got 1.16× faster, and three daily report queries got 7 to 10× slower because Postgres lost a parallel plan. So I dropped it.

Rollout: database first, code second

On this project, merging a pull request deploys it. That decided the order. Each SQL function had to exist on prod, and be registered in the API layer, before the code that calls it got merged. Otherwise the endpoint throws an error, or worse, the home dashboard quietly shows 0 visitors.

The indexes went in first, on their own. Later that day, the three functions, both app deploys and one extra index took 14 minutes, one step at a time. After every step I ran the old and new query on live data and compared the answers. Click through it:

What testing caught

Six problems showed up before they could reach prod. Tap a card to see what would have happened and how I fixed it.

What's still open

It's not all done. When nothing is cached in memory, the home dashboard's visitor count can still take 10 to 17 seconds. The next step is to vacuum the page view table so Postgres can answer from the index alone, and to read only page views instead of sessions too. The target is under 1 second, cold or warm.

One small, low-traffic count query also got slower after the change, from 19 ms to 224 ms. At its volume it barely matters, but I want to know why. And the pre-computed stats stay off until traffic makes them worth it.

What I'd tell myself at the start

Read the logs before you guess. I'd have started with the dashboards, and they were under 1% of the load. Measure every index, because two of mine made things slower. Don't trust tuning advice until you've tried it on a copy of your own data. And when merging means deploying, ship the database change before the code that needs it.