Ten thousand queries per second, median (p50) latency under 20 ms, and half the rows in your profiles table contain NULL. That's the exact bottleneck Maya faced in a SQL Portal breakdown of a case by @rauljuncoV (joinenginuity.com). The table had optional display_name, timezone, and bio fields, while several downstream services expected a single non-null value.
At first glance, the fix looks easy: wrap the columns in COALESCE, the SQL function that returns the first non-null argument. Convenient, yes—until high traffic turns it against you.
Why COALESCE on a Hot Path Slows Queries and Splits Logic
Reading a user profile is a hot path—one of the busiest queries in the system. Putting COALESCE there means the database burns CPU repeating the exact same fallback math ten thousand times every second.
-- Option A: defaults computed on every read
SELECT
user_id,
COALESCE(display_name, 'User #' || user_id::text)
AS effective_display_name,
COALESCE(timezone, 'UTC') AS effective_timezone
FROM user_profiles
WHERE user_id = $1;
CPU isn't the only headache. A standard B-tree index on display_name won't accelerate lookups on COALESCE(...)—you need an expression index matching the query character for character. Worse, different teams invent their own fallbacks ('Anonymous', 'User'), so one profile shows different values in the feed, push notifications, and analytics.
Four Options—and Why Precomputing on Write Wins
Adding DEFAULT and NOT NULL (Option C) sounds tidy, yet DEFAULT only covers new rows. Per the PostgreSQL ALTER TABLE docs, a plain SET NOT NULL scans the entire table (unless you do a two-step CHECK ... NOT VALID and VALIDATE CONSTRAINT), risking locks and bloat. Pushing defaults into each service or an API gateway (Option D) is even simpler, yet quickly produces five competing versions of the truth.
| Approach | Where it runs | Indexing | Main risk |
|---|---|---|---|
A. COALESCE in SELECT | At read time | Expression index | CPU load and drifting rules |
B. effective_* columns | At write time / CDC | Standard B-tree | Slower writes, backfill |
C. DEFAULT + NOT NULL | On new inserts | Standard B-tree | Heavy table migration |
| D. Service-side logic | In consumers | None on result | Conflicting defaults |
Maya picked Option B—write-time precomputation: compute the final value once on write and store it in effective_display_name, effective_timezone, and effective_bio. The raw columns stay intact, so you always know whether a user picked UTC themselves or got the system default.
For simple rules that don't touch other tables, PostgreSQL offers STORED generated columns.
CREATE TABLE user_profiles (
user_id bigint PRIMARY KEY,
display_name text,
timezone text,
-- Explicitly specify STORED to persist the value on disk
effective_display_name text GENERATED ALWAYS AS (
COALESCE(display_name, 'User #' || user_id::text)
) STORED,
effective_timezone text GENERATED ALWAYS AS (
COALESCE(timezone, 'UTC')
) STORED
);
CREATE INDEX idx_profiles_eff_name
ON user_profiles (effective_display_name);
Watch out for a PostgreSQL 18/19 detail: omitting STORED creates a VIRTUAL column that recomputes at read time again. Expressions must also be IMMUTABLE with no subqueries. For complex rules (locales, feature flags), let the service or a CDC (Change Data Capture, like Debezium) pipeline populate effective_* so reads stay pure I/O.
The Hidden Cost: Backfill, CDC Lag, and Idempotency
Fast SELECT queries come with write-side costs: slightly higher INSERT/UPDATE latency, extra disk space, and more WAL volume. Changing a GENERATED ... STORED formula via SET EXPRESSION AS rewrites the table and wipes planner stats, so run ANALYZE afterward.
Existing rows won't fill themselves either—you need a batched background backfill that avoids locking live traffic:
-- Worker loops in chunks until no NULLs remain
WITH batch AS (
SELECT user_id FROM user_profiles
WHERE effective_display_name IS NULL
LIMIT 5000 FOR UPDATE SKIP LOCKED
)
UPDATE user_profiles p
SET effective_display_name =
COALESCE(p.display_name, 'User #' || p.user_id::text),
effective_timezone = COALESCE(p.timezone, 'UTC')
FROM batch b WHERE p.user_id = b.user_id;
FOR UPDATE SKIP LOCKED keeps the worker out of the way of live queries, leaving the hot path as a plain SELECT on effective_*. With CDC, you also take on sync lag and must make consumers idempotent so replayed events after a crash don't corrupt state or lose races. For a service doing ten queries a second, keep COALESCE; move the math to write time once reads hit thousands per second.

