Капстоун: расследование деградации прода

Шаги

1. 01

   Проблема 1: раздутая таблица - диагностируй мусор

   Воспроизведём раздувание: таблица с отключённым автовакуумом, все строки которой переписаны (старые версии стали мёртвыми).

   sqlcopy

   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 больше нуля? Проверь:

   sqlcopy

   SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='cap_bloat';

   ✓ n_dead_tup > 0: половина версий мёртвые. Это и есть раздувание.

2. 02

   Проблема 1: почини через VACUUM

   Раздувание убирает vacuum (на проде сначала надо снять то, что держит горизонт - долгую транзакцию или idle in transaction).

   sqlcopy

   VACUUM cap_bloat;

   Предскажи: что станет с n_dead_tup после vacuum?

   ✓ n_dead_tup = 0. Vacuum убрал мёртвые версии - раздувание снято.

3. 03

   Проблема 2: отсутствующий индекс - создай его

   Запрос по passenger в таблице tickets идёт сейчас сканом - индекса нет. Создай его:

   sqlcopy

   CREATE INDEX ix_tickets_passenger ON tickets (passenger);

   Предскажи: появится ли индекс в каталоге pg_indexes.

   ✓ Индекс создан. Проверим, что запрос его берёт.

4. 04

   Проблема 2: подтверди использование индекса

   Таблица маленькая, поэтому планировщик сам может предпочесть seq scan - чтобы показать, что индекс работает, на время выключим seq scan и выполним запрос:

   sqlcopy

   SET enable_seqscan = off;

   SELECT * FROM tickets WHERE passenger = 'PAX 1';

   Предскажи: вырастет ли idx_scan у нового индекса? Счётчик накопительный, проверка увидит его в новом соединении:

   sqlcopy

   SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE indexrelname='ix_tickets_passenger';

   подсказка

   idx_scan в pg_stat_user_indexes - накопительный счётчик обращений к индексу.

   ✓ idx_scan > 0: индекс реально используется.

5. 05

   Проблема 3: дублирующий индекс - найди и удали

   Кто-то завёл второй индекс по той же колонке. Он только раздувает запись и не даёт выигрыша. Создай дубль, убедись, что он не используется (idx_scan = 0), и удали:

   sqlcopy

   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 после удаления.

   ✓ Дубль удалён. Запись больше не платит за бесполезный индекс.

6. 06

   Проблема 4: неверный тип индекса - поставь GIN

   Для полнотекстового поиска btree бесполезен - нужен GIN. Создай GIN-индекс по tsvector от passenger:

   sqlcopy

   CREATE INDEX ix_cap_fts ON tickets USING gin (to_tsvector('simple', passenger));

   Предскажи: каким методом доступа (amname) окажется этот индекс - btree или gin? Проверь через pg_am:

   sqlcopy

   SELECT a.amname FROM pg_class c JOIN pg_am a ON a.oid = c.relam WHERE c.relname='ix_cap_fts';

   ✓ amname = gin. Под полнотекстовый поиск выбран правильный метод доступа. Капстоун пройден.