Что лежит в памяти и что на диске, зачем нужны WAL и VACUUM, как работают индексы, где база упирается в потолок и как сделать, чтобы она не теряла данные.
Листайте стрелками ← →, свайпом или кнопками внизу. В схемах нажимайте кнопки и двигайте ползунки.
Postgres — это набор процессов ОС вокруг общей памяти. Каждое соединение — отдельный процесс. Все данные проходят через общий кэш страниц, а на диск попадают в двух местах: в журнал и в файлы данных.
Нажмите на элемент схемы, чтобы узнать, что он делает.
shared_buffers — общий кэш. Начинают с 25% RAM: больше обычно не нужно, ведь остальное всё равно кэширует ОС.work_mem выделяется на каждую сортировку и хеш в каждом запросе. 500 соединений × 64 МБ × несколько операций — и память кончилась.Оценка худшего случая: все соединения одновременно выполняют запрос с двумя сортировками. В жизни так бывает редко — но именно тогда приходит OOM killer.
Таблица — файл из страниц по 8 КБ. UPDATE не меняет строку на месте: он записывает новую версию, а старую помечает удалённой. У каждой версии есть xmin — кто создал — и xmax — кто удалил.
Так читатели не блокируют писателей: каждая транзакция видит версии, актуальные на момент её снимка. Откройте долгую транзакцию, сделайте несколько UPDATE и запустите VACUUM — старые версии он убрать не сможет.
VACUUM FULL (блокирует таблицу целиком) или pg_repack (почти без блокировки).Номер транзакции 32-битный, а сравнивать «раньше — позже» можно только в окне ~2,1 млрд. Чтобы старые строки не оказались вдруг «из будущего», VACUUM замораживает их: помечает видимыми для всех.
Если заморозка не успевает — из-за выключенного автовакуума, долгих транзакций или забытого слота репликации, — база в итоге перестаёт принимать запись. Следите за age(datfrozenxid).
Любая страница сначала загружается в shared_buffers. Когда места нет, алгоритм «часы» ищет жертву: стрелка идёт по кругу, уменьшая счётчик использования, и вытесняет первую страницу со счётчиком 0. Популярные страницы держатся в памяти.
Пока рабочий набор помещается в память, почти все чтения — попадания. Увеличьте его — и база начнёт читать с диска. Большое последовательное чтение использует маленький кольцевой буфер, чтобы не выбить из кэша всё полезное.
Изменение страницы сначала описывается в журнале предзаписи (WAL). При COMMIT на диск сбрасывается только журнал — последовательная запись, быстро. Сами страницы данных остаются «грязными» в памяти и пишутся позже.
После сбоя Postgres берёт последнюю контрольную точку и проигрывает журнал. Выключите synchronous_commit — коммит станет быстрее, но при сбое можно потерять последние подтверждённые транзакции. Целостность базы при этом не пострадает.
max_wal_size, а не по времени, — его пора увеличить. Это видно в log_checkpoints.
# postgresql.conf — разумная отправная точка
checkpoint_timeout = 15min
max_wal_size = 16GB
checkpoint_completion_target = 0.9 # размазать запись
wal_compression = lz4
shared_buffers = 8GB # ~25% RAM
effective_cache_size = 24GB # shared_buffers + кэш ОС
work_mem = 32MB
maintenance_work_mem = 1GB
random_page_cost = 1.1 # для SSD
Индекс — отдельный файл из тех же страниц по 8 КБ. В листьях лежат ключи по порядку и ссылки на строки в таблице (ctid). Внутренние страницы направляют поиск. Листья связаны друг с другом, поэтому диапазоны читаются подряд.
Здесь в узле до трёх ключей. В настоящей странице их сотни, поэтому дерево над миллиардом строк — всего 4–5 уровней. Вставьте ключи и посмотрите, как узлы делятся.
CREATE INDEX ON orders (user_id, created_at);
Записи отсортированы сначала по user_id, внутри — по created_at. Индекс помогает, когда запрос сужает поиск слева направо.
Выберите запрос — подсветится, какую часть индекса придётся прочитать.
LIKE 'abc%'. По умолчанию и в 90% случаев.jsonb, массивы, полнотекстовый поиск, pg_trgm для LIKE '%abc%'. Быстро ищет, медленнее обновляется.WHERE status = 'active' — индексирует только нужные строки.ON users (lower(email)) — для запросов с тем же выражением.INCLUDE (amount) — запрос берёт всё из индекса (index-only scan), если таблица недавно чистилась VACUUM.Каждый индекс замедляет запись. Если UPDATE не трогает индексированные колонки и на странице есть место, Postgres делает HOT-обновление и индексы не трогает вовсе. Лишний индекс на часто обновляемой колонке это ломает.
Для каждого плана Postgres оценивает стоимость: страницы последовательного и случайного чтения плюс обработка строк. Оценки строк берутся из статистики, которую собирает ANALYZE.
Индекс выгоден, когда запрос выбирает малую долю строк. На SSD случайное чтение почти так же дёшево, как последовательное: снизьте random_page_cost — и индекс выигрывает дольше. Неверная статистика — главная причина внезапно плохих планов.
ALTER TABLE берёт эксклюзивную блокировку. Пока идёт долгий запрос, ALTER ждёт — а все новые запросы к таблице выстраиваются в очередь за ним, хотя друг другу не мешают.
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;
-- не дождался — ошибка, повторить позже
Запустите долгий отчёт, затем ALTER. Потом повторите с lock_timeout.
Соединение с Postgres — это процесс с памятью, даже когда он ничего не делает. А у приложения 20 подов по 50 соединений в пуле — и большинство из них простаивает.
Пулер держит тысячи клиентских соединений и пропускает их через несколько десятков серверных: соединение с базой выдаётся только на время транзакции. Больше активных запросов, чем ядер, всё равно не ускоряет работу — они начинают мешать друг другу.
Серверное соединение закреплено за клиентом до отключения. Совместимо со всем, но почти не экономит соединения.
Соединение выдаётся на время транзакции. Основной режим. Между транзакциями клиент может попасть в другой процесс, поэтому состояние сессии терять нельзя:
SET → SET LOCAL внутри транзакцииLISTEN, временные таблицы — не работаютmax_prepared_statements)Соединение на каждый запрос. Транзакции из нескольких запросов запрещены. Нужен редко.
Альтернативы PgBouncer: Odyssey, PgCat, Supavisor. Пул внутри приложения всё равно нужен — он держит соединения до пулера.
ANY 1 (r1, r2).Postgres сам не переключает мастера. Это делает Patroni: узлы договариваются через etcd (или Consul, ZooKeeper, Kubernetes). Мастер — тот, кто держит ключ лидера и продлевает его, пока жив.
Ключ не продлён за TTL — реплики выбирают самую свежую и повышают её. Мастер, отрезанный от etcd, сам переходит в режим только чтения: двух мастеров не будет. Вернувшийся узел догоняет нового лидера через pg_rewind. Клиенты находят мастера через HAProxy или target_session_attrs=read-write.
Физический бэкап — копия файлов кластера — плюс непрерывный архив WAL. Восстановление: развернуть последний бэкап до нужного момента и проиграть журнал до нужной секунды.
restore_command = 'pgbackrest archive-get %f "%p"'
recovery_target_time = '2026-10-03 14:59:00'
recovery_target_action = 'promote'
В субботу в 15:00 кто-то выполнил DROP TABLE. Подвиньте ползунок и выберите момент восстановления.
archive_command падает, pg_wal растёт, пока не кончится диск.recovery_min_apply_delay = '4h') быстро спасает от ошибки человека.DROP TABLE доедет до неё за миллисекунды.cp или rsync — получится несогласованная копия.pg_dump для большой базы: восстановление занимает часы, а всё между дампами теряется.Postgres упирается в несколько типичных потолков: соединения, диск, журнал, мусор от MVCC, блокировки и плохие планы. Запись идёт только через один primary — её масштабируют вертикально или разбиением данных.
Выберите симптом — увидите причину, как её проверить и что делать.
pg_stat_statements.idle in transaction: они держат блокировки и мешают VACUUM.pg_wal и неактивные слоты репликации.age(datfrozenxid).Страховка от зависших сессий: idle_in_transaction_session_timeout и statement_timeout.
-- самые тяжёлые запросы
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
-- долгие и зависшие транзакции
SELECT pid, state, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL ORDER BY age DESC;
-- мёртвые строки
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;
-- отставание реплик
SELECT application_name, replay_lag FROM pg_stat_replication;
Из большой таблицы удалили 90% строк через DELETE и дождались autovacuum. Что стало с размером файла на диске?
VACUUM FULL (с эксклюзивной блокировкой) или pg_repack. Удалять много строк удобнее, если таблица разбита на партиции: старую партицию просто удаляют.Сессия уже 6 часов в состоянии idle in transaction: открыла транзакцию, сделала запрос и ничего не делает. Чем это плохо?
idle_in_transaction_session_timeout и исправлением кода, который забывает COMMIT.У primary одна синхронная реплика: synchronous_standby_names = 'r1'. Реплика упала. Что будет с записью?
ANY 1 (r1, r2) — коммит ждёт любую одну из них, и падение одной реплики запись не останавливает.shared_buffers — общий кэш, остальное кэширует ОС. work_mem умножается на число операций и соединений.EXPLAIN (ANALYZE, BUFFERS).lock_timeout.