Меры предосторожности при работе с РСУБД

В пылу сражения легко забыть базовые меры предосторожности. Именно по этой причине разговоры о подобных вещах всегда актуальны. Предлагаю пройтись по базовым рекомендациям при работе с РСУБД. За основу взят зажигательный доклад “Хайлоад на ровном месте” и дополнен моими комментариями.

• Количество соединений с БД — это ограниченный ресурс, который контролируется как со стороны БД, так и со стороны приложения. Чем больше параллельных обращений к БД, тем больше должен быть пул соединений; иначе тот, кому не досталось соединения, зависнет до появления свободного соединения. Это значит, что будут таймауты, рост числа wait-потоков, рост потребления памяти (как минимум, на стек), рост потребления CPU (как минимум, на переключение контекста) и множество ярких эмоций.

• Idle-соединение — это неактивное соединение, которое захватило приложение, но не использует его. Например, захват соединения, затем длительные вычисления (или даже поход во внешние сервисы), и только в конце работа с БД. При таком подходе пул соединений будет израсходован очень быстро, и указанные выше проблемы возникнут практически сразу.

• Idle-in-transaction-соединение — это idle-соединение, в котором начали выполнение транзакции. Чаще всего такое провоцируют фреймворки. Например, аннотация @Transactional была помещена на верхний уровень процесса обработки запроса и захватывает не только работу с БД. Ко всем вышеуказанным проблемам сюда можно смело докидывать увеличение времени транзакции, длительные блокировки в БД, расходы на хранение версий (MVCC), раздувание WAL (Wall-Ahead Log).

Для “защиты” PostgreSQL от неблагонадёжных приложений разработан PgBouncer, который проксирует взаимодействие с базой с целью пулинга соединений. Он позволяет активным соединениям вытеснять idle-соединения (но не idle-in-transaction), что увеличивает пропускную способность.

• Если транзакция открывается только для чтения (SELECTs), то при уровне изоляции Read Committed её лучше убрать, т.к. “толку” от неё не будет, и это позволит избавиться от idle-in-transaction-соединений. А вот если нужен “толк”, тогда нужно повышать уровень изоляции, как минимум, до Repeatable Read, но такое нужно далеко не каждому.

• Для минимизации времени блокировок в БД все UPDATEs в коде нужно сместить ближе к месту закрытия транзакции. Конечно, это не избавит от idle-in-transaction-соединений, но это лучше, чем ничего.

• Пессимистичную блокировку следует применять, если много коллизий (высокая конкурентность при изменениях). Однако часто достаточно оптимистичной блокировки, которая избавляет от необходимости явного открытия обрамляющей транзакции, следовательно, от описанных выше проблем. Если же пессимистичная блокировка всё-таки нужна, то её можно реализовать не на уровне БД, а с помощью внешних механизмов (например, key-value-хранилищ).

• Медленные запросы могут исчерпать пул соединений, даже если таких запросов не очень много. Поэтому контролируем время выполнения запросов и анализируем планы запросов (explain). Частые проблемы: нет индексов на внешние ключи (foreign keys); используется антипаттерн OrIsNull; используется offset pagination (часто в сочетании с OrIsNull) вместо keyset pagination; выбираются лишние данные (особенно TOAST).