--- 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` на старите събития продължава три часа и оставя таблицата раздута, а autovacuum не смогва. Нови индекси тук няма да помогнат: проблемът не е в търсенето на редовете, а в това, че дашбордът всеки път преизчислява нещо, което би могло да се изчисли веднъж. **Свързани материали:** [Оптимизация на заявки: 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, затова планировчикът оценява зле селективността на такива условия. Всичко, по което редовно филтрирате, групирате или правите join, трябва да е колона. --- ## Събираме всичко заедно ``` [ Доставчици / приложение ] │ 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()` скриват ключа от планировчика и той е принуден да прегледа всички партиции.