MVCC в PostgreSQL

Почему Uber перешёл на MySQL и что с этим делать

PostgreSQL — отличная СУБД. Но её реализация MVCC (Multi-Version Concurrency Control) имеет известные ограничения по сравнению с другими популярными реляционными базами. Это подтверждается исследованиями CMU и практическим опытом компаний вроде Uber.

На основе исследования CMU (VLDB 2018) и статьи OtterTune.
Что такое MVCC Как это делает PostgreSQL Четыре проблемы Сравнение с MySQL и Oracle Что делать

Что такое MVCC

MVCC позволяет нескольким транзакциям одновременно читать и писать данные без блокировок. Основная идея: вместо перезаписи строк СУБД создаёт новые версии.

Концепция описана в диссертации David Reed (MIT, 1978). Первая коммерческая реализация — InterBase в 1980-х. Сегодня MVCC используют практически все транзакционные СУБД.

Три ключевых решения при реализации MVCC

  1. Как хранить обновления — копировать строку целиком или только изменения (delta)
  2. Как находить нужную версию — порядок цепочки версий
  3. Как удалять старые версии — когда и как очищать

PostgreSQL принял решения по первому пункту в 1980-х, и это создало проблемы с остальными двумя, с которыми приходится считаться до сих пор.

Как это делает PostgreSQL

Append-only storage

При UPDATE PostgreSQL копирует всю строку в новое место и применяет изменения к копии. Оригинал остаётся на месте. 

-- Изменяем один столбец
UPDATE movies SET year = 1983 WHERE name = 'Shaolin and Wu Tang';

-- PostgreSQL:
-- 1. Находит строку в Table Page #1
-- 2. Копирует ВСЮ строку в Table Page #2
-- 3. Применяет изменение year к новой копии
-- 4. Старая версия остаётся в Table Page #1

Version chain (O2N)

Версии связаны в цепочку. PostgreSQL использует порядок oldest-to-newest (O2N): старая версия указывает на новую.

Важно: PostgreSQL — единственная популярная СУБД с O2N порядком. Это архитектурное решение 1980-х годов.

HOT updates

Оптимизация: если новая версия помещается на ту же страницу и не затрагивает индексированные столбцы, PostgreSQL не обновляет индексы (Heap-Only Tuple). Это значительно снижает write amplification для подходящих случаев.

По данным OtterTune (анализ ~100 тысяч реальных PostgreSQL-инстансов), только ~46% обновлений используют HOT. Остальные 54% платят полную цену. Причины:

Четыре проблемы

Проблема #1: Копирование версий

PostgreSQL копирует всю строку при каждом UPDATE, даже если изменился один столбец.

СУБД Подход UPDATE 1 столбца из 1000
PostgreSQL Append-only (полная копия) Копирует все 1000 столбцов
MySQL, Oracle Delta versions Сохраняет только изменение 1 столбца

Результат: PostgreSQL требует больше памяти и диска для хранения той же базы.

Проблема #2: Table bloat

Мёртвые строки (dead tuples) занимают место до очистки vacuum. При интенсивной записи они накапливаются быстрее, чем vacuum успевает убирать.

Пример: таблица с 10M живых строк и 40M мёртвых (80% — мусор):

Даже после очистки VACUUM не возвращает место ОС. Только VACUUM FULL или pg_repack реально уменьшают размер файлов — но они блокируют таблицу.

Проблема #3: Write amplification индексов

При каждом UPDATE (не HOT) PostgreSQL обновляет все индексы таблицы, даже те, которые не связаны с изменёнными столбцами. 

-- Таблица movies с индексами:
-- - movies_pkey (PRIMARY KEY на id)
-- - idx_name (на name)
-- - idx_director (на director)

UPDATE movies SET year = 1983 WHERE id = 1;
-- PostgreSQL обновляет ВСЕ ТРИ индекса,
-- хотя year не индексирован
Uber case: это главная причина, почему Uber перешёл с PostgreSQL на MySQL в 2016 году. Их write-heavy нагрузка с множеством вторичных индексов упиралась в эту проблему.

Почему MySQL и Oracle не страдают: их вторичные индексы хранят не физический адрес, а логический идентификатор (primary key). При UPDATE не нужно обновлять вторичные индексы.

Проблема #4: Управление Vacuum

Производительность PostgreSQL критически зависит от autovacuum. Но настроить его правильно сложно:

Сравнение с MySQL и Oracle

Аспект PostgreSQL MySQL/Oracle
Хранение версий Append-only (полная копия) Delta versions (только изменения)
Порядок цепочки O2N (oldest-to-newest) N2O (newest-to-oldest)
Вторичные индексы Физический адрес (обновляются при UPDATE) Логический ID (не обновляются)
Очистка Autovacuum (отдельный процесс) Встроенная в транзакции
Table bloat Серьёзная проблема Минимальная

Что делать

Мониторинг

-- Dead tuples по таблицам
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2) as dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Когда последний раз был vacuum
SELECT relname, last_vacuum, last_autovacuum, last_analyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;

Настройка autovacuum для больших таблиц

-- Для таблицы с 100M строк: vacuum после 1M изменений (1%)
ALTER TABLE large_table SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

-- Или абсолютный порог
ALTER TABLE large_table SET (
  autovacuum_vacuum_threshold = 1000000
);

Минимизация индексов

HOT updates

ALTER TABLE frequently_updated SET (fillfactor = 80);

Регулярное обслуживание

Что улучшается в новых версиях

Сообщество PostgreSQL активно работает над улучшением MVCC:

Итог: PostgreSQL остаётся отличной СУБД. Понимание особенностей MVCC помогает принимать взвешенные решения и избегать неприятных сюрпризов на продакшене с высокой нагрузкой на запись.
Связанные материалы: