Postgres aggregation: fixing an 18-second admin dashboard

If a dashboard card loads rows into your app just to add them up, the database is doing the cheap part and Node is doing the expensive part. Postgres aggregation flips that around: the database computes the totals and sends back a handful of numbers. This post covers how I moved the revenue cards on the Buffdudes admin dashboard from JavaScript totals to SQL, how I proved the response did not change (35 of 35 comparisons identical, key order included), and a related fix where a small in-process cache was the better answer.

What the dashboard was doing

The Buffdudes admin portal has a dashboard with revenue cards. Five of them each did the same thing: load every transaction in the selected date range, then total them in JavaScript.

For a wide range that meant about 43k rows with 47 columns each, pulled out of Postgres, sent over the network, turned into objects, and then looped over to produce a few sums. A total needs a few of those columns at most.

The portal fired all five requests at once. So for one page load, the database read the same rows five times, the API built five large arrays in memory, and five loops ran over them. On staging, the dashboard took 14 to 18 seconds. Worse, the load held up even the plain count cards, which do not depend on revenue at all.

Why totalling rows in Node.js is slow

None of the steps is slow alone. Together they add up:

• Transfer. Every row crosses the wire, including columns nobody uses.
• Hydration. The ORM builds an entity object per row, with every field converted.
• Memory. Five copies of a 43k-row result live in the Node process at the same time.
• The event loop. Node runs JavaScript on one thread. While it loops over a large array, other requests on that process wait, which is one way a slow card can stall unrelated ones.

Postgres, by contrast, can sum a column while it scans and never builds anything but the result. That is the job aggregates exist for.

Postgres aggregation: moving the totals into SQL

The fix was to let Postgres compute the totals, so only totals come back. My default is one query per card, or one query for several cards when they read the same rows. FILTER is the clause that makes this tidy, because it lets one pass compute totals for different subsets. A simplified version:

SELECT count(*)                                            AS transaction_count,
       coalesce(sum(amount), 0)                            AS gross,
       coalesce(sum(amount) FILTER (WHERE status = 'refunded'), 0) AS refunded
FROM transactions
WHERE created_at >= $1
  AND created_at <  $2;

The result is one row with three numbers. The aggregate expression syntax covers FILTER, and the aggregate functions reference lists what else is available. If a card needs a breakdown, say revenue per day or per plan, add a GROUP BY to the same query and the database still sends back one row per group instead of one per transaction. The modern-sql.com site has a clear page on the FILTER clause and how it gives an aggregate its own where condition.

Two details I watch for when making this change:

• Empty ranges. sum() over no rows returns NULL, where a JavaScript loop starting at zero returns 0. coalesce(..., 0) keeps the old behaviour.
• Number types. The pg driver returns bigint and numeric values as strings by default, because they can exceed what a JavaScript number holds exactly. count(*) is a bigint. If the old endpoint returned numbers, convert explicitly before sending, or the response changes shape.

That second point is exactly the kind of difference that slips through a quick review, which is why I did not rely on review alone.

Proving the response did not change

The dashboard's frontend expected a specific JSON shape, and the old JavaScript defined the numbers. The new version had to return the same thing. "Looks right" on one date range was not enough.

So I diffed the old and new output. Both implementations ran against two local databases over seven date ranges, chosen to hit the edges:

• the full range
• all time
• the last 30 days
• a single day
• one calendar month
• a range starting mid-month
• an empty range

All 35 of 35 compared responses were identical, key order included. Key order sounds pedantic, but it is a cheap signal that the new code builds the response the same way as the old, and it catches accidental renames or reshaping.

This kind of harness is the fastest way I know to review a rewrite, whoever wrote it. I go into the method in how to verify a refactor by diffing old and new output.

I don't have a new end-to-end dashboard time to quote, so I won't put a number on it here. What changed is the work per request: each card now gets totals instead of tens of thousands of rows.

When the answer is a cache instead

A related fix on the same backend went the other way. A "longest streak" suggestion scanned every logged day of every recently active user, on every call. On production that took 316 ms each time, for a list that is the same for everyone.

A faster query was one option. But when the result is identical for every caller and changes slowly, the better question is why compute it per request at all. It is now cached per process for 10 minutes. The pattern is small:

const TTL_MS = 10 * 60 * 1000;
let cached: { at: number; value: StreakSuggestion[] } | undefined;

export async function getLongestStreakSuggestions(): Promise<StreakSuggestion[]> {
  if (cached && Date.now() - cached.at < TTL_MS) return cached.value;
  const value = await loadLongestStreakSuggestions();
  cached = { at: Date.now(), value };
  return value;
}

Each API process computes the list at most once every 10 minutes and serves it from memory otherwise. With several processes, each keeps its own copy, so for a while two servers might return slightly different lists. For a suggestion list that is fine. For anything that must agree across servers, or that is expensive enough that even one computation per process hurts, a shared cache like Redis is the next step. I used that approach for leaderboards in scaling Buffdudes to 60k users.

Should you aggregate in the database or in application code?

Aggregate in the database whenever the answer is smaller than the data it comes from: sums, counts, averages and grouped totals belong in SQL. Do it in application code only when the logic cannot be expressed in SQL cleanly, or when the rows are already loaded for another reason. If the same aggregate is requested often and changes slowly, cache the result as well.

Where I'd start

1. Find endpoints that load rows only to total them. Logging response row counts next to response sizes makes them stand out.
2. Rewrite the totals as SQL, with coalesce for empty ranges and explicit number conversion.
3. Diff old and new output across edge-case date ranges before shipping.
4. Cache results that are the same for everyone, and check the queries left behind have the indexes they need; my Postgres index audit process covers that part.

If your admin dashboard has started to crawl, this is the kind of backend work I help with through my services.