Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

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

Питання з реальних співбесід з відповідями: Laravel і PHP, бази даних, JavaScript і фронтенд, Git, Docker, API, безпека й архітектура. Тими самими темами, що й тести.

116 питань

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

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).

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

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

Питання з реальних технічних співбесід - 116 питань у 9 темах, розібраних із відповідями. Нижче - розбивка за рівнями та темами, якщо хочете звузити підготовку.

Рівні
Junior 30 Middle 47 Senior 39

Готуєтесь до співбесіди не просто так: зараз на сайті 90 відкритих вакансій Laravel і PHP. Переглянути вакансії