Какие production-риски есть у PostgreSQL: блокировки, bloat, replication lag, backup/restore, schema changes или плохие запросы?

Обзор production-рисков PostgreSQL

PostgreSQL — надёжная СУБД, но в production она генерирует специфические классы аварий, каждый из которых требует отдельной системы предотвращения и реагирования.

Блокировки (Locks)

PostgreSQL использует многоуровневую систему блокировок. Самые опасные — ACCESS EXCLUSIVE (DDL-операции) и длинные транзакции, которые держат строчные блокировки.

-- Найти ожидающие и блокирующие запросы
SELECT
  blocked.pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocked.wait_event_type,
  blocked.wait_event
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

-- Превентивные меры
ALTER SYSTEM SET lock_timeout = '5s';
ALTER SYSTEM SET deadlock_timeout = '1s';
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
SELECT pg_reload_conf();

Table Bloat и Index Bloat

Dead tuples накапливаются быстрее, чем autovacuum успевает их убирать. Это особенно критично для таблиц с высоким UPDATE-трафиком (например, очереди задач, счётчики).

-- Оценка bloat по таблицам
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  n_dead_tup,
  n_live_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Для устранения bloat без блокировок используйте pg_repack: он переписывает таблицу в фоне с минимальным lock в конце.

Replication Lag

Отставание реплики критично при failover (потеря данных) и при использовании реплики как read-only (грязные чтения).

-- На primary: lag всех реплик в байтах и секундах
SELECT
  application_name,
  state,
  pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag_bytes,
  replay_lag
FROM pg_stat_replication;

-- На реплике
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

Причины лага: большие транзакции, медленный I/O на реплике, сеть, replication slots без consumer. Репликационные слоты без активного подписчика блокируют очистку WAL — это приводит к переполнению диска.

Backup / Restore

Две принципиально разные стратегии: pg_dump (логический, не требует downtime, но медленный restore) и base backup + WAL архив (PITR, позволяет восстановить на любой момент времени).

# pg_dump — снапшот без блокировки таблиц
pg_dump -Fc -d mydb -f mydb_$(date +%Y%m%d).dump

# pg_basebackup — физический снапшот
pg_basebackup -h localhost -U replicator -D /backup/base -Ft -z -P

# Проверка: restore ОБЯЗАТЕЛЕН, иначе backup не считается валидным
pg_restore -d mydb_test mydb_$(date +%Y%m%d).dump

Schema Changes на живой таблице

ALTER TABLE ADD COLUMN без DEFAULT безопасна с PG 11+. Но добавление DEFAULT или NOT NULL, создание индексов без CONCURRENTLY, добавление FK с VALIDATE — всё это берёт долгий lock.

-- Безопасное добавление индекса
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Безопасное добавление FK без долгой блокировки
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;  -- не проверяет существующие строки

ALTER TABLE orders VALIDATE CONSTRAINT fk_user;  -- отдельно, с ShareLock

Плохие запросы

-- Включить pg_stat_statements (один раз)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Топ-10 запросов по суммарному времени
SELECT
  LEFT(query, 100) AS query_snippet,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

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

• Replication slot без consumer — блокирует удаление WAL-сегментов и может заполнить диск на 100%, что убивает весь кластер. Мониторьте pg_replication_slots и удаляйте брошенные слоты.
• VACUUM FULL в пиковые часы — берёт ACCESS EXCLUSIVE lock, блокирует всех пользователей. Планируйте в окна обслуживания или используйте pg_repack.
• Отсутствие тестового restore — pg_dump может завершиться без ошибок, но содержать повреждённые данные. Автоматизируйте restore в тестовую среду ежесуточно.
• Долгие транзакции во время DDL — ALTER TABLE ждёт всех активных транзакций и потом блокирует все новые. В busy системе это может держать lock минутами.
• Seq Scan на больших таблицах без индекса убивает I/O. Используйте EXPLAIN (ANALYZE, BUFFERS), а не просто EXPLAIN.
• Connection storm при restart — все клиенты одновременно пытаются переподключиться. Используйте PgBouncer с retry logic на стороне клиента.
• Checkpoint pressure — если bgwriter не справляется, WAL checkpoint случается слишком часто и создаёт I/O пики. Настройте checkpoint_completion_target = 0.9 и max_wal_size.

• Говорить только о запуске PostgreSQL, но не об эксплуатации
• Не упоминать observability, обновления, безопасность и rollback
• Описывать риски абстрактно, без способов их снижать

• Видит production-риски PostgreSQL
• Говорит про monitoring, rollout, rollback и безопасность
• Умеет ранжировать риски по вероятности и влиянию