Fifteen numbers on one admin page, one round trip, and a row count Postgres never counts

Munchable has an operator panel with an overview page on it. Accounts, premium accounts, new accounts this week, open tickets, tickets awaiting a reply, closed tickets, tickets this week, products in the catalogue, a couple of filtered catalogue states, active ingredient entries, the curation backlog, rewards earned, rewards applied. Fifteen numbers, one screen, and the only person who ever loads it is me.

It is the most boring page in the product and it used to take unrelated requests down with it.

Fifteen counts is fifteen connections

The first version was the obvious one. Fifteen count(*) queries, each in its own helper, all handed to Promise.all because they are independent and waiting for them in series would be silly.

The production pool is three connections. Dev is five. That is deliberate: Supabase's connection budget on the plan we are on is small, and a serverless function that can scale to a hundred instances must not be allowed to ask for a hundred connections each. So fifteen parallel queries do not run in parallel. They queue three deep and drain in five waves.

That part was merely slow. The part that hurt was what happened when I closed the tab mid-render. The React Server Component render was abandoned, the fifteen promises were not, and some of them were still holding connections that nothing was going to await. They sat there until the statement timeout killed them. In the meantime, a real request to a real endpoint, something a phone in a shop was waiting on, could not get a connection either.

An admin page nobody is looking at should not be able to do that.

One statement

The whole thing is now a single select of scalar subqueries:

select
  (select count(*) from user_consent)::int                          as "accounts",
  (select count(*) from entitlements where plan = 'premium')::int    as "premium",
  (select count(*) from user_consent
     where accepted_at >= now() - interval '7 days')::int            as "newAccounts7",
  (select count(*) from support_tickets where status = 'open')::int  as "ticketsOpen",
  ...

One connection, one round trip, one thing to cancel. Postgres plans the subqueries independently anyway, so the work is the same work; what changes is that there is exactly one unit of it to own.

Two small things fell out of writing it this way.

The date windows are computed in SQL as now() - interval '7 days' rather than bound as a JavaScript Date. That is not a style preference. postgres.js cannot serialise a Date out of a raw template in this position, and discovering that at runtime in a template with fifteen subqueries in it is not a good afternoon. Computing the window server-side also means the number means "the last seven days as the database understands now", which is the same clock the rows were written with.

The result unwrapping has a wart:

const rows = (Array.isArray(result) ? result : (result as { rows?: unknown[] }).rows) ?? [];

postgres.js returns the rows array itself. Other drivers wrap it in .rows. Drizzle's db.execute passes whichever one through. Rather than pin the shape to the driver we happen to use today, the reader accepts both.

The count we do not run

One of those fifteen is not a count.

catalog.products is around a quarter of a million rows and grows every week. Postgres has no row count to look up: count(*) on it is a sequential scan of the whole table, every time, because MVCC means visibility is per transaction and there is no single true number sitting in a header somewhere. On a small instance that is hundreds of milliseconds of pure I/O so that a number ending in three zeroes can be printed on a dashboard.

The planner already keeps an estimate:

(select greatest(c.reltuples, 0)::bigint
   from pg_class c join pg_namespace n on n.oid = c.relnamespace
   where n.nspname = 'catalog' and c.relname = 'products')::int as "products"

reltuples is read in microseconds. It is maintained by ANALYZE and by autovacuum, so it lags, and for this screen it lags by an amount nobody can perceive. The greatest(..., 0) is not defensive padding: Postgres stores -1 in reltuples for a table that has never been analysed, and a dashboard reading "-1 products" is worse than one reading "0".

The interface says so out loud:

/** Planner estimate, not an exact count. See rule 2 above. */
products: number;

The two filtered catalogue counts next to it stay exact count(*)s, because they are small, indexed, and the whole point of them is that the exact number is the thing I act on. An estimate is fine for "how big is the catalogue". It is not fine for "how many rows are waiting for me".

Cached for thirty seconds, and never allowed to hang

Two more guards, both aimed at the same failure.

The result is cached for 30 seconds in a module-level global:

declare global {
  var __admin_overview_cache: { at: number; stats: OverviewStats } | undefined;
}

A global rather than a module variable so it survives Next's hot reload in development. Editing a component on the overview page used to throw the cache away and re-run the statement on every keystroke-triggered rebuild.

And the whole thing races a timer:

const stats = await Promise.race([queryStats(), timeout]);

Eight seconds, which is longer than any sane run of this query and shorter than a browser's patience. On timeout the promise rejects instead of hanging, so the page's Suspense boundary gets to render an error the operator can read. A hang renders nothing at all, forever, and then the browser gives up and shows its own message about the site being unavailable, which is a lie about a dashboard that is merely slow.

What actually changed

The numbers on the page are identical, except the product count, which is now openly an estimate. The page loads in one round trip instead of five waves. Closing the tab early strands nothing.

The pattern generalises badly, which is worth saying. Fifteen scalar subqueries in one statement is right here because the page is one person's dashboard and the alternative was starving a pool that real users share. On a page that many people load, the answer would have been a materialised view or a counts table maintained by triggers. Pick the cheap fix that matches how often the thing runs.

The public side of Munchable reads from the same database but shares none of this: the seven conditions the app covers and the 373 ingredient answer pages are generated at build time, which is its own set of trade-offs and its own set of bugs. The recipes are too. None of them touch a connection at request time, which is the only reason the pool is three and not thirty.