Практикум PostgreSQL — итоги

НЕ ОБЯЗАТЕЛЬНО

Кратко — что стоит унести из практикума PostgreSQL. Типичные сбои эксплуатации — в FAQ ниже; определения для зачёта — в чек-листе.

---

FAQ — Часто задаваемые вопросы

Типичные сбои эксплуатации PostgreSQL и ответы на частые запросы в поиске ("VACUUM PostgreSQL", "EXPLAIN ANALYZE", "PgBouncer", "pg_dump restore", "Patroni"). Краткий ответ здесь; разбор — в главах практикума. Заучивание — в чек-листе.

Вопрос. Приложение падает с "FATAL: sorry, too many clients already" — Postgres "упал"?

Ответ. Исчерпан лимит backend-подключений (max_connections минус служебные). Считайте реальные сессии в pg_stat_activity, закройте зависшие коннекты, поднимите лимит осознанно или поставьте PgBouncer перед инстансом. Подробнее здесь — конфигурация, репликация и PgBouncer.

Вопрос. В логе "could not write to file" / "No space left on device" — что чистить первым?

Ответ. Смотрите свободное место на томе PGDATA, рост pg_wal, застрявшие replication slots, огромные логи и дампы рядом с данными. Экстренно — место под WAL и архив; планово — retention и мониторинг диска. Подробнее здесь — архитектура и WAL, бэкапы, мониторинг.

Вопрос. Один запрос в psql выполняется секунды, из приложения — минуты и таймаут.

Ответ. Сравните параметры, роль, search_path и план (EXPLAIN (ANALYZE, BUFFERS)), блокировки в pg_locks, нагрузку на replica vs primary. Часто другой план из-за prepared statement или устаревшей статистики. Подробнее здесь — оптимизация, мониторинг.

Вопрос. n_dead_tup растёт, таблица раздувается, autovacuum "не видно".

Ответ. Проверьте autovacuum включён, нет ли долгих транзакций и блокировок, не отключён ли vacuum на таблице, хватает ли autovacuum_max_workers и I/O. Для аварии — ручной VACUUM (ANALYZE); VACUUM FULL только по окну обслуживания. Подробнее здесь — архитектура и VACUUM, конфигурация.

Вопрос. На replica отставание в pg_stat_replication / lag растёт часами.

Ответ. Узкое место — сеть, диск standby, нагрузка на primary, слот без consumer, слишком агрессивные запросы на standby. Сверьте write_lag / flush_lag / replay_lag, пропускную способность WAL. Подробнее здесь — репликация, мониторинг.

Вопрос. После docker compose down -v база "как новая" — пропали все данные.

Ответ. Флаг -v удаляет named volume с PGDATA. Для dev это норма; в проде volume, бэкап и restore вне контейнера обязательны. Init-скрипты в /docker-entrypoint-initdb.d/ снова отработают только при пустом томе. Подробнее здесь — PostgreSQL в Docker, бэкапы.

Вопрос. Через PgBouncer ORM ругается "prepared statement does not exist".

Ответ. В transaction pooling prepared statements привязаны к backend, который уже отдан другому клиенту. Отключите prepare в драйвере, используйте session pooling для этого приложения или pool_mode=session. Подробнее здесь — PgBouncer.

Вопрос. Пул соединений "висит": "waiting for connection", таймауты в приложении.

Ответ. Считайте активные vs idle in transaction, размер пула PgBouncer, default_pool_size, лимит Postgres. Долгая транзакция без COMMIT держит слот и душит очередь. Подробнее здесь — PgBouncer, конфигурация.

Вопрос. Диск забит каталогом pg_wal, Postgres отказывается стартовать.

Ответ. Частая причина — replication slot без подписчика или сломанная архивация WAL. Освободите место, найдите слот в pg_replication_slots, согласуйте retention с бэкапами. Подробнее здесь — архитектура, репликация, бэкапы.

Вопрос. В логах "database must be vacuumed within … transactions" / wraparound.

