One Badge, Fifteen Queries: When a Dashboard Becomes a Reliability Problem

There is a small number in the sidebar of our admin console. It says how many incidents are open.

To show that number, the backend ran fifteen queries.

That sounds like a harmless waste until you add two facts. Every saved session told each open console to refresh, and all of those refreshes shared one pool of database connections with login and incoming data. So one busy customer with a few tabs open could make unrelated work wait behind a badge.

This post is about the business problem underneath that, the three changes that fixed most of it, and the part we did not fix. I have left out the product, the customers and our real traffic. All the benchmark numbers below come from seeded test data on a laptop. The production section uses ratios only, because real traffic is not mine to publish.

In short#

• The problem: one stats endpoint served every screen, recalculated from raw history on each read, and had no limit on how many reads ran at once.
• The fix: each screen now asks for only what it shows, the expensive facts are stored when data is written, and stats reads wait in capped queues.
• The result: at a million sessions, the heaviest usage screen went from 32.6 s to 3.6 s, and an unrelated query's worst wait under load went from about 30 s to about half a second.
• In production: in the hours after the deploy, the typical stats call was about 15 times faster, and pages we never set out to change got faster too, because they stopped waiting for connections.
• The miss: it is still not perfect. The heaviest screens are over our target at a million rows, the slowest stats calls in production still miss the ceiling, and the queries need more work.

The business problem#

An admin console is the part of a B2B product the buyer looks at. It answers the questions they are paying to have answered: what is happening, how much of it, and what needs my attention. Three things were wrong, and only one of them was "this page is slow".

The product got worse as customers used it more. Each screen recalculated its numbers from a customer's whole history on every read. The customer who adopted the product most had the most history, so they got the slowest console. That is the wrong way round. Your best customer should not have your worst experience.

One customer could hurt everyone on the same server. Stats reads and everything else drew from the same pool of connections, and nothing limited how many stats reads ran at once. When the pool filled, a request that had nothing to do with stats still had to wait for a connection. A dashboard is the thing you can afford to have slow. Login and incoming data are not.

There was a ceiling on how big a customer we could serve. We plan for a million rows per table in one organisation. At that size, with ten million messages, the usage screen took 32 seconds. A customer that size could not really use the screen, and no sales conversation fixes that.

That gave us the brief:

How a reasonable design got here#

Nobody wrote a bad endpoint. The stats endpoint began as the data for one page, so returning the whole page was correct. Then other screens needed a number from it, and calling it was the obvious way to get one. The sidebar needed a count, so it called it too.

Later the console went live. A saved session sends an event, and open tabs refresh what it touches. A tab groups events into one refresh a second, which was a careful decision. Each step made sense alone. The cost was the product of all of them:

events  x  open tabs  x  15 queries per refresh

The traces showed something I nearly missed. During the slow hours, an unrelated search page looked terrible, so the natural move was to tune its query. But its SQL was fine. The time was going on waiting for a connection, and most of the extra calls were the badge.

So tuning the slow-looking query would have been wasted work. Before you optimise a query, split its time into waiting and running. A query that is waiting is a symptom of something else.

The options we did not take#

Rewrite the slow query. I tried this first on the worst read, the incident count, on a seeded million rows. A set-based rewrite took it from 72 s to 28 s with the same result. That is a good rewrite, and it is still 56 times over the target. Counting from raw history at read time cannot reach 500 ms at this size, however the SQL is written.

Fix only the frontend. Refreshing less often lowers the number of calls. It does nothing for the 32 seconds one call takes, or for a page that legitimately needs the data.

Add a cache. This was ruled out from the start. A cache would hide a slow read until the next miss, and this data changes every time a session is saved, so misses would be common. The first admin after each change would still pay the whole cost.

Buy a bigger database. It would buy time. The cost per read would still grow with each customer's history, so we would be paying more to arrive at the same problem later.

That left three changes, and each one goes after a different multiplier.

Change 1: ask only for what the screen shows#

