PostgreSQL: возможности, индексы, MVCC, расширения, эксплуатация

PostgreSQL — это то, что вы берёте по умолчанию, если не доказали обратное. Формулировка звучит как реклама, но за ней стоит инженерная логика: PostgreSQL закрывает OLTP, значительную часть аналитики, документную модель (jsonb), полнотекстовый поиск, геоданные (PostGIS), временные ряды (TimescaleDB), очереди (SKIP LOCKED), векторный поиск (pgvector) — и всё это в одном процессе, с одной резервной копией, одним набором прав и одним транзакционным контуром. Каждая специализированная база сделает свою задачу лучше. Но каждая специализированная база — это ещё один компонент, который надо мониторить, бэкапить, обновлять и держать консистентным с остальными.

Эта статья — про то, как PostgreSQL устроен внутри, потому что почти все проблемы с ним в продакшене — это следствия двух архитектурных решений: процесс на соединение и MVCC с версиями строк в самой таблице. Понимаете эти два решения — предсказываете 80% инцидентов.

Реляционную модель и SQL как язык мы разбирали в статье Реляционная модель, нормализация и SQL; здесь всё это считается известным.

Откуда он взялся и почему это важно

PostgreSQL — прямой потомок POSTGRES Майкла Стоунбрейкера (Беркли, 1986), проекта, который был задуман как «Ingres, но с расширяемыми типами». Расширяемость — не маркетинг, а буквально архитектура: типы данных, операторы, функции, методы доступа и даже методы индексирования описаны как строки в системных каталогах (pg_type, pg_operator, pg_am). Поэтому PostGIS может добавить тип geometry с собственным GiST-индексом, не патча ядро, а pgvector — тип vector с HNSW-индексом.

Второе следствие — консерватизм в вопросах корректности. PostgreSQL исторически предпочитает отказать, а не молча испортить данные. Классический контрапункт — MySQL со строгим режимом, появившимся заметно позже (об этом в статье про MySQL и MariaDB).

Архитектура: процессы, а не потоки

PostgreSQL — многопроцессная система. Один главный процесс (postmaster) принимает соединения и форкает backend на каждое клиентское подключение. Плюс набор фоновых процессов.

Практические следствия, которые бьют в проде:

1. Соединение стоит дорого. Форк процесса, приватная память (work_mem — на операцию, а не на соединение!), запись в PROC_ARRAY. 500 активных backend’ов на 16-ядерной машине — это не «база тормозит», это ваша база занята переключением контекстов. Отсюда обязательный пулер: PgBouncer в режиме transaction.
2. Один запрос — ограниченный параллелизм. Параллельные воркеры есть (max_parallel_workers_per_gather), но они появляются не всегда и стоят дорого. PostgreSQL заточен под много мелких запросов, а не под один гигантский (в отличие от ClickHouse).
3. shared_buffers — не весь кэш. PostgreSQL полагается на page cache ОС. Отсюда классическая рекомендация: 25% RAM в shared_buffers, остальное — ОС. Двойное кэширование — плата за портируемость.

Как физически лежат данные

Таблица — это heap: неупорядоченный набор файлов по 1 ГБ, разбитых на страницы по 8 КБ. Порядок строк в heap не гарантирован ничем (в отличие от кластеризованного индекса InnoDB).

Ключевые детали:

• ItemId (line pointer) растут от начала страницы, кортежи — от конца. Между ними — свободное место. Физический адрес строки — ctid = (номер страницы, номер указателя).
• ctid нестабилен: любой UPDATE его меняет. Никогда не используйте его как идентификатор — только для отладки и точечных DELETE ... WHERE ctid = ....
• TOAST: значение длиннее ~2000 байт сжимается (LZ4 или pglz) и при необходимости режется на куски в служебную таблицу pg_toast_*. Отсюда неочевидное: SELECT id FROM docs может быть в сотни раз быстрее SELECT * FROM docs, потому что во втором случае идут дополнительные чтения TOAST.
• Выравнивание колонок: (bool, bigint, bool) займёт больше места, чем (bigint, bool, bool), из-за padding. На таблице в миллиард строк это гигабайты. Проверить: SELECT pg_column_size(t) FROM t LIMIT 1.

-- Порядок колонок влияет на размер строки
CREATE TABLE bad  (a boolean, b bigint, c boolean, d bigint);  -- 8 байт потерь на строку
CREATE TABLE good (b bigint, d bigint, a boolean, c boolean);

-- Быстрая диагностика раздувания и распределения
SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid)) AS total,
       pg_size_pretty(pg_relation_size(oid))       AS heap,
       pg_size_pretty(pg_indexes_size(oid))        AS idx
FROM pg_class WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC LIMIT 10;

MVCC: главный компромисс PostgreSQL

Читатели не блокируют писателей, писатели не блокируют читателей. Достигается это тем, что UPDATE не изменяет строку на месте — он записывает новую версию и помечает старую как истёкшую.

У каждого кортежа в заголовке есть xmin (транзакция, создавшая версию) и xmax (транзакция, удалившая/обновившая её). Снимок (snapshot) транзакции — это, грубо говоря, «список транзакций, которые уже завершились к моменту старта». Версия видна, если её xmin виден снимку, а xmax — нет.

-- Посмотреть системные колонки своими глазами
SELECT ctid, xmin, xmax, * FROM accounts WHERE id = 42;
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 42;
SELECT ctid, xmin, xmax FROM accounts WHERE id = 42;  -- новый ctid, новый xmin
COMMIT;

Жизненный цикл версии строки

Три вывода, из которых растёт вся эксплуатация PostgreSQL:

1. Таблицы раздуваются (bloat). Каждый UPDATE оставляет мусор. Если таблица обновляется интенсивно, а VACUUM не успевает — файл растёт, чтения становятся дороже, кэш заполняется мёртвыми строками. pgstattuple и pg_stat_user_tables.n_dead_tup — ваши приборы.

2. Долгая транзакция замораживает уборку. VACUUM не может удалить версию, которая может быть видна хоть кому-то. Открытая на час транзакция в repeatable read (или просто idle in transaction из-за бага в приложении) удерживает xmin horizon, и мусор копится по всей базе, а не только в её таблицах. Это причина №1 внезапного роста диска.

-- Кто держит horizon прямо сейчас
SELECT pid, state, now() - xact_start AS xact_age,
       now() - state_change AS idle_for, left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR state = 'idle in transaction'
ORDER BY xact_start NULLS LAST LIMIT 20;

Обязательно ставьте предохранители:

idle_in_transaction_session_timeout = '5min'   # убивает забытые BEGIN
statement_timeout = '30s'                      # в приложении, не глобально для миграций
lock_timeout = '3s'                            # чтобы DDL не выстроил очередь

3. Transaction ID wraparound. Счётчик xid 32-битный. Когда возраст незамороженных строк приближается к autovacuum_freeze_max_age (по умолчанию 200 млн), autovacuum запускает агрессивную заморозку, а при подходе к 2 млрд база уходит в режим «только чтение», чтобы не потерять данные. В PostgreSQL 17+ есть 64-битные счётчики в отдельных структурах, но сама проблема freeze никуда не делась. Мониторьте:

SELECT relname, age(relfrozenxid) AS xid_age,
       round(100 * age(relfrozenxid) / 2e9::numeric, 1) AS pct_to_wraparound
FROM pg_class WHERE relkind IN ('r','m') ORDER BY age(relfrozenxid) DESC LIMIT 10;

HOT-обновления — как не платить за MVCC

Если UPDATE не меняет ни одной проиндексированной колонки и в текущей странице есть место, PostgreSQL делает HOT (Heap-Only Tuple) update: новая версия ложится в ту же страницу, индексы не обновляются вообще. Это разница в разы по нагрузке.

Как повысить долю HOT:

• Убрать лишние индексы (каждый индекс на часто обновляемой колонке убивает HOT).
• Снизить fillfactor для таблиц с горячим UPDATE: ALTER TABLE sessions SET (fillfactor = 80); — оставляем 20% страницы под будущие версии.
• Не индексировать поля вроде updated_at, если по ним не ищут.

-- Доля HOT-обновлений: чем ближе к 1, тем лучше
SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(n_tup_hot_upd::numeric / NULLIF(n_tup_upd,0), 3) AS hot_ratio
FROM pg_stat_user_tables WHERE n_tup_upd > 10000 ORDER BY hot_ratio LIMIT 10;

Настройка autovacuum

Дефолты autovacuum рассчитаны на базу 2005 года. На таблице в 100 млн строк порог 0.2 означает «убираться после 20 млн мёртвых строк» — то есть никогда вовремя.

