lesson ── postgres-labs ── ~35 мин ── 6 шагов
Финал курса. Ты пройдёшь единый алгоритм оптимизации на живых проблемах: раздувание таблицы и горизонт, отсутствующий индекс, неиспользуемый дубликат и неверный тип индекса. Каждую проблему сначала диагностируешь через системные представления, потом чинишь и подтверждаешь данными - не «вроде стало быстрее», а проверкой на представлении и плане. Для каждой предсказывай результат до выполнения.
интерактивный sandbox
Поднимется контейнер postgreslab/postgres-base с PostgreSQL 17 и psql. В браузере откроется терминал, база lab уже настроена. Каждый шаг проверяется автоматически. Сеть air-gapped, наружу контейнер не ходит.
stack ── PostgreSQL 17 · psql · 1 GB RAM · air-gapped · самоуничтожается через 45 мин простоя
Воспроизведём раздувание: таблица с отключённым автовакуумом, все строки которой переписаны (старые версии стали мёртвыми).
CREATE TABLE cap_bloat (id int, payload text) WITH (autovacuum_enabled = false);
INSERT INTO cap_bloat SELECT g, repeat('x', 100) FROM generate_series(1, 2000) g;UPDATE cap_bloat SET payload = repeat('y', 100);Предскажи: будет ли n_dead_tup больше нуля? Проверь:
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='cap_bloat';
✓ n_dead_tup > 0: половина версий мёртвые. Это и есть раздувание.
Раздувание убирает vacuum (на проде сначала надо снять то, что держит горизонт - долгую транзакцию или idle in transaction).
VACUUM cap_bloat;
Предскажи: что станет с n_dead_tup после vacuum?
✓ n_dead_tup = 0. Vacuum убрал мёртвые версии - раздувание снято.
Запрос по passenger в таблице tickets идёт сейчас сканом - индекса нет. Создай его:
CREATE INDEX ix_tickets_passenger ON tickets (passenger);
Предскажи: появится ли индекс в каталоге pg_indexes.
✓ Индекс создан. Проверим, что запрос его берёт.
Таблица маленькая, поэтому планировщик сам может предпочесть seq scan - чтобы показать, что индекс работает, на время выключим seq scan и выполним запрос:
SET enable_seqscan = off;
SELECT * FROM tickets WHERE passenger = 'PAX 1';
Предскажи: вырастет ли idx_scan у нового индекса? Счётчик накопительный, проверка увидит его в новом соединении:
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE indexrelname='ix_tickets_passenger';
idx_scan в pg_stat_user_indexes - накопительный счётчик обращений к индексу.
✓ idx_scan > 0: индекс реально используется.
Кто-то завёл второй индекс по той же колонке. Он только раздувает запись и не даёт выигрыша. Создай дубль, убедись, что он не используется (idx_scan = 0), и удали:
CREATE INDEX ix_dup ON tickets (passenger);
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE indexrelname='ix_dup';
DROP INDEX ix_dup;
Предскажи: что вернёт pg_indexes по ix_dup после удаления.
✓ Дубль удалён. Запись больше не платит за бесполезный индекс.
Для полнотекстового поиска btree бесполезен - нужен GIN. Создай GIN-индекс по tsvector от passenger:
CREATE INDEX ix_cap_fts ON tickets USING gin (to_tsvector('simple', passenger));Предскажи: каким методом доступа (amname) окажется этот индекс - btree или gin? Проверь через pg_am:
SELECT a.amname FROM pg_class c JOIN pg_am a ON a.oid = c.relam WHERE c.relname='ix_cap_fts';
✓ amname = gin. Под полнотекстовый поиск выбран правильный метод доступа. Капстоун пройден.
Единый алгоритм: сначала различи «вся база» (горизонт, мусор, блокировки) и «один запрос» (статистика, план, индексы). Раздутую таблицу лечит vacuum, отсутствующий индекс - создание правильного, лишний дубль - удаление, неверный тип - замена на GIN. Каждую починку подтверждай представлением или планом.
команды
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;где копится мусорVACUUM t;убрать мёртвые версииSELECT indexrelname, idx_scan FROM pg_stat_user_indexes;какие индексы используются, какие нетCREATE INDEX ix ON t USING gin (to_tsvector('simple', col));GIN под полнотекстовый поискконцепции