The request now names the blocks it needs. The sidebar names one.

// The sidebar badge
POST /stats
{ "include": ["incidents"] }
 
// The response has that block and nothing else
{ "incidents": { "open": 4, "resolved": 12 } }

The awkward part of a change like this is the deploy. A tab left open across a release still sends the old request, so the rules had to cover it.

The 400 matters more than it looks. If unknown names were ignored, a typo in a frontend hook would show up as an empty card, and nobody would connect it to the request.

While reading who used what, I also found five fields that no screen read at all, including a count across all of history. Those reads were deleted.

One more read moved out. A card that needed data from an outside vendor could hold a request for up to five seconds, so it now has its own endpoint, and the other cards no longer wait for it.

Change 2: do the work when the data is written#

The heavy reads scanned every message of every session to work out facts that hardly ever change after a session is saved. So the backend now works them out once, at save time, and stores them.

Each session save, outcome change and pricing rewrite refreshes the facts. Reads use the stored values. A row with no facts yet falls back to the old calculation at read time, so the screen stays correct while old rows catch up.

Moving work to write time has a bill, and it comes due in three places:

• Every write path must keep the facts fresh. Miss one, and a screen is quietly wrong. This is the real risk of the design, so those paths have integration tests.
• The backfill makes the first boot longer. Existing rows need their facts filled in by the migration.
• Rolling deploys leave a gap. A container still on the old build can save a session after the backfill has passed that row, leaving stale facts. Sessions touched during the rollout need one refresh afterwards.

It also does not always win. With one message per session, the stored facts save almost nothing, and one screen came out 14% slower than before on that data shape, 454 ms against 398 ms. I would rather print that here than let the big numbers imply otherwise.

Change 3: cap the reads, so one feature cannot take the pool#

The first two changes make each read cheaper. This one limits the damage when many arrive at once. The tool is a Go buffered channel used as a semaphore. A simplified version looks like this:

// limited runs read once a slot is free; a request that ends while waiting never reads.
func limited(ctx context.Context, slots chan struct{}, read func() error) error {
	select {
	case slots <- struct{}{}:
		defer func() { <-slots }()
	case <-ctx.Done():
		return ctx.Err()
	}
	return read()
}

The ctx.Done() branch is the important part. If an admin closes the tab, the request stops queueing and never touches the database.

There are two queues, not one, each allowing three reads at once per container. Heavy usage reads go in one, and quick block reads go in the other. With a single queue, the sidebar would sit behind a usage read that takes seconds. With two, stats can hold six connections at most, and the rest of the pool stays free for everything else.

I tested no limit, 3, 4 and 6. Three finished soonest. That surprised me until I thought about what the reads compete for: they are CPU-heavy, so past a point more of them at once only makes each one slower.

The same thing happened with parallelism inside a single request. Running the two usage reads side by side was 28% faster for one request, and 64% slower with four requests at once, while holding twice the connections. A benchmark of one request would have told me to ship it. They run one after the other.

Here is 12 heavy usage requests at once on the densest data:

The limit made the heavy requests finish sooner too. It feels backwards that making requests wait gets them done faster. Before, every heavy read fought for the same CPU. Now the database works through a short line.

The strange one: a read that spent longer compiling than running#

After all of that, some reads were still slow in a way that made no sense. The query plan showed a scan of a small table with hundreds of milliseconds of start time.

It was Postgres JIT, which compiles a query to machine code just before running it. That pays off on long queries. Postgres decides whether to do it from the planner's cost estimate, and the default threshold is 100,000. The real work does not enter into it.

Our fallback for rows with no stored facts made the planner expect a huge amount of work. That branch almost never runs, but the estimate counted it. So Postgres spent hundreds of milliseconds compiling a read that needed a couple of milliseconds.

BEGIN;
SET LOCAL jit = off;  -- ends with this transaction
-- the usage reads
COMMIT;

SET LOCAL matters here. A plain SET belongs to the session, and a pooled connection's session outlives your request, so it can leak to whoever gets that connection next. Only these reads use it, and JIT stays on for the rest of the database. JIT changes how a query runs and never what it returns.

