Акты 4–6 в глубину: MVCC bloat, connection pooling и безопасные миграции

Nightly report runner держит одну транзакцию открытой четыре часа. Он читает снапшот — никаких writes, ничего вредного. Но VACUUM не может вернуть мёртвые tuples ниже xmin-горизонта. Таблица вздувается с 200 MB до 80 GB. Новые строки не вставлялись. Report runner — единственный виновник.

Акт 4 — MVCC и xmin-горизонт

Каждая строка в Postgres несёт xmin (transaction id создания) и xmax (transaction id удаления). DELETE не освобождает строку — он устанавливает xmax. VACUUM возвращает строки, чей xmax старше глобального xmin-горизонта — самого старого активного transaction ID, видимого по всему кластеру.

Если одна аналитическая сессия оставляет транзакцию открытой на часы, горизонт не продвигается. Ни один мёртвый tuple, созданный после этого горизонта, не может быть возвращён. Таблица вздувается; запросы, сканирующие таблицу, замедляются, потому что читают мёртвые строки вместе с живыми.

Исправление операционное:

1. Убить long transaction (SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND query_start < now() - interval '5 minutes').
2. Установить idle_in_transaction_session_timeout = 60s — любая сессия, удерживающая транзакцию без выполнения запроса 60 секунд, завершается.
3. Запустить pg_repack для возврата диска без AccessExclusiveLock (VACUUM FULL берёт его).
4. Постоянное исправление: перенести отчётность на read replica; xmin-горизонт на replica не блокирует vacuum на primary при hot_standby_feedback = off.

При 1B строк одна idle-сессия может раздуть таблицу на 10% за один день. Bloat замедляет каждый последующий запрос, потому что heap pages содержат мёртвые tuples рядом с живыми, заставляя sequential scanner читать и пропускать их.

Акт 5 — Математика размера connection pool

Каждый Postgres backend — это fork-процесс ОС со своим memory footprint (~5–10 MB RSS). Сервер с 32 GB RAM может поддерживать примерно 2000–3000 backends до того, как kernel scheduler начнёт трасить и latency запросов деградирует.

При 50 app pods по 20 workers каждый cold-start или pod-rollout открывает 1000 backends одновременно. Запросы, ранее занимавшие 4 мс, занимают 4 с не из-за объёма данных, а потому что ОС-scheduler не может эффективно time-slice 1000 процессов.

PgBouncer в transaction-mode:

• 100 server-side backends (вписывается в sweet spot kernel scheduler).
• 10000 client connections мультиплексированы на эти 100 backends.
• Каждый backend используется только во время активной транзакции, затем возвращается в пул.

Математика размера пула:

pool_size = active_concurrent_transactions × safety_factor

Не max_app_workers. Важное число — сколько транзакций одновременно достигает базы, а не общее число workers. Для 16-ядерного SSD-only сервера 30–60 server-side backends обычно sweet spot.

Ловушка с prepared statements в transaction-mode: PgBouncer в transaction-mode перепривязывает соединения после каждой транзакции, так что server-side prepared statements (PREPARE foo AS SELECT ...) теряются между транзакциями. PgBouncer 1.21+ поставляется с server_prepared_statements = on, который кешируёт plan IDs server-side и реплицирует их — без этого app frameworks, автоматически готовящие statements, платят re-prepare overhead при каждой транзакции.

Акт 6 — Lock matrix и expand-contract

ALTER TABLE берёт AccessExclusiveLock по умолчанию — несовместимый со всеми другими режимами блокировки, включая чтения. Пока он ждёт блокировку, каждый новый запрос ждёт позади него. Пока он удерживает блокировку, каждый запрос ждёт.

Короткий ALTER под низкой нагрузкой завершается за миллисекунды. ALTER позади долгого запроса (как в Акте 4) ждёт, строит очередь, и очередь замораживает базу. Одна 60-секундная блокировка на горячей таблице может каскадироваться в 10-минутный инцидент из-за connection timeout и app-level retry storms.

Рецепт expand-contract для безопасного добавления колонки tenant_id BIGINT NOT NULL:

1. ALTER TABLE users ADD COLUMN tenant_id BIGINT — nullable, без default. Нет перезаписи.
2. Backfill: UPDATE users SET tenant_id = 0 WHERE tenant_id IS NULL LIMIT 10000 — батчами, низкое lock pressure.
3. ALTER TABLE users ADD CONSTRAINT chk_tenant_id CHECK (tenant_id IS NOT NULL) NOT VALID — не сканирует таблицу.
4. ALTER TABLE users VALIDATE CONSTRAINT chk_tenant_id — сканирует под ShareUpdateExclusiveLock, совместимым с чтениями и записями.
5. ALTER TABLE users ALTER COLUMN tenant_id SET NOT NULL — только metadata после успешной валидации, миллисекунды.
6. ALTER TABLE users DROP CONSTRAINT chk_tenant_id — очистка.

Инструменты как pgroll и Atlas кодифицируют этот рецепт. Без него наивный ADD COLUMN ... NOT NULL DEFAULT на Postgres 10 и ранее вызывает полную перезапись таблицы; на Postgres 11+ это metadata-change для константных defaults, но блокировка всё равно ставит в очередь все запросы.