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

Тригер - функція, яку PostgreSQL викликає автоматично при INSERT, UPDATE, DELETE чи TRUNCATE.

CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Варіанти тригерів:

Типові застосування:

• журнал аудиту змін (хто, що, коли - навіть при змінах поза застосунком);
• денормалізовані лічильники й агрегати;
• підтримка tsvector для повнотекстового пошуку;
• перевірки, які не виразити обмеженням CHECK.

Підводні камені:

• невидима логіка: розробник бачить UPDATE products SET price = ... і не знає, що змінюються ще три таблиці. Тригери мають бути задокументовані й мати зрозумілі імена;
• продуктивність: рядковий тригер виконується для кожного рядка - масовий UPDATE мільйона рядків стає мільйоном викликів функції. Для масових змін - тригери рівня оператора з таблицями переходів;
• каскади й рекурсія: тригер оновлює таблицю, на якій теж є тригер, - ланцюжки важко відстежити, а рекурсію - зупинити (pg_trigger_depth());
• блокування й взаємоблокування: тригер, що оновлює рядок-лічильник, серіалізує всі паралельні вставки на цьому рядку;
• тести: у тестах Laravel з SQLite тригерів PostgreSQL немає - тести мають працювати на тій самій СУБД, що й продакшен.

Тригер чи подія Eloquent:

• тригер спрацьовує завжди, навіть при масовому update(), сирому SQL чи зміні з іншого сервісу;
• подія Eloquent - лише при збереженні моделі, зате вона в PHP-коді, тестується звичайно й може ставити завдання в чергу.

Правило: інваріанти даних - у базі, побічні ефекти (листи, кеш, черги) - у застосунку.

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

LISTEN / NOTIFY - вбудований у PostgreSQL механізм публікації й підписки. Одне з'єднання підписується на канал, інше надсилає в нього повідомлення:

-- з'єднання A
LISTEN order_events;

-- з'єднання B
NOTIFY order_events, '{"order_id": 42, "status": "paid"}';
-- або функцією, зручно в тригерах
SELECT pg_notify('order_events', json_build_object('order_id', 42)::text);

Підписник отримує повідомлення асинхронно - без опитування таблиці.

Ключові властивості:

• транзакційність: повідомлення доставляються лише після коміту транзакції, що їх надіслала. Відкат - повідомлень немає. Це головна перевага перед відправкою подій із застосунку;
• дедуплікація: однакові повідомлення в одній транзакції об'єднуються в одне;
• без збереження: якщо в момент NOTIFY ніхто не слухає, повідомлення зникає. Підписник, що перепідключився, пропущених повідомлень не отримає;
• розмір: до 8000 байтів за замовчуванням - передають ідентифікатор, а не дані;
• черга повідомлень на сервері обмежена (8 ГБ за замовчуванням): повільний підписник, що не читає повідомлення, врешті блокує NOTIFY для всіх.

Типове застосування - тригер + NOTIFY:

CREATE FUNCTION notify_order_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    PERFORM pg_notify('order_events', NEW.id::text);
    RETURN NEW;
END;
$$;

Сервіс-підписник отримує id, читає актуальні дані й інвалідує кеш, оновлює пошуковий індекс чи відправляє подію в WebSocket.

Обмеження для веб-застосунку:

• PHP-FPM не тримає довгих з'єднань - слухати потрібно окремим довгоживучим процесом (консольна команда під Supervisor), що читає повідомлення через pgsqlGetNotify у PDO;
• PgBouncer у режимі transaction pooling не підтримує LISTEN - підписнику потрібне пряме з'єднання з базою;
• гарантій доставки немає: для подій, які не можна втратити, - transactional outbox (таблиця подій), а NOTIFY лише як сигнал «перевір таблицю», щоб не опитувати її щосекунди.

Порівняно з Redis pub/sub і Reverb: NOTIFY не потребує додаткової інфраструктури й знає про транзакції, але не масштабується на тисячі підписників і не призначений для доставки клієнтам напряму. Схема на практиці: база → NOTIFY → процес-слухач → broadcasting через Reverb до браузерів.

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

Частковий індекс (PostgreSQL) індексує лише рядки, що відповідають умові WHERE. Він менший, швидше оновлюється і краще тримається в пам'яті.

