COALESCE at Read Time vs Write-Time Materialization: Speeding Up a Hot SQL PathNote · digest for November 19
COALESCE at Read Time vs Write-Time Materialization: Speeding Up a Hot SQL Path
Note3 sections≈ 4 min

COALESCE at Read Time vs Write-Time Materialization: Speeding Up a Hot SQL Path

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.

ApproachWhere it runsIndexingMain risk
A. COALESCE in SELECTAt read timeExpression indexCPU load and drifting rules
B. effective_* columnsAt write time / CDCStandard B-treeSlower writes, backfill
C. DEFAULT + NOT NULLOn new insertsStandard B-treeHeavy table migration
D. Service-side logicIn consumersNone on resultConflicting 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.

Want a deep diveVotes help us choose what to break down in detail. This material has 0 votes.
Sources · 4