Ответ. Критично: freeze старых XID отстаёт. Срочно VACUUM на проблемных таблицах, проверьте autovacuum и долгие транзакции; при запущенном shutdown — только по runbook. Подробнее здесь — архитектура.

Вопрос. После аварийного выключения сервер долго в "starting up" / recovery.

Ответ. Идёт replay WAL — чем больше необработанный журнал, тем дольше. Следите за checkpoint, корректным shutdown и питанием тома. Подробнее здесь — архитектура и WAL.

Вопрос. В логе "deadlock detected" — кто виноват, приложение или DBA?

Ответ. Два запроса взяли блокировки в разном порядке; Postgres убил одну транзакцию. Упорядочите доступ к таблицам, укоротите транзакции, повторите операцию в коде. Подробнее здесь — архитектура.

Вопрос. Запрос отменён: "canceling statement due to lock timeout".

Ответ. Другая сессия держит блокирующий lock. Найдите пару в pg_locks + pg_stat_activity, завершите зависшую транзакцию или разнесите миграции по окну. Подробнее здесь — архитектура, мониторинг.

Вопрос. На primary диск тает, в pg_replication_slots один слот с огромным restart_lsn lag.

Ответ. Подписчик не читает WAL (упавший standby, забытый CDC). Восстановите consumer или удалите слот после согласования — иначе WAL копится бесконечно. Подробнее здесь — репликация, бэкапы.

Вопрос. После failover приложение пишет в два разных "primary".

Ответ. Признак split-brain без координатора. Нужен HA-оркестратор (Patroni), fencing старого primary, единая точка записи для клиентов. Подробнее здесь — HA-кластеры, репликация.

Вопрос. pg_dump на проде "вешает" таблицы и блокирует запись.

Ответ. Логический дамп даёт ACCESS SHARE, но длинные транзакции мешают vacuum. Делайте дамп с replica, в окно, -Fc параллельно; для больших БД — physical backup + WAL. Подробнее здесь — бэкапы.

Вопрос. PITR восстановил кластер, но данных "на нужный момент" нет.

Ответ. Проверьте базовый backup, цепочку WAL, целевое время и timeline, не восстанавливали ли поверх живого PGDATA. Тест restore по расписанию ловит такие ошибки заранее. Подробнее здесь — бэкапы.

Вопрос. OOM killer убил postgres при отчёте с JOIN и сортировкой.

Ответ. work_mem умножается на число операций сортировки/hash в запросе и на параллелизм. Уменьшите work_mem, ограничьте max_parallel_workers_per_gather, перепишите запрос или вынесите отчёт на replica. Подробнее здесь — конфигурация, оптимизация.

Вопрос. Индекс есть, в плане всё равно Seq Scan на миллионы строк.

Ответ. Планировщик считает seq scan дешевле — устаревшая статистика, неверный предикат, функция на колонке, низкая selectivity. ANALYZE, пересмотрите индекс под реальный WHERE, проверьте enable_seqscan только для отладки. Подробнее здесь — оптимизация.

Вопрос. Запрос WHERE data @> '{"status":"ok"}' по JSONB тормозит при миллионах строк.

Ответ. Нужен GIN по JSONB (jsonb_path_ops или default ops), осмысленный путь в @>, актуальный ANALYZE. Подробнее здесь — JSONB и партиции, оптимизация.

Вопрос. Партиционированная таблица: запрос по дате сканирует все партиции.

Ответ. Partition pruning срабатывает, когда предикат явно сопоставим с ключом партиции (тип и выражение без лишних функций). Проверьте план и границы FOR VALUES. Подробнее здесь — JSONB и партиции.

Вопрос. После добавления триггера INSERT стал в десятки раз медленнее.

Ответ. Триггер на каждую строку с тяжёлой логикой, внешними вызовами или каскадом — типичный узкий участок. Упростите тело, перенесите в batch/async, проверьте WHEN и уровень FOR EACH ROW. Подробнее здесь — PL/pgSQL и триггеры.