1. Запити завжди дивляться на малу частину таблиці:

-- 99% замовлень завершені, але черга обробки дивиться лише на pending
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

Індекс займає частку від повного, і запит WHERE status = 'pending' ORDER BY created_at бере з нього кілька рядків.

2. Унікальність лише серед частини рядків - найкорисніше застосування:

-- Email унікальний лише серед не видалених (soft delete)
CREATE UNIQUE INDEX users_email_active_uniq ON users (email) WHERE deleted_at IS NULL;

-- Лише одна активна підписка на користувача
CREATE UNIQUE INDEX subscriptions_one_active ON subscriptions (user_id) WHERE active;

Без часткового індексу видалений користувач «займав» би email назавжди, і зареєструватися знову було б неможливо.

Обмеження:

• Планувальник використає індекс, лише якщо з умови запиту можна вивести умову індексу. WHERE status = 'pending' - так; WHERE status = $1 з параметром, невідомим на етапі планування, - не завжди.
• MySQL часткових індексів не має. Альтернативи - згенерована колонка, яка NULL для «неактивних» рядків (унікальний індекс пропускає NULL), або функціональний індекс.

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

Звичайний CREATE INDEX у PostgreSQL бере блокування, яке забороняє запис у таблицю на весь час побудови. На таблиці в сотні мільйонів рядків це хвилини чи години простою для INSERT/UPDATE.

CREATE INDEX CONCURRENTLY будує індекс без блокування запису:

CREATE INDEX CONCURRENTLY orders_created_at_idx ON orders (created_at);

Ціна й підводні камені:

• Працює довше: два проходи по таблиці й очікування завершення всіх транзакцій, що вже йшли.
• Не можна в транзакції. У Laravel-міграції для цього потрібно вимкнути транзакцію міграції (public $withinTransaction = false;).
• Якщо побудова впала (наприклад, порушено унікальність), лишається індекс у стані INVALID. Він не використовується для читання, але сповільнює запис. Його треба знайти (pg_index.indisvalid = false), видалити DROP INDEX CONCURRENTLY і створити знову.
• Довга транзакція десь у системі змушує побудову чекати - перед операцією варто перевірити pg_stat_activity.

MySQL (InnoDB) з версії 5.6 будує більшість індексів online (ALGORITHM=INPLACE, LOCK=NONE), але на початку й наприкінці все одно потрібне коротке метаданих-блокування. Для великих таблиць часто використовують gh-ost чи pt-online-schema-change.

Загальне правило: зміни схеми великих таблиць ганяють окремо від деплою коду, з lock_timeout, щоб операція краще впала, ніж повісила всю базу в черзі за блокуванням.

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

MVCC (Multi-Version Concurrency Control) - спосіб дати паралельним транзакціям узгоджений знімок даних без блокувань на читання. Читачі не блокують письменників, письменники - читачів.

Як це влаштовано в PostgreSQL: UPDATE не змінює рядок на місці, а створює нову версію рядка, позначаючи стару як застарілу (з якої транзакції вона вже невидима). DELETE лише позначає рядок. Кожна транзакція бачить ті версії, що були актуальні на момент її знімка.

Звідси потреба у VACUUM: старі версії («мертві кортежі») лишаються в таблиці, доки жодна транзакція вже не може їх бачити. VACUUM:

• звільняє місце мертвих кортежів для повторного використання;
• оновлює visibility map (потрібна для index-only scan);
• «заморожує» старі ідентифікатори транзакцій, щоб уникнути переповнення лічильника транзакцій (transaction ID wraparound) - аварійної ситуації, за якої база перестає приймати запис.

Зазвичай усе це робить фоновий autovacuum.

Типові проблеми на проді:

• Роздування (bloat) таблиць з частими UPDATE: autovacuum не встигає, таблиця й індекси ростуть, запити сповільнюються. Лікують тонким налаштуванням autovacuum для конкретних таблиць.
• Довга транзакція (чи забута відкрита сесія idle in transaction) не дає вакууму прибирати навіть старі версії по всій базі.
• VACUUM FULL повертає місце ОС, але переписує таблицю під ексклюзивним блокуванням - на проді його уникають.

