Модуль 3. Расширения и плагины PostgreSQL в распределённых системах ⭐

Цель модуля: систематизировать экосистему расширений PostgreSQL для распределённых систем — чем реально пользуются в проде, а что осталось в 2015-м. Каждый плагин разбираем по одной схеме: роль → когда брать → подводные камни → Go-интеграция. Две лабораторные (Citus-кластер, Patroni failover с живым CDC-слотом) и задел Debezium-цепочки, на которой строится вся вторая половина курса.

Перед началом: Модуль 2 — без внутренностей (MVCC, блокировки, пулы) вердикты этого модуля не понять. Инструменты: docker-compose курса; плагины ставим из специализированных образов (citusdata/citus, spilo, timescale/timescaledb) — обычный postgres:16 их не содержит. Трудоёмкость: ~10–12 ч (теория 4 ч · практика 5–6 ч · квиз 1 ч).

🔥 Инцидент: реплика, которая молча отстала

Read-реплика в OrderFlow держит историю оператора: список заказов клиента, статусы, отмены. Приложение читает оттуда, чтобы не грузить мастер. И вот утром оператор открывает историю и видит как активный заказ, который час назад был отменён. Ещё несколько обращений — и в поддержке тикеты про «воскресшие» заказы, которые на самом деле давно закрыты.

Разбор показал неприятное: реплика отставала на 40 минут. На replay_lag никто не смотрел — метрику не собирали, алертов на отставание репликации не было, и дашборды всё это время выглядели зелёными. Ночью по мастеру прошёл тяжёлый запрос, WAL пошёл быстрее, чем standby успевал его накатывать, — и отставание тихо накопилось.

Сначала реплику просто перезапустили: она догнала, и всё выглядело решённым. Но через неделю отставание повторилось, а вместе с ним и тикеты: алертов по-прежнему не было, и заметили проблему опять по жалобе. В разборе выяснилось, что ручной failover, если бы мастер упал в тот момент, занял бы ещё час: процедура не отрепетирована, слот репликации отстал, непонятно, кому переключать. Реплика оказалась не «ещё одной копией», а отдельной системой со своей задержкой, конфликтами и метриками.

В модуле разберём, как PostgreSQL живёт в распределённой конфигурации: физическую и логическую репликацию, Patroni и настоящий failover, CDC через Debezium — задел для второй половины курса, — а также наблюдаемость и аудит продакшена.

📖 3.1. Шардирование и горизонтальное масштабирование

Сначала партиции, потом шарды

Прежде чем делить данные между узлами, делите их внутри узла. Декларативное партиционирование (PG10+) решает 80% задач «таблица стала огромной»: vacuum и индексы работают по кусочкам, старые данные отсоединяются мгновенно (DETACH вместо миллионного DELETE), планировщик отсекает чужие партиции (partition prune). Вручную партиции заводить скучно и ошибкоопасно — этим занимается pg_partman.

pg_partman: автоматизация партиций и retention

sql
CREATE EXTENSION pg_partman;

-- Ежедневные партиции по created_at
SELECT partman.create_parent(
  p_parent_table := 'public.events',
  p_control      := 'created_at',
  p_interval     := '1 day'
);

-- Retention: держим 3 месяца, старое — отсоединять и удалять
UPDATE partman.part_config
SET retention = '3 months', retention_keep_table = false
WHERE parent_table = 'public.events';

Плановое обслуживание (создание будущих партиций, отсечение старых) — вызов CALL partman.run_maintenance_proc(); по расписанию. Расписание ставит pg_cron (урок 3.4) — связка «pg_partman + pg_cron» это стандарт де-факто.

Подводные камни, о которых узнают поздно:

Уникальные ключи и PK обязаны включать ключ партиционирования. PRIMARY KEY (id) на партициях по created_at создать нельзя — только PRIMARY KEY (id, created_at). Это меняет дизайн приложений и FK.
Перемещение строки между партициями (обновили created_at за границу) — это DELETE + INSERT с триггерными сюрпризами; не делайте так.
Слишком мелкие партиции (минуты) — планировщик тонет в планировании, а vacuum не успевает. Час/день/месяц — разумный диапазон.

Citus: шардированный Postgres

Citus — open-source расширение, превращающее кластер PostgreSQL в распределённую СУБД: координатор парсит и планирует запросы, воркеры хранят шарды. Ядро Azure Cosmos DB for PostgreSQL — главный ответ рынка на вопрос «нужен шардированный Postgres» (с 2023 открыт и развивается как расширение).

Три типа таблиц:

Тип	Что делает	Для чего
distributed table	делится на шарды по ключу распределения	заказы, события, платежи
reference table	копируется целиком на каждый воркер	справочники: товары, категории, курсы валют
local table	обычная таблица на координаторе	админские сущности, мелочь

sql
CREATE EXTENSION citus;

-- регистрируем воркеры (из координатора)
SELECT citus_add_node('worker1', 5432);
SELECT citus_add_node('worker2', 5432);

-- таблицы фактов: ключ распределения выбирается ОДИН РАЗ и навсегда
SELECT create_distributed_table('orders', 'customer_id');
SELECT create_distributed_table('order_items', 'customer_id');  -- та же колокация!

-- справочник: на все воркеры
SELECT create_reference_table('products');

-- что получилось
SELECT table_name, distribution_column, table_type FROM citus_tables;

