Middle: питання на співбесіді з теми «Експлуатація БД»

Логічний бекап (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

За асинхронної реплікації репліка застосовує зміни з запізненням - зазвичай мілісекунди, але під навантаженням, при великих транзакціях чи проблемах мережі це можуть бути секунди й хвилини.

Що бачить користувач:

• Створив замовлення, його перенаправили на сторінку замовлення - а там «не знайдено», бо читання пішло на репліку, куди запис ще не дійшов.
• Змінив пароль чи налаштування - а сторінка показує старе значення.
• Воркер черги отримує завдання з ID щойно створеного запису, читає з репліки - і не знаходить його.

Як з цим жити:

• Читати свої записи з primary. У Laravel - опція sticky => true: після запису в межах того самого запиту всі читання йдуть на primary.
• Критичні читання - завжди з primary: перевірка балансу перед списанням, авторизація, все, що веде до запису.
• Передавати дані, а не лише ID, або відкладати завдання черги до коміту (afterCommit) і читати їх з primary.
• Моніторити затримку і прибирати відсталу репліку з ротації читання. У PostgreSQL - pg_stat_replication на primary та now() - pg_last_xact_replay_timestamp() на репліці.

Синхронна реплікація прибирає затримку для підтверджених транзакцій, але кожен COMMIT чекає репліку - запис повільніший, а падіння репліки може зупинити запис на primary. Тому її вмикають свідомо, для даних, втрата яких неприпустима.

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

Фізична (потокова) реплікація передає на репліку WAL - журнал змін на рівні байтів сторінок. Репліка - точна побайтова копія всього кластера.

• Копіюється все: усі бази, таблиці, індекси, зміни схеми.
• Репліка лише для читання (hot standby) і може стати новим primary при відмові.
• Primary і репліка мусять мати ту саму мажорну версію й архітектуру.

Логічна реплікація передає зміни даних на рівні рядків - «вставлено цей рядок у таку таблицю» - за моделлю публікація/підписка:

-- на джерелі
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;

-- на отримувачі
CREATE SUBSCRIPTION orders_sub
    CONNECTION 'host=primary dbname=app user=replicator'
    PUBLICATION orders_pub;

• Можна реплікувати окремі таблиці (і навіть рядки й колонки - з PostgreSQL 15).
• Отримувач - звичайна база, у яку можна писати: мати власні таблиці, індекси, іншу структуру.
• Працює між різними мажорними версіями - основа оновлення з мінімальним простоєм.

Обмеження логічної реплікації:

• Зміни схеми (DDL) не реплікуються - таблиці на отримувачі створюють і змінюють окремо.
• Послідовності не реплікуються - перед перемиканням їх значення переносять вручну.
• Таблиці потребують ідентифікатора рядка (первинний ключ або REPLICA IDENTITY) для UPDATE і DELETE.
• Конфлікти (наприклад, порушення унікальності на отримувачі) зупиняють підписку, доки їх не розв'язати.

Типові задачі:

• Фізична - відмовостійкість, масштабування читання, бекапи з репліки.
• Логічна - оновлення версії без простою, міграція на інший сервер чи в хмару, передача частини даних в аналітичне сховище, консолідація кількох баз в одну.

Обидві використовують слоти реплікації, і забутий неактивний слот утримує WAL на диску primary - це поширена причина переповнення диска.

Докладніше в документації: Логічна реплікація

Autovacuum запускає вакуум для таблиці, коли кількість мертвих рядків перевищить поріг:

поріг = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × кількість рядків
      = 50 + 0.2 × кількість рядків

Тобто вакуум починається, коли змінено 20% таблиці. Для таблиці на 100 мільйонів рядків - це 20 мільйонів мертвих версій до першого вакууму: таблиця роздута, запити повільні.

Налаштування для конкретних таблиць:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,     -- 1% замість 20%
    autovacuum_analyze_scale_factor = 0.005
);

-- таблиця-черга з постійними вставками й видаленнями
ALTER TABLE jobs SET (
    autovacuum_vacuum_scale_factor = 0,
    autovacuum_vacuum_threshold = 1000         -- кожні 1000 мертвих рядків
);

Швидкість роботи вакууму. Autovacuum навмисно сповільнений, щоб не заважати запитам (autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay). На сучасних SSD значення за замовчуванням часто занадто обережні - вакуум не встигає за змінами. Типове рішення - збільшити autovacuum_vacuum_cost_limit (глобально чи для таблиці).