InnoDB теж використовує MVCC, але зберігає старі версії в undo log, тож окремого вакууму не потребує (його роль виконує фоновий purge).

Докладніше в документації: Регулярне очищення (VACUUM)

Advisory-блокування (PostgreSQL) - блокування за довільним числом, яке не прив'язане до жодного рядка чи таблиці. База лише гарантує, що один ключ одночасно тримає лише одна сесія; що цей ключ означає - вирішує застосунок.

-- Чекати, доки звільниться
SELECT pg_advisory_lock(42);
-- ... робота ...
SELECT pg_advisory_unlock(42);

-- Не чекати: true, якщо взяли, false - якщо зайнято
SELECT pg_try_advisory_lock(hashtext('import:prices'));

-- Знімається автоматично в кінці транзакції
SELECT pg_advisory_xact_lock(42);

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

• Захистити дію, а не рядок: «лише один процес імпорту одночасно», «лише один сервер виконує міграції під час деплою».
• Блокування того, чого ще немає: не можна взяти FOR UPDATE на рядок, якого ще не створено, а advisory-ключ від, наприклад, email - можна.
• Без зайвої інфраструктури: коли Redis для розподілених блокувань немає, а PostgreSQL уже є.

Пастки:

• Сесійні блокування живуть, поки живе з'єднання. Якщо процес «забув» зняти блокування, але з'єднання лишилося в пулі, ключ буде зайнятий безстроково. Транзакційний варіант (_xact_) безпечніший.
• З PgBouncer у режимі transaction pooling сесійні блокування ламаються: наступний запит може піти іншим з'єднанням.
• Ключ - число; рядкові ідентифікатори перетворюють хешем, і про можливі колізії варто пам'ятати.

MySQL має схожі GET_LOCK('name', timeout) / RELEASE_LOCK('name') з рядковими іменами.

Докладніше в документації: Advisory-блокування

Повільний запит - не лише той, що виконується 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

Головна небезпека не в тривалості самої зміни, а в блокуваннях. ALTER TABLE у PostgreSQL бере ACCESS EXCLUSIVE-блокування. Якщо в цей момент іде довгий запит, ALTER стає в чергу - і всі нові запити до таблиці стають у чергу за ним. Сайт лягає, хоча сама операція займає мілісекунди.

Правила, що рятують:

SET lock_timeout = '3s';       -- краще впасти й повторити, ніж повісити таблицю
SET statement_timeout = '60s';
ALTER TABLE orders ADD COLUMN note text;

Що дешево, а що ні (PostgreSQL):

• Додати колонку без значення за замовчуванням чи з незмінним default (PostgreSQL 11+) - миттєво, лише метадані.
• Додати NOT NULL до наявної колонки - повна перевірка таблиці під блокуванням. Безпечніше: CHECK (col IS NOT NULL) NOT VALID, потім VALIDATE CONSTRAINT (без блокування запису), потім SET NOT NULL (PostgreSQL 12+ використає перевірене обмеження).
• Зовнішній ключ - так само: NOT VALID, потім VALIDATE.
• Змінити тип колонки - часто переписування всієї таблиці. Краще нова колонка, поступове заповнення, перемикання.
• Індекс - лише CONCURRENTLY.

Перейменування й видалення - через «розширити, потім звузити» (expand/contract):

1. Додати нову колонку, код пише в обидві.
2. Перенести старі дані порціями.
3. Код читає з нової.
4. Окремим деплоєм прибрати стару колонку.

Так кожен крок сумісний і з попередньою, і з наступною версією коду, і деплой можна відкотити.

MySQL: багато змін виконуються online (ALGORITHM=INSTANT для додавання колонки з 8.0), для решти - gh-ost чи pt-online-schema-change.

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

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

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamptz NOT NULL,
    payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Коли це виправдано:

• Дані «старіють»: логи, події, метрики, де запити дивляться на останні тижні, а старе треба видаляти. Видалити місяць - DROP TABLE events_2025_10 (мить), а не DELETE мільйонів рядків з навантаженням на вакуум.
• Запити майже завжди фільтрують за ключем партиціонування - тоді спрацьовує partition pruning: база читає лише потрібні частини.
• Таблиця настільки велика, що індекси не вміщаються в пам'ять, а обслуговування (вакуум, перебудова індексу) окремих частин значно легше.

