PostgreSQL¶

Тип материала: архитектурный разбор. Схемы и примеры иллюстративные, если не указано иное.

Область версий: PostgreSQL 17. Проверка источников: 2026-11-02.

1. Архитектура и модель данных¶

FACT. Реляционный сервер с heap tables, процессами backend, shared buffers и WAL. Владелец записи — primary; read replica не становится независимым источником команд. SQL-транзакция охватывает таблицы одной базы, но отправка Kafka-сообщения за её пределами требует отдельного протокола. Это физическая реализация, совместимая с Event Sourcing и CQRS, а не замена этим паттернам.

flowchart LR
  Client --> Transaction[SQL transaction]
  Transaction --> WAL[WAL flush]
  Transaction --> Heap[Heap and indexes]
  WAL --> Standby[Physical standby]
  WAL --> CDC[Logical decoding]

2. Физическое хранение и путь записи¶

INSERT создаёт tuple в heap и записи WAL. UPDATE обычно создаёт новую версию; старая остаётся для snapshots. Системные xmin/xmax обозначают транзакции создания/удаления или блокировки: xmax не всегда означает уже удалённую строку, а видимость нельзя определять простым сравнением этих двух чисел. HOT update возможен при подходящих индексах и свободном месте на странице; он сокращает обновления индексов, но не отменяет версионность.

VACUUM отмечает доступное место, очищает ненужные версии и поддерживает visibility map; обычный VACUUM обычно не возвращает весь свободный объём ОС. VACUUM FULL переписывает таблицу с сильной блокировкой. Freeze защищает от transaction ID wraparound. Длинная транзакция, старый snapshot или replication slot удерживают горизонт очистки и могут вызвать bloat/рост WAL.

Источники механизма: документация 2, документация 3.

3. Индексы и путь чтения¶

B-tree обслуживает equality/range и сортировку; уникальный индекс обеспечивает уникальность с учётом конкурентных транзакций. BRIN хранит сводки диапазонов страниц: полезен при корреляции времени с физическим порядком, требует recheck и не заменяет точечный B-tree. GIN индексирует составные значения, например элементы JSONB/массивов, с ценой записи и обслуживания pending list. GiST — расширяемый framework, в том числе для spatial/range и exclusion constraints.

SELECT идёт через planner к scan heap/index; index-only scan всё равно проверяет heap, если visibility map не подтверждает all-visible. В EXPLAIN ANALYZE сравнивают estimates и actual rows, loops, buffers, spill и filter selectivity. ANALYZE действительно выполняет запрос, включая изменяющий данные: сначала изолированная среда; rollback не отменяет все внешние эффекты функций.

Источники механизма: документация 6, документация 8.

4. Транзакции и consistency guarantees¶

READ COMMITTED использует snapshot каждого statement; REPEATABLE READ — snapshot транзакции, предотвращает phantom reads в PostgreSQL, но допускает write skew. SERIALIZABLE использует SSI, может завершить транзакцию serialization failure; повторяют всю бизнес-транзакцию с ограниченным backoff. READ UNCOMMITTED работает как READ COMMITTED.

COMMIT при обычном synchronous_commit ждёт локальный WAL flush; синхронная реплика зависит от synchronous_standby_names и режима ожидания. fsync=off и asynchronous commit меняют последствия сбоя. ACID одной базы не даёт exactly-once внешней доставки.

Источники механизма: документация 7.

5. Репликация, partitioning и sharding¶

Streaming replication передаёт WAL и может быть async/sync. Logical replication передаёт выбранные изменения строк; требует replica identity для нужных UPDATE/DELETE, не переносит автоматически DDL и состояние sequences. В PG17 logical slot failover требует специальной настройки и контроля синхронизации, не возникает от наличия standby.

Declarative RANGE/LIST/HASH partitioning — несколько локальных таблиц и pruning, не распределённое sharding. UNIQUE/PRIMARY KEY на partitioned table должен включать все partition keys. Встроенного прозрачного multi-node write sharding нет; расширения и приложение добавляют отдельные гарантии и расходы.

Источники механизма: документация 5, документация 9.

6. Конкурентная запись и конфликты¶

SELECT FOR UPDATE сериализует изменение баланса; блокировать несколько счетов следует в одном порядке. Проверка баланса отдельным SELECT без блокировки и последующий UPDATE дают гонку. Альтернатива — условный UPDATE ... WHERE balance >= amount и проверка числа строк, если весь инвариант действительно локален.

Deadlock возникает при цикле ожидания: сервер прерывает одну транзакцию; клиент повторяет её целиком. Держать row locks во время HTTP-вызова опасно. Уникальный idempotency key должен проверять также неизменность payload: повтор с другой суммой нельзя молча признать успешным.

