Сгенерированные столбцы PostgreSQL для быстрых фильтров в админке

Почему панели админки быстро становятся медленными и неаккуратными

Админ‑страницы обычно начинают просто: таблица, пара фильтров, сортировка «сначала новые». А затем начинается настоящая работа. Саппорт просит поиск по имени, email и телефону. Отдел продаж — сортировку по «последней активности». Финансы — «просроченный баланс». Каждое требование добавляет условия, JOIN‑ы и дополнительные вычисления.

Большинство списков админки тормозят по одной и той же причине: каждый клик меняет запрос. Фильтрация и сортировка могут заставить базу просмотреть много строк, особенно если запросу нужно вычислить значение для каждой строки, чтобы понять, что подходит.

Поворотный момент обычно наступает, когда WHERE и ORDER BY заполняются выражениями. Вместо фильтрации по простому столбцу вы фильтруете по lower(email), date_trunc('day', last_seen_at) или CASE, который сводит несколько статусов в один «бакет». Эти выражения не только медленнее. Они усложняют SQL, затрудняют индексирование и увеличивают риск ошибок.

Грязный админ‑SQL обычно происходит от нескольких повторяющихся шаблонов:

• Одно поле «поиск», которое проверяет несколько полей разными правилами
• Сортировка по вычисляемому значению (полное имя, скор приоритетности, «последнее значимое событие»)
• Бизнес‑правила, раскиданные по разным экранам (активен/неактивен, оплачен/просрочен)
• Мелкие «хелперы» (trim, lower, coalesce), разбросанные повсюду
• Одно и то же вычисляемое значение используется в списке, фильтрах и сортировке

Команды часто пытаются скрыть это в слое приложения: динамические билдеры запросов, условные JOIN‑ы или предварительные вычисления в коде. Это может сработать, но разбивает логику между UI и базой, из‑за чего отладка медленных запросов становится болезненной.

Цель простая: быстрые запросы, которые остаются читабельными. Если вычисляемое значение появляется снова и снова на разных админ‑экранах, сгенерированные столбцы PostgreSQL помогут держать правило в одном месте и при этом позволят базе оптимизировать работу.

Сгенерированные столбцы простыми словами

Сгенерированный столбец — это обычный столбец таблицы, значение которого вычисляется из других столбцов. Вы не пишете это значение вручную. PostgreSQL заполняет его по выражению, которое вы задаёте.

В PostgreSQL сгенерированные столбцы хранятся на диске. Значение вычисляется при вставке или обновлении строки и сохраняется как обычный столбец. Для админ‑экранов это как правило то, что нужно: быстрые чтения и возможность индексировать вычисленное значение.

Это отличается от повторного вычисления внутри каждого запроса. Если вы постоянно пишете WHERE lower(email) = lower($1) или сортируете по last_name || ', ' || first_name, вы платите за это снова и снова, и SQL превращается в шум. Сгенерированный столбец переносит это повторяющееся вычисление в определение таблицы. Запросы становятся проще, и результат везде одинаков.

Когда исходные данные меняются, PostgreSQL автоматически обновляет сгенерированное значение для этой строки. Приложению не нужно помнить о синхронизации.

Полезная мыслеформула:

• Один раз определите формулу.
• PostgreSQL вычисляет её при записи.
• Запросы читают это как обычный столбец.
• Поскольку значение хранится, его можно индексировать.

Если вы позже измените формулу, потребуется изменение схемы. Планируйте это как миграцию: существующие строки будут пересчитаны под новое выражение.

Где хорошо подходят вычисляемые поля для фильтров и сортировки

Сгенерированные столбцы особенно полезны, когда значение всегда выводится из других столбцов и вы часто на нём фильтруете или сортируете. Для одноразовых отчётов они малоэффективны.

Поля поиска, которыми действительно пользуются люди

Поиск в админке редко бывает «чистым». Ожидают, что поле справится с небрежным вводом, разным регистром и лишними пробелами. Если хранить сгенерированный «search key», который уже нормализован, WHERE останется понятным и поведёт себя одинаково на всех экранах.

Хорошие кандидаты: объединённое полное имя, приведённый к нижнему регистру и обрезанный от пробелов текст для нечувствительного к регистру поиска, очищенная версия, сжимающая пробелы, или метка статуса, выведенная из нескольких полей.

Например, вместо повторения lower(trim(first_name || ' ' || last_name)) в каждом запросе, создайте full_name_key и фильтруйте по нему.

Ключи сортировки, которые соответствуют человеческому восприятию

Сортировка — то место, где вычисляемые поля зачастую окупаются быстрее всего, потому что сортировка может заставить PostgreSQL оценить выражения для множества строк.

Типичные ключи сортировки: числовой ранг (уровень плана, сопоставленный с 1, 2, 3), единый таймштамп «последней активности» (например max из двух timestamp), или отформатированный код, правильно сортирующийся как текст.

Когда ключ сортировки — обычный индексируемый столбец, ORDER BY становится гораздо дешевле.

Производные флаги для быстрых фильтров