-- Для больших горячих таблиц: масштабный коэффициент почти в ноль, фиксированный порог
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor  = 0.01,
  autovacuum_vacuum_threshold     = 5000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay    = 0     -- не тормозить уборку (PG 12+ дефолт 2ms)
);

Глобально на современном железе (NVMe):

autovacuum_max_workers = 6
autovacuum_naptime = '15s'
autovacuum_vacuum_cost_limit = 3000     # дефолт 200 — смехотворно мало для SSD
maintenance_work_mem = '2GB'            # ускоряет фазу очистки индексов

Разбухшую таблицу лечит pg_repack (онлайн, без долгой эксклюзивной блокировки) или VACUUM FULL (переписывает таблицу под ACCESS EXCLUSIVE — только в окно обслуживания).

WAL, долговечность и контрольные точки

Перед изменением страницы PostgreSQL пишет запись в WAL (write-ahead log). Коммит — это fsync WAL, а не файлов данных. Файлы данных догоняются позже, фоновыми процессами.

Что настраивать:

wal_compression = lz4          # заметно уменьшает объём full-page writes
max_wal_size = '16GB'          # реже чекпоинты → меньше full-page writes
min_wal_size = '2GB'
checkpoint_timeout = '15min'
checkpoint_completion_target = 0.9
wal_level = replica            # logical — если нужна логическая репликация/CDC
synchronous_commit = on        # off допустим только для заведомо теряемых данных

Тонкость про full-page writes: после каждого чекпоинта первое изменение страницы пишет в WAL страницу целиком (защита от torn pages). Поэтому частые чекпоинты = гигантский WAL. Если видите пилообразный график трафика WAL — увеличивайте max_wal_size.

synchronous_commit = off даёт кратный прирост на write-heavy нагрузке ценой потери последних миллисекунд при падении сервера. Это не нарушает консистентность (база восстановится корректно), теряются только последние транзакции. Для аудита и денег — недопустимо, для счётчиков и логов — вполне.

Типы данных: где PostgreSQL выигрывает

Богатая система типов — это не «приятный бонус», а способ убрать логику из приложения и валидацию из четырёх сервисов сразу.

CREATE TYPE order_status AS ENUM ('draft','paid','shipped','cancelled');

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers(id),
  status       order_status NOT NULL DEFAULT 'draft',
  total_cents  bigint NOT NULL CHECK (total_cents >= 0),
  currency     char(3) NOT NULL,
  -- вычисляемая колонка: хранится, индексируется
  total_eur    numeric GENERATED ALWAYS AS (total_cents / 100.0) STORED,
  tags         text[] NOT NULL DEFAULT '{}',
  attrs        jsonb NOT NULL DEFAULT '{}'::jsonb,
  valid_period tstzrange NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  -- никакие два периода действия для одного заказа не пересекаются
  EXCLUDE USING gist (id WITH =, valid_period WITH &&)
);

Здесь пять вещей, которых нет «из коробки» у большинства конкурентов: enum-типы, массивы как полноценные значения, jsonb с индексируемым поиском, диапазоны с оператором пересечения && и exclusion constraint — обобщение уникальности, которое умеет запрещать пересечения интервалов (бронирования, тарифы, версионирование).

Про jsonb отдельно: это разобранный бинарный формат с индексируемым доступом, а не текст. Но это не повод превращать PostgreSQL в MongoDB.

Практическое правило: стабильные, запрашиваемые атрибуты — колонки; разреженные, клиентские, редко фильтруемые — jsonb. Подробнее про документную модель — в статье про MongoDB.

Индексы: шесть методов доступа

Это главное конкурентное преимущество PostgreSQL. Общая теория индексов и планов — в отдельной статье Индексы и планы выполнения; здесь — специфика PostgreSQL и честное сравнение.

Разница в стоимости настолько велика, что стоит запомнить порядок величин: на таблице событий в 500 млн строк B-tree по created_at займёт ~11 ГБ, BRIN по той же колонке — около 200 КБ, при этом на запросах «за последние сутки» отработает сопоставимо. Но если данные вставляются не по возрастанию времени (например, бэкфилл), BRIN превращается в полный скан.

Приёмы, которые дают больше всего

-- 1. Частичный индекс: индексируем только то, что ищем
CREATE INDEX idx_orders_unpaid ON orders (created_at)
  WHERE status IN ('draft','pending');
-- В типичном магазине это 0.5% строк → индекс в 200 раз меньше

-- 2. Покрывающий индекс (index-only scan)
CREATE INDEX idx_orders_cust ON orders (customer_id, created_at) INCLUDE (total_cents);
-- Планировщик отдаст ответ, не заглядывая в heap — если visibility map «прогрет» VACUUM'ом

-- 3. Индекс по выражению
CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);  -- обязан совпадать буквально

-- 4. Триграммы для поиска подстроки и опечаток
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%карбюратор%';
SELECT name, similarity(name, 'карбюратр') AS s FROM products
 WHERE name % 'карбюратр' ORDER BY s DESC LIMIT 10;

-- 5. jsonb: два класса операторов
CREATE INDEX idx_orders_attrs ON orders USING gin (attrs);            -- все операторы, больше
CREATE INDEX idx_orders_attrs_p ON orders USING gin (attrs jsonb_path_ops); -- только @>, компактнее и быстрее
SELECT * FROM orders WHERE attrs @> '{"channel":"mobile"}';

-- 6. BRIN для append-only
CREATE INDEX idx_events_time_brin ON events USING brin (created_at) WITH (pages_per_range = 32);

Всегда создавайте индексы в проде через CREATE INDEX CONCURRENTLY — обычный CREATE INDEX берёт блокировку, запрещающую запись, на всё время построения. Цена: два прохода по таблице и риск оставить INVALID-индекс при неудаче (его надо найти и удалить).

-- Неиспользуемые индексы: чистый убыток на записи и в бэкапе
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid)
WHERE idx_scan < 50 AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;

-- Битые индексы после неудачного CONCURRENTLY
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Планировщик и чтение EXPLAIN

Планировщик PostgreSQL — стоимостной: он перебирает планы и выбирает минимальную оценочную стоимость, опираясь на статистику из pg_statistic (гистограммы, топ-значения, корреляция).

Правильная форма запроса при разборе:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT TEXT)
SELECT c.name, count(*) AS orders, sum(o.total_cents) AS revenue
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= now() - interval '30 days'
GROUP BY c.name ORDER BY revenue DESC LIMIT 20;

Пример вывода и что в нём читать:

Limit  (cost=248913.4..248913.5 rows=20 width=48) (actual time=1842.1..1842.1 rows=20 loops=1)
  Buffers: shared hit=51204 read=189331
  ->  Sort  (cost=248913.4..249164.2 rows=100310 width=48) (actual time=1842.1..1842.1 rows=20 loops=1)
        Sort Key: (sum(o.total_cents)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=244228.0..246234.2 rows=100310 width=48)
                           (actual time=1791.4..1826.3 rows=98442 loops=1)
              ->  Hash Join  (cost=4820.0..231702.0 rows=1670400 width=30)
                             (actual time=61.2..1204.7 rows=1668211 loops=1)
                    Hash Cond: (o.customer_id = c.id)
                    ->  Seq Scan on orders o  (cost=0..204118.0 rows=1670400 width=22)
                                              (actual time=0.1..712.9 rows=1668211 loops=1)
                          Filter: (created_at >= (now() - '30 days'::interval))
                          Rows Removed by Filter: 8331789
Planning Time: 0.42 ms
Execution Time: 1849.6 ms

На что смотреть, по убыванию полезности:

1. rows= оценка против rows= фактических. Расхождение в 100 раз — корень почти любого плохого плана. Лечится ANALYZE, расширенной статистикой или переписыванием запроса.
2. Rows Removed by Filter — прочитали 10 млн, оставили 1.6 млн. Кандидат на индекс (здесь — частичный или BRIN по created_at).
3. Buffers: read= — это промахи кэша (страницы из ОС/диска). hit — попадания в shared_buffers. 189 тыс. прочитанных блоков = 1.5 ГБ ввода-вывода.
4. Sort Method: external merge Disk: ... kB — сортировка не влезла в work_mem, ушла на диск. Часто чинится увеличением work_mem для конкретной сессии.
5. loops=N у вложенного узла — стоимость надо умножать на N. Nested Loop с 100 тыс. итераций — классический симптом недооценки кардинальности.

Когда планировщик ошибается: коррелированные колонки

-- Планировщик считает условия независимыми и получает 1/1000 * 1/50
EXPLAIN SELECT * FROM addresses WHERE city = 'Москва' AND region = 'Московская область';
-- Оценка: 12 строк. Реальность: 480 000 строк.

