PostgreSQL в проде: пулер, очистка, блокировки

Соединение это процесс

В PostgreSQL на каждое клиентское соединение поднимается отдельный процесс. Мой замер: пустая база держит 8 процессов, при 30 открытых соединениях их 69, а занятая память выросла с 49 до 120 мегабайт. Соединения при этом простаивали - платим просто за факт их существования.

Отсюда предел на число соединений, и он не про капризы: у меня он стоял на 50, и упереться в него легко. Приложение с пулом на 50 соединений и десятью экземплярами хочет 500, а база столько процессов не потянет. Дальше начинается интересное: превышение даёт не медленную работу, а прямой отказ в соединении.

// Лечится пулером - посредником, который держит небольшое число настоящих соединений к базе и раздаёт их приложениям по очереди. Приложение думает, что у него сотни соединений, а база видит десятки. Режим раздачи выбирают по тому, что нужно приложению: соединение выдаётся на одну транзакцию (транзакция - это группа действий, которая применяется целиком или не применяется вовсе) либо на весь сеанс. Первый режим экономнее в разы, но ломает всё, что живёт между транзакциями: временные таблицы, заранее разобранные запросы, настройки сеанса. Второй совместим со всем и экономит меньше.

предел соединений

сколько клиентских процессов база согласна поднять; сверху - отказ

пулер

посредник, держащий немного настоящих соединений и раздающий их приложениям

Долгая транзакция держит мусор

PostgreSQL не правит строку на месте: обновление создаёт НОВУЮ версию строки, а старая остаётся, пока её кто-то может увидеть. Этот механизм называется MVCC (multiversion concurrency control) - многоверсионность, и благодаря ему читатели не блокируют писателей. Убирает старые версии отдельный процесс очистки.

Проверил, что бывает, когда очистке мешают. Открыл транзакцию, которая просто читает и не завершается, и параллельно трижды обновил всю таблицу. Таблица выросла с 14 мегабайт до 53, мёртвых версий строк накопилось 600 тысяч, и очистка не смогла убрать НИ ОДНОЙ: старая транзакция всё ещё имеет право их видеть. Как только транзакция завершилась, та же очистка убрала все 600 тысяч.

// И важная деталь: после очистки таблица осталась размером 53 мегабайта. Место внутри файла переиспользуется под новые строки, но файл сам по себе не сжимается. Чтобы вернуть место операционной системе, нужна полная перестройка таблицы, а она берёт исключительную блокировку. Отсюда две практики: следить за самой старой открытой транзакцией и не оставлять открытыми сеансы, которые «просто посмотреть».

MVCC

многоверсионность: обновление создаёт новую версию строки, старая живёт до очистки

мёртвая версия строки

старая версия, которую уже никто не увидит; занимает место до очистки

очистка

процесс, который освобождает место от мёртвых версий внутри файла

Миграция и очередь блокировок

Добавление колонки со значением по умолчанию на 200 тысячах строк заняло у меня 85 миллисекунд - в современных версиях это правка только описания таблицы, данные не переписываются. Казалось бы, безопасно.

Теперь тот же запрос при живом трафике. Я открыл транзакцию, которая читает таблицу и не завершается, и запустил добавление колонки. Оно встало в ожидание блокировки. Дальше пришёл обычный запрос на чтение - и он тоже встал, хотя читатели друг другу не мешают. Причина в очереди: запрос на исключительную блокировку встаёт в неё и не пропускает вперёд тех, кто пришёл после. В моём замере в очереди оказалось два запроса, и картина «база встала на ровном месте» готова.

// Отсюда правила миграций (миграция - это изменение структуры базы, разложенное в скрипт и применяемое при выкатке). Ставить короткое ограничение времени ожидания блокировки, чтобы миграция отваливалась сама, а не копила очередь. Разбивать тяжёлые изменения на шаги, каждый из которых берёт блокировку на миллисекунды. Строить индексы в режиме без блокировки записи. И проверять, нет ли долгих транзакций, ПЕРЕД тем как запускать миграцию.

исключительная блокировка

право на таблицу, несовместимое ни с чем; берётся изменением её структуры

очередь блокировок

ждущий исключительную блокировку не пропускает вперёд пришедших после него

ограничение ожидания блокировки

настройка, после которой запрос сам отваливается, а не копит очередь

Как отвечать: «Запустили alter table, и база встала. Что произошло?»

Скорее всего, изменение структуры встало за долгой транзакцией, а очередь блокировок закрыла проход всем остальным. Я это воспроизводил: сама операция добавления колонки на двухстах тысячах строк занимает около ста миллисекунд, потому что переписывать данные не надо. Но если рядом живёт открытая транзакция, которая просто читает таблицу, изменение структуры уходит в ожидание, и следующий за ним обычный запрос на чтение тоже встаёт, хотя читатели друг другу не мешают. Лечение и профилактика: ставить короткое ограничение ожидания блокировки, чтобы миграция отваливалась сама, смотреть на самую старую открытую транзакцию перед запуском и разбивать тяжёлые изменения на шаги, каждый из которых держит блокировку миллисекунды.

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

На чём валятся

• −Считают соединение бесплатным и открывают их сотнями без пулера.
• −Держат открытую транзакцию «просто посмотреть» и останавливают очистку по всей базе.
• −Думают, что очистка вернёт место операционной системе. Она освобождает его только внутри файла.
• −Запускают миграцию без ограничения ожидания блокировки и получают очередь из невиновных запросов.
• −Судят о безопасности миграции по времени самой операции, а не по взятой блокировке.