Эксплуатация, наблюдаемость, анти-паттерны

Что отвечать

Первый - `pg_stat_activity`: кто сейчас подключён, что выполняет, как долго, в каком состоянии (active, idle, idle in transaction), чего ждёт (`wait_event`). По нему сразу видно зависшие транзакции и блокировки. `pg_stat_statements` (расширение) - агрегат по нормализованным запросам: суммарное время, число вызовов, среднее, чтения буферов; главный инструмент «какие запросы съедают сервер». `pg_locks` показывает кто кого блокирует. `pg_stat_user_tables` и `pg_stat_user_indexes` - сканы, мёртвые версии, последний autovacuum, использование индексов. `pg_stat_io` (PG 16) даёт картину чтений и записей по типам. Это набор, с которого начинается любой разбор инцидента.

Что хотят услышать

Senior должен: - назвать `pg_stat_activity` как стартовую точку и что в нём смотреть (state, wait_event, длительность) - знать `pg_stat_statements` как способ найти тяжёлые запросы по агрегатам - перечислить `pg_locks`, `pg_stat_user_tables/indexes`, `pg_stat_io` и что каждое отвечает - подходить от симптома к представлению: медленно вообще - statements, висит конкретный - activity плюс locks

Подводные камни

• ✗ Начинать диагностику с гадания по логам вместо `pg_stat_activity` и `pg_stat_statements`
• ✗ Не включить `pg_stat_statements` заранее и остаться без истории на момент инцидента
• ✗ Смотреть только среднее время запроса и пропустить редкий, но катастрофически дорогой

Follow-up

• ? Как по `pg_stat_activity` найти транзакцию, висящую в idle in transaction?
• ? Что даёт `pg_stat_statements`, чего не видно в логе медленных запросов?
• ? Куда смотреть, чтобы понять, используется ли индекс?

Что отвечать

Сначала находим виновника, потом разбираем. Находим через `pg_stat_statements`: сортируем по суммарному времени (`total_exec_time`) или по среднему и смотрим топ. Параллельно включаем лог медленных запросов (`log_min_duration_statement`), чтобы ловить конкретные выполнения с параметрами, и `auto_explain` для автоматического плана долгих запросов прямо в лог. Найдя запрос, гоняем `EXPLAIN (ANALYZE, BUFFERS)` и сравниваем оценки с фактом, ищем где улетает кардинальность, есть ли seq scan там, где просится индекс, не уходит ли сортировка на диск. Правило: сначала измеряй (`pg_stat_statements`, EXPLAIN), потом меняй, а не наоборот.

Что хотят услышать

Senior должен: - разделить шаги: найти тяжёлый запрос (statements/лог) и разобрать его (EXPLAIN ANALYZE BUFFERS) - назвать `log_min_duration_statement` и `auto_explain` как способы поймать конкретные выполнения - читать план по приёму «оценка против факта» и искать seq scan, sort на диск, рост кардинальности - держать принцип «сначала измерь, потом меняй» вместо угадывания индексов

Подводные камни

• ✗ Накидывать индексы по догадке, не найдя реального виновника в `pg_stat_statements`
• ✗ Смотреть только среднее время и пропустить запрос с редкими, но огромными выбросами
• ✗ Менять конфиг наугад вместо разбора конкретного плана

Follow-up

• ? Чем `pg_stat_statements` дополняет `log_min_duration_statement`?
• ? Что показывает `auto_explain` и когда его включать?
• ? Что в `EXPLAIN (ANALYZE, BUFFERS)` укажет на нехватку `work_mem`?

Что отвечать

Каждое соединение в PostgreSQL - отдельный процесс ОС со своей памятью. Тысяча коннектов это тысяча процессов: они конкурируют за CPU, переключение контекста съедает время, а суммарный `work_mem` способен выесть всю память, потому что он считается на операцию, а не на сервер. Поэтому больше соединений почти всегда означает медленнее, а не быстрее. Решение - пул: pgbouncer держит небольшое число реальных коннектов к базе и мультиплексирует на них множество клиентских. Режим transaction pooling отдаёт серверный коннект на время транзакции и возвращает в пул - так сотни клиентов работают через десятки реальных соединений. Разумный потолок реальных коннектов обычно в районе числа ядер, помноженного на небольшой коэффициент.

Что хотят услышать

Senior должен: - объяснить, что соединение это процесс, и почему рост числа коннектов бьёт по CPU и памяти - связать опасность с `work_mem`: он на операцию, и много коннектов умножают потребление - описать pgbouncer и transaction pooling как мультиплексирование на малое число реальных коннектов - дать ориентир по числу реальных соединений и предупредить про несовместимости transaction pooling (prepared statements, advisory locks)