CREATE STATISTICS addr_geo (dependencies, ndistinct, mcv) ON city, region FROM addresses;
ANALYZE addresses;   -- теперь оценка корректна

Другие рычаги, по возрастанию грубости:

ALTER TABLE events ALTER COLUMN user_id SET STATISTICS 1000;  -- точнее гистограмма (дефолт 100)
SET LOCAL work_mem = '256MB';                                 -- для одного тяжёлого отчёта
SET LOCAL enable_nestloop = off;                              -- диагностика, не решение

Ставить enable_* в конфиг — почти всегда ошибка: вы чините один запрос и ломаете сто. Используйте их, чтобы узнать, какой план был бы лучше, а потом добивайтесь его статистикой и индексами.

Транзакции и блокировки: специфика PostgreSQL

Общая теория уровней изоляции — в статье Транзакции, уровни изоляции, блокировки и аномалии. Что важно именно про PostgreSQL:

• Дефолт — READ COMMITTED, и он берёт новый снимок на каждый оператор. Внутри одной транзакции два одинаковых SELECT могут вернуть разное.
• REPEATABLE READ в PostgreSQL — это настоящий snapshot isolation, он не подвержен фантомам (в отличие от стандарта ANSI). Но подвержен write skew.
• SERIALIZABLE реализован через SSI (Serializable Snapshot Isolation) — не через блокировки, а через отслеживание опасных зависимостей. Транзакции не ждут, но могут упасть с 40001 serialization_failure. Приложение обязано уметь ретраить. Это дешёвая и очень сильная гарантия, сильно недоиспользуемая на практике.

Про блокировки в DDL — источник большинства аварий при деплое:

-- Опасно: ACCESS EXCLUSIVE + перезапись таблицы в старых версиях
ALTER TABLE big ADD COLUMN flag boolean NOT NULL DEFAULT false;  -- в PG 11+ мгновенно, раньше — часы

-- Всегда защищайте миграции от очереди блокировок
SET lock_timeout = '3s';
ALTER TABLE big ADD CONSTRAINT chk CHECK (x > 0) NOT VALID;  -- быстро, без полного скана
ALTER TABLE big VALIDATE CONSTRAINT chk;                     -- потом, под слабой блокировкой

Механизм очереди: ALTER TABLE ждёт долгий SELECT, а все новые запросы встают за ним. Одна безобидная миграция кладёт продакшн на минуты. Отсюда правило: lock_timeout в каждой миграции, повтор в цикле.

Очередь задач без отдельного брокера — тоже транзакционный приём:

-- Конкурентные воркеры без коллизий и без внешнего брокера
WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'pending' AND run_at <= now()
  ORDER BY run_at
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs SET status = 'running', started_at = now()
FROM next WHERE jobs.id = next.id
RETURNING jobs.*;

Это выдерживает десятки тысяч задач в секунду и снимает целый класс проблем «задача выполнена, но транзакция откатилась» — потому что постановка задачи и бизнес-изменение в одной транзакции.

Партиционирование

Декларативное партиционирование (с PG 10, доведено до ума к PG 13+) — стандартный способ управлять таблицами от сотен миллионов строк.

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  created_at timestamptz NOT NULL,
  user_id bigint NOT NULL,
  payload jsonb NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- Индексы объявляются на родителе, наследуются партициями
CREATE INDEX ON events (user_id, created_at);

Что реально даёт партиционирование:

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

Расширения: за что PostgreSQL любят

Стартовый набор для любого прода:

shared_preload_libraries = 'pg_stat_statements,auto_explain'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = off      # on даёт точность ценой заметного оверхеда
auto_explain.log_buffers = on

