Питання на співбесіді з PostgreSQL

Швидкий запит - хто чекає й на кого:

SELECT waiting.pid                          AS waiting_pid,
       waiting.query                        AS waiting_query,
       now() - waiting.query_start          AS waiting_for,
       blocking.pid                         AS blocking_pid,
       blocking.state                       AS blocking_state,
       blocking.query                       AS blocking_query
FROM pg_stat_activity waiting
JOIN LATERAL unnest(pg_blocking_pids(waiting.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid
WHERE waiting.wait_event_type = 'Lock';

pg_blocking_pids(pid) повертає процеси, що блокують даний, - найпростіший шлях до відповіді.

На що дивитися в результаті:

• blocking_state = 'idle in transaction' - класика: застосунок відкрив транзакцію, щось змінив і «забув» її закрити (чекає на HTTP-запит, впав посередині, тримає транзакцію в довгому циклі).
• Довгий blocking_query - звіт, міграція, масове оновлення.
• Ланцюжки - A чекає на B, B чекає на C; корінь - той, хто сам ні на кого не чекає.

Як розблокувати в аварійній ситуації:

SELECT pg_cancel_backend(12345);      -- скасувати поточний запит процесу
SELECT pg_terminate_backend(12345);   -- розірвати з'єднання повністю

pg_cancel_backend м'якший, але не допоможе з idle in transaction - там немає запиту, який можна скасувати, лише terminate.

Детальніше - pg_locks: усі утримувані й очікувані блокування з режимами й об'єктами (relation::regclass). Корисно, щоб зрозуміти, яке саме блокування конфліктує.

Профілактика: log_lock_waits = on (логувати очікування довші за deadlock_timeout), idle_in_transaction_session_timeout, lock_timeout для міграцій, моніторинг кількості очікуючих сесій.

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

bigint з послідовністю:

• компактний (8 байтів), швидкі індекси й JOIN;
• значення зростають - нові рядки дописуються в кінець індексу;
• але розкриває інформацію: /orders/1042 каже, скільки замовлень у вас було, і дозволяє перебирати чужі ID;
• генерується лише базою - не можна створити ID до вставки чи в іншій системі.

UUID (16 байтів):

• можна генерувати будь-де: у застосунку, на клієнті, в офлайн-режимі, в кількох базах без конфліктів;
• не розкриває кількість записів і не підбирається;
• зручно для злиття даних з різних джерел і публічних посилань.

Проблема UUIDv4 - випадковість. Нові значення потрапляють у випадкові місця B-tree індексу. На великій таблиці це означає постійні вставки в різні сторінки, розщеплення сторінок, роздутий індекс, гірше використання кешу - вставка помітно повільніша, ніж з послідовним ключем.

UUIDv7 (RFC 9562) починається з мітки часу в мілісекундах, а далі - випадкова частина. Значення приблизно впорядковані за часом, тож вставляються в кінець індексу, як bigint, зберігаючи переваги UUID.

-- PostgreSQL 18+
id uuid PRIMARY KEY DEFAULT uuidv7()

У старіших версіях PostgreSQL UUIDv7 генерують у застосунку: у Laravel - Str::uuid7(), а трейт моделі HasUuids у свіжих версіях фреймворку генерує саме UUIDv7.

Нюанс UUIDv7: мітка часу всередині розкриває момент створення запису. Для більшості даних це неважливо, але якщо час створення - чутлива інформація, це варто врахувати.

Поширений компроміс: внутрішній bigint-ключ для зв'язків і швидкості плюс окрема унікальна колонка public_id (UUID чи коротший ідентифікатор) для URL і API.

Докладніше в документації: Тип UUID

PostgreSQL дозволяє створити власний перелічуваний тип:

CREATE TYPE order_status AS ENUM ('new', 'paid', 'shipped', 'cancelled');

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'new'
);

Плюси: компактне зберігання (4 байти), перевірка значень базою, сортування в порядку оголошення.

Мінуси, через які його часто уникають:

• Додати значення можна (ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'paid'), а от видалити - ні. Доведеться створити новий тип, перенести колонку й видалити старий - на великій таблиці це переписування даних.
• ADD VALUE до PostgreSQL 12 не можна було виконати в транзакції - міграції ламалися.
• Тип - окремий об'єкт схеми: його треба створювати до таблиці, переносити між базами, враховувати в міграціях. ORM підтримують його гірше, ніж звичайні колонки.

Альтернатива 1 - text з CHECK:

status text NOT NULL DEFAULT 'new'
    CHECK (status IN ('new', 'paid', 'shipped', 'cancelled'))

Змінити перелік - замінити обмеження (NOT VALID + VALIDATE для великих таблиць), без зміни типу колонки. У Laravel так працює $table->enum() на PostgreSQL.

Альтернатива 2 - таблиця-довідник із зовнішнім ключем: коли значень багато, вони змінюються адміністраторами, або в них є атрибути (назва для показу, колір, порядок, чи активний).

Як обирати:

• Перелік стабільний і короткий, змінюється лише з кодом - CHECK або нативний enum.
• Перелік змінюється без деплою чи має атрибути - довідник.
• Логіка значень живе в застосунку - PHP enum для коду плюс CHECK у базі для цілісності.

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

Діапазонний тип зберігає проміжок значень як одне значення: int4range, numrange, daterange, tsrange, tstzrange. Межі можуть бути включними [ чи виключними ).

SELECT tstzrange('2026-10-04 10:00', '2026-10-04 12:00', '[)');

-- оператори
period @> '2026-10-04 11:00'::timestamptz     -- містить момент
period && other_period                          -- перетинаються
upper(period) - lower(period)                   -- тривалість

Найцінніше застосування - exclusion constraint. Задача: кімнату не можна забронювати двічі на час, що перетинається. Перевірка в застосунку («SELECT, чи вільно, потім INSERT») має гонку: два запити одночасно побачать «вільно».

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    period tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

Обмеження означає: не може бути двох рядків, у яких room_id рівні і періоди перетинаються. База перевіряє це атомарно, навіть під паралельним навантаженням - друга вставка отримає помилку 23P01 (exclusion_violation).

btree_gist потрібен, щоб звичайну рівність (room_id WITH =) можна було поєднати з GiST-індексом.

Переваги діапазонів над двома колонками starts_at/ends_at:

• Перетин і вкладеність - один оператор замість чотирьох порівнянь з урахуванням меж.
• GiST-індекс прискорює пошук «що перетинається з цим проміжком».
• Exclusion constraints неможливі з двома окремими колонками без діапазону.

Ще застосування: періоди дії цін і тарифів, графіки змін, версії записів у часі («який тариф діяв 15 березня»).

Мультидіапазони (PostgreSQL 14+) - кілька непересічних діапазонів одним значенням: робочі години з перервою на обід.

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

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

CREATE TABLE order_items (
    price numeric(10, 2) NOT NULL,
    quantity int NOT NULL,
    total numeric(12, 2) GENERATED ALWAYS AS (price * quantity) STORED
);

ALTER TABLE users
    ADD COLUMN email_normalized text GENERATED ALWAYS AS (lower(trim(email))) VIRTUAL;

Два види:

• STORED - обчислюється при вставці чи оновленні й зберігається на диску, як звичайна колонка. Читання безкоштовне; можна індексувати.
• VIRTUAL (PostgreSQL 18+) - не займає місця, обчислюється при читанні, як подання. У PostgreSQL 18 це вид за замовчуванням.

Навіщо:

• Похідні значення завжди узгоджені: сума позиції, нормалізований email, повне ім'я - неможливо забути оновити в одному з місць коду.
• Індекс для пошуку: tsvector для повнотекстового пошуку, нормалізоване значення для унікальності без урахування регістру - збережену колонку можна проіндексувати звичайним індексом.
• Витягти поле з JSON у звичайну колонку для фільтрів і індексів.

Обмеження:

• Вираз має бути детермінованим (immutable): без now(), випадкових чисел, запитів до інших таблиць.
• Не можна посилатися на інші згенеровані колонки чи колонки інших таблиць.
• Зміна виразу збереженої колонки - переписування таблиці.

Альтернативи: індекс за виразом (якщо значення потрібне лише для пошуку, а не для читання), тригер (для складнішої логіки з іншими таблицями), обчислення в застосунку (якщо значення не потрібне в базі).

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

Домен - іменований тип на основі наявного, з обмеженнями. Правило описується один раз і використовується в багатьох колонках.

CREATE DOMAIN email AS text
    CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');

CREATE DOMAIN positive_money AS numeric(12, 2)
    CHECK (VALUE >= 0);

CREATE DOMAIN country_code AS char(2)
    CHECK (VALUE ~ '^[A-Z]{2}$');

CREATE TABLE customers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    contact_email email NOT NULL,
    billing_email email,
    credit_limit positive_money NOT NULL DEFAULT 0,
    country country_code NOT NULL
);

Переваги:

• Одне правило - скрізь однаково. Не буде таблиці, де email перевіряється інакше чи не перевіряється взагалі.
• Зміна в одному місці: ALTER DOMAIN email ADD CONSTRAINT ... застосується до всіх колонок.
• Самодокументування: тип колонки positive_money пояснює більше, ніж numeric(12, 2).

Обмеження й нюанси:

• NOT NULL у домені працює неочевидно (наприклад, при LEFT JOIN і в результатах функцій) - документація радить ставити NOT NULL на колонку, а не в домен.
• Зміна обмеження домену перевіряє всі колонки цього типу в усій базі - на великих таблицях довго. Як і з CHECK, є NOT VALID + VALIDATE CONSTRAINT.
• Підтримка ORM: Laravel-міграції не мають методу для доменів - колонку додають сирим SQL, а для застосунку це звичайний text чи numeric.
• Перевірка email регулярним виразом у базі - лише базовий захист від сміття. Справжню валідацію (і повідомлення користувачу) робить застосунок; база гарантує, що навіть код в обхід валідації не запише явно некоректне значення.

Домени корисні в базах, куди пишуть кілька застосунків чи сервісів: правило живе там, де дані, а не в кожному з них окремо.

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

За стандартом SQL NULL означає «невідоме», а невідоме не дорівнює іншому невідомому. Тому унікальне обмеження дозволяє скільки завгодно NULL:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    phone text UNIQUE
);