Коли НЕ варто: таблиця на кілька мільйонів рядків, з якою добре справляються індекси. Партиціонування - не заміна індексам.

Обмеження й пастки:

• Запит без ключа партиціонування в умові читає всі частини - часто повільніше, ніж одна таблиця.
• Первинний ключ і унікальні обмеження мусять містити ключ партиціонування: PRIMARY KEY (id, created_at).
• Частини треба створювати заздалегідь (cron чи розширення pg_partman), інакше вставка в майбутній місяць упаде - або потрапить у DEFAULT-партицію.

Партиціонування ≠ шардинг: частини живуть на одному сервері. Розподіл по серверах - окреме, набагато складніше рішення.

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

BRIN (Block Range INdex) зберігає не кожне значення, а підсумок для діапазону сторінок таблиці: мінімум і максимум значення колонки в кожних, скажімо, 128 сторінках.

Запит WHERE created_at >= '2026-10-01' перевіряє підсумки й читає лише ті діапазони сторінок, де значення можуть бути, - решту пропускає.

CREATE INDEX events_created_brin ON events USING brin (created_at);
CREATE INDEX events_created_brin ON events USING brin (created_at) WITH (pages_per_range = 32);

Головна перевага - розмір. На таблиці в сотні гігабайтів B-tree займає десятки гігабайтів, а BRIN - мегабайти. Він майже не впливає на швидкість вставки.

Коли BRIN працює: значення корелюють з фізичним розташуванням рядків на диску. Ідеальний випадок - журнали, події, метрики, де рядки лише дописуються в кінець, а час вставки зростає. Тоді кожен діапазон сторінок містить вузький проміжок часу.

Коли не працює:

• Значення розкидані по таблиці випадково (наприклад, user_id у журналі подій) - кожен діапазон містить майже всі значення, і BRIN нічого не відсікає.
• Часті UPDATE чи видалення з подальшими вставками на звільнене місце руйнують кореляцію.
• Потрібен точковий пошук одного рядка - BRIN поверне діапазони сторінок, які доведеться перечитати.

Як перевірити придатність: correlation у pg_stats для колонки - близько до 1 чи -1 означає, що BRIN підійде.

Обслуговування: нові сторінки підсумовуються вакуумом або автоматично (autosummarize = on), або вручну brin_summarize_new_values(). Поки діапазон не підсумовано, його доводиться читати завжди.

Типова архітектура: BRIN за часом на великій таблиці подій + партиціонування за місяцями + B-tree лише на тих колонках, за якими справді шукають окремі записи.

Докладніше в документації: BRIN-індекси

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

HOT (Heap-Only Tuple) - оптимізація, коли оновлення не чіпає індекси взагалі. Нова версія кладеться на ту саму сторінку, а стара посилається на неї ланцюжком. Індекси продовжують вказувати на стару позицію й знаходять нову через ланцюжок.

Умови HOT:

1. Жодна індексована колонка не змінилася.
2. На тій самій сторінці є вільне місце для нової версії.

Чому зайвий індекс шкодить UPDATE: індекс на колонці updated_at чи views_count, яку змінює майже кожне оновлення, робить HOT неможливим для всіх таких оновлень. Кожен UPDATE ... SET views_count = views_count + 1 тепер пише в усі індекси таблиці й більше роздуває їх.

Як збільшити частку HOT:

• Не індексувати «гарячі» колонки, які часто змінюються, без явної потреби.
• fillfactor менше 100 - залишати на сторінках вільне місце для нових версій:

ALTER TABLE counters SET (fillfactor = 80);

(діє для нових сторінок; для наявних - після VACUUM FULL чи перебудови).

• Винести часто змінювані лічильники в окрему вузьку таблицю.

Як виміряти:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_percent
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;

Низький відсоток HOT на таблиці з багатьма оновленнями - сигнал переглянути індекси й fillfactor.

Докладніше в документації: Heap-Only Tuples (HOT)

Кожен індекс уповільнює запис, займає пам'ять і диск, подовжує бекапи й вакуум. З роками в базі накопичуються індекси, створені «про всяк випадок» чи для запитів, яких уже немає.

