A Function in Your Query Runs Once Per Row

It’s convenient to put logic where the data is. Need every returned row transformed — decrypted, formatted, computed? Wrap it in a function and call it right there in the query. The database does the work, the application gets clean values, done. Then you watch that query under real load and discover the convenience had a price tag you didn’t read: the function runs once per row, on the database’s CPU, inline with every other query the database is trying to serve. Where computation lives isn’t a style choice. It’s a throughput decision.

Per row is a multiplier, and multipliers are how you get surprised

A function that takes a few milliseconds sounds harmless. Call it on a single value and it is. Call it in a query that returns a thousand rows and you’ve bought a thousand executions. Put that query on a hot read path and multiply again by every request. The per-call cost you shrugged off is now the dominant term, because you multiplied it by row count and by request rate. I’ve seen a modest-looking transform on a read path make a query roughly an order of magnitude slower and cut a node’s sustainable throughput by a similar factor — purely from being called once per row, over and over.

A few milliseconds per row is free until you remember how many rows there are and how often you ask. Then it’s the whole bill.

The database is the worst place to burn CPU

Here’s what makes server-side computation especially dangerous: it runs on the resource you can least afford to spend, and can least easily add. Your application tier is usually easy to scale out — add instances, spread the load, the work parallelizes across cheap boxes. The database is not that. It’s a shared chokepoint that every request funnels through, and scaling it is a genuine project, not a slider. So every CPU cycle a per-row function eats is a cycle the database isn’t spending on its actual job — reading, writing, and coordinating — and you can’t just bolt on more database to get it back.

That’s the asymmetry that should drive the decision. Compute in the app tier competes with a resource you can grow. Compute in the database competes with a resource you mostly can’t. Same work, very different consequence depending on where it runs.

Where should the work live?

Once you see it as a placement decision, the options line up by cost:

• In the app tier. Pull the raw rows, do the transform on the application side where you can scale horizontally and where a slow transform slows one request, not the whole database. Usually the right home for anything heavy or per-row.
• Precomputed / materialized. If the transformed value is read far more than it’s written, compute it once at write time (or in a materialized view) and just read it back. You pay once per change instead of once per read — a great trade when reads dominate.
• In the database, deliberately. Sometimes server-side really is right: the work is cheap, it drastically cuts the data shipped over the wire, or correctness demands it live next to the data. Fine — just choose it knowing it runs per row on the scarce resource, not by default because it was convenient to type.

The wrong version of this is picking “in the database” because it made the query tidy, then discovering the throughput cost in production.

And if it must live server-side, don’t make it worse

If you do land on server-side computation, the per-row framing tells you where the follow-up wins are: anything constant that the function rebuilds on every single call is that per-row cost multiplied by pure waste. Hoisting that constant setup out of the hot path is often a massive, algorithm-free speedup — which is a whole lesson of its own in your hot path is rebuilding the same thing every call. The two ideas stack: first decide whether the work belongs in the database at all, then, if it does, make sure it isn’t redoing constant work on every row.

Ask “how many times will this run?” before “where’s it easiest to put?”

The habit this leaves me with is a single question I ask before embedding any logic in a query: how many times will this execute — per row, per query, per request — and on whose CPU? If the answer is “once per row, on the database, on a hot path,” that’s a decision worth making on purpose, weighed against precomputing it or moving it to a tier I can actually scale. It’s the placement-of-computation cousin of deciding where encryption belongs in your stack: the question isn’t just whether to do the work, it’s which layer pays for it. Convenient and correct are different axes, and the database is the one place where “convenient” quietly costs the most. If you’ve moved a per-row function off a database and watched the throughput come back, I’d love to hear about it.