Четыре предыдущих урока строили теорию: страницы, кортежи, fillfactor, файлы. Этот урок — прикладной. У тебя есть Postgres-сервер в проде, и тебе нужно ежедневно отвечать на вопросы: какие таблицы растут быстрее всего, где образуется bloat, какие TOAST’и переростают heap, какая HOT ratio падает. Здесь — готовый набор инструментов и SQL’ей для этого.

Базовая иерархия размеров

PostgreSQL предоставляет несколько функций, и важно понимать разницу:

Ключевая ошибка новичков — использовать pg_relation_size и думать, что это полный размер таблицы. На самом деле для таблицы с большим JSONB и парой индексов реальный размер легко в 10x больше того, что показывает pg_relation_size.

Для customers (только текстовые колонки, без больших JSONB) разница между heap и total обычно небольшая — таблица узкая, без TOAST. На реальных таблицах с TOAST разница может быть огромная.

Топ самых больших таблиц

Стандартный запрос для daily monitoring:

Это базовая «обзорная» команда: при подключении к незнакомой БД её стоит запустить первой. Сразу видишь, где сосредоточены данные, и какие таблицы заслуживают внимания.

Dead tuples и bloat ratio

Простейшая оценка через pg_stat_user_tables:

Эмпирические пороги:

• dead_pct < 10% — норма.
• dead_pct = 10-25% — обрати внимание, проверь автовакуум.
• dead_pct > 25% — алерт. Скорее всего, autovacuum не успевает; пора либо VACUUM руками, либо тюнинговать параметры (autovacuum_vacuum_scale_factor).

Замечание для pglite: автовакуум работает упрощённо или вовсе выключен — там этот запрос не покажет реальной динамики. На реальной БД он самый полезный.

pgstattuple: точная оценка bloat

pg_stat_user_tables показывает только то, что Postgres увидел при последнем VACUUM/ANALYZE. Реальный bloat может быть выше — например, dead tuples, оставшиеся от долгой транзакции, которая держит snapshot.

Точная оценка — через расширение pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('orders');

Возвращает:

• table_len — полный размер heap в байтах;
• tuple_count — число живых tuples;
• tuple_len — суммарный размер живых tuples;
• tuple_percent — % полезного содержимого;
• dead_tuple_count, dead_tuple_len, dead_tuple_percent — то же про dead;
• free_space — суммарный free space в страницах;
• free_percent.

В pglite расширения pgstattuple обычно нет, но логику запоминай: на реальной БД ты будешь его использовать постоянно.

Альтернатива без расширения — статистическая оценка bloat через pg_class.reltuples, relpages и среднюю длину кортежа. Это не точно, но не требует прав суперюзера:

bloat_ratio_pct > 150% — таблица существенно раздута. Этот же запрос (с поправкой на схему) — основа всех известных bloat-чеков (например, в check_postgres от Bucardo).

Размер индексов отдельно

Индексы тоже bloat’ятся, и часто сильнее, чем таблицы. Стандартный запрос:

Колонка idx_scan — количество использований индекса с момента сброса статистики. Если индекс существует, но idx_scan = 0 и таблица активная — это неиспользуемый индекс, кандидат на удаление. Каждый неиспользуемый индекс — это: место на диске, замедление каждого INSERT/UPDATE, дополнительная нагрузка на autovacuum.

Скорость роста таблицы

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

CREATE TABLE storage_history (
  measured_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  table_name  TEXT NOT NULL,
  total_bytes BIGINT NOT NULL,
  PRIMARY KEY (table_name, measured_at)
);

-- ежедневный job:
INSERT INTO storage_history (table_name, total_bytes)
SELECT schemaname || '.' || relname, pg_total_relation_size(schemaname || '.' || relname)
FROM pg_stat_user_tables;

Потом — самый практичный запрос: «топ-таблиц по скорости роста за последние 7 дней»:

Этот тип отчёта показывает не что большое, а что становится большим. Часто алерт по росту срабатывает за неделю до того, как место кончится — даёт время среагировать.

TOAST и Free Space Map

Расширение pg_freespacemap (стандартное, нужно CREATE EXTENSION) даёт подробную карту FSM:

CREATE EXTENSION pg_freespacemap;

SELECT * FROM pg_freespace('orders') LIMIT 10;
-- блок, доступное место (округлено до 32 байт)

Это полезно при отладке: если на таблице много dead tuples и при этом FSM показывает много свободного места, но INSERT’ы продолжают аппендить новые страницы — что-то не так с автовакуумом или с долгими транзакциями, держащими snapshot.

В pglite этого расширения обычно нет; на реальной БД — ставится одной командой.

Размер TOAST отдельно

Часто полезно понять, какая часть полного размера — это TOAST:

Если toast_pct > 50% — таблица в первую очередь TOAST-bound. Запросы, выбирающие varlena-колонки, будут читать TOAST отдельно. Имеет смысл подумать: (1) вынести большие колонки в отдельную таблицу с lazy join, (2) сменить стратегию (EXTERNAL для уже-сжатых, MAIN для маленьких varlena), (3) включить колоночное сжатие lz4 (если ещё pglz).

Минимальный «daily storage report»

Собирая всё вышесказанное — минимальный отчёт, который стоит запускать раз в день:

Этот запрос — основа большинства production-dashboard’ов. В Grafana / Datadog / etc. его обычно крутят раз в час и алертят на dead_pct > 25, hot_pct < 50, или резкое падение last_autovacuum.

Чек-лист

• pg_relation_size — только heap (без TOAST и индексов). Часто вводит в заблуждение.
• pg_total_relation_size — полный размер: heap + forks + TOAST + индексы. Использовать по умолчанию.
• pg_indexes_size — сумма всех индексов; pg_relation_size(rel, 'fsm'/'vm') — отдельные forks.
• pg_stat_user_tables — основной источник n_live_tup, n_dead_tup, n_tup_hot_upd, last_autovacuum. Это «грубая, но достаточная» аналитика.
• pgstattuple — точный bloat (но требует расширения и обычно суперюзера).
• pg_stat_user_indexes — idx_scan = 0 на активной таблице → кандидат на DROP.
• Топ-3 запроса для daily monitoring: топ по pg_total_relation_size, по dead_pct, по скорости роста (через свою таблицу истории).
• Все алерты строй на относительных метриках (%), а не на абсолютных размерах — иначе они быстро устаревают.