COALESCE при чтении или материализация при записи: как ускорить горячий путь в SQLЗаметка · дайджест за 19 ноября
COALESCE при чтении или материализация при записи: как ускорить горячий путь в SQL
Заметка3 раздела≈ 4 мин

COALESCE при чтении или материализация при записи: как ускорить горячий путь в SQL

Десять тысяч запросов в секунду, ответ быстрее 20 мс по медиане (p50) — и при этом в половине строк таблицы профилей лежит NULL. В такую задачу упёрлась разработчик Майя из разбора SQL Portal по кейсу @rauljuncoV (joinenginuity.com). В таблице были необязательные display_name, timezone и bio, а несколько сервисов ждали единое непустое значение без NULL.

На первый взгляд всё просто: оберни поля в COALESCE — эта функция SQL возвращает первый непустой аргумент. Удобно. Но под высокой нагрузкой такой ход быстро выходит боком.

Почему COALESCE на горячем пути тормозит и ломает логику

Чтение профиля — горячий путь (hot path), самый частый участок системы. Если там крутится COALESCE, база десять тысяч раз в секунду жжёт CPU на одни и те же расчёты.

-- Вариант A: дефолты считаются на каждом чтении
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;

Беда не только в процессоре. Обычный B-tree индекс по display_name не ускоряет поиск по COALESCE(...): нужен индекс по выражению, совпадающий с запросом символ в символ. Хуже другое — разные команды подставляют свои дефолты ('Anonymous', 'User'), и один профиль выглядит по-разному в ленте, пушах и аналитике.

Четыре варианта и почему побеждает запись заранее

Повесить DEFAULT и накатить NOT NULL (вариант C) звучит чисто, но DEFAULT чинит лишь новые строки. По доке ALTER TABLE в PostgreSQL, обычное SET NOT NULL сканирует таблицу целиком (без двухшагового CHECK ... NOT VALID и VALIDATE CONSTRAINT), грозя блокировками и раздуванием диска. Отдать дефолты самим сервисам или API Gateway (вариант D) ещё проще, но тогда быстро появляются пять разных версий правды.

ПодходГде считаетсяИндексыГлавный риск
A. COALESCE в SELECTПри чтенииПо выражениюCPU и разнобой правил
B. Колонки effective_*При записи / CDCОбычный B-treeСложнее запись, backfill
C. DEFAULT + NOT NULLПри вставке новыхОбычный B-treeТяжёлая миграция
D. Логика у сервисовУ потребителяНет по итогамРазные версии правды

Майя выбрала вариант B — материализацию при записи (write-time precomputation): считать итог один раз при записи и хранить в effective_display_name, effective_timezone и effective_bio. Сырые поля остаются — всегда ясно, ввёл ли юзер UTC сам или его подставила система.

Для простых правил без чужих таблиц в PostgreSQL есть генерируемые столбцы STORED.

CREATE TABLE user_profiles (
    user_id      bigint PRIMARY KEY,
    display_name text,
    timezone     text,
    -- Явно пишем STORED, чтобы хранить итог на диске
    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);

Нюанс PostgreSQL 18/19: без STORED создаётся VIRTUAL-колонка, которая снова считается при чтении. Плюс формула обязана быть IMMUTABLE и без подзапросов. Для сложной логики (локали, фичефлаги) effective_* пишет сам сервис или CDC (Change Data Capture — потоковое отслеживание изменений вроде Debezium). Тогда чтение сводится к чистому I/O.

Скрытая цена: backfill, лаг CDC и идемпотентность

За быстрый SELECT платишь на записи: чуть растёт задержка INSERT/UPDATE, объём диска и WAL. Смена формулы у GENERATED ... STORED через SET EXPRESSION AS переписывает таблицу и сбрасывает статистику — потом нужен ANALYZE.

Старые строки тоже не заполнятся сами: нужен backfill — фоновое дозаполнение батчами без блокировок.

-- Воркер крутит запрос порциями, пока не кончатся NULL
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 не даёт воркеру мешать живым запросам, а на горячем пути остаётся простой SELECT по effective_*. При CDC добавляется лаг синхронизации, а обработчик обязан быть идемпотентным — повтор события после сбоя не должен портить данные, плюс нужно разруливать гонки. Поэтому на сервисе с десятком запросов в секунду проще оставить COALESCE, а переносить расчёты в запись стоит при тысячах чтений в секунду.

Хочу подробный разборГолоса помогают выбрать, что разобрать подробно. За этот материал 0 голосов.
Источники · 4