Запрос с фильтром по ключу распределения выполняется pushdown'ом — на одном шарде (single-shard routing):

text
EXPLAIN SELECT count(*) FROM orders WHERE customer_id = 42;
-- Custom Scan (Citus Adaptive)
--   -> Task Count: 1        <-- один шард, никакого scatter-gather
--      -> Index Scan using orders_customer_id_idx ...

Джойны distributed-таблиц с одним ключом распределения (как orders и order_items выше) тоже выполняются локально на воркерах — это называется colocation и это главная архитектурная фича Citus. Всё, что не легло на ключ (JOIN ON o.region_id = r.id без общего ключа), превращается в scatter-gather с пересылкой промежуточных данных.

Подводные камни:

Ключ распределения — решение навсегда. Сменить = перезалить таблицу. Выбирайте то, что встречается в большинстве WHERE и JOIN (у нас — customer_id, а не id заказа!).
Глобальные уникальные ограничения на distributed-таблице работают только с ключом распределения в составе.
Часть операций (DDL, some extensions) живут на координаторе: он — и точка масштабирования планирования, и точка отказа; в проде — HA-координатор.

🧪 Лабораторная 1: Citus — координатор + 2 воркера

yaml
# docker-compose.citus.yml
services:
  coordinator:
    image: citusdata/citus:13.0
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: course
    ports: ["5432:5432"]
  worker1:
    image: citusdata/citus:13.0
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: course
  worker2:
    image: citusdata/citus:13.0
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: course

bash
docker compose -f docker-compose.citus.yml up -d
psql "postgres://postgres:course@localhost:5432/postgres"

sql
CREATE EXTENSION IF NOT EXISTS citus;
SELECT citus_add_node('worker1', 5432);
SELECT citus_add_node('worker2', 5432);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY,
  customer_id bigint NOT NULL,
  status      text NOT NULL DEFAULT 'new',
  total       numeric(12,2) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id, customer_id)          -- PK обязан содержать ключ!
);
SELECT create_distributed_table('orders', 'customer_id');

INSERT INTO orders (customer_id, status, total)
SELECT (g % 1000), 'paid', g FROM generate_series(1, 1000000) g;

-- сравните планы:
EXPLAIN SELECT count(*) FROM orders WHERE customer_id = 42;  -- 1 шард
EXPLAIN SELECT count(*) FROM orders;                         -- scatter-gather

Упражнения: добавьте order_items с той же колокацией и покажите локальный джойн в плане; сломайте колокацию (распределите по id) и посмотрите, во что превратился тот же джойн.

TimescaleDB: time-series на одном мощном узле

TimescaleDB — расширение для time-series: hypertables, которые автоматически нарезаются на chunks по времени (по сути партиции с умным планировщиком), сжатие на 90%+ для старых чанков и continuous aggregates — инкрементально досчитываемые агрегаты:

sql
CREATE EXTENSION timescaledb;

CREATE TABLE metrics (
  ts     timestamptz NOT NULL,
  host   text NOT NULL,
  cpu    real,
  memory real
);
SELECT create_hypertable('metrics', 'ts', chunk_time_interval => INTERVAL '1 day');

-- сжатие старых чанков
ALTER TABLE metrics SET (timescaledb.compress);
SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- continuous aggregate: агрегат поддерживается при вставке
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) AS bucket, host,
       avg(cpu) AS cpu_avg, max(memory) AS mem_max
FROM metrics GROUP BY 1, 2;

Теперь честно, как в плане курса: multi-node режим TimescaleDB объявлен deprecated (2.13) и удалён (2.14). Современный TimescaleDB — это один мощный узел, отличный для метрик и IoT-потоков, но не «кластер на 10 узлах». Если данных больше, чем влезает на один узел, честные альтернативы: scale-up (RAM/NVMe дешевле разработчиков), Citus — если это по-прежнему OLTP-профиль, и ClickHouse — если профиль аналитический. Второй путь и есть наш курс: в Модуле 4 мы построим аналитику по событиям именно там.

CockroachDB / YugabyteDB: не плагины, а другой мир

CockroachDB и YugabyteDB — не расширения PostgreSQL, а переписанные распределённые ядра с Postgres-совместимым проводным протоколом (wire compatibility). Raft-консенсус на запись, автоматические реплики и шарды, multi-region «из коробки». Это честно powerful — но:

расширения не переедут: ни Citus, ни PostGIS (частично), ни pg_partman — экосистема PostgreSQL не ваша;
семантика отличается: латентность записи = консенсус через регионы, транзакционные гарантии и планёрные поведенческие нюансы — не 1:1 с PG;
лицензии не вечны: CockroachDB менял лицензию на BUSL — проверяйте условия под ваш продукт до выбора, а не после.

Берите, когда требование «multi-region OLTP с консистентностью» первично и вы готовы жить в их экосистеме. Не берите «потому что Postgres-совместимый» — совместимость заканчивается на первом же расширении.

Правило выбора (заучить)

Партиции (pg_partman) — всегда, когда таблицы растут по времени. Дёшево, нативно, чинит vacuum/retention/prune. Это не масштабирование, это гигиена.

Citus — когда один узел исчерпан (CPU/IO/размер), а запросы хорошо ложатся на ключ распределения. Главный ответ на «шардированный Postgres».