Подводные камни

• ✗ Поднимать `max_connections` до тысяч вместо пула - сервер задохнётся на переключении контекста и памяти
• ✗ Забыть, что `work_mem` умножается на число операций и коннектов, и словить OOM
• ✗ Использовать session-фичи (prepared statements, advisory locks) в transaction pooling без оглядки на режим

Follow-up

• ? Почему `work_mem` опасно умножается при большом числе соединений?
• ? Чем transaction pooling отличается от session pooling?
• ? Какой грубый ориентир по числу реальных коннектов к базе?

Что отвечать

`shared_buffers` - общий буферный кеш на весь сервер, разумный старт около четверти ОЗУ; остальное оставляют кешу ОС, потому что PostgreSQL опирается и на него. `work_mem` - память на одну операцию сортировки или хеша в запросе, не на запрос и не на сервер: сложный запрос с несколькими сортами и параллелизмом может занять несколько `work_mem` сразу, а сотни соединений умножают это многократно - поэтому его держат умеренным и поднимают точечно. `maintenance_work_mem` - память под обслуживание (vacuum, `CREATE INDEX`), её можно ставить щедро, потому что таких операций немного одновременно. Ключевая ловушка - думать, что `work_mem` выделяется один раз на сервер.

Что хотят услышать

Senior должен: - различить области: shared_buffers (весь сервер), work_mem (на операцию), maintenance_work_mem (на обслуживание) - объяснить опасность `work_mem`: умножается на операции, параллелизм и число коннектов - дать стартовые ориентиры и сказать, почему не отдают всю память под shared_buffers (кеш ОС, риск тяжёлых контрольных точек) - советовать поднимать `work_mem` локально для тяжёлых аналитических запросов, а не глобально

Подводные камни

• ✗ Считать `work_mem` глобальным лимитом - он на операцию, и суммарно легко уводит в OOM
• ✗ Отдавать почти всю память под `shared_buffers` - кеш ОС тоже нужен
• ✗ Ставить большой `work_mem` глобально ради пары аналитических запросов и рисковать памятью на OLTP-нагрузке

Follow-up

• ? Почему `work_mem` нельзя выкручивать глобально на нагруженном сервере?
• ? Зачем оставлять память кешу ОС, а не отдавать всё `shared_buffers`?
• ? Чем `maintenance_work_mem` отличается от `work_mem` по риску?

Что отвечать

Логический бэкап (`pg_dump`/`pg_dumpall`) выгружает данные как набор команд или архив: переносимо между версиями и платформами, удобно для отдельной базы или таблицы, но медленно восстанавливается на больших объёмах и даёт снимок только на момент дампа. Физический бэкап (`pg_basebackup` или копия каталога) плюс непрерывный архив WAL дают PITR (point-in-time recovery): можно восстановить кластер на любой момент между базовым бэкапом и концом архива - например на секунду до ошибочного `DELETE`. Для больших продакшенов база это физический бэкап плюс архив WAL; логический дамп идёт дополнением для переносимости и выборочного восстановления. И то и другое надо регулярно проверять пробным восстановлением.

Что хотят услышать

Senior должен: - противопоставить логический (переносимый, на момент дампа, медленное восстановление) и физический плюс WAL (быстрое восстановление, PITR) - объяснить PITR: базовый бэкап плюс архив WAL дают восстановление на точную точку времени - связать с предыдущим: именно PITR, а не реплика, спасает от логической ошибки - подчеркнуть проверку бэкапов: непроверенный бэкап это не бэкап

Подводные камни

• ✗ Полагаться только на `pg_dump` для большого прода - восстановление займёт часы и потеряет всё после дампа
• ✗ Хранить бэкапы и ни разу не пробовать восстановление - в инцидент окажется, что они битые
• ✗ Считать архив WAL необязательным - без него физический бэкап даёт только момент базовой копии, без PITR

Follow-up

• ? Что нужно помимо базового бэкапа, чтобы получить PITR?
• ? Почему `pg_dump` плохо подходит как единственный бэкап терабайтной базы?
• ? Как проверить, что бэкап рабочий, не дожидаясь аварии?

Что отвечать

Отключать autovacuum «чтобы не мешал» - прямой путь к раздуванию и аварии wraparound. Держать долгие и idle in transaction транзакции - они стопорят горизонт и копят мусор. Лепить индексы на каждый столбец - каждый замедляет запись и ест место, а планировщик их часто не берёт. Гнать `ALTER TABLE` на горячей таблице без `lock_timeout` - очередь блокировок встаёт колом. Раздувать число соединений вместо пула. Хранить гигантские значения без оглядки на TOAST и UPDATE-нагрузку. Делать `SELECT *` и тянуть TOAST там, где он не нужен. Не мониторить возраст транзакций и слотов репликации. Каждый пункт - типовая причина реального инцидента, а не теория.