INSERT INTO users (phone) VALUES (NULL), (NULL);   -- обидва вставлено

Для необов'язкових унікальних полів (телефон, нікнейм) це якраз потрібна поведінка: багато користувачів без телефону, але два однакові телефони - заборонено.

Де це підводить - складені ключі з необов'язковою частиною:

CREATE TABLE settings (
    user_id bigint NOT NULL,
    team_id bigint,          -- NULL = налаштування на рівні користувача
    key text NOT NULL,
    value text,
    UNIQUE (user_id, team_id, key)
);

INSERT INTO settings VALUES (1, NULL, 'theme', 'dark'), (1, NULL, 'theme', 'light');
-- обидва вставлено: дублікат, бо NULL ≠ NULL

NULLS NOT DISTINCT (PostgreSQL 15+) - вважати NULL однаковими:

UNIQUE NULLS NOT DISTINCT (user_id, team_id, key)

Тепер друга вставка порушить унікальність.

До PostgreSQL 15 обходили так:

• два часткові унікальні індекси - WHERE team_id IS NULL і WHERE team_id IS NOT NULL;
• індекс за виразом COALESCE(team_id, 0) - працює, але «магічне» значення легко пропустити.

Пов'язане - ON CONFLICT: upsert спирається на унікальне обмеження. Без NULLS NOT DISTINCT рядки з NULL у ключі ніколи не конфліктують, і upsert замість оновлення вставлятиме дублікати.

MySQL поводиться так само, як PostgreSQL за замовчуванням (кілька NULL дозволено), а налаштування на зразок NULLS NOT DISTINCT не має.

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

PostgreSQL має три алгоритми з'єднання таблиць, і планувальник обирає один для кожного JOIN за оцінками кількості рядків.

Nested Loop - для кожного рядка зовнішньої таблиці шукати відповідні рядки у внутрішній.

• Чудовий, коли зовнішніх рядків мало, а у внутрішній таблиці є індекс на ключі з'єднання: 20 замовлень → 20 пошуків по індексу користувачів.
• Катастрофічний, коли зовнішніх рядків багато: мільйон × пошук = мільйон звернень.

Hash Join - побудувати хеш-таблицю з меншого набору, потім пройти більший і шукати збіги в хеш-таблиці.

• Для великих наборів без потрібного порядку - типовий вибір для аналітики й звітів.
• Лише для умов рівності (=).
• Хеш-таблиця будується в пам'яті (work_mem × hash_mem_multiplier); якщо не вміщується - ділиться на частини на диску, і з'єднання сповільнюється.

