Базы данных
PostgreSQL: архитектура и ключевые фичи
В 2012 году Instagram обслуживал 30 миллионов пользователей на MySQL. За один выходной команда переехала на PostgreSQL - без даунтайма, без потери данных. Секрет: PostgreSQL позволяет читать старую версию данных пока новая версия записывается. Два пользователя, два состояния базы одновременно - без единой блокировки.
- **Instagram 2012**: миграция MySQL -> PostgreSQL за выходные благодаря MVCC - читатели не блокируют писателей
- **Apple, Reddit, Twitch, Discord**: PostgreSQL в продакшне при нагрузках от 100K до 10M запросов в секунду
- **pgvector**: встроенный ANN-поиск в PostgreSQL - embeddings и RAG без отдельного Qdrant или Pinecone
- **Logical replication через WAL**: основа для zero-downtime миграций и event sourcing в системах с высоким SLA
MVCC и WAL: как PostgreSQL читает и пишет одновременно
2012 год. Команда Instagram переезжает с MySQL на PostgreSQL за один выходной - без даунтайма, без потери единого байта. Секрет не в скорости миграции и не в умелых руках инженеров. Секрет - в том, что PostgreSQL может читать и писать одновременно без блокировок читателей. MySQL InnoDB тоже поддерживает MVCC, но PostgreSQL строит его иначе - и разница в архитектуре решила исход для Instagram.
MVCC (Multi-Version Concurrency Control) - это принцип: вместо того чтобы блокировать строку при записи, PostgreSQL создаёт новую версию строки. Каждая транзакция видит **снимок** базы на момент своего старта. Писатель не мешает читателям. Читатели не мешают писателям. SELECT никогда не ждёт UPDATE.
WAL - Write-Ahead Log - второй кит архитектуры. Перед тем как изменить данные на диске, PostgreSQL записывает описание этого изменения в лог. Сервер упал - при рестарте WAL восстанавливает состояние. Это физическая реализация Durability из ACID: данные считаются сохранёнными в момент записи в WAL на диск, а не в момент обновления heap-файлов.
WAL - это не только для аварийного восстановления. Logical Replication в PostgreSQL использует WAL-stream для репликации между серверами. pgvector, timescaledb, citus - все читают WAL. Это основа экосистемы.
Транзакция A стартовала в момент txid=100. В это время транзакция B (txid=90) ещё не завершилась и обновила строку. Что увидит транзакция A при SELECT?
VACUUM и TOAST: цена версионности
Версионность данных в MVCC имеет цену. Каждый UPDATE создаёт новую версию строки - старая не исчезает мгновенно. Без механизма очистки таблицы раздуваются до бесконечности. Появляется феномен с пугающим названием: **table bloat**. PostgreSQL решает это через VACUUM.
VACUUM сканирует таблицу и помечает мёртвые версии строк как пространство для повторного использования. VACUUM не возвращает место операционной системе - для этого нужен VACUUM FULL (но он блокирует таблицу). Autovacuum - фоновый процесс, запускающийся автоматически по порогу мёртвых строк.
**Transaction ID Wraparound** - одна из редких, но катастрофических проблем PostgreSQL. Счётчик txid 32-битный: при 2 млрд транзакций старые данные становятся 'будущими' и база переходит в read-only. Autovacuum настроен предотвращать это, но перегруженные системы с отставшим VACUUM - реальный production риск. Мониторить: `SELECT max_age FROM pg_database WHERE datname = current_database()`.
TOAST (The Oversized-Attribute Storage Technique) - механизм хранения больших значений. PostgreSQL не умеет хранить в одной строке значение длиннее ~8 KB (размер страницы). Длинные строки, JSON-документы, массивы - всё это прозрачно для пользователя перемещается в отдельную TOAST-таблицу. `pg_toast.pg_toast_XXXXXX` создаётся автоматически для каждой таблицы с потенциально большими колонками.
| Стратегия TOAST | Описание | Когда применяется |
|---|---|---|
| PLAIN | Без TOAST, хранить inline всегда | Числа, boolean, фиксированные типы |
| EXTENDED | Сжатие + внешнее хранение (default) | text, jsonb, bytea - по умолчанию |
| EXTERNAL | Внешнее хранение без сжатия | Когда нужен substring без декомпрессии |
| MAIN | Сжатие, внешнее только в крайнем случае | Хотим inline, но готовы к TOAST |
Почему SELECT * на таблице с jsonb-колонкой медленнее, чем SELECT id, name? TOAST. При SELECT * PostgreSQL вынужден читать TOAST-таблицу для каждой строки с большим значением. SELECT только нужных колонок - это не про трафик, это про I/O и TOAST-деккомпрессию.
После миллиона UPDATE на таблице без ручного VACUUM таблица занимает 10 GB, хотя фактических данных - 500 MB. Это явление называется...
Индексы GiST, GIN, BRIN: за пределами B-tree
B-tree - рабочая лошадка реляционных баз. Но PostgreSQL приходит с арсеналом специализированных индексов, каждый из которых выигрывает B-tree в своей нише на порядки. GIN на полнотекстовом поиске быстрее B-tree в 10-100 раз. BRIN на временных рядах занимает в 1000 раз меньше места при сравнимой скорости. GiST покрывает задачи, для которых у B-tree нет языка вообще.
| Тип индекса | Структура данных | Лучший для | Пример |
|---|---|---|---|
| B-tree | Сбалансированное дерево | =, <, >, BETWEEN, LIKE 'prefix%' | WHERE created_at > '2024-01-01' |
| Hash | Хеш-таблица | Только = (быстрее B-tree при точном равенстве) | WHERE user_id = 42 |
| GiST | Обобщённое дерево поиска | Геометрия, полнотекст, ranges, пересечения | WHERE point <-> '(0,0)' < 10 |
| GIN | Инвертированный индекс | Массивы, jsonb, tsvector, contains | WHERE tags @> '{python}' |
| BRIN | Диапазоны блоков | Огромные таблицы с физическим порядком (time-series) | WHERE event_time BETWEEN ... |
| SP-GiST | Непересекающееся дерево | Квадродеревья, k-d деревья, пространство | Geocoordinates, IP ranges |
Partial indexes - индексы по подмножеству строк. Если 95% запросов идут к активным пользователям, а неактивных в базе 80% - индекс `WHERE is_active = true` в 5 раз меньше и быстрее полного. Expression indexes позволяют индексировать результат функции: `CREATE INDEX ON users (lower(email))` - и `WHERE lower(email) = 'test@example.com'` использует индекс.
pgvector - расширение, добавляющее тип `vector` и индекс HNSW/IVFFlat для поиска ближайших соседей. Это буквально то, на чём строятся RAG-пайплайны: embeddings от OpenAI или sentence-transformers сохраняются в pgvector, и `SELECT ... ORDER BY embedding <-> query_vec LIMIT 10` - это ANN-поиск прямо в PostgreSQL без отдельного Qdrant или Pinecone.
Нужно добавить индекс на каждую колонку, по которой делается WHERE
Индексы ускоряют чтение, но замедляют запись и занимают место. Индексируют по данным о реальных запросах через pg_stat_statements и EXPLAIN ANALYZE
Каждый INSERT/UPDATE/DELETE должен обновить все индексы таблицы. На write-heavy таблице 10 индексов дают 10x overhead на запись. Partial indexes, expression indexes и BRIN часто дают 10x преимущество перед наивным 'индекс на всё'.
Таблица `events` с 500 млн строк, данные вставляются только в хронологическом порядке, запросы всегда по диапазонам дат. Какой индекс оптимален по соотношению размер/скорость?
Ключевые идеи
- **MVCC** даёт читателям снимок на момент старта транзакции через xmin/xmax в каждой строке - SELECT никогда не ждёт UPDATE
- **WAL** записывает изменения до применения: основа Durability, репликации и логического декодирования
- **VACUUM** очищает мёртвые версии строк от MVCC - без него table bloat убивает производительность
- **TOAST** прозрачно переносит большие значения (jsonb, text > 8KB) в отдельную таблицу - не забывать при проектировании схемы
- **GIN/GiST/BRIN** - специализированные индексы: BRIN на 500M строк занимает 1 MB против 10 GB у B-tree
Связанные темы
PostgreSQL - это реализация нескольких фундаментальных концепций одновременно:
- ACID транзакции — WAL реализует Durability, MVCC реализует Isolation
- MVCC в деталях — Механика xmin/xmax, transaction ID, vacuum и bloat
- B-tree индексы — Фундамент для понимания GiST/GIN/BRIN как расширений
- Транзакции и изоляция — Уровни изоляции READ COMMITTED / REPEATABLE READ в PostgreSQL
Вопросы для размышления
- VACUUM FULL возвращает место ОС, но блокирует таблицу. Как организовать очистку на prod без простоя? (подсказка: pg_repack, partitioning)
- Если MVCC создаёт несколько версий строки, как PostgreSQL гарантирует, что две транзакции не запишут разные значения в одну строку одновременно?
- GIN-индекс на jsonb-колонке ускоряет поиск по содержимому, но замедляет INSERT. При каком соотношении read/write GIN перестаёт окупаться?
Связанные уроки
- db-14-mvcc — Глубокий разбор MVCC: xmin/xmax, снимки транзакций
- db-13-transactions — MVCC понятен только через транзакционную модель
- db-15-locks — MVCC заменяет блокировки чтения - сравнение подходов
- db-09-indexes-btree — B-tree - фундамент, GiST/GIN/BRIN - надстройки
- db-03-acid — WAL - физическая реализация Durability и Atomicity
- os-07-memory