1. Невикористані:

SELECT s.relname AS table, s.indexrelname AS index, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique           -- унікальні тримають цілісність
ORDER BY pg_relation_size(s.indexrelid) DESC;

2. Дублікати - однакові колонки в тому самому порядку:

SELECT indrelid::regclass AS table, array_agg(indexrelid::regclass) AS indexes
FROM pg_index
GROUP BY indrelid, indkey, indexprs::text, indpred::text
HAVING count(*) > 1;

3. Надлишкові за лівим префіксом. Індекс (user_id) зайвий, якщо є (user_id, created_at): другий обслуговує ті самі запити. Виняток - коли перший унікальний чи значно менший і використовується для гарячого запиту.

Перш ніж видаляти:

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

Інструменти: запити з PostgreSQL Wiki, pgstattuple, звіти в pganalyze, Percona Monitoring. Аналіз варто повторювати регулярно, а не раз на кілька років після проблем зі швидкістю.

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

Планувальник оцінює кількість рядків для умов окремо по кожній колонці й припускає, що колонки незалежні: вибірковість A AND B = вибірковість A × вибірковість B.

Проблема - корельовані колонки:

SELECT * FROM addresses WHERE city = 'Львів' AND region = 'Львівська';

Якщо city = 'Львів' дає 2% рядків, а region = 'Львівська' - 5%, планувальник очікує 0.1%. Насправді ж майже всі Львови - у Львівській області, і результат близький до 2% - у 20 разів більше. З такою оцінкою він обирає Nested Loop там, де потрібен Hash Join, і запит сповільнюється на порядки.

Розширена статистика (PostgreSQL 10+) дозволяє зібрати статистику по комбінації колонок:

CREATE STATISTICS addresses_city_region (dependencies, ndistinct, mcv)
    ON city, region FROM addresses;

ANALYZE addresses;

Види:

• dependencies - функціональні залежності (місто визначає область).
• ndistinct - кількість унікальних комбінацій значень; важливо для оцінки GROUP BY city, region.
• mcv (PostgreSQL 12+) - найчастіші комбінації значень з частотами; найточніша для конкретних пар значень.

З PostgreSQL 14 статистику можна збирати й для виразів: CREATE STATISTICS ... ON (lower(email)) FROM users.

Як зрозуміти, що це потрібно: EXPLAIN ANALYZE показує оцінку rows, що в десятки разів відрізняється від actual rows, на умові з кількома колонками однієї таблиці, а статистика окремих колонок свіжа.

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

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

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

Горизонт вакууму. VACUUM може прибрати мертву версію рядка, лише якщо вона невидима жодній активній транзакції. Найстаріша відкрита транзакція (чи знімок) встановлює горизонт: усі версії, що з'явилися після її початку, захищені від очищення - у всіх таблицях бази, не лише тих, що вона читала.

Що відбувається, поки транзакція висить годинами:

• Мертві версії накопичуються в таблицях з частими оновленнями (черги, лічильники, сесії).
• Таблиці й індекси роздуваються, запити сповільнюються - бо читають сторінки, повні мертвих рядків.
• Autovacuum працює, але марно: «не може видалити, бо ще потрібні».
• Після закриття транзакції роздуття саме не зникає - місце повторно використовується, але файли лишаються великими.

Джерела довгих транзакцій:

• idle in transaction - застосунок відкрив транзакцію й робить щось повільне поза базою (HTTP-запит, обробка файлу) або впав, не закривши її.
• Довгі звіти й аналітика на primary.
• Репліка з hot_standby_feedback = on, де йде довгий запит, - вона теж тримає горизонт на primary.
• Забуті prepared transactions і неактивні слоти реплікації (утримують WAL і горизонт).

Як знайти:

SELECT pid, state, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

Захист:

• idle_in_transaction_session_timeout - розривати сесії, що зависли в транзакції.
• transaction_timeout (PostgreSQL 17+) - обмеження на тривалість усієї транзакції.
• Аналітику - на репліку без hot_standby_feedback чи в окреме сховище.
• У коді - не робити повільних зовнішніх дій усередині DB::transaction().

Докладніше в документації: Налаштування клієнтських з'єднань