Что хотят услышать

Senior должен: - перечислить ядро анти-паттернов: выключенный autovacuum, долгие транзакции, переизбыток индексов, DDL без lock_timeout, отказ от пула - для каждого назвать конкретный вред, а не просто «плохо» - связать их с уже разобранными механизмами (горизонт, блокировки, TOAST, wraparound) - показать, что профилактика это мониторинг (возраст транзакций, слоты, раздувание), а не реакция по факту

Подводные камни

• ✗ Называть анти-паттерны без объяснения механизма вреда - это и проверяют
• ✗ Считать «индекс на всё» оптимизацией - на запись это чистый налог
• ✗ Путать симптом (медленно) с причиной (раздувание, долгая транзакция, плохой план)

Follow-up

• ? Почему «индекс на каждый столбец» вредит, а не помогает?
• ? Чем именно опасно отключение autovacuum?
• ? Какие метрики надо мониторить, чтобы предупредить эти проблемы?

Что отвечать

Раздувание (bloat) - место, занятое мёртвыми версиями строк и пустотами в страницах, которое уже не несёт полезных данных. Оно растёт, когда мёртвых версий появляется больше, чем успевает убирать vacuum: тяжёлая UPDATE/DELETE-нагрузка, отстающий autovacuum, удержанный горизонт от долгих транзакций. Симптомы: таблица и индексы растут, а число живых строк нет; index-only scan вырождается в Heap Fetches. Обнаруживают через `pg_stat_user_tables` (`n_dead_tup`), расширение `pgstattuple` для точной оценки, и оценочные запросы по каталогу. Лечат по нарастающей: наладить autovacuum и убрать долгие транзакции (профилактика), а для уже раздутого - `VACUUM FULL`, `CLUSTER` или `pg_repack`/`REINDEX CONCURRENTLY` для индексов.

Что хотят услышать

Senior должен: - определить bloat как мёртвые версии плюс пустоты и назвать причины: нагрузка, отстающий vacuum, удержанный горизонт - назвать диагностику: `n_dead_tup`, `pgstattuple`, рост размера при неизменном числе строк - различить профилактику (autovacuum, короткие транзакции) и лечение (VACUUM FULL, pg_repack, REINDEX) - понимать, что без устранения причины раздувание вернётся после любой перестройки

Подводные камни

• ✗ Чинить раздувание `VACUUM FULL` по кругу, не убрав причину (долгие транзакции, слабый autovacuum)
• ✗ Мерить bloat по размеру файла без `pgstattuple` и переоценивать или недооценивать его
• ✗ Забыть про раздувание индексов - его лечит `REINDEX`, а не обычный vacuum таблицы

Follow-up

• ? Как отличить рост таблицы от роста именно раздувания?
• ? Почему `VACUUM FULL` без устранения причины - временная мера?
• ? Чем лечить раздувание индексов, а не таблицы?

Что отвечать

Несколько слоёв. Аутентификация - `pg_hba.conf`: кто, откуда и каким методом подключается; ставят `scram-sha-256` вместо устаревшего md5 и закрывают `trust` на проде. Авторизация - роли и привилегии по принципу наименьших прав: приложение не должно ходить суперпользователем, у него своя роль с грантами только на нужные объекты, схема `public` не открыта на запись всем. Транспорт - TLS для соединений по сети. Защита данных - разделение ролей на владельца схемы и рабочую роль приложения, аккуратный `SET ROLE`, отзыв лишних `GRANT`. Плюс гигиена: не хранить пароли в коде, ограничить сеть на уровне firewall, держать сервер за периметром, а не на публичном адресе.

Что хотят услышать

Senior должен: - разложить по слоям: аутентификация (`pg_hba`, scram), авторизация (роли, наименьшие права), транспорт (TLS), сеть (firewall, периметр) - назвать конкретику: убрать `trust`, не работать суперпользователем, закрыть запись в `public` - понимать принцип наименьших привилегий для роли приложения - связать с эксплуатацией: безопасность это не один флаг, а набор дефолтов, которые легко забыть

Подводные камни

• ✗ Оставлять `trust` или `md5` в `pg_hba.conf` на проде вместо `scram-sha-256`
• ✗ Гонять приложение под суперпользователем вместо роли с минимальными правами
• ✗ Держать сервер на публичном адресе без firewall, полагаясь только на пароль

Follow-up

• ? Чем `scram-sha-256` лучше `md5` в `pg_hba.conf`?
• ? Почему запись в схему `public` для всех - это проблема?
• ? Как выглядит принцип наименьших привилегий для роли приложения?