Distributed SQL (CockroachDB/YugabyteDB) — когда нужен multi-region OLTP с консенсусом на запись «из коробки». Цена: вы выходите из экосистемы PostgreSQL.

Пропустить шаг 1 и сразу ставить Citus — как покупать внедорожник, потому что в квартире бардак.

Инструмент	Роль	Честный вердикт
pg_partman	автоматические партиции + retention	стандарт де-факто; делать ДО Citus
Citus	шардирование: distributed/reference, colocation, pushdown	⭐ главный «шардированный PG»; ключ распределения — навсегда
TimescaleDB	hypertables, сжатие 90%+, continuous aggregates	отлично для метрик; ⚠️ multi-node deprecated — это один мощный узел
CockroachDB / YugabyteDB	distributed SQL, multi-region OLTP	не PG: расширения и семантика не переедут

📖 3.2. Репликация и отказоустойчивость

Streaming replication: физический клон кластера

Встроенная streaming-репликация (PG с 9.0, hot standby с 9.1) — побайтовая передача WAL на standby, который непрерывно восстанавливается. Это основа read-replicas и HA:

ini
# postgresql.conf мастера
wal_level = replica
max_wal_senders = 10
synchronous_standby_names = 'FIRST 1 (pg1, pg2)'   # синхронная реплика, опционально

Синхронные режимы (synchronous_commit = remote_write | remote_apply) дают разную цену/гарантии: remote_apply — реплика увидит коммит до ответа клиенту (медленнее, read-your-writes на реплике). Ключевые ограничения, о которых забывают: реплицируется весь кластер целиком (нельзя «одну таблицу»), и standby отстаёт — read-your-writes через реплику ломается без remote_apply. Конфликты восстановления на standby (vacuum против запросов реплики) лечатся hot_standby_feedback — ценой всё того же bloat на мастере (xmin-горизонт, урок 2.2). Всё связано.

Logical replication: публикация/подписка и источник CDC

Встроенная логическая репликация (PG10+, декодирование через плагин pgoutput) реплицирует не WAL-байты, а изменения строк по подписке на публикацию:

sql
-- на мастере
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;

-- на подписчике
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=master dbname=app'
  PUBLICATION orders_pub;

Зачем она нужна: выборочная репликация (только нужные таблицы), репликация между версиями (апгрейд мажора с почти нулевым даунтаймом — стандартный рецепт), слияние нескольких баз и — главное для нашего курса — источник CDC: слот логического декодирования, из которого изменения вычитывает Debezium (урок 3.3). Честные ограничения:

DDL не реплицируется — схему на подписчике меняете сами, синхронно с релизом (порядок: сначала подписчик, потом издатель);
sequences не реплицируются — после переключения значения надо докатывать;
конфликты (данные на обеих сторонах) решаются руками, не автоматически.

С этой базы ответ на вечный вопрос «зачем pglogical?» — в 10% случаев. pglogical (2ndQuadrant/EDB) добавляет фильтры строк/колонок, конфликт- резолюцию, репликацию между произвольными узлами — берите, когда встроенного механизма мало. И не путайте с коммерческим BDR (bidirectional multi-master от той же конторы) — это другой продукт за другие деньги.

Patroni: стандарт выживания PostgreSQL

Мастер умер. Ручной failover — 10–40 минут поиска «а что вообще случилось». Patroni — шаблон HA-кластера: агент на каждом узле, следящий за локальным PostgreSQL и конкурирующий за лидерство через DCS (distributed configuration store — etcd, Consul): raft-хранилище, которое даёт единый ответ на вопрос «кто сейчас мастер». Схема:

text
[etcd] ← выборы/конфиг   [pg1: Patroni+PG (leader)]
                         [pg2: Patroni+PG (standby)]
                         [pg3: Patroni+PG (standby)]
                                   ↑
                    REST API :8008 + patronictl

выборы нового лидера за ~10–30 секунд, DCS гарантирует, что лидер один (защита от split-brain);
REST API и patronictl — switchover/reinit без SQL-шаманства;
synchronous_mode — не терять подтверждённые коммиты при failover.

Что Patroni не делает: не проксирует трафик. Приложение (или PgBouncer) должно находить нового мастера: VIP, DNS, консуль-шаблоны или pgbouncer, перенацеленный patroni-хуками. Прод-кластер = Patroni + DCS + пулер на входе.

🧪 Лабораторная 2: Patroni × 3 — kill мастера

yaml
# docker-compose.patroni.yml (фрагмент; полная версия — в репозитории курса)
services:
  etcd:
    image: quay.io/coreos/etcd:v3.5.14
    command: etcd --listen-client-urls http://0.0.0.0:2379
                  --advertise-client-urls http://etcd:2379
  pg1:
    image: ghcr.io/zalando/spilo-16:3.0-p2      # Spilo = Patroni + PG от Zalando
    environment: &spilo
      ETCD3_HOSTS: http://etcd:2379
      SCOPE: course
      PATRONI_NAME: pg1
    depends_on: [etcd]
  pg2:
    image: ghcr.io/zalando/spilo-16:3.0-p2
    environment: { <<: *spilo, PATRONI_NAME: pg2 }
    depends_on: [etcd]
  pg3:
    image: ghcr.io/zalando/spilo-16:3.0-p2
    environment: { <<: *spilo, PATRONI_NAME: pg3 }
    depends_on: [etcd]