Merge Join - обидва набори впорядковані за ключем з'єднання, і їх «зшивають» за один прохід, як дві відсортовані колоди.

• Ефективний, коли обидва набори вже відсортовані (наприклад, читаються з індексів) або результат усе одно потрібен відсортованим.
• Якщо сортування треба робити окремо, часто програє Hash Join.

Як читати в EXPLAIN ANALYZE:

Nested Loop  (actual rows=950000 loops=1)
  -> Seq Scan on orders (actual rows=950000)
  -> Index Scan using users_pkey on users (actual rows=1 loops=950000)

loops=950000 - сигнал: планувальник очікував кілька рядків зовні, а отримав майже мільйон. Майже завжди причина - помилкова оцінка кількості рядків (застаріла статистика, корельовані умови), а не сам алгоритм.

Виправляють не алгоритм, а оцінки: ANALYZE, розширена статистика, переписаний запит. Для перевірки гіпотези в сесії можна вимкнути алгоритм (SET enable_nestloop = off), але на проді так не залишають.

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

work_mem - скільки пам'яті може використати одна операція запиту: сортування (ORDER BY, DISTINCT, Merge Join), хеш-таблиця (Hash Join, GROUP BY через хеш). За замовчуванням - 4 МБ.

Якщо даних більше, операція переходить на тимчасові файли на диску - на порядки повільніше.

Як побачити в плані:

Sort Method: external merge  Disk: 285440kB     -- сортування на диску
Sort Method: quicksort  Memory: 3120kB          -- у пам'яті
Hash ... Batches: 16  Memory Usage: 4096kB      -- хеш поділено на частини через нестачу пам'яті

Ще - лог тимчасових файлів (log_temp_files = 0) і temp_files/temp_bytes у pg_stat_database.

Чому не поставити 1 ГБ - пастка множення. Ліміт діє на кожну операцію, а не на з'єднання:

• один запит може мати кілька сортувань і хешів одночасно;
• паралельний запит множить на кількість воркерів;
• хеш-операції можуть брати work_mem × hash_mem_multiplier (за замовчуванням ×2);
• і все це - на кожне з сотень з'єднань.

200 з'єднань × 3 операції × 256 МБ = 150 ГБ потенційно - сервер піде в OOM під навантаженням.

Як налаштовувати:

• Глобально - помірне значення (16-64 МБ залежно від пам'яті й кількості з'єднань).
• Точково більше - для важких звітів і аналітики:

SET LOCAL work_mem = '512MB';   -- лише в поточній транзакції
ALTER ROLE reporting SET work_mem = '256MB';

• Для обслуговування (CREATE INDEX, VACUUM) - окремий параметр maintenance_work_mem.

Але спершу - сам запит: часто сортування великого набору не потрібне взагалі - вистачить індексу, що віддає рядки в потрібному порядку, чи фільтра, що зменшує набір до сортування.

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

shared_buffers - власний кеш сторінок даних PostgreSQL у спільній пам'яті. Кожне читання спершу шукає сторінку тут; якщо її немає - читає з файлу.

Подвійне кешування. PostgreSQL читає файли через звичайну файлову систему, тож сторінки, яких немає в shared_buffers, часто вже лежать у кеші ОС (page cache). Тому:

• shared_buffers не має забирати всю пам'ять - решту ефективно використовує ОС;
• типова рекомендація документації - близько 25% оперативної пам'яті на виділеному сервері бази (більше дає менший виграш, бо дублюється з кешем ОС);
• effective_cache_size - підказка планувальнику, скільки кешу всього (shared_buffers + кеш ОС), зазвичай 50-75% пам'яті. Пам'ять він не виділяє, лише робить індексні плани «дешевшими» в оцінках.

# сервер з 32 ГБ, лише під PostgreSQL
shared_buffers = 8GB
effective_cache_size = 24GB

Значення за замовчуванням - лише 128 МБ - розраховане на запуск будь-де, а не на продуктивність. На сервері для продакшену його майже завжди треба змінити (і перезапустити PostgreSQL).

Як оцінити, чи вистачає кешу:

SELECT round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_percent
FROM pg_stat_database
WHERE datname = current_database();

