Бази даних: експлуатація й масштабування

Повільний запит - не лише той, що виконується 5 секунд один раз. Часто більше шкодить запит на 20 мс, який виконується мільйон разів на день. Тому дивляться на сумарний час.

PostgreSQL - pg_stat_statements. Розширення збирає статистику по кожному нормалізованому запиту (параметри замінено на $1): кількість викликів, сумарний і середній час, прочитані блоки.

-- shared_preload_libraries = 'pg_stat_statements' у postgresql.conf, потім:
CREATE EXTENSION pg_stat_statements;

SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Доповнення:

• log_min_duration_statement = 500 - записувати в лог усі запити довші за 500 мс, з параметрами.
• auto_explain - автоматично логувати плани повільних запитів. Без цього план на проді, де дані й статистика інші, ніж локально, часто не відтворити.
• pg_stat_activity - що виконується просто зараз, хто кого чекає.

MySQL: slow query log (long_query_time), performance_schema і sys.statement_analysis - аналог pg_stat_statements.

На рівні застосунку APM (Sentry Performance, New Relic, Telescope локально) пов'язує запит з місцем у коді, яке його породило. Часто проблема - не сам запит, а N+1: сто швидких запитів там, де мав бути один.

Порядок дій: знайти топ за сумарним часом → EXPLAIN (ANALYZE, BUFFERS) на реальних параметрах → індекс, переписаний запит чи кеш → порівняти статистику після змін.

Докладніше в документації: pg_stat_statements

Кожне з'єднання з PostgreSQL - це окремий процес сервера з власною пам'яттю. Сотні з'єднань споживають багато ресурсів, а встановлення нового з'єднання помітно повільне. Тим часом PHP-FPM з 200 воркерами на кількох серверах легко відкриває тисячу з'єднань, більшість з яких простоює.

PgBouncer стоїть між застосунком і базою: тисячі клієнтських з'єднань він обслуговує невеликим пулом серверних.

Режими:

• Session pooling - серверне з'єднання видається клієнту на всю сесію. Безпечно, але виграш малий.
• Transaction pooling - з'єднання видається лише на час транзакції (або окремого запиту поза транзакцією). Головна економія - і головні сюрпризи.
• Statement pooling - на окремий запит; транзакції з кількох запитів неможливі.

Що ламається в transaction pooling: усе, що прив'язане до сесії, бо наступний запит може піти іншим серверним з'єднанням:

• SET (часовий пояс, search_path, statement_timeout) без LOCAL - налаштування «перетече» до іншого клієнта.
• Сесійні advisory-блокування, LISTEN/NOTIFY, тимчасові таблиці.
• Підготовлені запити (prepared statements) - PgBouncer підтримує їх на рівні протоколу лише з версії 1.21 і з налаштуванням max_prepared_statements. До того PDO з емуляцією вимкненою отримував помилки на кшталт prepared statement does not exist.

Альтернативи й доповнення: керовані пули (RDS Proxy, Supavisor у Supabase), довгоживучі процеси (Octane), що тримають сталі з'єднання, і просто обмеження кількості PHP-воркерів під реальну ємність бази.

Докладніше в документації: Можливості PgBouncer

Найпростіший спосіб - логічний дамп:

# PostgreSQL: власний стиснений формат, зручний для вибіркового відновлення
pg_dump -Fc -d app > app.dump
pg_restore -d app_restored app.dump

# MySQL
mysqldump --single-transaction --routines app > app.sql

pg_dump бере узгоджений знімок і не блокує роботу застосунку. Для MySQL на InnoDB той самий ефект дає --single-transaction.

Бекап, який ніхто не пробував відновити, - не бекап. Мінімум:

• Регулярно відновлювати копію на окремому сервері й перевіряти, що застосунок з нею працює. Автоматично, за розкладом.
• Зберігати копії не там, де база: інший сервер, інший дата-центр, об'єктне сховище. Пожежа чи видалений акаунт не мають забрати і базу, і бекапи.
• Кілька поколінь: щоденні за тиждень, щотижневі за місяць. Помилку в даних часто помічають не одразу.
• Моніторинг: сповіщення, якщо бекап не створився чи раптом став набагато меншим.

Правило 3-2-1: три копії, на двох різних носіях, одна - поза основним майданчиком.

Обмеження дампу: відновлення - лише на момент створення копії. Усе, що сталося після, втрачено. Для відновлення на довільний момент потрібне безперервне архівування журналу (WAL, binlog).

Докладніше в документації: Резервне копіювання і відновлення

Репліка - копія бази на іншому сервері, яка безперервно отримує зміни з основного сервера (primary) і застосовує їх у себе.

Як це працює: primary записує всі зміни в журнал (WAL у PostgreSQL, binlog у MySQL), а репліка отримує цей журнал по мережі й програє його. Зазвичай реплікація асинхронна: primary не чекає підтвердження від репліки.

Навіщо:

• Відмовостійкість. Якщо primary впав, репліку можна підвищити до нового primary і продовжити роботу за хвилини, а не за години відновлення з бекапу.
• Масштабування читання. Важкі звіти, аналітику й частину запитів на читання відправляють на репліки, розвантажуючи primary.
• Обслуговування без простою. Оновлення чи перевірки можна спершу робити на репліці.

Чого репліка НЕ замінює - бекапів. DELETE FROM users без WHERE за мілісекунди реплікується на всі копії. Від логічних помилок рятує лише бекап чи відновлення на момент у часі.

Головний нюанс для розробника - затримка реплікації: щойно записані дані на репліці можуть з'явитися із запізненням. У Laravel є опція sticky: після запису в межах того самого запиту читання йде на primary.

Докладніше в документації: Висока доступність і реплікація

Логічний бекап (pg_dump, mysqldump) зберігає дані як SQL-команди чи логічний формат: «створити таблицю, вставити ці рядки».

• Плюси: переноситься між версіями й платформами, можна відновити одну таблицю.
• Мінуси: на великих базах створення й особливо відновлення (перебудова всіх індексів) тривають години; відновлення лише на момент дампу.

Фізичний бекап (pg_basebackup, Percona XtraBackup, знімок диска) - копія файлів даних як вони є.

• Плюси: швидке відновлення - файли просто кладуть на місце; основа для реплік.
• Мінуси: прив'язаний до мажорної версії й платформи; лише цілий кластер.

PITR (Point-In-Time Recovery) = фізичний базовий бекап + безперервний архів журналу змін (WAL у PostgreSQL, binlog у MySQL). Під час відновлення база розгортає базовий бекап і програє журнал до потрібного моменту:

# PostgreSQL: архівувати кожен заповнений сегмент WAL
archive_mode = on
archive_command = '...'   # або готовий інструмент: pgBackRest, WAL-G, Barman

# під час відновлення
recovery_target_time = '2026-10-04 14:31:00'

Навіщо це на практиці: о 14:32 хтось виконав руйнівну міграцію. Дамп учорашньої ночі втратить пів дня даних, а PITR поверне базу на 14:31.

На керованих базах (RDS, Cloud SQL, DigitalOcean) PITR зазвичай увімкнений за замовчуванням з вікном у кілька днів - варто знати, як ним скористатися, до аварії.

Докладніше в документації: Безперервне архівування і PITR