-- Топ по суммарному времени: с этого начинается любая оптимизация
SELECT round(total_exec_time)::bigint AS total_ms, calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(100 * shared_blks_hit::numeric /
             NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_pct,
       left(query, 90) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 15;

Конфигурация: рабочая база

Отправная точка для выделенного сервера 16 vCPU / 64 ГБ RAM / NVMe, OLTP-нагрузка:

# --- Память ---
shared_buffers = 16GB                  # ~25% RAM
effective_cache_size = 48GB            # подсказка планировщику, память не выделяется
work_mem = 32MB                        # НА УЗЕЛ сортировки/хеша, не на соединение!
maintenance_work_mem = 2GB
huge_pages = try

# --- WAL и чекпоинты ---
wal_compression = lz4
max_wal_size = 16GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

# --- Планировщик под SSD ---
random_page_cost = 1.1                 # дефолт 4.0 — из эпохи HDD, главный «неверный» дефолт
effective_io_concurrency = 200
default_statistics_target = 200

# --- Параллелизм ---
max_worker_processes = 16
max_parallel_workers = 12
max_parallel_workers_per_gather = 4

# --- Соединения и предохранители ---
max_connections = 200                  # реальный контроль — на PgBouncer
idle_in_transaction_session_timeout = 5min
lock_timeout = 3s

# --- Наблюдаемость ---
log_min_duration_statement = 500ms
log_lock_waits = on
log_autovacuum_min_duration = 0
log_checkpoints = on
log_temp_files = 0                     # ловим переполнение work_mem
track_io_timing = on

Про work_mem — самая частая ошибка конфигурации. Он выделяется на каждый узел сортировки/хеширования в каждом запросе. Запрос с тремя сортировками и параллелизмом 4 может съесть work_mem × 12. При 200 соединениях work_mem = 1GB — это способ получить OOM killer. Правило: держите глобально скромно, поднимайте SET LOCAL для отчётов.

Репликация и высокая доступность — кратко

Подробности в статье Репликация, шардирование и высокая доступность; здесь — карта решений PostgreSQL:

• Физическая (streaming) репликация — побайтовая копия кластера. Реплики доступны для чтения (hot standby). Простая, надёжная, реплицирует всё целиком, версии должны совпадать.
• Логическая репликация (wal_level = logical, publication/subscription) — по таблицам, между разными мажорными версиями. Основа для мажорных апгрейдов без простоя и для CDC (Debezium читает слот репликации).
• Слоты репликации — гарантируют, что WAL не удалят до чтения. Обратная сторона: мёртвый слот забивает диск WAL до 100% и роняет мастер. Ставьте max_slot_wal_keep_size и мониторьте pg_replication_slots.active.
• Автопереключение — Patroni (плюс etcd/Consul) де-факто стандарт. Сам PostgreSQL не умеет failover; всё, что говорит обратное, — обвязка.
• hot_standby_feedback = on на реплике убирает конфликты «отменил ваш запрос из-за VACUUM на мастере» — ценой того, что долгие запросы на реплике удерживают horizon на мастере. Классический размен.

Резервное копирование: pgBackRest (лучший выбор — инкрементальные, параллельные, проверка контрольных сумм) или barman. pg_dump — это логический экспорт, а не бэкап кластера: он не даёт PITR и на терабайте восстанавливается сутками. Бэкап, который ни разу не восстанавливали, не существует.

Типичные проблемы в проде

Connection storm. Приложение с пулом «50 соединений на под × 40 подов» = 2000 соединений. PostgreSQL уйдёт в постоянные context switch. Лечение: PgBouncer в transaction-режиме, default_pool_size ≈ 2–4 × число ядер. Ограничение: в transaction-режиме не работают сессионные штуки — SET, подготовленные выражения на стороне сессии, advisory-локи на сессию, LISTEN/NOTIFY.

«База внезапно заняла весь диск». Три подозреваемых, в порядке частоты: неактивный слот репликации, долгая транзакция (bloat), временные файлы от запросов, не влезших в work_mem.

Раздутый индекс. B-tree после массовых удалений не отдаёт место. REINDEX INDEX CONCURRENTLY (PG 12+) — онлайн-лечение.

Медленный count(*). MVCC не позволяет хранить точный счётчик: у разных транзакций разное «количество строк». Для приблизительного значения — pg_class.reltuples; для точного и частого — материализованный счётчик через триггер, но осторожно с конкурентными вставками (горячая строка становится точкой сериализации).

ORM N+1 и SELECT *. Не проблема PostgreSQL, но именно он получает счёт. pg_stat_statements с calls в миллионах и mean_ms в микросекундах — это она.

Мажорный апгрейд. pg_upgrade --link — минуты простоя. Логическая репликация — минуты, но требует подготовки. Важно: после апгрейда обязателен ANALYZE всей базы — статистика не переносится, и первые часы вы живёте с планами по умолчанию.

Небезопасный DELETE на миллионы строк. Одна транзакция на 50 млн строк — это гигантский WAL, раздувание и долгий horizon. Правильно — батчами:

DO $$
DECLARE affected int;
BEGIN
  LOOP
    DELETE FROM events WHERE ctid IN (
      SELECT ctid FROM events WHERE created_at < now() - interval '1 year' LIMIT 10000
    );
    GET DIAGNOSTICS affected = ROW_COUNT;
    EXIT WHEN affected = 0;
    COMMIT;
    PERFORM pg_sleep(0.05);   -- дать autovacuum и репликам догнать
  END LOOP;
END $$;

Замеры: чтобы разговор был предметным

pgbench — встроенный инструмент, TPC-B-подобная нагрузка.

# Подготовка: масштаб 1000 ≈ 100 млн строк, ~15 ГБ
pgbench -i -s 1000 --foreign-keys bench

# Смешанная нагрузка: 32 клиента, 8 потоков, 10 минут
pgbench -c 32 -j 8 -T 600 -P 10 --progress-timestamp bench

# Только чтение (-S) — верхняя граница по чтению
pgbench -c 64 -j 16 -T 300 -S bench

# Своя нагрузка из файла
pgbench -c 16 -T 300 -f ./my_workload.sql bench

Порядки величин на типичном облачном сервере 16 vCPU / 64 ГБ / NVMe (для калибровки ожиданий, не как обещание):

Последняя строка — главная. Именно она объясняет, почему аналитику уносят в ClickHouse: построчное хранение обязано прочитать все колонки строки.

Когда НЕ брать PostgreSQL

Честность здесь важнее лояльности.

Важная оговорка: пороги выше, чем принято думать. PostgreSQL на нормальном железе спокойно держит десятки терабайт, десятки тысяч TPS и аналитику по сотням миллионов строк. Большинство миграций «на что-то более масштабируемое» на практике решались индексом, партиционированием и настройкой autovacuum. Считайте цифры, а не читайте посты в блогах.

Стоимость владения

Лицензия — PostgreSQL License (BSD-подобная): бесплатно, без ограничений на использование, без «open core». Это качественно отличает его от MySQL (GPL + коммерческая Oracle), MS SQL и Oracle, которые разбираются в статье про корпоративные СУБД.

Чеклист боевого PostgreSQL

• pg_stat_statements и auto_explain в shared_preload_libraries
• PgBouncer в transaction-режиме перед базой
• random_page_cost = 1.1 на SSD
• idle_in_transaction_session_timeout и lock_timeout выставлены
• autovacuum настроен пер-таблично для горячих таблиц
• Мониторинг: age(relfrozenxid), лаг репликации, неактивные слоты, n_dead_tup, лаг autovacuum
• pgBackRest с PITR и проверенным восстановлением по расписанию
• Все миграции — с lock_timeout и CREATE INDEX CONCURRENTLY
• Patroni для автоматического failover, отработанный на учениях
• Отдельная реплика под аналитику с hot_standby_feedback (осознанно)
• ANALYZE после каждого мажорного апгрейда

Мини-итог

PostgreSQL — это MVCC-движок со строчным хранением, расширяемой системой типов и очень хорошим стоимостным планировщиком. Его сильные стороны — корректность, богатство модели данных и экосистема расширений; его слабые стороны — стоимость соединения, необходимость постоянной уборки мусора и построчное хранение, плохо подходящее для аналитических сканов. Практически всё, что вы будете делать в эксплуатации, — это следить за horizon транзакций, за autovacuum, за планами запросов и за тем, чтобы соединений было мало, а индексов ровно столько, сколько нужно.

Источники

• PostgreSQL Documentation — эталонная документация, одна из лучших в индустрии
• Внутреннее устройство PostgreSQL, Хироноби Судзуки — детальный разбор MVCC, буферов, VACUUM с иллюстрациями
• Егор Рогов, «PostgreSQL изнутри» — бесплатная книга Postgres Professional
• Use The Index, Luke — Маркус Винанд про индексы и SQL-производительность
• PostgreSQL Wiki: Don’t Do This — короткий список антипаттернов
• pgtune — генератор стартовой конфигурации по железу
• Cybertec и pganalyze blog — разборы планировщика и производительности
• Patroni и pgBackRest — документация по HA и бэкапам

Что дальше

MySQL и MariaDB: InnoDB, репликация, различия форков — вторая по распространённости реляционная СУБД, устроенная принципиально иначе: кластеризованный индекс вместо heap, undo-лог вместо версий в таблице, и совсем другая история совместимости.