--- 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()` приховують ключ від планувальника, і він змушений переглядати всі партиції.