--- title: 'PostgreSQL для дашбордов: партицирование, материализованные представления и JSONB | DevSense' description: 'Как сделать быстрые дашборды на PostgreSQL: партицирование по дате, мгновенное удаление старых данных, материализованные представления и rollup-таблицы, JSONB с правильными индексами в Laravel.' faq: - { question: 'Когда таблицу стоит партицировать по дате?', answer: 'Когда в ней сотни миллионов строк, запросы почти всегда фильтруют по времени, а старые данные нужно регулярно удалять. Партицирование даёт отсечение ненужных партиций при планировании запроса (partition pruning) и позволяет удалять целый месяц данных командой DROP или DETACH PARTITION за миллисекунды вместо долгого DELETE с последующим VACUUM.' } - { question: 'Что даёт REFRESH MATERIALIZED VIEW CONCURRENTLY?', answer: 'Обычный REFRESH берёт эксклюзивную блокировку, и дашборд не может читать представление, пока оно пересчитывается. CONCURRENTLY строит новую версию рядом и применяет разницу, не блокируя чтение. Для этого у материализованного представления должен быть уникальный индекс, покрывающий все строки.' } - { question: 'Какой индекс нужен для запросов по JSONB?', answer: "Для поиска по вхождению (оператор @>) подходит GIN-индекс с классом операторов jsonb_path_ops: он компактнее обычного jsonb_ops. Если постоянно фильтруете по одному ключу, эффективнее индекс по выражению (payload->>'game_id') или сгенерированная колонка с обычным B-tree индексом." } - { question: 'Когда PostgreSQL перестаёт подходить для аналитики?', answer: 'Когда объёмы исчисляются миллиардами строк, а запросы агрегируют большие диапазоны по многим измерениям в реальном времени. Строковое хранение и MVCC делают такие сканы дорогими, и колоночные базы вроде ClickHouse выигрывают на порядки. До этого момента партицирование и предагрегация обычно закрывают потребности дашбордов.' } published: '2026-09-28' --- # PostgreSQL для дашбордов: партицирование, материализованные представления и JSONB Дашборд статистики открывается 40 секунд. Таблица `events` выросла до 400 миллионов строк, индекс по `occurred_at` есть, `EXPLAIN` показывает Index Scan, но запрос «выручка по провайдерам за 30 дней» всё равно читает десятки миллионов строк. Ночной `DELETE` старых событий идёт три часа и оставляет таблицу раздутой, а автовакуум не успевает. Новые индексы здесь не помогут: проблема не в поиске строк, а в том, что дашборд каждый раз пересчитывает то, что можно было посчитать один раз. **Связанные материалы:** [Оптимизация запросов: EXPLAIN и партиционирование](database-query-optimization) · [Индексы баз данных](database-indexes-deep-dive) · [Сбор событий под высокой нагрузкой](high-load-event-ingestion) ## Содержание * [Почему таблица транзакций — плохой источник для дашборда](#oltp-vs-dashboards) * [Партицирование по времени](#partitioning) * [Удаление старых данных за миллисекунды](#retention) * [Материализованные представления](#materialized-views) * [Rollup-таблицы: инкрементальная предагрегация](#rollups) * [JSONB для данных провайдеров](#jsonb) * [Собираем всё вместе](#architecture) * [Где подход перестаёт работать](#limitations) * [Частые ошибки](#common-mistakes) * [Чеклист](#checklist) * [Квиз для самопроверки](#self-test-quiz) --- ## Почему таблица транзакций — плохой источник для дашборда Транзакционная таблица оптимизирована под короткие операции: вставить событие, найти заказ по ID, обновить статус. Дашборд делает противоположное: читает огромные диапазоны и сворачивает их в несколько чисел. Когда оба типа нагрузки идут в одну таблицу: * **Каждый просмотр дашборда повторяет одну и ту же работу.** Десять менеджеров, открывших страницу, — это десять полных агрегаций за месяц. * **Индекс не спасает от объёма.** Даже идеальный индекс по дате находит 30 миллионов строк за месяц, и их всё равно нужно прочитать и сложить. * **Удаление старых данных дорогое.** `DELETE` помечает строки мёртвыми, и таблица раздувается до следующего `VACUUM`. **Быстрый дашборд на PostgreSQL строится не на индексах, а на форме данных: сырые события партицируются по времени, агрегаты считаются заранее и обновляются инкрементально, а переменные атрибуты провайдеров хранятся в JSONB с индексами только там, где по ним фильтруют.** --- ## Партицирование по времени Декларативное партицирование (PostgreSQL 10+) делит одну логическую таблицу на физические партиции по диапазону ключа. Для событий ключ почти всегда — время: ```php // database/migrations/2026_09_28_200000_create_events_table.php startOfMonth(); for ($i = 0; $i <= (int) $this->option('months'); $i++) { $from = $start->addMonths($i); $to = $from->addMonth(); $name = 'events_'.$from->format('Y_m'); DB::statement(sprintf( "CREATE TABLE IF NOT EXISTS %s PARTITION OF events FOR VALUES FROM ('%s') TO ('%s')", $name, $from->toDateString(), $to->toDateString(), )); } return self::SUCCESS; } } ``` ```php // routes/console.php Schedule::command('events:partitions')->daily(); ``` Теперь запрос за последние 30 дней затрагивает одну-две партиции. В `EXPLAIN` это видно по тому, что в плане перечислены только нужные партиции — это **partition pruning**. Условие должно быть на сам ключ: `WHERE occurred_at >= now() - interval '30 days'` отсекает партиции, а `WHERE date(occurred_at) = ...` — нет, потому что функция скрывает ключ от планировщика. > [!TIP] > **BRIN вместо B-tree для времени.** В append-only таблице события пишутся почти в хронологическом порядке. BRIN-индекс по `occurred_at` занимает килобайты вместо гигабайт и отлично подходит для диапазонных запросов: `CREATE INDEX ON events USING brin (occurred_at);` --- ## Удаление старых данных за миллисекунды Главный практический выигрыш партицирования — в политике хранения. Удалить событие старше года: ```sql -- Без партиций: часы работы, раздувание таблицы, нагрузка на WAL и реплики DELETE FROM events WHERE occurred_at < now() - interval '1 year'; -- С партициями: операция над метаданными ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY; -- PostgreSQL 14+ DROP TABLE events_2025_09; ``` Отсоединённую партицию можно не удалять, а выгрузить в холодное хранилище (`pg_dump -t events_2025_09`) и только потом удалить. > [!WARNING] > **Осторожнее с партицией по умолчанию.** `DEFAULT`-партиция принимает строки, не попавшие ни в один диапазон. Но при создании новой партиции PostgreSQL должен проверить, что в `DEFAULT` нет строк из её диапазона, — а это полный скан под блокировкой. Надёжнее всегда создавать партиции заранее и мониторить, что `DEFAULT` пуста. --- ## Материализованные представления Материализованное представление (materialized view) — это результат запроса, сохранённый как таблица. Дашборд читает готовый результат, а пересчёт идёт по расписанию: ```sql CREATE MATERIALIZED VIEW daily_provider_stats AS SELECT date_trunc('day', occurred_at)::date AS day, provider, count(*) AS events, sum(amount) AS revenue FROM events WHERE occurred_at >= now() - interval '90 days' GROUP BY 1, 2; -- Обязателен для REFRESH ... CONCURRENTLY CREATE UNIQUE INDEX daily_provider_stats_uidx ON daily_provider_stats (day, provider); ``` ```php // routes/console.php Schedule::call(fn () => DB::statement('REFRESH MATERIALIZED VIEW CONCURRENTLY daily_provider_stats')) ->everyFifteenMinutes() ->name('refresh-daily-provider-stats') ->withoutOverlapping() ->onOneServer(); ``` ```php // app/Models/DailyProviderStat.php final class DailyProviderStat extends Model { protected $table = 'daily_provider_stats'; public $timestamps = false; public $incrementing = false; } ``` Дашборд обращается к модели как к обычной таблице: `DailyProviderStat::where('day', '>=', now()->subDays(30))->get()`. * **`CONCURRENTLY`** пересчитывает данные, не блокируя чтение. Без него дашборд будет ждать окончания пересчёта. Для `CONCURRENTLY` нужен уникальный индекс по всем строкам представления. * **`withoutOverlapping()` и `onOneServer()`** не дают запустить два пересчёта одновременно, если прошлый ещё не закончился или у вас несколько серверов. У материализованных представлений один серьёзный минус: `REFRESH` каждый раз выполняет запрос **целиком**. Пока это секунды — отлично. Когда пересчёт 90 дней начинает занимать минуты, пора переходить к инкрементальной агрегации. --- ## Rollup-таблицы: инкрементальная предагрегация Rollup-таблица хранит агрегаты по «корзинам» (час, день) и обновляется только за последний период: ```php // database/migrations/2026_09_28_210000_create_hourly_provider_stats_table.php Schema::create('hourly_provider_stats', function (Blueprint $table) { $table->timestampTz('hour'); $table->string('provider'); $table->unsignedBigInteger('events'); $table->bigInteger('revenue'); $table->primary(['hour', 'provider']); }); ``` ```php // app/Console/Commands/RollupHourlyStats.php public function handle(): int { // Пересчитываем только последние 2 часа: запоздавшие события тоже учтутся DB::statement(<<<'SQL' INSERT INTO hourly_provider_stats (hour, provider, events, revenue) SELECT date_trunc('hour', occurred_at), provider, count(*), sum(amount) FROM events WHERE occurred_at >= date_trunc('hour', now()) - interval '2 hours' GROUP BY 1, 2 ON CONFLICT (hour, provider) DO UPDATE SET events = EXCLUDED.events, revenue = EXCLUDED.revenue SQL); return self::SUCCESS; } ``` Каждый запуск читает события за два часа, а не за 90 дней, поэтому стоимость не растёт вместе с историей. Дневные и месячные цифры дашборд получает суммированием часовых строк — это тысячи строк вместо миллионов. Окно пересчёта больше одного часа нужно для событий, которые приходят с опозданием. --- ## JSONB для данных провайдеров Провайдеры присылают разные поля: у одного `round_id` и `bet_lines`, у другого `session` и `multiplier`. Заводить колонку под каждое поле каждого провайдера бессмысленно, и здесь JSONB уместен: ```php // Запросы из Laravel Event::where('payload->game_id', 'book-of-ra')->count(); // payload->>'game_id' = ? Event::whereJsonContains('payload->tags', 'bonus')->count(); // payload->'tags' @> '["bonus"]' Event::whereRaw("payload @> ?", [json_encode(['currency' => 'EUR'])])->sum('amount'); ``` Индексы подбираются под конкретные запросы: ```sql -- Поиск по вхождению (@>): компактный GIN CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops); -- Постоянный фильтр по одному ключу: индекс по выражению CREATE INDEX events_game_id_idx ON events ((payload->>'game_id')); ``` Если по ключу не только фильтруют, но и группируют в отчётах, вынесите его в сгенерированную колонку (PostgreSQL 12+). У неё есть статистика для планировщика и обычный B-tree индекс: ```php // database/migrations/2026_09_28_220000_add_game_id_to_events.php Schema::table('events', function (Blueprint $table) { $table->string('game_id')->nullable()->storedAs("payload->>'game_id'"); $table->index('game_id'); }); ``` Учтите, что добавление `STORED`-колонки переписывает всю таблицу под блокировкой. На большой таблице это делают при создании схемы или в окно обслуживания, а в работающей системе обходятся индексом по выражению. > [!NOTE] > **JSONB — не замена схеме.** PostgreSQL не собирает статистику по ключам внутри JSONB, поэтому планировщик плохо оценивает селективность таких условий. Всё, по чему вы регулярно фильтруете, группируете или джойните, должно быть колонкой. --- ## Собираем всё вместе ``` [ Провайдеры / приложение ] │ INSERT (батчами) ▼ [ events ] — партиции по месяцам, JSONB payload, BRIN по времени │ каждые 5 минут: rollup за последние 2 часа ▼ [ hourly_provider_stats ] — тысячи строк вместо миллионов │ REFRESH CONCURRENTLY каждые 15 минут ▼ [ materialized views ] — сложные срезы для отдельных виджетов │ ▼ [ Дашборд ] — читает только агрегаты (лучше с реплики) ``` Сырые события остаются для детальных отчётов и расследований, но дашборд их не трогает. Старые партиции уходят в архив по расписанию, а размер «горячих» данных остаётся постоянным. --- ## Где подход перестаёт работать * **Слишком много партиций.** Каждая партиция увеличивает время планирования. Тысячи мелких партиций (например, по дням за несколько лет) замедлят даже простые запросы. Для большинства дашбордов хватает месячных партиций. * **Уникальность через партиции.** Уникальные ограничения обязаны включать ключ партицирования. Глобальную уникальность, например `external_id`, придётся обеспечивать отдельной таблицей или на уровне приложения. * **Индексы на партицированных таблицах нельзя строить `CONCURRENTLY` на родителе.** Приходится создавать индекс на каждой партиции по отдельности и затем прикреплять их через `ALTER INDEX ... ATTACH PARTITION`. * **Задержка данных.** Предагрегаты всегда немного отстают. Если бизнесу нужны цифры с точностью до секунды, показывайте их из rollup плюс «хвост» из сырых данных за последние минуты. * **Масштаб аналитики.** На миллиардах строк с произвольными срезами колоночные базы (ClickHouse) выигрывают на порядки. PostgreSQL остаётся источником истины, а аналитика уезжает туда, где ей место — см. [сбор событий под высокой нагрузкой](high-load-event-ingestion). --- ## Частые ошибки **1. Функция над ключом партицирования в `WHERE`.** `WHERE date(occurred_at) = '2026-09-01'` отключает partition pruning. Пишите диапазон: `occurred_at >= '2026-09-01' AND occurred_at < '2026-09-02'`. **2. `DELETE` для очистки истории.** Часы работы и раздутая таблица. Удаляйте целые партиции через `DETACH` и `DROP`. **3. Партиции создаются «по факту».** В начале месяца вставки падают или уходят в `DEFAULT`. Создавайте партиции на несколько месяцев вперёд по расписанию. **4. `REFRESH MATERIALIZED VIEW` без `CONCURRENTLY`.** Дашборд блокируется на время пересчёта. Добавьте уникальный индекс и используйте `CONCURRENTLY`. **5. Пересчёт всей истории на каждый запуск.** Материализованное представление за 3 года пересчитывается дольше интервала обновления. Переходите на rollup-таблицы с инкрементальным upsert. **6. Всё в JSONB.** Фильтры и группировки по ключам JSONB без статистики дают плохие планы. Частые ключи выносите в колонки. **7. Дашборд на основной базе.** Тяжёлые агрегации конкурируют с транзакциями пользователей. Читайте агрегаты с реплики. --- ## Чеклист 1. Большие таблицы событий партицированы по времени; первичный ключ включает ключ партицирования. 2. Партиции создаются заранее командой по расписанию; `DEFAULT`-партиция пуста или отсутствует. 3. Запросы фильтруют по ключу партицирования без функций; `EXPLAIN` показывает pruning. 4. Старые данные удаляются через `DETACH` и `DROP PARTITION`, при необходимости с архивацией. 5. Дашборд читает только агрегаты: rollup-таблицы с инкрементальным upsert или материализованные представления. 6. `REFRESH MATERIALIZED VIEW CONCURRENTLY` с уникальным индексом, `withoutOverlapping()` и `onOneServer()`. 7. Для JSONB — GIN `jsonb_path_ops` под `@>` и индексы по выражению под частые ключи; ключи для группировок вынесены в колонки. 8. Тяжёлые запросы дашборда идут на реплику. --- ## Итог Медленный дашборд почти никогда не лечится ещё одним индексом. Он лечится тем, что данные заранее приведены к форме, в которой их читают: партиции отсекают ненужное время и делают удаление истории бесплатным, предагрегаты превращают миллионы строк в тысячи, а JSONB даёт гибкость там, где схема действительно меняется от провайдера к провайдеру. --- ## Квиз для самопроверки ### Вопрос 1: Почему на партицированной по `occurred_at` таблице нельзя создать `PRIMARY KEY (id)`? - А) PostgreSQL не поддерживает первичные ключи на партицированных таблицах. - Б) Уникальность проверяется внутри каждой партиции, поэтому ключ партицирования обязан входить в любое уникальное ограничение. - В) Колонка `id` должна быть типа `uuid`.
Показать правильный ответ **Правильный ответ: Б** Каждая партиция имеет свой индекс, и PostgreSQL не умеет проверять уникальность между ними. Включение `occurred_at` гарантирует, что одинаковые значения ключа всегда попадают в одну партицию, где уникальность проверяется локально.
### Вопрос 2: Что нужно для `REFRESH MATERIALIZED VIEW CONCURRENTLY`? - А) Уникальный индекс на материализованном представлении, покрывающий все строки. - Б) Партицирование исходной таблицы. - В) Запуск команды от имени суперпользователя.
Показать правильный ответ **Правильный ответ: А** В режиме `CONCURRENTLY` PostgreSQL сравнивает старую и новую версии построчно, и уникальный индекс нужен, чтобы сопоставить строки. Без него доступен только обычный `REFRESH`, который блокирует чтение.
### Вопрос 3: Какое условие позволяет PostgreSQL отсечь ненужные партиции? - А) `WHERE date(occurred_at) >= '2026-09-01'` - Б) `WHERE occurred_at >= '2026-09-01'` - В) `WHERE extract(month FROM occurred_at) = 9`
Показать правильный ответ **Правильный ответ: Б** Partition pruning работает, когда условие наложено непосредственно на ключ партицирования. Функции `date()` и `extract()` скрывают ключ от планировщика, и он вынужден просматривать все партиции.