Таблица событий выросла до миллиардов строк и запросы по ней стали непредсказуемо медленными в разные дни. Как спроектировать стратегию партиционирования и кластеризации с учётом разных диалектов (PostgreSQL, ClickHouse, BigQuery)?

Короткий ответ

Единого решения нет: стратегия зависит от типичных фильтров в реальных запросах и от конкретной СУБД — в PostgreSQL это декларативное партиционирование по дате плюс индексы, в ClickHouse — ключ партиционирования (PARTITION BY) и ключ сортировки (ORDER BY), в BigQuery — партиционирование по дате и кластеризация по часто фильтруемым столбцам; выбор всегда компромисс между скоростью чтения, скоростью записи и операционной сложностью.

Как это работает подробнее

партиционирование физически разделяет таблицу на меньшие сегменты (обычно по диапазону дат), позволяя запросу с фильтром по этому диапазону читать только нужные сегменты (partition pruning); кластеризация/сортировочный ключ внутри сегмента дополнительно упорядочивает данные так, чтобы фильтрация по вторичным столбцам также требовала меньше операций ввода-вывода.

Компромисс. более мелкое партиционирование и более специфичная кластеризация ускоряют конкретные частые запросы, но увеличивают операционную сложность (больше партиций для мониторинга, больше правил для оптимизатора, возможное удорожание записи) и могут ухудшить производительность запросов с другими, нетипичными паттернами фильтрации; выбор всегда специфичен для фактической нагрузки и должен пересматриваться по мере её изменения.

Пример кода

question-75.sqlКопировать

-- BigQuery: партиционирование по дате + кластеризация по user_id
CREATE TABLE events
PARTITION BY event_date
CLUSTER BY user_id
AS SELECT * FROM events_raw;

Что использовать на практике

• Практика. перед выбором ключей нужно проанализировать реальные паттерны запросов (через логи медленных запросов или метрики BI-инструмента), а не угадывать — например, если 90% запросов фильтруют по user_id, кластеризация должна учитывать именно этот столбец, а не произвольно выбранный. Непредсказуемая медленность «в разные дни» часто говорит о неравномерном распределении данных между партициями (data skew) — например, один день с аномально большим количеством событий создаёт «горячую» партицию.
• Проверка. Проверка: сравнить объём обработанных байт (dry run) для типичного запроса с фильтром по event_date и user_id до и после применения партиционирования/кластеризации — ожидается кратное снижение.

Подводные камни

• Частая ошибка. выбирают слишком мелкое партиционирование (например, по часам при небольшом суточном объёме), что создаёт избыточное количество маленьких партиций и увеличивает накладные расходы на их обработку метаданными; меняют схему партиционирования без миграции существующих данных, получая «смешанную» таблицу с частью данных в старой схеме.