Кількість воркерів: autovacuum_max_workers (за замовчуванням 3). Якщо великих таблиць багато, воркери зайняті на одній, а інші чекають.

Як зрозуміти, що вакуум не встигає:

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_percent,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Високий відсоток мертвих рядків і давній last_autovacuum - сигнал.

Якщо вакуум працює, а мертві рядки не зникають - його блокує довга транзакція, забутий слот реплікації чи prepared transaction. Налаштування тут не допоможуть: потрібно прибрати те, що утримує горизонт.

Не вимикати autovacuum «бо він навантажує сервер» - без нього таблиці роздуваються, а лічильник транзакцій наближається до переповнення.

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

З'єднання:

• кількість активних і простоюючих з'єднань щодо max_connections;
• сесії в стані idle in transaction і їхня тривалість;
• очікування блокувань (wait_event_type = 'Lock').

Запити:

• найдорожчі запити за сумарним і середнім часом (pg_stat_statements);
• кількість повільних запитів (log_min_duration_statement);
• найдовший запит і найстаріша відкрита транзакція.

Кеш і ввід-вивід:

• коефіцієнт попадання в кеш (blks_hit / blks_read у pg_stat_database);
• тимчасові файли (temp_files, temp_bytes) - сортування й хеші на диску через нестачу work_mem;
• навантаження на диск на рівні ОС.

Обслуговування:

• мертві рядки й час останнього вакууму для великих таблиць (pg_stat_user_tables);
• вік найстарішої транзакції - age(datfrozenxid) - захист від переповнення лічильника;
• розмір таблиць і індексів, темп їх зростання.

Реплікація:

• затримка реплік у байтах і секундах (pg_stat_replication на primary);
• неактивні слоти реплікації й обсяг WAL, який вони утримують (pg_replication_slots).

Ресурси сервера: CPU, пам'ять, вільне місце на диску для даних і для WAL, кількість контрольних точок (checkpoints_req проти checkpoints_timed - часті вимушені контрольні точки означають замалий max_wal_size).

Інструменти: postgres_exporter + Prometheus + Grafana, pganalyze, Datadog, моніторинг керованих сервісів (RDS Performance Insights).

Сповіщення варто налаштувати насамперед на те, що веде до аварії: місце на диску, кількість з'єднань близько до ліміту, затримка реплікації, вік транзакцій, неактивні слоти реплікації. Решта метрик - для розслідування й планування.

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

Кожна транзакція, що змінює дані, отримує ідентифікатор (XID) - 32-бітне число. Версія рядка позначена XID транзакції, що її створила, і PostgreSQL порівнює XID, щоб вирішити, які версії видимі якій транзакції.

Проблема: 32 біти - це близько 4 мільярдів значень, і лічильник «закручується» по колу. Порівняння працює в межах ~2 мільярдів транзакцій у минуле. Якби рядок, створений дуже давно, «пережив» цю межу, він раптом став би виглядати як створений у майбутньому - і зник би для всіх запитів.

Як PostgreSQL це запобігає - «заморожування». VACUUM позначає старі версії рядків як заморожені: «видимі всім, XID більше не має значення». Для цього autovacuum періодично запускає агресивний вакуум таблиці, навіть якщо мертвих рядків немає (autovacuum_freeze_max_age, за замовчуванням 200 мільйонів транзакцій).

Якщо заморожування не встигає:

1. Спершу - попередження в логах про наближення межі.
2. Близько до межі PostgreSQL перестає приймати транзакції, що змінюють дані, щоб не пошкодити дані. База доступна лише для читання, доки вакуум не виконано, - це повноцінна аварія.

Чому вакуум може не встигати:

• Довгі транзакції й забуті слоти реплікації блокують заморожування.
• Autovacuum вимкнений чи занадто повільний для навантаження.
• Дуже велика таблиця, агресивний вакуум якої триває годинами й постійно переривається.

Моніторинг:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

Сповіщення варто ставити задовго до 2 мільярдів - наприклад, на 500 мільйонів-1 мільярд.

Хороша новина: нові версії PostgreSQL значно покращили заморожування (visibility map з позначкою «усе заморожено», агресивніший вакуум), тож на налаштованому сервері з працюючим autovacuum це рідкість. Проблема з'являється саме тоді, коли щось систематично заважає вакууму.

Докладніше в документації: Запобігання переповненню ID транзакцій