Вопрос. NOTIFY/LISTEN: часть событий "пропала" после рестарта воркера.

Ответ. NOTIFY без очереди доставки — подписчик должен быть подключён; при обрыве события теряются. Для гарантированной доставки — таблица outbox + polling или брокер (Kafka). Подробнее здесь — NOTIFY/LISTEN.

Вопрос. В Kubernetes после пересоздания Pod база пустая.

Ответ. Данные жили в ephemeral volume контейнера, PVC не привязан или emptyDir. StatefulSet + PVC, оператор (CloudNativePG) или managed DB. Подробнее здесь — Kubernetes и облако, Docker.

Вопрос. Logical replication: на подписчике "duplicate key" / конфликты.

Ответ. На subscriber уже были строки с теми же ключами, другой порядок миграций или запись мимо replication. Сверьте начальный sync, политику conflict (pg_replication_origin), при необходимости пересоздайте подписку. Подробнее здесь — репликация.

Вопрос. pg_stat_statements пустой или extension "does not exist".

Ответ. Нужны shared_preload_libraries = 'pg_stat_statements' и рестарт, затем CREATE EXTENSION в БД. На managed-сервисах расширение включают через параметры облака. Подробнее здесь — мониторинг, конфигурация.

Вопрос. Terraform применился, а postgresql.conf на VM старый.

Ответ. Terraform держит облачный инстанс и сеть; параметры на железе часто тянет Ansible с handler reload/restart. Разделите ответственность и проверьте, кто последним трогал конфиг. Подробнее здесь — Ansible и Terraform, конфигурация.

Вопрос. На Hot Standby тяжёлый отчёт падает с "conflict with recovery".

Ответ. Долгий запрос на replica мешает применению WAL — vacuum на primary удалил строки, которые читает standby. Укоротите отчёты, настройте max_standby_streaming_delay / hot_standby_feedback, вынесите аналитику на отдельную replica. Подробнее здесь — репликация и Hot Standby.

Вопрос. PostgreSQL VACUUM FULL — когда нужен и чем опасен?

Ответ. Обычный VACUUM убирает мёртвые версии без длительной эксклюзивной блокировки; VACUUM FULL переписывает таблицу и блокирует запись — только в окне обслуживания при сильном bloat. Подробнее здесь — архитектура и VACUUM.

Вопрос. EXPLAIN ANALYZE в PostgreSQL — как читать план медленного запроса?

Ответ. Смотрите actual time, rows, loops, Buffers — расхождение estimated vs actual rows сигналит устаревший ANALYZE. Seq Scan на большой таблице при наличии индекса — повод проверить предикат и статистику. Подробнее здесь — оптимизация и EXPLAIN.

Вопрос. PgBouncer pool_mode transaction и session — что выбрать для ORM?

Ответ. Transaction pooling экономит backend, но ломает prepared statements и temp tables; session — для приложений с prepare и advisory locks. Сверьте default_pool_size с max_connections. Подробнее здесь — PgBouncer.

Вопрос. pg_dump custom format — как сделать дамп и восстановить на другой сервер?

Ответ. pg_dump -Fc -f backup.dump dbname, restore — pg_restore -d newdb -j 4 backup.dump. Для только схемы — -s, данные — -a. Большие БД — с replica или в окно. Подробнее здесь — бэкапы.

Вопрос. Patroni failover — как проверить, что кластер переключился корректно?

Ответ. patronictl list, единственный Leader, старый primary в read-only или выведен (fencing). Приложение должно ходить через VIP, DNS или service discovery, а не на IP мёртвого узла. Подробнее здесь — HA-кластеры.

Вопрос. shared_buffers PostgreSQL — сколько выделять от RAM сервера?

Ответ. Стартовая эвристика 25% RAM, но смотрите на размер hot dataset и конкуренцию с OS page cache. После смены — мониторинг cache hit ratio и checkpoint spikes. Подробнее здесь — конфигурация postgresql.conf.