bash
docker compose -f docker-compose.patroni.yml up -d
docker exec -it pg1 patronictl list
#   Cluster: course ─┬─ Member ─ Host ─ Role    ─ State
#                    ├─ pg1  ...    Leader     running
#                    ├─ pg2  ...    Sync Standby running
#                    └─ pg3  ...    Replica    running

docker kill pg1            # убиваем лидера
docker exec -it pg2 patronictl list   # pg2 или pg3 — новый Leader

Зафиксируйте время до переключения и поведение приложения (коннекты рвутся — это нормально; в Модуле 11 добавим pgbouncer-слой, чтобы переживалось прозрачно).

PgBouncer / Odyssey: пул на входе в кластер

Урок 2.7 разобрал режимы; здесь — место в HA-архитектуре. PgBouncer ставится перед кластером (или на каждом узле рядом с Patroni — и указывает на текущего лидера через его REST API/VIP). Odyssey (Яндекс) — многопоточный пулер на десятки тысяч клиентских соединений: так живёт Yandex Managed PostgreSQL. Вердикт прежний: PgBouncer — дефолт, Odyssey — большие масштабы.

pg_failover_slots: чтобы CDC пережил смену мастера

Самое неприятное в связке «Patroni + CDC»: replication slots не реплицируются на standby. Слот живёт в памяти/каталоге конкретного мастера. При failover новый лидер про него не знает → Debezium теряет позицию в WAL → либо дыра в данных, либо переснапшот всей базы (детали — в уроке 3.3 и Модуле 6).

pg_failover_slots (родом из Cloudflare) синхронизирует logical slots на standby и активирует их на новом лидере после promotion:

yaml
# patroni.yml (фрагмент)
bootstrap:
  dcs:
    postgresql:
      parameters:
        wal_level: logical                  # обязателен для logical slots
        max_slot_wal_keep_size: 50GB        # страховка диска от мёртвого слота
# плагин pg_failover_slots установлен на узлах: standby периодически
# синхронизирует слоты с лидера; после promotion слот уже на месте

С PG17 механика есть нативно (sync_replication_slots = on, standby slot sync) — но в существующих парках pg_failover_slots остаётся рабочим ответом. Проверьте, что делает ваш managed-провайдер: обещание «HA» без судебного разбора судьбы слотов — это обещание без CDC.

Инструмент	Роль	Вердикт
Streaming replication	физическая реплика, hot standby	база read-replicas и HA; реплицирует всё целиком
Logical replication (pgoutput)	публикация/подписка на таблицы	источник CDC + апгрейды; DDL/sequences — вручную
pglogical	фильтры, конфликт-резолюция	когда встроенной мало (10% случаев); не путать с BDR
Patroni	автопереключение, REST, DCS	⭐ стандарт HA; сам трафик не проксирует
PgBouncer / Odyssey	пулы на входе	PgBouncer — дефолт; Odyssey — десятки тысяч коннектов
pg_failover_slots	слоты переживают failover	обязателен при Patroni + CDC; в PG17 — нативно

📖 3.3. CDC: вынос изменений из Postgres

Слот: закладка в WAL

Logical decoding читает WAL и превращает изменения в поток строк для плагина- декодера. Точка потребления фиксируется в replication slot — закладке, которая запрещает мастеру выбрасывать WAL, ещё не вычитанный консьюмером. Отсюда главный операционный риск: умерший консьюмер = растущий WAL = диск под завязку. Страховки: max_slot_wal_keep_size (лимит) и мониторинг pg_replication_slots (active = false + растущий restart_lsn — алерт).

Плагин	Формат вывода	Кто использует
pgoutput	бинарный протокол репликации	встроенная logical replication, Debezium
wal2json	JSON	кастомные консьюмеры, отладка
decoderbufs	Protobuf	legacy-коннекторы Debezium

wal2json отлично показывает суть потока:

sql
SELECT pg_create_logical_replication_slot('demo', 'wal2json');

UPDATE orders SET status = 'paid' WHERE id = 42;

SELECT data FROM pg_logical_slot_get_changes('demo', NULL, NULL);
-- {"change":[{"kind":"update","table":"orders",
--   "columnnames":["id","status"],"columnvalues":[42,"paid"], ...}]}

Для собственного консьюмера на Go есть jackc/pglogrepl — низкоуровневый клиент протокола репликации. Каркас ниже компилируется и делает три вещи, без которых CDC не существует: создаёт слот идемпотентно, стримит WAL и регулярно подтверждает LSN (иначе слот бесконечно растёт — см. выше про диск). Разбор самих pgoutput-сообщений (Relation/Begin/Insert/Update/Delete) вынесен в boilerplate/internal/cdc/pgoutput.go; полная версия с Kafka-продюсером — в Модуле 6.