A reviewer asked whether we needed it at all, which was a fair question, because the code comment said how it was scoped and not why it existed. So I removed it and measured. The usage screen got far slower for larger accounts, so the setting went back with a better comment. Turning JIT off for the whole database gained almost nothing on top of that, so we left the database alone.

To be fair to the old code, I timed it with JIT off as well. None of the speed-up in the tables here comes from JIT.

Proving the numbers did not move#

Speed was only half the rule. Every number on every screen had to match the old build.

I captured the old and new builds against the same database, across every request shape, and compared them value by value. The step that made this trustworthy was running the old build against itself first. Some fields move on their own, like anything based on the current time, and without that run they look like bugs. Fields that no screen reads were allowed to change or go away.

That comparison found zero differences across thousands of values. One honest gap: it ran before the last structural change, which replaced the parallel reads with the capped queues. That change alters when reads run, not what they ask for, but I have not repeated the comparison on the final code, so it is still an open item.

Results, and what we still miss#

Seeded data at a million sessions, Postgres in Docker on a laptop, median of repeated requests through the real handler, one at a time. Dense means ten million messages. Sparse means one million.

The sidebar meets the target. The usage screen on sparse data and both heavy screens on dense data do not. The target was 500 ms and the ceiling was 800 ms, and 3.6 s is neither. Getting there needs summaries added up ahead of time, by day for example, and that is a schema design of its own. I would rather ship a measured improvement and name the gap than call it done.

These are also laptop numbers. They compare two builds fairly, but they are not a prediction of production latency.

What did change, whatever the absolute numbers turn out to be, is how the system fails. Before, too many stats requests slowed everything. Now they queue, the wait is logged, and after 60 seconds the request deadline ends them. By my arithmetic, not a test, that takes about 19 heavy requests at once per container on the densest data. Only stats gets slow, and the rest of the pool stays free.

What production showed#

Server traces from before and after the deploy, measured on the server, so my own network is not in them:

The drop began about ten minutes after the merge, and it held while the number of stats calls went up.

The last three rows are the ones I care about most. Those requests were never slow on their own. They were queueing for a connection behind stats, and they stopped queueing. That was the business problem from the top of this post: one feature could no longer make everyone else wait.

Three cautions about this table. The traces I could read cover only about three hours after the deploy. Other, earlier changes may have gone out in the same release, so the timing is strong evidence and not proof. And the stats calls are not like for like, because callers now ask for less on purpose.

It is still not perfect#

This was a big improvement, and it is not finished. I would not call the job done:

• The slowest stats calls still miss the ceiling. The slowest 5% in production are over 800 ms, even though the typical call is well under the target.
• The heaviest screen is still the slowest thing I can click. In a live check, the sidebar cost almost nothing beyond the network. The big overview screen cost far more, and that was on data much smaller than a million rows.
• The seeded numbers still fail at full size. Seconds are not 500 ms, however good the ratio looks.
• Other queries are now easier to see. With the waiting gone, what remains is real query time. One search still misses the target on a typical call, and it was hidden behind the queueing before.

The next round is query work: read the plans for what is left, add the summaries the heavy screens need, test again at a million rows, and repeat the comparison against the old numbers. The first round stopped the damage. The second has to make the queries properly fast.

What I took from this#

• Multiply your costs. Events, tabs and queries were each fine alone. Look for the product, because the product is what hits the database.
• Split waiting from running. The slowest-looking page was not the problem.
• Ask who pays for a read. If the cost grows with history, a rewrite only delays the day it stops working. Moving the work to write time changes the curve, and then you own keeping it correct.
• Test concurrency, not a single request. Parallel reads looked like a win until four requests ran together.
• A limit protects the neighbours. Its job is to decide which work is allowed to get slow, and that decision is a business one. Stats can wait. Login cannot.

The badge still says the same number. It can no longer take the rest of the app down with it.