Источники механизма: документация 10.

7. Отказы и восстановление¶

После crash WAL redo восстанавливает изменения страниц; незакоммиченные версии не становятся видимыми. PITR требует согласованного base backup и непрерывной WAL-цепочки до целевого момента. Реплика не спасает от логически ошибочного DELETE, а slot может заполнить диск primary.

Для failover сначала fencing старого primary, затем promotion, маршрутизация и сверка подтверждённых операций. Потерянный ответ COMMIT — ambiguous outcome: искать idempotency key после восстановления, а не выполнять перевод с новым ключом.

Источники механизма: документация 4, документация 11.

8. Масштабирование и ограничения¶

Scale-up, оптимизация запросов, pooling, read replicas и partition pruning увеличивают доступную ёмкость разными путями. Один горячий счёт остаётся точкой сериализации; больше соединений не делает его параллельным. Sharding по клиенту осложняет переводы между клиентами. Ограничения измеряют на размерах working set, WAL bandwidth, storage latency и lock contention, а не по универсальному TPS.

9. Performance trade-offs¶

Индексы ускоряют чтение ценой WAL, page splits и write amplification. Fillfactor может помочь HOT, но расходует место. Partitioning ускоряет pruning/drop старых диапазонов, но слишком много partitions увеличивает planning/DDL costs. JSONB удобен для переменных атрибутов, однако не заменяет типы и constraints финансового ядра.

10. Мониторинг и эксплуатация¶

Наблюдать pg_stat_activity, pg_locks, pg_stat_statements (расширение), pg_stat_user_tables, pg_stat_wal, pg_replication_slots и pg_stat_replication. Проверять wait events, age(datfrozenxid), dead tuples, autovacuum progress, slot retained WAL, checkpoint pressure, replica replay lag и свободное место. Названия метрик и права зависят от версии.

Runbook: найти удерживающую транзакцию → оценить влияние её отмены → устранить причину → VACUUM/ANALYZE по необходимости; не лечить bloat отключением autovacuum. Restore drill должен проверять бизнес-инварианты, а не только успешный старт сервера.

11. Сценарии FinTech и iGaming¶

INFERENCE. Ledger, settlement state, лимиты и idempotency registry хорошо соответствуют локальным ACID constraints. Financial Ledger показывает две проводки, атомарный balance cache и audit trail. Outbox вставляется в той же транзакции, relay читает committed изменения и доставляет at-least-once; downstream хранит event_id. CDC — наблюдение изменений, а не самостоятельный business event log.

12. Когда не выбирать¶

Не выбирать единственный PostgreSQL primary как универсальное хранилище бесконечного event firehose и full-history OLAP без расчёта retention/IO. Для глобальной записи с независимым прогрессом при сетевом разрыве single-primary модель не подходит. Распределённый ledger требует явной границы счетов, протокола перевода и reconciliation.

13. Вопросы на собеседовании¶

Почему xmin нельзя использовать как вечный business version? XID имеет wraparound/freeze semantics и не является бизнес-последовательностью.

Почему REPEATABLE READ не гарантирует лимит суммы по нескольким строкам? Два snapshot могут обновить разные строки и совместно нарушить предикат; нужны общий lock/constraint или SERIALIZABLE с retry.

Почему outbox может дублировать событие? Relay может отправить его и упасть до фиксации собственного offset; уникальный event_id и идемпотентный consumer закрывают повтор.

14. Практические SQL/config примеры¶

Полный учебный SQL: ledger.sql. Состояние проверки SQL и отсутствие PostgreSQL runtime зафиксированы в отчёте (архив рабочего проекта).

EXPLAIN (ANALYZE, BUFFERS)
SELECT account_id, balance_minor FROM ledger.account
WHERE tenant_id = 1 AND account_id = 1;
SELECT slot_name, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

Запуск EXPLAIN предполагает созданную схему из примера. Отдельное упражнение: удержать snapshot, выполнить много UPDATE в другом соединении и сопоставить dead tuples с VACUUM; затем воспроизвести deadlock двух переводов. Эти конкурентные эксперименты в текущей среде не выполнены.

15. Дополнительные источники¶

Политика версий и ограничений: реестр.

Типичные ошибки проектирования¶

Путать local commit с гарантией failover без потери; удерживать snapshots часами; считать partitioning sharding; выполнять ledger и outbox в разных транзакциях.

Практические упражнения¶

Составьте таблицу исходов: commit применён/ответ потерян/relay повторил событие. Для каждого укажите, какой constraint или retry protocol сохраняет баланс. Полный сценарий — Ledger.