Вопрос. autovacuum настройка PostgreSQL — какие параметры трогать первыми?

Ответ. autovacuum_vacuum_scale_factor, autovacuum_max_workers, cost limits на шумных таблицах — per-table storage parameters. Долгие транзакции блокируют freeze — ищите их в pg_stat_activity. Подробнее здесь — конфигурация, архитектура.

Вопрос. PostgreSQL Docker Compose — как не потерять данные при пересборке?

Ответ. Named volume на PGDATA, без docker compose down -v на prod-like стенде. Переменные POSTGRES_PASSWORD, init-скрипты в docker-entrypoint-initdb.d/ — только при первом старте тома. Подробнее здесь — PostgreSQL в Docker.

Вопрос. pg_basebackup streaming replication — минимальный набор шагов для standby?

Ответ. На primary: replication slot, wal_level=replica, pg_hba для replication role. pg_basebackup -h primary -D /var/lib/postgresql/data -R -Xs -P, затем start standby и проверка pg_stat_replication. Подробнее здесь — репликация.

Вопрос. pg_stat_statements top queries — как найти самые дорогие запросы?

Ответ. Сортировка по total_exec_time, mean_exec_time, calls; смотрите shared_blks_read и temp files. После тюнинга — pg_stat_statements_reset() и сравнение до/после. Подробнее здесь — мониторинг.

Вопрос. JSONB GIN index PostgreSQL — какой операторный класс выбрать?

Ответ. Для containment @> часто хватает jsonb_path_ops (компактнее), для разнообразных операторов — default jsonb_ops. После создания — ANALYZE и проверка плана. Подробнее здесь — JSONB и партиции.

Вопрос. PostgreSQL partition by RANGE — как убедиться, что сработал partition pruning?

Ответ. В EXPLAIN должны быть Append с Subplans только нужных партиций, предикат по ключу без лишних функций и с совпадающим типом. Default partition ловит outliers — не забывайте про неё в планах. Подробнее здесь — JSONB и партиции.

Вопрос. PL/pgSQL function RETURNS TABLE — когда выносить логику в функцию?

Ответ. Уместно для повторяемой серверной логики, batch-операций и контроля прав. Тяжёлые отчёты с множеством round-trip из приложения часто быстрее одной функцией или SQL. Подробнее здесь — PL/pgSQL и триггеры.

Вопрос. PostgreSQL trigger AFTER INSERT — как не убить производительность массовой загрузки?

Ответ. Минимизируйте тело триггера, используйте WHEN, statement-level где возможно, для bulk load временно отключайте триггер по runbook. NOTIFY из триггера на каждую строку — узкое место. Подробнее здесь — PL/pgSQL и триггеры.

Вопрос. Wal-G backup PostgreSQL в S3 — чем отличается от pg_dump?

Ответ. Wal-G даёт физический backup + непрерывный WAL для PITR на больших кластерах; pg_dump — логика и перенос между версиями. Для prod OLTP чаще physical + WAL, restore тестируют регулярно. Подробнее здесь — бэкапы.

Вопрос. pg_restore parallel jobs — сколько -j ставить при восстановлении?

Ответ. Начните с числа vCPU, следите за I/O и max_connections. Конфликты FK и индексы после data — отдельные фазы; на слабом диске меньше jobs стабильнее. Подробнее здесь — бэкапы.

Вопрос. PostgreSQL Kubernetes StatefulSet PVC — минимальный checklist persistence?

Ответ. volumeClaimTemplates, StorageClass с достаточным IOPS, anti-affinity по нодам, backup вне кластера. Pod restart без PVC = пустая БД. Подробнее здесь — Kubernetes и облако.

Вопрос. CloudNativePG operator — когда предпочесть самописный Helm chart?

Ответ. Оператор закрывает failover, backup CRD, rolling upgrade из коробки; свой chart — только если команда готова поддерживать Patroni/sidecar вручную. Сверьте SLA и опыт команды. Подробнее здесь — Kubernetes и облако.