Для OLTP-навантаження зазвичай очікують 99%+. Але blks_read рахує читання повз shared_buffers, а ці сторінки могли прийти з кешу ОС - низький відсоток не завжди означає читання з диска. Точніше - EXPLAIN (ANALYZE, BUFFERS) для конкретних запитів і метрики вводу-виводу ОС.

Ще пов'язані параметри: huge_pages (для великих shared_buffers зменшує накладні витрати пам'яті), wal_buffers. У керованих базах (RDS, Cloud SQL) розумні значення вже виставлені під розмір інстанса.

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

Найповільніший спосіб - окремий INSERT на кожен рядок в autocommit: кожен рядок - окрема транзакція з записом у WAL і синхронізацією з диском.

Від швидкого до найшвидшого:

1. Одна транзакція і масові INSERT:

BEGIN;
INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', now()), (2, 'login', now()), /* ...тисяча рядків... */;
-- ще порції
COMMIT;

Порції по 500-5000 рядків в одному INSERT - у десятки разів швидше за поодинокі. У Laravel - DB::table('events')->insert($batch).

2. COPY - спеціальний протокол масового завантаження, найшвидший спосіб:

COPY events (user_id, type, created_at) FROM '/data/events.csv' WITH (FORMAT csv, HEADER true);

COPY ... FROM читає файл на сервері бази; з клієнта - \copy у psql чи Pdo\Pgsql::copyFromFile() / copyFromArray() у PHP 8.4+ (раніше - методи pgsqlCopyFrom* на самому PDO).

3. Для величезних одноразових завантажень:

• Створити індекси й зовнішні ключі після завантаження - побудувати індекс один раз швидше, ніж оновлювати його мільйон разів.
• Збільшити maintenance_work_mem для швидшої побудови індексів.
• UNLOGGED-таблиця для проміжних даних - без запису в WAL, але не переживає збій і не реплікується.
• Тимчасово збільшити max_wal_size, щоб рідше робилися контрольні точки.

Після завантаження - обов'язково ANALYZE, інакше планувальник працюватиме зі статистикою порожньої таблиці. А для таблиць, куди більше не писатимуть, - VACUUM (FREEZE, ANALYZE), щоб пізніше не було масового «заморожування».

Обережно на проді: масове завантаження генерує багато WAL - репліки відстають, а сервер з архівуванням WAL заповнює сховище. Великі імпорти ділять на порції з паузами й стежать за затримкою реплікації.

Докладніше в документації: Наповнення бази

Звичайне подання (view) - збережений запит: щоразу при зверненні він виконується заново.

Матеріалізоване подання зберігає результат запиту як таблицю. Читати його так само швидко, як таблицю, - але дані оновлюються лише за командою.

CREATE MATERIALIZED VIEW sales_by_month AS
SELECT date_trunc('month', created_at) AS month,
       product_id,
       sum(total) AS revenue,
       count(*) AS orders
FROM orders
WHERE status = 'paid'
GROUP BY 1, 2;

CREATE UNIQUE INDEX sales_by_month_key ON sales_by_month (month, product_id);

Оновлення:

REFRESH MATERIALIZED VIEW sales_by_month;                -- блокує читання на весь час оновлення
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_by_month;   -- читання не блокується

CONCURRENTLY будує новий результат поруч, порівнює зі старим і застосовує різницю. Вимагає унікального індексу на поданні й працює довше, зате дашборди, що читають подання, не зависають.

Коли доречно:

• Важкі агрегати для дашбордів і звітів, де допустимі дані «станом на кілька хвилин чи годин тому».
• Денормалізовані дані для пошуку чи API, які дорого збирати з багатьох таблиць на кожен запит.

Обмеження:

• Немає автоматичного оновлення - лише REFRESH за розкладом (cron, планувальник Laravel) чи за подією.
• Оновлюється цілком: навіть при зміні одного рядка запит перераховується повністю. Для дуже великих агрегатів - інкрементальні підходи (окрема таблиця з оновленням за тригером чи пакетними задачами, розширення на кшталт pg_ivm).
• Займає місце, як звичайна таблиця, і потребує індексів під свої запити.

Альтернатива на рівні застосунку - кеш результату запиту (Redis). Матеріалізоване подання краще, коли результат великий, його фільтрують і з'єднують з іншими таблицями прямо в SQL.

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

Фізична (потокова) реплікація передає на репліку 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).

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

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