go
// Stream читает logical slot и отдаёт строки в emit. dsn — БЕЗ query-параметров.
func Stream(ctx context.Context, dsn, slot, publication string,
	emit func(Row) error) error {

	sysConn, err := pgconn.Connect(ctx, dsn) // служебный: IdentifySystem, CreateSlot
	if err != nil {
		return err
	}
	defer sysConn.Close(ctx)

	replConn, err := pgconn.Connect(ctx, dsn+"?replication=database")
	if err != nil {
		return err
	}
	defer replConn.Close(ctx)

	sysID, err := pglogrepl.IdentifySystem(ctx, sysConn)
	if err != nil {
		return err
	}

	if _, err := pglogrepl.CreateReplicationSlot(ctx, sysConn, slot, "pgoutput",
		pglogrepl.CreateReplicationSlotOptions{}); err != nil {
		var pgErr *pgconn.PgError
		// 42710 duplicate_object — слот уже есть, это штатный рестарт
		if !errors.As(err, &pgErr) || pgErr.Code != "42710" {
			return err
		}
	}

	if err := pglogrepl.StartReplication(ctx, replConn, slot, sysID.XLogPos,
		pglogrepl.StartReplicationOptions{PluginArgs: []string{
			"proto_version '1'",
			"publication_names '" + publication + "'",
		}}); err != nil {
		return err
	}

	var (
		typeMap   = pgtype.NewMap()
		clientLSN = sysID.XLogPos
		nextAck   = time.Now() // подтвердить позицию сразу
	)
	for {
		if !time.Now().Before(nextAck) {
			if err := pglogrepl.SendStandbyStatusUpdate(ctx, replConn,
				pglogrepl.StandbyStatusUpdate{WALWritePosition: clientLSN}); err != nil {
				return err
			}
			nextAck = time.Now().Add(10 * time.Second)
		}

		recvCtx, cancel := context.WithTimeout(ctx, time.Until(nextAck))
		raw, err := replConn.ReceiveMessage(recvCtx)
		cancel()
		if pgconn.Timeout(err) {
			continue // таймаут приёма — норма: просто пора подтвердить LSN
		} else if err != nil {
			return err
		}

		copyData, ok := raw.(*pgproto3.CopyData)
		if !ok {
			continue
		}
		switch copyData.Data[0] {
		case pglogrepl.XLogDataByteID:
			xld, err := pglogrepl.ParseXLogData(copyData.Data[1:])
			if err != nil {
				return err
			}
			rows, err := DecodePgoutput(xld.WALData, typeMap) // Relation-кэш + типы
			if err != nil {
				return err
			}
			for _, r := range rows {
				if err := emit(r); err != nil {
					return err
				}
			}
			clientLSN = xld.WALStart + pglogrepl.LSN(len(xld.WALData))
		case pglogrepl.PrimaryKeepaliveMessageByteID:
			pkm, err := pglogrepl.ParsePrimaryKeepaliveMessage(copyData.Data[1:])
			if err != nil {
				return err
			}
			if pkm.ReplyRequested {
				nextAck = time.Now() // сервер просит подтверждение немедленно
			}
		}
	}
}

Три грабли, которые видно только в бою: PluginArgs передаются одной строкой на аргумент ("proto_version '1'", а не парой элементов); слот создаётся через обычный коннект, а стрим — через replication=database; clientLSN двигается на WALStart + len(WALData), а не «по факту обработки» — поэтому неподтверждённый хвост при падении приезжает повторно (at-least-once, как и везде в курсе).

Debezium ⭐: стандарт CDC

Debezium — Kafka Connect-коннектор, который делает за вас весь конвейер: первичный снапшот, чтение слота (pgoutput), преобразование в события, обработка схем, отправка в топики Kafka. Наш путь PG → CH (Модули 6 и 11) строится именно на нём.

bash
curl -X POST http://localhost:8083/connectors \
  -H 'Content-Type: application/json' -d '{
  "name": "pgcourse-orders",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "postgres",
    "database.port": "5432",
    "database.user": "postgres",
    "database.password": "course",
    "database.dbname": "app",
    "topic.prefix": "pgc",
    "table.include.list": "public.orders,public.order_items",
    "slot.name": "debezium_orders",
    "publication.autocreate.mode": "filtered",
    "snapshot.mode": "initial",
    "heartbeat.interval.ms": "10000"
  }
}'

Что нужно знать заранее (глубоко — Модуль 6):

REPLICA IDENTITY. Чтобы события UPDATE/DELETE несли «старое» значение ключа, таблица должна идентифицировать строки: DEFAULT (PK) — ок; таблицам без PK нужен REPLICA IDENTITY FULL (или INDEX); NOTHING превращает DELETE в событие без ключа — Debezium такое не любит.
```
sql
ALTER TABLE no_pk_table REPLICA IDENTITY FULL;
```
Снапшот на старте: initial — при первом запуске коннектор читает таблицы целиком и записывает в топики, потом переключается на слот.
Heartbeats (heartbeat.interval.ms) держат слот живым на малоактивных базах — иначе WAL-чекпоинт консьюмера не двигается.
Лаг слота — главный алерт цепочки; потеря слота (failover, урок 3.2) = переснапшот.

postgres_fdw / clickhouse_fdw: федерация вместо пайплайна

postgres_fdw (в ядре) позволяет из одной базы SQL-джойнить таблицы другой PostgreSQL; clickhouse_fdw (community-расширение, ставится отдельно) — мост PG → ClickHouse. Не путайте врапперы: clickhouse_fdw регистрирует свой FOREIGN DATA WRAPPER clickhouse_fdw.

sql
CREATE EXTENSION clickhouse_fdw;

CREATE SERVER ch_server FOREIGN DATA WRAPPER clickhouse_fdw
  OPTIONS (host 'warehouse', port '9000');
CREATE USER MAPPING FOR CURRENT_USER SERVER ch_server
  OPTIONS (user 'reader', password '***');          -- креды из Lockbox (Модуль 8.4)

