My dashboard had a useMemo doing arithmetic it had no business doing.
It was a Pokémon TCG Pocket collection tracker, but that part doesn't matter. What matters is that the home page needed to show three things: overall completion, completion per set, and which set you were closest to finishing. The way I'd built it, the browser was fetching every card and every owned record, then grinding through the math on each render to figure all of that out.
It worked. It also got slower and harder to read every time the data grew or I added a metric. So I moved the aggregation out of React and into Postgres, and the surprising result was that my frontend got boring. Fewer hooks, less state, almost nothing left to break.
That's the whole argument of this post: aggregation belongs in the database, and when you put it there, the React code that's left over is the kind of boring you actually want in the layer your users touch.
What "fetching everything into React" actually looks like
Here's the shape of the original dashboard. Load all the cards, load the user's owned rows, then derive everything on the client.
const [cards, setCards] = useState<Card[]>([]);
const [owned, setOwned] = useState<OwnedCard[]>([]);
useEffect(() => {
(async () => {
const { data: allCards } = await supabase.from("cards").select("*");
const { data: ownedRows } = await supabase
.from("user_cards")
.select("card_id");
setCards(allCards ?? []);
setOwned(ownedRows ?? []);
})();
}, []);
const ownedIds = useMemo(() => new Set(owned.map((o) => o.card_id)), [owned]);
const perSet = useMemo(() => {
const groups: Record<string, { total: number; have: number }> = {};
for (const card of cards) {
const g = (groups[card.set_id] ??= { total: 0, have: 0 });
g.total += 1;
if (ownedIds.has(card.id)) g.have += 1;
}
return groups;
}, [cards, ownedIds]);
const overall = useMemo(() => {
const total = cards.length;
const have = cards.filter((c) => ownedIds.has(c.id)).length;
return total ? have / total : 0;
}, [cards, ownedIds]);
It's not bad code. But look at what it's costing:
- The payload grows with the collection. I'm shipping every card row to the browser just to count them. The user never sees most of that data. It exists only to be reduced to a few numbers.
- The logic can't be tested without mounting a component. The "closest set to finishing" rule lives inside hooks, tangled up with fetching and state.
-
Every new metric is more client state. Want a "rarest card you're missing" stat? That's another loop, another
useMemo, another thing that re-runs on render.
I was using React as a database query engine. It's not one.
Why this is the wrong layer
Counting, grouping, and computing ratios across rows is a set operation. It's the thing relational databases were built to do, indexed and compiled, sitting right next to the data.
When I do it in React instead, I pay for the same work twice. Once to serialize every row and send it over the wire, and again to recompute the aggregates in JavaScript on the client. The database already knew the answer was "you own 41 of 86 cards in this set." I just declined to ask it that question and asked for all 86 rows instead.
This isn't really a performance micro-optimization. It's a question of which layer owns which job. Aggregation is a database job. I'd put it in the wrong place.
Moving it into an RPC function
Supabase lets you call Postgres functions directly with rpc(), so I wrote one that returns exactly the rows the dashboard needs: one row per set, with the counts already computed.
create or replace function public.get_home_set_completion_rows()
returns table (
set_id text,
set_name text,
set_slug text,
release_date date,
image_url text,
data_version text,
total_cards integer,
owned_cards integer,
missing_cards integer
)
language sql
stable
security invoker
set search_path = public
as $$
select
sets.id as set_id,
sets.name as set_name,
sets.slug as set_slug,
sets.release_date,
sets.image_url,
sets.data_version,
count(cards.id)::integer as total_cards,
count(user_cards.card_id)::integer as owned_cards,
(count(cards.id) - count(user_cards.card_id))::integer as missing_cards
from public.sets
left join public.cards
on cards.set_id = sets.id
left join public.user_cards
on user_cards.card_id = cards.id
and user_cards.user_id = auth.uid()
group by
sets.id,
sets.name,
sets.slug,
sets.release_date,
sets.image_url,
sets.data_version
order by
sets.release_date desc nulls last,
sets.id;
$$;
The left join against user_cards filtered by auth.uid() is doing the per-user work right inside the query. Sets the user has zero cards from still show up with owned = 0, which is what I want.
And the call site collapses to this:
const { data } = await supabase.rpc("get_home_set_completion_rows");
That's it. No fetching everything, no client-side grouping, no Set of owned IDs. The component receives compact, purpose-built rows and maps over them. Overall completion is a sum of two columns. "Closest to finishing" is a sort. Both are now trivial because the hard part already happened in the database.
The React file went from a pile of hooks to a Server Component that calls one function and renders the result. That's the "my frontend got boring" moment, and deleting all that state felt great.
When a view beats a function (and vice versa)
I reached for an RPC function here because of that auth.uid() filter, since the result depends on who's asking. But it's worth knowing when a plain view is the better tool:
- Use a view when the aggregation is stable, reusable, and doesn't need parameters. A view reads like a table, composes into other queries, and is the simplest thing that works. Card counts per set that aren't user-specific would be a great view.
-
Use a function when you need arguments (a user ID, a set filter, a scope toggle) or multi-step logic that a single
selectcan't express cleanly.
One footgun worth flagging: both views and functions interact with row-level security, and security definer versus security invoker decides whose permissions the code runs under. A security definer function bypasses the caller's RLS, which is occasionally what you want and frequently a way to leak other users' data by accident. If you're using RLS to protect per-user rows, and you should be, make sure your function respects it rather than quietly stepping around it.
The objections worth taking seriously.
A few reasonable complaints, because this trade isn't free.
"Now my logic is split across two languages and harder to grep." True. I moved complexity, I didn't delete it. The aggregation now lives in SQL instead of TypeScript, so there's a context switch to read the whole story. My argument is just that it lands in the right place, with the database owning database work, and that the SQL version is shorter and more declarative than the loops it replaced.
"SQL is harder to test." I'd push back on this one. The function is deterministic: give it fixture rows and it produces the same counts every time, with no component to mount. And separating it out made the logic that correctly stayed in TypeScript easier to test too. My pack-recommendation engine, which sums pull probabilities for missing cards and ranks packs by expected value, is real application logic, not aggregation, so it belongs in app code. Once it wasn't tangled up with fetching and dashboard math, I could unit-test it on its own. The point isn't "move everything into Postgres." It's "move the aggregation, keep the decisions."
"This is just more infrastructure to manage." Fair, but it's version-controlled SQL living next to the rest of the schema, not a magic black box. It migrates like everything else.
The rule of thumb
If you take one thing from this post, take this:
Push aggregation and filtering down to the database. Keep decisions and presentation in the app.
Or, more bluntly: if React is looping to count things, that loop probably wants to be a GROUP BY.
The frontend got boring, with smaller payloads, fewer hooks, and less state to corrupt, and boring is exactly what you want from the layer your users actually touch. The interesting, opinionated, easy-to-get-wrong logic moved to where it could be expressed simply and tested in isolation.
The genuinely contested part is where you draw the line. I keep ranking and business decisions in app code and push counting and filtering into the database, but I know people who'd move more in either direction. Where do you draw it?
(Full code, if you want to see the whole thing wired together: mwiginton/pocketdex-tracker. Or try the live app: pocketdex-tracker.vercel.app.)