Пользователи админки любят чекбоксы вроде «Просрочен» или «Клиент высокого значения». Такие вещи хорошо делать сгенерированными столбцами, если логика стабильна и основывается только на данных строки.

Например, для списка клиентов можно добавить has_unread boolean (из unread_count > 0) и is_overdue (из due_date < now() и paid_at is null) — тогда фильтры UI будут превращаться в простые условия.

Выбор между сгенерированными столбцами, индексами и другими опциями

Админ‑экраны требуют трёх вещей: быстрой фильтрации, быстрой сортировки и понятного SQL через месяцы. Реальное решение — где хранить вычисление: в таблице, в индексе, в представлении или в коде приложения.

Сгенерированные столбцы подходят, когда вы хотите, чтобы значение вело себя как настоящий столбец: по нему легко ссылаться, его видно в SELECT, и его не забудешь при добавлении новых фильтров. Они естественно сочетаются с обычными индексами.

Индекс‑выражение проще добавить, потому что не меняет схему таблицы. Если вам важна скорость и не жалко «грязного» SQL, индекс‑выражение часто достаточно. Минус — читаемость и зависимость от точного совпадения выражения планировщиком.

Представления полезны, когда вы хотите единый «вид» данных, особенно при множественных JOIN. Но сложные представления могут скрывать дорогие операции и создавать второе место для отладки.

Триггеры могут держать обычный столбец в синхронизации, но это добавляет движущиеся части: они замедляют массовые обновления и их легко пропустить при отладке.

Иногда лучший вариант — обычный столбец, заполняемый приложением. Если пользователи могут редактировать значение или формула часто меняется по бизнес‑решениям, явное хранение понятнее.

Короткий выбор:

• Хочете читабельные запросы и стабильную формулу, зависящую только от данных строки? Используйте сгенерированный столбец.
• Нужна скорость для одного фильтра и не важна чистота SQL? Используйте индекс‑выражение.
• Нужна объединённая форма данных из нескольких таблиц? Рассмотрите представление.
• Нужна логика через таблицы или сайд‑эффекты? Сначала приложение, триггеры — в последнюю очередь.

Пошагово: добавляем сгенерированный столбец и используем его в запросе

Начните с одного медленного админ‑запроса, который заметен в UI. Запишите фильтры и сортировки, которые экран использует чаще всего. Улучшайте сначала этот запрос.

Выберите вычисляемое поле, которое убирает повторяющуюся работу, и дайте ему ясное имя в snake_case, чтобы другие могли понять назначение без повторного чтения выражения.

1) Добавьте сгенерированный столбец (STORED)

ALTER TABLE customers
ADD COLUMN full_name_key text
GENERATED ALWAYS AS (
  lower(concat_ws(' ', last_name, first_name))
) STORED;

Проверьте на реальных строках перед добавлением индекса:

SELECT id, first_name, last_name, full_name_key
FROM customers
ORDER BY id DESC
LIMIT 5;

Если вывод неверен — исправьте выражение сейчас. STORED означает, что PostgreSQL будет поддерживать значение при вставках и обновлениях.

2) Добавьте индекс, соответствующий вашему экрану админки

Если экран фильтрует по статусу и сортирует по имени, индексируйте именно такую комбинацию:

CREATE INDEX customers_status_full_name_key_idx
ON customers (status, full_name_key);

3) Обновите админ‑запрос для использования нового столбца

Раньше у вас мог быть громоздкий ORDER BY. Теперь всё очевидно:

SELECT id, status, first_name, last_name
FROM customers
WHERE status = 'active'
ORDER BY full_name_key ASC
LIMIT 50 OFFSET 0;

Используйте сгенерированные столбцы для тех частей, по которым люди фильтруют и сортируют ежедневно, а не для редких экранов.

Паттерны индексирования, соответствующие реальным админ‑экранам

Админ‑экраны повторяют несколько моделей: фильтрация по нескольким полям, сортировка по одному и постраничная навигация. Лучший подход — редко «индексировать всё». Лучше индексировать точную форму самых частых запросов.

Практическое правило: ставьте наиболее частые фильтруемые колонки первыми, а колонку сортировки последней. Если у вас мульти‑тенантность, workspace_id часто идёт первым: (workspace_id, status, created_at).

Текстовый поиск — отдельная история. Многие поиск‑поля превращаются в ILIKE '%term%', что тяжело ускорить обычными btree. Полезный паттерн — искать по нормализованному вспомогательному столбцу вместо сырых текстовых полей (нижний регистр, обрезка, конкатенация). Если UI может допустить префиксный поиск (term%), btree на нормализованной колонке поможет. Если нужен contains‑поиск (%term%), подумайте об ужесточении поведения UI для больших таблиц или ограничении области поиска.

Также проверяйте селективность перед добавлением индексов. Если 95% строк имеют одно значение (например status = 'active'), индексирование только этого столбца мало поможет. Комбинируйте с более селективной колонкой или используйте частичный индекс для редкого значения.

Реалистичный пример: список клиентов, который остаётся быстрым