-- импортируем схему аналитики как «чужие» таблицы
IMPORT FOREIGN SCHEMA analytics LIMIT TO (events)
  FROM SERVER ch_server INTO ch_remote;

-- разовый джойн OLTP-данных с аналитикой
SELECT o.id, e.event_type
FROM orders o
JOIN ch_remote.events e ON e.order_id = o.order_id
WHERE o.created_at > now() - interval '1 day';

Честный вердикт: для разовых джойнов и ад-хок-анализа — да; для продовых пайплайнов — нет. Латентность, ограниченный pushdown, зависимости версий fdw от движка назначения. Регулярная синхронизация — это CDC (Debezium) или batch ETL, а не FDW.

Инструмент	Роль	Вердикт
Logical decoding + pgoutput	встроенный поток изменений из WAL	фундамент CDC; слот надо мониторить
wal2json	JSON для кастомных консьюмеров	удобен для отладки и простых синков
Debezium PG connector	PG → Kafka: снапшот + слот + схемы	⭐ стандарт CDC, наш путь в ClickHouse
postgres_fdw / clickhouse_fdw	федеративные запросы, мост PG↔CH	разовые джойны, не пайплайны

📖 3.4. Наблюдаемость, аудит, эксплуатация

pg_stat_statements ⭐: включать всегда, первым делом

Нормализованная статистика всех запросов: calls, total/mean время, строки, буферы. Два шага и вы видите, что база делает на самом деле:

ini
# postgresql.conf + рестарт (это preload-библиотека!)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

sql
CREATE EXTENSION pg_stat_statements;

