10 критических ошибок эксплуатации PostgreSQL: от autovacuum до ротации WAL
СУБД PostgreSQL по праву считается одной из самых надежных и продвинутых реляционных баз данных с открытым исходным кодом. Однако стандартные настройки PostgreSQL, поставляемые по умолчанию в дистрибутивах Linux, рассчитаны на минимальное потребление ресурсов и тестовые среды. При переходе в реальный продакшен под высокой нагрузкой неочевидные нюансы конфигурации СУБД часто приводят к внезапным авариям, падению производительности и даже к невосполнимой потере данных.
Анализ практических аудитов крупнонагруженных систем позволяет выделить 10 ключевых архитектурных и эксплуатационных ошибок, с которыми систематически сталкиваются команды разработки и эксплуатации.
1. Иллюзия бэкапа: отсутствие регулярной проверки восстановления
Самая опасная ошибка эксплуатации — считать регулярное выполнение pg_dump или pg_basebackup гарантией сохранности данных. Бэкап считается существующим только тогда, когда процедура его восстановления (Point-in-Time Recovery, PITR) регулярно проверяется на изолированном тестовом стенде. Регулярно встречаются ситуации, когда файл бэкапа оказывается повреждён, содержит неполные бинарные файлы или разворачивается с критическими ошибками расхождения схем.
2. Неконтролируемое накопление WAL-логов и аварии дисковой подсистемы
Журнал предзаписи (Write-Ahead Log, WAL) обеспечивает отказоустойчивость СУБД. Однако если в системе настроена архивация WAL (archive_command), но сетевое хранилище недоступно, либо сломан репликационный слот (replication slot), PostgreSQL будет бессрочно копить WAL-файлы в каталоге pg_wal. В результате дисковый раздел мгновенно переполняется (100% disk space), и СУБД экстренно аварийно завершает работу (Emergency Shutdown).
3. Стандартный autovacuum на таблицах с десятками миллионов строк
Благодаря архитектуре MVCC (Multiversion Concurrency Control) при операции UPDATE или DELETE PostgreSQL не удаляет старые версии строк физически, а лишь помещает их в категорию «мертвых» туплов (dead tuples). Процесс autovacuum обязан своевременно очищать их.
Дефолтные настройки autovacuum_vacuum_scale_factor = 0.2 означают, что очистка таблицы начнётся только после изменения 20% её строк. Для таблицы на 50 миллионов записей это требует изменения 10 миллионов строк! В результате таблица катастрофически разрастается (bloat), индексы теряют компактность, а дисковый ввод-вывод деградирует.
4. Прямые подключения приложений без пулеров соединений
Каждое новое клиентское подключение к PostgreSQL порождает отдельный процесс ОС (postgres: user db host). Этот процесс расходует от нескольких мегабайт оперативной памяти на контекст сокета и служебные структуры work_mem. Создание сотен прямых бэкенд-подключений без промежуточного пулера (PgBouncer) приводит к исчерпанию RAM и непрерывному контекстному переключению процессора.
5. Игнорирование риска Transaction ID Wraparound
Каждая транзакция в PostgreSQL получает 32-битный идентификатор (XID). Так как 32-битное число ограничено 4.2 миллиардами значений, при достижении порога в 2 миллиарда транзакций СУБД обязана провести «заморозку» (freeze) старых транзакций. Если autovacuum freeze не успевает обрабатывать базу, PostgreSQL переходит в режим защиты от закольцовывания XID и полностью прекращает приём любых транзакций на запись.
6. Длительные зависшие транзакции в статусе idle in transaction
Сессия бэкенда, открывшая транзакцию (BEGIN) и уснувшая без вызова COMMIT или ROLLBACK, удерживает минимальный XID базы. Это блокирует работу autovacuum для всей таблицы: очистка не может удалить мертвые строки, созданные после начала самой старой зависшей транзакции.
7. Отсутствие индексов на внешних ключах (Foreign Keys)
В отличие от некоторых СУБД, PostgreSQL не создаёт индексы на столбцы FOREIGN KEY автоматически. При удалении или изменении строки в родительской таблице PostgreSQL вынужден делать полное сканирование (Seq Scan) всей дочерней таблицы для проверки каскадных ограничений, что вызывает мертвые блокировки (deadlocks).
8. Неиспользуемые и раздутые btree-индексы
Создание десятков дублирующих или неиспользуемых индексов замедляет любую операцию INSERT и UPDATE. Каждый индекс должен регулярно проверяться на предмет использования через системные представления pg_stat_user_indexes.
9. Ненастроенные параметры памяти shared_buffers и work_mem
Использование дефолтных shared_buffers = 128MB на сервере с 64 ГБ RAM лишает СУБД возможности эффективного кэширования страниц данных в оперативной памяти.
10. Ручные правок схемы в продакшене без инструментов миграций
Выполнение ручных DDL-запросов (ALTER TABLE) на живой базе без выставления параметров lock_timeout может заблокировать все читающие и пишущие запросы к таблице на время ожидания эксклюзивной блокировки AccessExclusiveLock.
Набор SQL-запросов для оперативного аудита СУБД
Для регулярной проверки состояния базы рекомендуется запускать следующие диагностические запросы:
-- 1. Проверка процента мертвых строк (bloat) в таблицах
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_percent DESC;
-- 2. Поиск зависших транзакций в статусе idle in transaction
SELECT pid, usename, state, age(clock_timestamp(), query_start) AS duration, query
FROM pg_stat_activity
WHERE state LIKE '%idle in transaction%'
ORDER BY duration DESC;
-- 3. Проверка приближения к Transaction ID Wraparound (2 млрд XID)
SELECT datname, age(datfrozenxid) AS xid_age,
round(age(datfrozenxid) * 100.0 / 2000000000, 2) AS wraparound_percent
FROM pg_database
ORDER BY xid_age DESC;
Чек-лист безопасной настройки PostgreSQL
-- Тюнинг параметров очистки для крупных таблиц
ALTER TABLE large_table SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_cost_limit = 1000
);
-- Ограничение времени жизни зависших транзакций в postgresql.conf
idle_in_transaction_session_timeout = '60s'
statement_timeout = '30s'
lock_timeout = '5s'
Обязательно установите PgBouncer в режиме transaction pooling, настройте алерты Prometheus на рост pg_stat_activity и задержку репликации, а также автоматизируйте тестовое разворачивание бэкапов.