Представьте типичную страницу клиентов: поле поиска, пара фильтров (неактивные, диапазон баланса) и сортируемая колонка «Последний визит». Со временем SQL превращается в набор LOWER(), TRIM(), COALESCE(), вычислений по датам и CASE, повторяющихся по экранам.

Один из способов сохранить быстродействие и читабельность — перенести эти повторяющиеся выражения в сгенерированные столбцы.

Таблица и сгенерированные столбцы

Предположим таблицу customers с полями name, email, last_seen и balance. Добавим три вычисляемых поля:

• search_key: нормализованный текст для простого поиска
• is_inactive: boolean для фильтрации без повторения логики по датам
• balance_bucket: метка для быстрой сегментации

ALTER TABLE customers
  ADD COLUMN search_key text
    GENERATED ALWAYS AS (
      lower(trim(coalesce(name, ''))) || ' ' || lower(trim(coalesce(email, '')))
    ) STORED,
  ADD COLUMN is_inactive boolean
    GENERATED ALWAYS AS (
      last_seen IS NULL OR last_seen < (now() - interval '90 days')
    ) STORED,
  ADD COLUMN balance_bucket text
    GENERATED ALWAYS AS (
      CASE
        WHEN balance < 0 THEN 'negative'
        WHEN balance < 100 THEN '0-99'
        WHEN balance < 500 THEN '100-499'
        ELSE '500+'
      END
    ) STORED;

Теперь админ‑запросы выглядят как UI.

Читаемый фильтр + сортировка

«Неактивные клиенты, сначала последние посещения» превращается в:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE is_inactive = true
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

А базовый поиск:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE search_key LIKE '%' || lower(trim($1)) || '%'
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

Главный выигрыш — согласованность. Одни и те же поля питают разные экраны без переписывания логики:

• Поисковая строка использует search_key
• Вкладка «Неактивные» использует is_inactive
• Чипы фильтра баланса используют balance_bucket

Частые ошибки и ловушки

Сгенерированные столбцы кажутся простым решением: положил математику в таблицу и запросы стали аккуратными. Они помогают только если совпадают с тем, как экран фильтрует и сортирует, и если вы добавили правильный индекс.

Наиболее частые ошибки:

• Думать, что это ускорит всё без индексирования. Вычисленное значение всё ещё нужно индексировать для быстрого поиска и сортировки в масштабе.
• Напихать в одно поле слишком много логики. Если столбец превратился в мини‑программу, люди перестают ему доверять. Держите выражение коротким и давайте понятные имена.
• Использовать неиммутабельные функции. PostgreSQL требует, чтобы выражение для сохранённого сгенерированного столбца было иммутабельным. now() и random() нарушают ожидания и обычно не допускаются.
• Игнорировать стоимость записи. Вставки и обновления должны поддерживать вычисленное значение. Быстрые чтения не стоят того, если импорты и интеграции становятся заметно медленнее.
• Создавать почти‑дубликаты. Стандартизируйте один‑два паттерна (например один нормализованный ключ), вместо накопления пяти похожих столбцов.

Если в списке используются contains‑поиски (ILIKE '%ann%'), один сгенерированный столбец не решит проблему. Может потребоваться другой подход к поиску. Но для повседневных «фильтр и сортировка» рабочих запросов сгенерированные столбцы плюс правильный индекс обычно делают производительность более предсказуемой.

Короткий чек‑лист перед релизом

Перед пушем изменений в админ‑лист убедитесь, что вычисляемое значение, запрос и индекс совпадают по назначению.

• Формула стабильна и её можно объяснить одной фразой.
• Ваш запрос действительно использует сгенерированный столбец в WHERE и/или ORDER BY.
• Индекс соответствует реальному использованию, а не одноразовому тесту.
• Вы сравнили результаты старой логики и новой на краевых случаях (NULL, пустые строки, странные пробелы, разный регистр).
• Вы протестировали производительность вставок/обновлений, если таблица загружена (импорты, фоновые обновления, интеграции).

Следующие шаги: примените это к своим админ‑экранам

Выберите небольшую, но высоко‑влиятельную точку старта: 2–3 экрана, которые открывают весь день (заказы, клиенты, тикеты). Отметьте, что тормозит (фильтр по дате, сортировка по "последней активности", поиск по объединённому имени, фильтрация по меткам статуса). Затем стандартизируйте короткий набор вычисляемых полей, которые можно переиспользовать.

План раскатки, который легко измерить и откатить:

• Добавьте сгенерированные столбцы с понятными именами.
• Ненадолго держите старую и новую логику параллельно, если заменяете существующие выражения.
• Добавьте индекс под основной фильтр или сортировку.
• Переключите запрос экрана на новый столбец.
• Замерьте до и после (время запроса и число просканированных строк), затем удалите старые обходные решения.

Если вы строите внутренние админ‑инструменты в AppMaster (appmaster.io), эти вычисляемые поля хорошо ложатся в общую модель данных: база хранит правило, а UI‑фильтры ссылаются на понятные имена полей вместо повторения выражений по экранам.