PostgreSQL
Безопасные миграции PostgreSQL
GitHub 2014. Таблица `pull_requests` - 20M строк. Нужно добавить индекс для нового фильтра. `CREATE INDEX` без CONCURRENTLY - 8 минут блокировки на production. 8 минут когда тысячи разработчиков не могут создать pull request. После этого инцидента GitHub опубликовал руководство 'How we made github fast' - и CONCURRENTLY стал стандартом индустрии.
- **GitHub** - публичное руководство по zero-downtime миграциям: expand-contract для переименований, CONCURRENTLY для индексов, батчинг для backfills. Стало де-факто стандартом
- **Shopify** - railsconf 2019: Large-Scale Migrations at Shopify. Инструмент LHM (Large Hadron Migrator) для ALTER TABLE без downtime на таблицах 100B+ строк
- **Stripe** - строгое правило: никакой миграции без rollback плана. Expand-contract для всех изменений схемы. Staging с production volume обязателен перед применением
CREATE INDEX CONCURRENTLY: без блокировки
**CREATE INDEX** без CONCURRENTLY берёт SHARE lock - блокирует INSERT/UPDATE/DELETE на время создания (минуты на большой таблице). **CREATE INDEX CONCURRENTLY** строит индекс в несколько проходов без блокировки DML. Дольше, но production продолжает работу.
**CREATE INDEX CONCURRENTLY нельзя в транзакции.** При прерывании оставляет INVALID индекс, который замедляет запросы но не используется планировщиком. Мониторинг indisvalid обязателен после миграций.
CREATE INDEX CONCURRENTLY на таблице 500M строк упал через 30 минут из-за ошибки. Какое состояние индекса?
Безопасный ALTER TABLE: минимальные блокировки
**ALTER TABLE берёт ACCESS EXCLUSIVE lock** - блокирует все запросы на время выполнения. Некоторые операции выполняются мгновенно (metadata change), другие - требуют переписи всей таблицы (часы на больших таблицах). Знать разницу критично для zero-downtime деплоя.
**PostgreSQL 12+**: `ADD COLUMN column_name TYPE DEFAULT value` мгновенно даже для ненулевых defaults - значение хранится в метаданных, не переписывается в каждой строке. До PostgreSQL 11 это требовало полной перезаписи таблицы.
Нужно добавить CHECK (amount > 0) на таблицу 200M строк без downtime. Какая проблема с обычным ADD CONSTRAINT?
Expand-Contract: переименование колонок без downtime
**Expand-Contract** - паттерн для изменения схемы без downtime. Применяется когда нужно переименовать колонку, изменить тип, или перенести данные. Суть: сначала расширить схему (expand), затем мигрировать данные, затем убрать старое (contract).
**Backfill батчами** критичен: UPDATE всей таблицы 200M строк = одна огромная транзакция, lock на минуты, bloat от dead tuples. Батч по 10K строк с паузой между батчами - autovacuum успевает чистить, lock минимален, production не ощущает.
Backfill 100M строк одним UPDATE без батчей. Что произойдёт с производительностью таблицы после?
pg_repack: дефрагментация без блокировки
**pg_repack** - расширение PostgreSQL для дефрагментации (repack) таблиц и индексов без длительной блокировки. Аналог VACUUM FULL, но держит ACCESS EXCLUSIVE lock только в самом конце (секунды). Создаёт новую таблицу, копирует данные, атомарно заменяет.
**Когда нужен pg_repack:** bloat > 30-40% таблицы после массовых DELETE/UPDATE, индексы сильно раздулись (REINDEX CONCURRENTLY альтернатива для индексов). Cloudflare использует pg_repack для weekly maintenance на таблицах с высоким UPDATE rate.
pg_repack работает на таблице 500 ГБ. Production запросы блокируются?
Инструменты миграций: Flyway, Liquibase, sqitch
**Инструменты миграций** управляют историей изменений схемы. Каждая миграция - версионированный SQL файл, применяется один раз в нужном порядке. Таблица migration history фиксирует что уже применено. Rollback миграций - отдельная стратегия.
| Инструмент | Язык | Особенности |
|---|---|---|
| Flyway | Java (CLI доступен) | Простой, version-based, широкая поддержка |
| Liquibase | Java | XML/YAML/SQL форматы, rollback из коробки, changelog |
| sqitch | Perl/Any | Git-like, deploy/revert/verify, no version numbers |
| golang-migrate | Go | Легковесный, CLI + library, embed support |
ALTER TABLE быстрый - это просто изменение метаданных
Некоторые ALTER TABLE мгновенны (добавление nullable колонки, DROP COLUMN), другие переписывают всю таблицу (изменение типа, добавление NOT NULL с проверкой). На таблице 100M строк перепись = часы блокировки
PostgreSQL должен проверить или переписать каждую строку при некоторых изменениях. Изменение типа INT на BIGINT требует проверки каждого значения. Добавление NOT NULL - проверки наличия значений. PostgreSQL 12+ оптимизировал ADD COLUMN DEFAULT - теперь мгновенно. Знание какие операции мгновенны - критичный навык для zero-downtime деплоя
Flyway migration V5 содержит CREATE INDEX CONCURRENTLY. Flyway оборачивает каждую миграцию в транзакцию. Что произойдёт?
Итоги
- **CREATE INDEX CONCURRENTLY** - всегда для production. Прерванный CONCURRENTLY = INVALID индекс, нужен DROP + rebuild
- **ALTER TABLE** - проверить перед применением: ADD COLUMN nullable мгновенно, изменение типа = полная перепись
- **NOT VALID + VALIDATE CONSTRAINT** - безопасное добавление CHECK без полного scan под ACCESS EXCLUSIVE
- **Expand-Contract** - паттерн для zero-downtime переименований и изменений типа: add new → sync trigger → backfill → switch app → drop old
- **Backfill батчами** по 10K строк с паузами - autovacuum успевает, lock минимален, production не замечает
Связанные темы
Миграции затрагивают несколько ключевых аспектов PostgreSQL:
- Блокировки (Locks) — Понимание lock levels (ACCESS EXCLUSIVE vs SHARE UPDATE EXCLUSIVE) - основа выбора безопасной стратегии миграции
- MVCC — Батчинг UPDATE важен из-за MVCC dead tuples: каждое UPDATE создаёт мёртвую версию строки
- Обновление PostgreSQL — Zero-downtime major upgrade через logical replication - применяет те же принципы expand-contract на уровне всей БД
Вопросы для размышления
- Таблица `transactions` имеет колонку `amount NUMERIC(10,2)`. Нужно изменить на `NUMERIC(15,4)`. Как это сделать без downtime? Что произойдёт если просто сделать `ALTER TABLE transactions ALTER COLUMN amount TYPE NUMERIC(15,4)`?
- Backfill 50M строк: разработчик написал цикл UPDATE по 10K строк. После 1000 итераций (10M строк) pg_stat_user_tables показывает n_dead_tup = 9M. Autovacuum запущен. Нужно ли останавливать backfill?
- Migration V10 создаёт индекс CONCURRENTLY и добавляет CHECK constraint без NOT VALID. Flyway выполняет всё в одной транзакции. Что сломается и как исправить структуру миграции?