-- Топ-10 по совокупному времени — «рейтинг врагов»
SELECT calls,
       round(total_exec_time::numeric) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       round(100 * shared_blks_hit /
             NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Смотрите не только на mean, но и на calls × mean: запрос за 3 мс, вызываемый 5000 раз в секунду, дороже отчёта за 2 секунды. Сброс статистики — pg_stat_statements_reset(); прод-практика — сбрасывать по расписанию и снимать дельты (pg_cron, урок ниже).

auto_explain: ловец «внезапно замедлившегося»

Запрос был быстрым, а в пятницу вечером — нет; воспроизвести не можете. auto_explain пишет в лог планы запросов, дольше порога:

ini
# session_preload_libraries — без рестарта; или shared_preload_libraries
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_buffers = on
auto_explain.sample_rate = 0.1      # в проде логируем 10% медленных
# auto_explain.log_analyze = on     # точные числа, но замеряет дороже — включать точечно

Пара pg_stat_statements (кто дорогой) + auto_explain (почему) закрывает 90% расследований производительности до прихода «всё тормозит» в чат.

pgAudit: аудит, когда требует регуляторика

ini
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role'    # классы: read, write, function, role, ddl, misc

Полные журналы операций для compliance (PCI DSS, отраслевые требования). Цена честно: объём логов растёт на порядки — pgAudit включают адресно (объектный аудит) или на контурах, где аудит действительно обязателен. Для «просто посмотреть, кто удалил заказ» достаточно log_statement и приложения с корреляцией по пользователю.

pg_repack: пересборка bloat под нагрузкой

VACUUM FULL держит ACCESS EXCLUSIVE всё время работы — в проде нельзя. pg_repack пересобирает таблицу онлайн: копирует данные в новую, ловит дельты триггерами и в конце делает короткий swap с двумя brief-блокировками:

bash
pg_repack -h localhost -U postgres -d app -t orders
# требуется: PRIMARY KEY/unique index, ~2x размера таблицы свободного места

Помните урок 2.2: сначала уберите причину bloat (долгие транзакции, тюнинг autovacuum), потом repack. Пересборка не лечит болезнь, она убирает симптом.

pg_cron: cron внутри БД

sql
CREATE EXTENSION pg_cron;

-- ночное обслуживание партиций (связка с pg_partman из 3.1)
SELECT cron.schedule('partman-maintenance', '10 2 * * *',
  $$CALL partman.run_maintenance_proc()$$);

-- почистить сессии и сбросить статистику запросов
SELECT cron.schedule('nightly-cleanup', '30 3 * * *',
  $$DELETE FROM sessions WHERE expires_at < now()$$);

Мелочь, а без неё pg_partman скучает. Ограничения: гранулярность — минута; задачи выполняются в контексте одной базы/узла (в кластере — на координаторе); тяжёлые джобы лучше всё же держать в планировщике приложения/ОС. Точечные retention-очистки — идеальный кейс.

pgBackRest / WAL-G ⭐: бэкапы и PITR

Инкрементальные бэкапы + восстановление на момент времени (PITR) — база базовых бэкапов + проигрывание WAL. WAL-G — проект из Яндекса, pgBackRest — «старший брат» с параллелизмом и rich CLI; берите тот, что ближе вашей эксплуатации, — оба честно работают с S3-совместимыми хранилищами.

bash
# WAL-G
wal-g backup-push /var/lib/postgresql/16/main
wal-g backup-list

# pgBackRest
pgbackrest --stanza=main --type=full backup

Восстановление на момент (PITR): свежий базовый бэкап + WAL до целевой точки:

ini
# recovery конфиг (postgresql.auto.conf / restore-запуск)
restore_command = 'wal-g wal-fetch "%f" "%p"'
recovery_target_time = '2026-09-15 12:30:00+03'

Правило курса: бэкап без теста восстановления — не бэкап. Restore-drill (поднять копию из бэкапа, восстановиться на время T, сверить контрольные суммы данных) — регулярная операция, а не подвиг. Оцените заранее RPO (сколько данных готовы потерять — у синхронной реплики ≈ 0, у бэкапов — часы) и RTO (сколько восстанавливаться).

pgcrypto: точечное шифрование колонок

sql
CREATE EXTENSION pgcrypto;

INSERT INTO secrets (payload)
VALUES (pgp_sym_encrypt('card-token-123', :key));

SELECT pgp_sym_decrypt(payload, :key) FROM secrets;

Для точечных случаев (несколько чувствительных полей). Честные оговорки: ключи не живут в БД (ни в таблицах, ни в функциях, ни в логах) — приложение передаёт их параметрами; шифрование на стороне приложения часто гибче и не светит ключи в SQL-логах; pgcrypto не делает поиск по зашифрованному (детерминированное шифрование — отдельный компромисс). TLS в транзите — обязателен независимо от pgcrypto.

pgvector и PostGIS: обзорно

pgvector — векторные колонки (vector(1536)) и ANN-индексы (HNSW, IVFFlat) для AI/RAG-поиска:

sql
CREATE EXTENSION vector;
CREATE TABLE docs (id bigserial PRIMARY KEY, body text, embedding vector(1536));
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
SELECT id FROM docs ORDER BY embedding <=> $1 LIMIT 10;  -- ближайшие соседи

Глубоко — опциональный Модуль 10 (RAG-пайплайн, гибридный поиск с tsvector из урока 2.4). Здесь запомните факт: до миллионов векторов PostgreSQL + pgvector закрывает поиск, отдельная Vector DB не обязательна.

PostGIS — геоданные: типы geometry/geography, индексы GiST, ST_DWithin и компания. Зрелый и огромный мир, но вне MVP курса — обзорно и всё.

Инструмент	Роль	Вердикт
pg_stat_statements	топ запросов с нормализацией	⭐ включать всегда, первым делом
auto_explain	авто-лог медленных планов	ловец «внезапно замедлившегося»; sample_rate в проде
pgAudit	полный аудит (compliance)	когда требует регуляторика; логи растут на порядки
pg_repack	online-пересборка без EXCLUSIVE	лечение bloat под нагрузкой; нужен PK и 2x диска
pg_cron	cron внутри БД	мелочь, а pg_partman без него скучает
pgBackRest / WAL-G	инкрементальные бэкапы + PITR	⭐ бэкап без теста восстановления — не бэкап
pgcrypto	шифрование строк/колонок	точечно; ключи — не в БД
pgvector	векторные индексы (HNSW/IVFFlat)	обзорно; глубоко — Модуль 10
PostGIS	геоданные	вне MVP, обзорно

💻 Практика

Лабораторная 1 (Citus): поднимите координатор + 2 воркера, создайте orders/order_items с колокацией по customer_id и reference-таблицу products. Покажите в EXPLAIN: single-shard план по ключу, локальный джойн колокированных таблиц, scatter-gather по неключевому фильтру. Сломайте колокацию и зафиксируйте деградацию джойна.
pg_partman: на таблице events (id, created_at, payload) настройте ежедневные партиции + retention 3 месяца + pg_cron-задачу обслуживания. Докажите: INSERT со старой датой попал в правильную (или default-) партицию, EXPLAIN с фильтром по времени показывает pruning.
Лабораторная 2 (Patroni + слот): поднимите Patroni ×3, создайте logical slot и сымитируйте консьюмера (wal2json из урока 3.3). docker kill лидера: без pg_failover_slots слот исчезает; с плагином (или PG17 sync_replication_slots) — переживает failover. Это прототип чекпойнта S2 MVP-проекта.
Debezium-цепочка: зарегистрируйте коннектор из урока 3.3, меняйте строки в orders и наблюдайте события в топиках Kafka (pgc.public.orders). Добавьте таблицу без PK и воспроизведите проблему с DELETE → почините через REPLICA IDENTITY FULL.
Наблюдаемость: включите pg_stat_statements + auto_explain, снимите топ-5 запросов практики Модуля 2, поймайте auto_explain-логом «замедлившийся» запрос (устройте ему деградацию: SET enable_indexscan = off в отдельной сессии). Настройте sample_rate.
Бэкап + PITR: снимите бэкап WAL-G/pgBackRest, сделайте деструктивное изменение (UPDATE orders SET status='paid' — все сразу), затем восстановитесь на время до изменения в отдельный инстанс. Restore без проверки — не бэкап: сверните count и контрольную сумму.

🧩 Quiz "Проверь себя"

1. Мастер сменился (failover), Debezium отвалился: «слот не найден». Какого плагина не хватает в архитектуре?

2. Команда хочет TimescaleDB на 10 узлах. Что вы скажете и какие альтернативы предложите?

3. Чем pg_repack лучше VACUUM FULL под продовой нагрузкой?

4. За что в Patroni-кластере отвечает DCS (etcd)?

5. Зачем в Citus reference table?

6. Таблица с PRIMARY KEY (id) партиционируется по created_at через pg_partman. Что не так?

7. Citus: 90% запросов к orders фильтруют или джойнят по customer_id. Что выбрать ключом распределения?

8. Расставьте по порядку «правило выбора» масштабирования PostgreSQL.

9. Задача — поток изменений из PostgreSQL в Kafka. Какой механизм берём за основу?

10. У таблицы нет PRIMARY KEY, и Debezium присылает события DELETE без ключа. Что настроить на источнике?

11. Что верно про Citus? — выберите все подходящие

12. Добавили pg_stat_statements в shared_preload_libraries. Что нужно, чтобы он заработал?

13. Связка pg_cron + pg_partman закрывает... — выберите все подходящие

14. Честный вердикт по postgres_fdw / clickhouse_fdw?

15. Через логическую репликацию выполнен ALTER TABLE ADD COLUMN на мастере. Что произойдёт на подписчике?

Разбор сценария: «слот не найден»

Вопрос из плана — разберём текстом. Сценарий: Patroni-кластер, Debezium читает изменения через logical slot на мастере. docker kill лидера → Patroni поднимает новый → Debezium перезапускается и получает «replication slot ... does not exist». Причина фундаментальна: слот — локальный объект мастера, physическая (streaming) репликация его не переносит — она передаёт WAL-байты, а не каталог слотов. Лечение системное, а не героическое: pg_failover_slots (синхронизирует слоты на standby и активирует после promotion), нативные failover slots PG17 (sync_replication_slots), либо managed-решение с задокументированным поведением слотов. И страховка на случай «уже случилось»: восстановить позицию нельзя — только переснапшот (snapshot.mode=initial) с последующей дедупликацией на приёмнике (ReplacingMergeTree в ClickHouse, Модуль 6). Поэтому в MVP (S2) чекпойнт формулируется именно так: «kill -9 мастера → приложение не упало, слот жив».

Разбор сценария: «TimescaleDB на 10 узлах»

Второй плановый вопрос. Ответ архитектора: «multi-node режим TimescaleDB объявлен deprecated (2.13) и удалён (2.14) — это осознанный выбор вендора, ставить на него новую архитектуру нельзя». Дальше — варианты по профилю: если это метрики/IoT и данных хватает на один узел — TimescaleDB на мощной машине со сжатием честно закрывает годы данных (scale-up дешевле команды). Если профиль OLTP и нужен настоящий кластер — Citus с его колокацией. Если профиль аналитический (агрегаты по миллиардам событий) — ClickHouse, куда этот курс и идёт: Модуль 5 покажет настоящий распределённый OLAP (2×2, Keeper, Distributed-таблицы), а Модуль 9 — рескейлинг. Запрещать TimescaleDB не надо: на одном узле он великолепен — надо честно понимать, что «кластер TimescaleDB» — это больше не продукт.

✅ Итоги модуля

После прохождения ты должен уметь:

Шпаргалка модуля

Расширения PG: когда что

Задача	Инструмент	Не берите, если...
Партиции + retention	pg_partman (+ pg_cron)	таблицы на гигабайты — хватит нативного вручную
Шардирование	Citus	запросы не ложатся на ключ распределения
Time-series на узле	TimescaleDB	нужен честный кластер — multi-node deprecated
Multi-region OLTP	CockroachDB / YugabyteDB	вам нужны PG-расширения и точная семантика PG
HA / autofailover	Patroni (+ DCS)	нет ресурса на DCS и эксплуатацию — берите managed
Десятки тысяч коннектов	Odyssey	их не десятки тысяч — хватит PgBouncer
Слот живёт при failover	pg_failover_slots / PG17	managed без доступа к узлам — требуйте SLA по слотам
CDC в Kafka	Debezium (pgoutput)	данные нужны раз в сутки — batch ETL дешевле
Разовый джойн с CH	clickhouse_fdw	это регулярный путь данных — только CDC
Топ запросов	pg_stat_statements	— (включать всегда)
Онлайн-пересборка	pg_repack	нет 2x диска под таблицу
Бэкапы + PITR	pgBackRest / WAL-G	не готовы регулярно тестировать restore
Шифрование колонки	pgcrypto	планируете хранить ключи в самой БД
Векторный поиск	pgvector	миллиарды векторов — отдельная Vector DB (или Модуль 10)

Точки отказа, которые держим под контролем

Точка	Что сломается	Страховка
Logical slot без консьюмера	диск: растёт WAL	`max_slot_wal_keep_size` + алерт на `active=false`
Failover мастера	потеря слота = переснапшот	pg_failover_slots / PG17 sync
Долгая транзакция	xmin-горизонт, bloat (Модуль 2)	таймауты на роли, алерт на xact_age
DCS (etcd)	нет выборов — кластер замирает	DCS сам в 3 узлах, мониторинг
«Бэкап есть»	данных нет	restore-drill по расписанию

🔗 Мостик к Модулю 4

Теперь у PostgreSQL есть всё: партиции и шарды, HA с живым CDC-слотом, наблюдаемость и бэкапы. Но тяжёлая аналитика по-прежнему бьёт его по лицу — и мы честно выносим её в другой мир. Дальше — ClickHouse изнутри: колоночное хранение и SIMD, части (parts) и merges, почему SELECT быстрее в 100 раз — и почему там нет честных транзакций, и почему это не проблема. Изменения, которые мы научились выносить из PostgreSQL через Debezium, будут вставляться в MergeTree-семейство — разберём его в Модуле 4 до винтика.

← Модуль 2. PostgreSQL: фундамент, который нельзя пропускать Модуль 4. ClickHouse: столбцовый движок изнутри →