Вопрос. effective_cache_size и work_mem — как связаны с планом запроса?

Ответ. effective_cache_size влияет на оценку index vs seq scan; work_mem — лимит sort/hash на операцию (умножается на узлы плана). Завышение work_mem при параллелизме ведёт к OOM. Подробнее здесь — конфигурация, оптимизация.

Вопрос. partial index PostgreSQL B-tree — пример когда он эффективнее полного?

Ответ. Индекс с WHERE status = 'active' на таблице, где 95% строк archived — меньше размер и быстрее hot subset. Запрос должен повторять предикат индекса в WHERE. Подробнее здесь — оптимизация и индексы.

Вопрос. replication slot PostgreSQL — зачем нужен и когда удалять?

Ответ. Слот удерживает WAL для подписчика (standby, logical replication, CDC). Мёртвый consumer без чтения забивает диск — мониторьте pg_replication_slots и lag. Подробнее здесь — репликация.

Вопрос. Ansible role PostgreSQL — что автоматизировать, а что оставить ручным?

Ответ. В Ansible: пакеты, postgresql.conf, pg_hba, extensions, reload/restart handlers. Terraform держит облачный инстанс; major upgrade и PITR restore — отдельный runbook. Подробнее здесь — Ansible и Terraform.

---

Резюме раздела

Внутреннее устройство — MVCC хранит версии строк с xmin/xmax; VACUUM и autovacuum убирают мёртвые версии и защищают от wraparound XID. WAL + shared buffers + checkpointer/bgwriter обеспечивают durability и crash recovery.

Оптимизация — EXPLAIN (ANALYZE, BUFFERS) показывает реальный план; индексы подбирают под запрос (B-tree, GIN для JSONB, GiST для гео, BRIN для time-series, partial для hot subset).

Конфигурация — shared_buffers, work_mem, effective_cache_size, random_page_cost, autovacuum и WAL-параметры настраивают под железо и профиль нагрузки.

Расширения SQL — JSONB для документов, declarative partitioning для жизненного цикла данных; оконные функции и CTE — в 3.07.

Серверная логика — PL/pgSQL, триггеры, event triggers, NOTIFY/LISTEN для аудита и лёгких событий.

Масштабирование — streaming replication, logical replication, Hot Standby для чтения, PgBouncer для пулинга тяжёлых backend-процессов.

Контейнеры и K8s — volume обязателен; production HA — операторы (Patroni, CloudNativePG) или managed-сервисы.

Кластеры — Patroni для OLTP HA; Greenplum и CockroachDB — другие классы задач (MPP analytics vs distributed SQL).

Бэкапы — pg_dump для логики, physical + WAL + Wal-G/pg_probackup для PITR; restore тестируют по расписанию.

Наблюдаемость — pg_stat_statements, auto_explain, pgBadger, Prometheus.

IaC — Ansible для VM, Terraform для managed DB и облачной инфраструктуры.

---

Ключевые термины

Термин	Определение
MVCC	Многоверсионность — читатели не блокируют писателей
Bloat	Раздувание таблицы мёртвыми версиями строк
Wraparound	Исчерпание 32-bit XID без freeze
PITR	Восстановление на точку во времени по WAL
Hot Standby	Read-only запросы на replica
Patroni	HA-оркестратор failover для Postgres

---

Маршрут по статьям

1. Архитектура — MVCC, VACUUM, WAL
2. Оптимизация — EXPLAIN, индексы
3. Конфигурация — postgresql.conf
4. JSONB и партиции
5. PL/pgSQL и NOTIFY
6. Репликация и PgBouncer
7. Docker
8. Kubernetes и облако
9. HA-кластеры
10. Бэкапы
11. Мониторинг
12. Ansible и Terraform
13. Чек-лист

---

Следующие шаги

• Углубление SQL — 3.07, 888.
• Веб-клиент на PHP-стеке — phpPgAdmin.
• Admin reference — 3.08/2.
• Интеграция в микросервисы — 8.05/1191 Kafka + PostgreSQL.

---