PostgreSQL

Найпопулярніші запитання та відповіді на співбесіді з PostgreSQL

1. Що таке PostgreSQL?

PostgreSQL

PostgreSQL — це open-source об’єктно-реляційна СУБД (ORDBMS), яка реалізує SQL і робить акцент на:

• транзакційність і надійність (ACID, WAL);
• конкурентний доступ без блокування читачів (MVCC);
• розширюваність (типи даних, оператори, функції, розширення, індекси).

Коротко:

• PostgreSQL — production-ready реляційна СУБД з MVCC і ACID.
• Сильні сторони: надійність, багатий SQL, розширюваність.

2. Чим PostgreSQL відрізняється від інших реляційних баз даних?

PostgreSQL

Ключові відмінності PostgreSQL зазвичай проявляються у практичних можливостях:

• Розширюваність: власні типи/оператори/функції, розширення (наприклад, PostGIS), різні методи індексації.
• Сильний SQL-движок: CTE (WITH), window functions, LATERAL, вирази, агрегати, продумані оптимізації.
• Робота з напівструктурованими даними: JSONB + індекси (часто GIN) для пошуку всередині документів.
• Індекси й доступ до даних: B-tree, GIN, GiST, BRIN (вибір під задачу і розмір даних).
• Конкурентність: MVCC зменшує конфлікти між читанням і записом.
• Безпека: ролі/привілеї, (за потреби) row-level security, аудит/розширення.

Коротко:

• Сильні сторони: розширюваність, потужний SQL, JSONB та індекси.
• Архітектурно PostgreSQL орієнтований на надійність і конкурентність через MVCC.

3. Які основні можливості PostgreSQL?

PostgreSQL

Основні можливості, які найчастіше використовують у прикладних системах:

• Транзакції: ACID, рівні ізоляції, блокування, керування конкуренцією.
• Багатий SQL: CTE, window functions, підзапити, агрегати, RETURNING.
• Upsert: INSERT ... ON CONFLICT ... DO UPDATE для ідемпотентних записів.
• JSONB: зберігання й пошук напівструктурованих даних, індексація.
• Індекси: B-tree (за замовчуванням), GIN (JSONB/arrays/full-text), GiST (гео/діапазони), BRIN (дуже великі таблиці з корельованими значеннями).
• Цілісність: constraints (PK/FK/UNIQUE/CHECK), NOT NULL, деферамбельні обмеження.
• Операційні можливості: реплікація, резервні копії через WAL, партиціювання, розширення.

INSERT INTO accounts (email, profile)
VALUES ('dev@example.com', '{"plan":"pro","active":true}'::jsonb)
ON CONFLICT (email) DO UPDATE
SET profile = EXCLUDED.profile
RETURNING id, email;

Коротко:

• PostgreSQL покриває транзакції, складний SQL, цілісність і продуктивність.
• Є сучасні фічі для практики: upsert, RETURNING, JSONB, різні типи індексів.

4. Що таке база даних у PostgreSQL?

PostgreSQL

У PostgreSQL кластер (інстанс) може містити кілька баз даних. База даних — це логічний контейнер із власними системними каталогами (metadata) та об’єктами (схеми, таблиці, функції тощо), до якого підключаються клієнти.

Практичні наслідки:

• запити виконуються в межах однієї бази даних на одне підключення;
• “крос-база” запити напряму не підтримуються; для цього використовують FDW або dblink;
• права доступу і налаштування часто задаються на рівні бази.

CREATE DATABASE app_db;

Коротко:

• База даних — окремий логічний простір усередині кластера PostgreSQL.
• Підключення й запити працюють в межах однієї БД; між БД — через FDW/dblink.

5. Що таке схема (schema)?

PostgreSQL

Schema — це простір імен усередині бази даних, який групує об’єкти: таблиці, представлення, типи, функції тощо. Схеми допомагають:

• розділяти домени/модулі (наприклад, billing, auth);
• уникати конфліктів імен;
• керувати правами доступу на рівні схем.

Пошук об’єктів без явного префікса керується search_path (часто за замовчуванням використовується public).

CREATE SCHEMA billing;

CREATE TABLE billing.invoices (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

Коротко:

• Schema — namespace у межах однієї БД.
• Об’єкти можна адресувати як schema.object, а пошук керується search_path.

6. У чому різниця між базою даних і схемою?

PostgreSQL

Різниця — у рівні ізоляції та області видимості:

• База даних (database): окремий логічний контейнер у кластері; підключення завжди до конкретної БД; системні каталоги й об’єкти ізольовані від інших БД.
• Схема (schema): namespace всередині конкретної БД; використовується для організації об’єктів і керування доступом.

Практичний підхід:

• різні середовища/продукти/тенанти з жорсткою ізоляцією — частіше окремі БД;
• модулі в одному продукті — частіше окремі схеми.

Коротко:

• База даних — контейнер у кластері; схема — namespace у межах БД.
• До іншої БД не “перейдеш” запитом — потрібне окреме підключення або FDW/dblink.

7. Що таке таблиця?

PostgreSQL

Таблиця — це реляційний об’єкт з рядками (records) і стовпцями (columns), де стовпці мають типи даних і можуть мати обмеження (constraints). У PostgreSQL таблиці також можуть бути:

• partitioned (партиційовані) для великих наборів даних;
• temporary (тимчасові) для сесійних/ETL задач;
• unlogged для швидких, але не критичних до втрат даних таблиць.

CREATE TABLE users (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

Коротко:

• Таблиця зберігає дані у вигляді рядків зі строго типізованими стовпцями.
• Constraints визначають правила цілісності, індекси — швидкість доступу.

8. Що таке первинний ключ (Primary Key)?

PostgreSQL

Primary Key — це обмеження, яке гарантує унікальність і NOT NULL для одного або кількох стовпців. У PostgreSQL для PRIMARY KEY автоматично створюється унікальний B-tree індекс, який використовується для швидкого пошуку та як ціль для зовнішніх ключів.

Практичні поради:

• обирай ключ, який стабільний і не змінюється;
• для сурогатного ключа в PostgreSQL зазвичай використовують GENERATED ... AS IDENTITY (замість SERIAL).

CREATE TABLE orders (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Коротко:

• PRIMARY KEY = унікально + не NULL.
• PostgreSQL створює унікальний індекс для первинного ключа автоматично.

9. Що таке зовнішній ключ (Foreign Key)?

PostgreSQL

Foreign Key — це обмеження, яке вимагає, щоб значення у стовпці(ях) дочірньої таблиці посилались на існуючий PRIMARY KEY або UNIQUE ключ у батьківській таблиці. Воно підтримує цілісність зв’язків і може задавати дії при зміні/видаленні батьківського рядка:

• ON DELETE RESTRICT|NO ACTION|CASCADE|SET NULL|SET DEFAULT
• ON UPDATE ...

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

CREATE TABLE orders (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
);

Коротко:

• FOREIGN KEY гарантує, що посилання веде на існуючий рядок у батьківській таблиці.
• Для продуктивності часто додають індекс на FK-стовпці вручну.

10. Що таке референційна цілісність?

PostgreSQL

Референційна цілісність — це гарантія, що зв’язки між таблицями залишаються коректними: у дочірніх таблицях немає “сиріт” (rows без відповідного батька), а зміни в батьківських рядках узгоджені з правилами зв’язку.

У PostgreSQL вона забезпечується переважно через FOREIGN KEY constraints, зокрема:

• перевірка існування посилання при INSERT/UPDATE;
• контроль поведінки при DELETE/UPDATE в батьківській таблиці (каскади/обмеження);
• можливість відкладеної перевірки в межах транзакції (DEFERRABLE), якщо потрібно тимчасово порушити порядок змін.

ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_fk
  FOREIGN KEY (order_id)
  REFERENCES orders(id)
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED;

Коротко:

• Референційна цілісність не допускає “сиріт” і неузгоджених зв’язків.
• У PostgreSQL це реалізовано через FOREIGN KEY + правила ON DELETE/ON UPDATE і (за потреби) DEFERRABLE.

11. Що таке послідовність (sequence) у PostgreSQL?

PostgreSQL

SEQUENCE - це окремий об'єкт БД, який генерує послідовність чисел (часто для ID). Найчастіше використовується через nextval(...), а також має currval(...) і setval(...).

Практично:

• sequence не гарантує "без дірок" (через rollback, cache, паралельність);
• значення унікальні в межах налаштувань sequence;
• sequence можна прив'язати до конкретного стовпця через OWNED BY.

CREATE SEQUENCE orders_id_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE orders (
  id bigint PRIMARY KEY DEFAULT nextval('orders_id_seq'),
  created_at timestamptz NOT NULL DEFAULT now()
);

Коротко:

• SEQUENCE генерує числові значення незалежно від таблиці.
• Використовується переважно для авто-генерації ключів.

12. Що таке SERIAL і як він працює?

PostgreSQL

SERIAL - це синтаксичний шорткат, а не окремий тип даних. При SERIAL PostgreSQL фактично:

• створює стовпець integer NOT NULL;
• створює sequence;
• ставить DEFAULT nextval('...') для цього стовпця;
• зв'язує sequence зі стовпцем (OWNED BY).

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email text NOT NULL UNIQUE
);

Сучасний рекомендований підхід - GENERATED ... AS IDENTITY.

Коротко:

• SERIAL автоматизує зв'язку integer + sequence + default.
• Це не "справжній" тип, а зручний шорткат DDL.

13. У чому різниця між SERIAL та INTEGER?

PostgreSQL

INTEGER - просто числовий тип. SERIAL - авто-налаштування для INTEGER зі sequence і DEFAULT nextval(...).

CREATE TABLE a (
  id INTEGER PRIMARY KEY
);

CREATE TABLE b (
  id SERIAL PRIMARY KEY
);

У таблиці a значення id треба задавати вручну (або окремо створювати sequence/default). У таблиці b значення генерується автоматично.

Коротко:

• INTEGER визначає лише тип числа.
• SERIAL додає автоматичну генерацію значень через sequence.

14. Що таке представлення (VIEW)?

PostgreSQL

VIEW - це збережений SQL-запит, який виглядає як таблиця під час читання. Дані у звичайному view не зберігаються окремо: щоразу виконується базовий запит.

CREATE VIEW active_users AS
SELECT id, email, created_at
FROM users
WHERE is_active = true;

Практичне використання:

• спрощення складних запитів;
• стабільний інтерфейс для звітів/API;
• обмеження доступу до частини полів/рядків.

Коротко:

• VIEW - віртуальна таблиця на основі SELECT.
• Добре підходить для повторного використання логіки читання.

15. Що таке матеріалізоване представлення (materialized view)?

PostgreSQL

MATERIALIZED VIEW зберігає результат запиту фізично, тому читання зазвичай швидше, ніж у звичайного VIEW. Дані оновлюються не автоматично, а через REFRESH MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW sales_daily AS
SELECT date_trunc('day', created_at) AS day, sum(total_cents) AS total_cents
FROM orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_daily;

Коротко:

• MATERIALIZED VIEW зберігає готовий результат запиту на диску.
• Потрібен явний REFRESH, щоб дані були актуальні.

16. Що таке індекс і для чого він використовується?

PostgreSQL

Індекс - це структура доступу, яка пришвидшує пошук/фільтрацію/сортування і join-и, зменшуючи обсяг сканування таблиці.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Компроміс:

• швидше SELECT для відповідних умов;
• повільніше INSERT/UPDATE/DELETE (бо індекси теж треба оновлювати);
• додаткове місце на диску.

Коротко:

• Індекс прискорює читання за вибраними полями.
• Надлишок індексів погіршує запис і збільшує storage.

17. Які типи індексів доступні в PostgreSQL?

PostgreSQL

Найуживаніші типи:

• B-tree - дефолтний, добре для =, <, >, BETWEEN, ORDER BY.
• GIN - для JSONB, масивів, full-text пошуку.
• GiST - для геоданих, діапазонів, nearest-neighbor запитів.
• BRIN - для дуже великих таблиць із фізично впорядкованими значеннями.
• Hash - вузький випадок, зазвичай рідше за B-tree.

CREATE INDEX idx_events_payload_gin ON events USING GIN (payload);

Коротко:

• Вибір типу індексу залежить від типу даних і профілю запитів.
• Для JSONB найчастіше використовують GIN.

18. У чому різниця між CHAR, VARCHAR і TEXT?

PostgreSQL

• CHAR(n) - фіксована довжина: коротші значення доповнюються пробілами.
• VARCHAR(n) - змінна довжина з лімітом n.
• TEXT - змінна довжина без ліміту.

У PostgreSQL продуктивність VARCHAR і TEXT зазвичай еквівалентна для більшості кейсів; вибір - це переважно про бізнес-обмеження, а не швидкість.

CREATE TABLE profiles (
  country_code CHAR(2),
  username VARCHAR(50) NOT NULL,
  bio TEXT
);

Коротко:

• CHAR(n) фіксує довжину, VARCHAR(n) обмежує максимум, TEXT без ліміту.
• На практиці часто беруть TEXT або VARCHAR(n) + CHECK за потреби.

19. У чому різниця між DATE і TIMESTAMP?

PostgreSQL

• DATE зберігає лише календарну дату (YYYY-MM-DD).
• TIMESTAMP зберігає дату і час без часової зони.
• TIMESTAMPTZ (timestamp with time zone) зберігає момент часу коректно для різних TZ і зазвичай кращий для backend-подій.

CREATE TABLE sessions (
  session_date DATE NOT NULL,
  started_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Коротко:

• DATE - тільки дата, TIMESTAMP - дата+час.
• Для подій у продакшені частіше використовують TIMESTAMPTZ.

20. Що таке pgAdmin?

PostgreSQL

pgAdmin - графічний клієнт (GUI) для адміністрування PostgreSQL: перегляд об'єктів, виконання SQL, керування ролями, моніторинг, backup/restore операції через інтерфейс.

Практично використовується для:

• швидкої навігації по схемах і таблицях;
• ручного запуску запитів і перегляду планів;
• адміністративних задач без CLI.

Коротко:

• pgAdmin - GUI-інструмент для роботи з PostgreSQL.
• Зручний для адміністрування й операційних задач.

21. Що таке psql?

PostgreSQL

psql - офіційний CLI-клієнт PostgreSQL для запуску SQL і адміністрування з термінала.

\c app_db
\dt
SELECT now();

Корисно для автоматизації (scripts/CI), швидких перевірок і роботи на серверах без GUI.

Коротко:

• psql - командний інтерфейс PostgreSQL.
• Найкращий для скриптів, DevOps-задач і швидких SQL-операцій.

22. Що таке транзакція?

PostgreSQL

Транзакція - це група SQL-операцій, які виконуються як одна атомарна одиниця: або фіксуються всі (COMMIT), або скасовуються всі (ROLLBACK).

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

Якщо будь-який крок падає, роблять ROLLBACK, щоб не залишити систему в неконсистентному стані.

Коротко:

• Транзакція забезпечує цілісність групи змін.
• COMMIT фіксує, ROLLBACK відміняє зміни.

23. Що таке властивості ACID?

PostgreSQL

ACID - базові гарантії надійної транзакційної системи:

• Atomicity - все або нічого;
• Consistency - дотримання constraints і правил цілісності;
• Isolation - паралельні транзакції не ламають одна одну;
• Durability - після COMMIT зміни не губляться (підтримується, зокрема, через WAL).

Коротко:

• ACID гарантує коректність і передбачуваність транзакцій.
• У PostgreSQL ці гарантії вбудовані в ядро системи.

24. Які рівні ізоляції транзакцій підтримує PostgreSQL?

PostgreSQL

PostgreSQL підтримує:

• READ COMMITTED;
• REPEATABLE READ;
• SERIALIZABLE.

READ UNCOMMITTED у PostgreSQL поводиться як READ COMMITTED (через MVCC), тому "брудне читання" фактично не використовується.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM orders WHERE status = 'new';
COMMIT;

Коротко:

• Реально використовуються 3 рівні: READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
• Вищий рівень ізоляції зменшує аномалії, але може знизити паралелізм.

25. Який рівень ізоляції використовується за замовчуванням?

PostgreSQL

За замовчуванням у PostgreSQL використовується READ COMMITTED.

Це означає:

• кожен SQL-оператор бачить лише дані, закомічені до початку цього оператора;
• у межах однієї транзакції різні SELECT можуть бачити різні "зрізи" даних, якщо інші транзакції встигли закомітити зміни.

SHOW default_transaction_isolation;

Коротко:

• Дефолтний рівень ізоляції: READ COMMITTED.
• Це баланс між консистентністю і продуктивністю для більшості OLTP-кейсів.

26. Які типи JOIN існують у PostgreSQL?

PostgreSQL

У PostgreSQL використовують такі основні типи JOIN:

• INNER JOIN - тільки рядки, де є збіг у обох таблицях;
• LEFT JOIN - усі рядки з лівої таблиці + збіги з правої;
• RIGHT JOIN - усі рядки з правої таблиці + збіги з лівої;
• FULL OUTER JOIN - усі рядки з обох таблиць, навіть без збігів;
• CROSS JOIN - декартовий добуток (кожен з кожним);
• SELF JOIN - join таблиці самої на себе.

SELECT o.id, c.email
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

Коротко:

• Тип JOIN визначає, які незбіглі рядки потрапляють у результат.
• Найчастіше в практиці: INNER JOIN і LEFT JOIN.

27. У чому різниця між INNER JOIN і LEFT JOIN?

PostgreSQL

• INNER JOIN повертає лише рядки з відповідністю в обох таблицях.
• LEFT JOIN повертає всі рядки лівої таблиці; якщо відповідності немає, колонки правої таблиці будуть NULL.

SELECT c.id, c.email, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Це корисно, коли треба бачити і сутності "без зв'язаних записів" (наприклад, клієнтів без замовлень).

Коротко:

• INNER JOIN відкидає незбіглі рядки.
• LEFT JOIN зберігає всі рядки з лівої таблиці.

28. У чому різниця між INNER JOIN і OUTER JOIN?

PostgreSQL

INNER JOIN повертає тільки перетин двох наборів.
OUTER JOIN (тобто LEFT/RIGHT/FULL) додатково повертає незбіглі рядки з однієї або обох сторін із NULL у колонках іншої таблиці.

Коротко:

• INNER JOIN = тільки збіги.
• OUTER JOIN = збіги + незбіглі рядки (залежно від типу).

29. Що таке підзапит (subquery)?

PostgreSQL

Підзапит - це запит усередині іншого запиту. Його можна використовувати в SELECT, FROM, WHERE, HAVING або в DML-операціях.

SELECT id, email
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE created_at >= now() - interval '30 days'
);

Коротко:

• Підзапит дозволяє вкладати одну логіку вибірки в іншу.
• Може бути некорельованим або корельованим.

30. Коли варто використовувати JOIN, а коли підзапит?

PostgreSQL

Практичне правило:

• використовуй JOIN, коли треба повернути поля з кількох таблиць в одному наборі;
• використовуй підзапит, коли потрібна проміжна логіка фільтрації/агрегації або перевірка існування (EXISTS);
• для читабельності складних кроків часто краще CTE (WITH).

SELECT c.id, c.email
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.status = 'paid'
);

Коротко:

• JOIN зручний для "склеювання" даних.
• Підзапит/EXISTS зручний для умовної перевірки та фільтрації.

31. Що таке GROUP BY?

PostgreSQL

GROUP BY групує рядки за вибраними полями, щоб застосувати агрегатні функції (count, sum, avg, min, max) до кожної групи.

SELECT customer_id, count(*) AS orders_count
FROM orders
GROUP BY customer_id;

Коротко:

• GROUP BY формує групи рядків.
• Застосовується разом з агрегатними функціями.

32. Що таке HAVING?

PostgreSQL

HAVING фільтрує вже згруповані результати після GROUP BY, зазвичай за умовами на агрегати.

SELECT customer_id, count(*) AS orders_count
FROM orders
GROUP BY customer_id
HAVING count(*) >= 5;

Коротко:

• HAVING працює після агрегації.
• Використовується для фільтрації груп.

33. У чому різниця між WHERE і HAVING?

PostgreSQL

• WHERE фільтрує сирі рядки до GROUP BY;
• HAVING фільтрує вже згруповані рядки після GROUP BY.

SELECT customer_id, count(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING count(*) >= 3;

Коротко:

• WHERE - фільтр до агрегації.
• HAVING - фільтр після агрегації.

34. У чому різниця між UNION і UNION ALL?

PostgreSQL

• UNION об'єднує результати і прибирає дублікати (додаткова вартість сортування/хешування).
• UNION ALL просто конкатенує результати без видалення дублікатів (швидше).

SELECT email FROM leads
UNION ALL
SELECT email FROM customers;

Коротко:

• UNION видаляє дублікати.
• UNION ALL швидший, якщо дублікати допустимі.

35. Що таке CTE (Common Table Expression)?

PostgreSQL

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

WITH recent_orders AS (
  SELECT id, customer_id, total_cents
  FROM orders
  WHERE created_at >= now() - interval '7 days'
)
SELECT customer_id, sum(total_cents) AS weekly_total
FROM recent_orders
GROUP BY customer_id;

Коротко:

• CTE покращує читабельність складних запитів.
• Працює лише в межах одного SQL-виразу.

36. У чому різниця між CTE і VIEW?

PostgreSQL

• CTE живе тільки в одному запиті (WITH ...), не зберігається як об'єкт БД.
• VIEW - постійний об'єкт схеми, який можна багаторазово використовувати різними запитами.

Практично:

• CTE - для локальної, одноразової декомпозиції складного запиту;
• VIEW - для стабільного повторного інтерфейсу доступу до даних.

Коротко:

• CTE тимчасовий і локальний для запиту.
• VIEW постійний і перевикористовуваний.

37. Що таке віконні функції (window functions)?

PostgreSQL

Віконні функції обчислюють значення по "вікну" рядків, не згортаючи результат до одного рядка на групу (на відміну від GROUP BY).

SELECT
  customer_id,
  created_at,
  total_cents,
  sum(total_cents) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders;

Типові функції: row_number, rank, dense_rank, lag, lead, sum(...) OVER (...).

Коротко:

• Window functions дають аналітику по рядках без втрати деталізації.
• Ключове: OVER (PARTITION BY ... ORDER BY ...).

38. Коли варто використовувати віконні функції замість GROUP BY?

PostgreSQL

Використовуй window functions, коли треба:

• зберегти всі рядки детального набору;
• додати агрегати/ранги/попереднє-наступне значення поверх деталізації;
• порахувати "running totals" або топ-N у групі.

GROUP BY підходить, коли потрібен саме підсумок по групі з меншою кількістю рядків.

Коротко:

• Window functions: агрегати + детальні рядки одночасно.
• GROUP BY: компактний підсумковий результат по групах.

39. Як реалізувати пагінацію в PostgreSQL?

PostgreSQL

Базовий варіант: LIMIT/OFFSET.
Для великих таблиць краще keyset pagination (WHERE id > ... ORDER BY id LIMIT ...) - стабільніше і швидше.

SELECT id, email, created_at
FROM users
ORDER BY id
LIMIT 50 OFFSET 100;

SELECT id, email, created_at
FROM users
WHERE id > 1000
ORDER BY id
LIMIT 50;

Коротко:

• LIMIT/OFFSET простий, але дорожчає на великих зміщеннях.
• Keyset pagination кращий для high-load і великих даних.

40. Як виконати пошук без урахування регістру?

PostgreSQL

Найпростіше:

• ILIKE для pattern-пошуку без регістру;
• або нормалізація через lower(...) з функціональним індексом;
• для рівності можна застосувати тип citext (через extension).

SELECT id, email
FROM users
WHERE email ILIKE '%@example.com';

Коротко:

• Для LIKE-подібного пошуку використовують ILIKE.
• Для продуктивності варто продумати індекси або citext.

41. Що таке JSON і JSONB?

PostgreSQL

• JSON зберігає текстове представлення JSON майже "як є".
• JSONB зберігає бінарне, нормалізоване представлення JSON для ефективної обробки.

CREATE TABLE events (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

Коротко:

• JSON і JSONB дозволяють зберігати напівструктуровані дані.
• У більшості production-кейсів вибирають JSONB.

42. У чому різниця між JSON і JSONB?

PostgreSQL

Основні відмінності:

• JSON швидше вставляється як сирий текст, але повільніше обробляється;
• JSONB трохи дорожчий на записі, зате краще для фільтрації/пошуку;
• JSONB підтримує ефективне індексування (зокрема GIN);
• JSONB не зберігає порядок ключів як вхідний текст.

CREATE INDEX idx_events_payload_gin
ON events
USING GIN (payload);

Коротко:

• JSON - текстовий формат зберігання.
• JSONB - оптимізований для запитів і індексів.

43. Коли варто використовувати JSONB замість реляційних колонок?

PostgreSQL

JSONB доцільний, коли:

• структура даних змінюється часто або має багато опційних полів;
• частина даних напівструктурована (settings, metadata, payload);
• потрібна гнучкість без частих DDL-міграцій.

Реляційні колонки кращі, коли:

• поля стабільні, часто фільтруються/join-яться;
• потрібні строгі constraints і чіткі типи на рівні схеми.

Комбінований підхід у практиці найчастіший: ключові поля реляційно, змінна частина в JSONB.

Коротко:

• JSONB - для гнучкої, змінної структури.
• Критичні для запитів поля краще тримати окремими колонками.

44. Що таке повнотекстовий пошук (full-text search) у PostgreSQL?

PostgreSQL

Full-text search (FTS) - це вбудований механізм пошуку по тексту з лексичним аналізом: токенізація, нормалізація слів, ранжування релевантності.

Базові інструменти:

• to_tsvector(...) - підготовка текстового вектора;
• to_tsquery(...) / plainto_tsquery(...) - пошуковий вираз;
• оператор @@ - перевірка збігу;
• ts_rank(...) - ранжування.

SELECT id, title
FROM articles
WHERE to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(body, ''))
  @@ plainto_tsquery('simple', 'postgres optimization');

Коротко:

• FTS дає якісний пошук по тексту в самій БД.
• Для швидкості зазвичай додають GIN-індекс по tsvector.

45. Що таке збережені процедури (stored procedures)?

PostgreSQL

Stored procedure - це серверна процедура, яку викликають через CALL. На відміну від функцій, процедура може керувати транзакціями всередині свого виконання (залежно від контексту виклику).

Використання:

• інкапсуляція бізнес-операцій на боці БД;
• централізація складної багатокрокової логіки.

CALL process_monthly_billing('2026-01-01');

Коротко:

• Procedure викликається CALL і орієнтована на операційні сценарії.
• Дозволяє перенести частину бізнес-логіки ближче до даних.

46. Що таке тригери і коли їх використовують?

PostgreSQL

Тригер - це механізм автоматичного виклику функції при подіях INSERT/UPDATE/DELETE/TRUNCATE (до або після події, на рядок або на statement).

Типові кейси:

• аудит змін;
• автоматичне заповнення технічних полів;
• складні правила цілісності, які важко виразити простими constraints.

CREATE TRIGGER users_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Коротко:

• Тригери автоматизують реакцію на зміни даних.
• Варто застосовувати обережно, щоб не ховати критичну бізнес-логіку.

47. Що таке EXPLAIN?

PostgreSQL

EXPLAIN показує план виконання запиту, який обрав оптимізатор: типи сканувань, join-стратегії, оцінки рядків і вартості.

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;

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

Коротко:

• EXPLAIN показує запланований (оцінений) шлях виконання.
• Основа для діагностики продуктивності SQL.

48. Що таке EXPLAIN ANALYZE?

PostgreSQL

EXPLAIN ANALYZE реально виконує запит і показує фактичні метрики: реальний час, реальну кількість рядків, loops, memory usage (залежно від параметрів).

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42;

Порівняння estimated vs actual допомагає виявити помилки оцінок статистики й вузькі місця.

Коротко:

• EXPLAIN ANALYZE дає реальну картину виконання.
• Найцінніший інструмент для тюнінгу конкретного запиту.

49. Як виявити повільні запити?

PostgreSQL

Практичний підхід:

• увімкнути pg_stat_statements і дивитися top SQL за часом/частотою;
• налаштувати log_min_duration_statement для логування довгих запитів;
• аналізувати плани через EXPLAIN ANALYZE;
• контролювати I/O, locks і автоваакум для проблемних таблиць.

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Коротко:

• Починай з pg_stat_statements і логів повільних запитів.
• Потім точково аналізуй найважчі SQL через EXPLAIN ANALYZE.

50. Як оптимізувати продуктивність запитів у PostgreSQL?

PostgreSQL

Робоча стратегія оптимізації:

• прибрати зайві дані в запиті (SELECT тільки потрібні колонки, рання фільтрація);
• створити правильні індекси під реальні WHERE/JOIN/ORDER BY;
• перевіряти плани (EXPLAIN ANALYZE) і статистику (ANALYZE, pg_stat_statements);
• уникати N+1 на рівні застосунку;
• оптимізувати важкі агрегації через pre-aggregation/materialized views;
• тримати VACUUM/ANALYZE і autovacuum у здоровому стані.

Коротко:

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

51. У яких випадках PostgreSQL не використовує індекс?

PostgreSQL

Типові випадки:

• умова повертає дуже велику частку таблиці, і Seq Scan дешевший;
• на індексовану колонку накладена функція/каст без відповідного функціонального індексу;
• використовується LIKE '%term' без спеціалізованого індексу;
• статистика застаріла (ANALYZE давно не виконувався);
• OR/складні умови дають планувальнику дорожчий шлях через індекс.

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE lower(email) = 'dev@example.com';

Коротко:

• Індекс не гарантує використання, якщо планувальнику вигідніший Seq Scan.
• Найчастіші причини: селективність, функції над колонками, застаріла статистика.

52. Що таке складений індекс (composite index)?

PostgreSQL

Складений індекс містить кілька колонок в одному індексі, порядок колонок важливий.

CREATE INDEX idx_orders_customer_created_at
ON orders (customer_id, created_at DESC);

Такий індекс добре працює для запитів, які фільтрують по customer_id і сортують/фільтрують по created_at.

Коротко:

• Composite index = один індекс на кілька полів.
• Найбільша користь, коли порядок колонок відповідає реальним запитам.

53. Що таке частковий індекс (partial index)?

PostgreSQL

Частковий індекс індексує лише рядки, що задовольняють умову WHERE в індексі.

CREATE INDEX idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'paid';

Це зменшує розмір індексу і прискорює запити по "гарячому" піднабору даних.

Коротко:

• Partial index покриває лише потрібну частину таблиці.
• Добре працює для частих запитів з фіксованою умовою.

54. Що таке VACUUM і для чого він потрібен?

PostgreSQL

VACUUM прибирає "мертві" версії рядків (через MVCC), оновлює visibility map і допомагає уникати роздування таблиць та індексів.

VACUUM (VERBOSE, ANALYZE) orders;

Коротко:

• VACUUM звільняє простір для повторного використання всередині таблиці.
• Критично важливий для стабільної продуктивності в OLTP.

55. У чому різниця між VACUUM і VACUUM FULL?

PostgreSQL

• VACUUM не блокує таблицю ексклюзивно і не повертає файл ОС повністю;
• VACUUM FULL переписує таблицю, повертає дисковий простір ОС, але бере ACCESS EXCLUSIVE lock.

VACUUM FULL orders;

VACUUM FULL використовують рідко, зазвичай для сильного bloat і у вікно обслуговування.

Коротко:

• VACUUM - регулярна "гігієна" без важкої блокировки.
• VACUUM FULL - агресивна операція з повним переписом таблиці.

56. Що таке ANALYZE?

PostgreSQL

ANALYZE збирає статистику розподілу значень у колонках, яку планувальник використовує для вибору оптимального плану.

ANALYZE orders;

Без актуальної статистики оптимізатор може обирати погані плани.

Коротко:

• ANALYZE не "чистить" дані, а оновлює статистику.
• Актуальна статистика напряму впливає на якість планів.

57. Що таке Autovacuum?

PostgreSQL

Autovacuum - фонова служба PostgreSQL, яка автоматично запускає VACUUM і ANALYZE за порогами змін таблиці.

Ключові задачі:

• не дати накопичитись dead tuples;
• підтримувати актуальну статистику;
• запобігати transaction ID wraparound.

Коротко:

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

58. Що таке роздування таблиці (table bloat)?

PostgreSQL

Table bloat - це надлишковий обсяг таблиці/індексів через накопичення мертвих версій рядків і неефективне перевикористання сторінок.

Наслідки:

• більше I/O;
• повільніші scan/join;
• зайвий дисковий простір.

Коротко:

• Bloat зростає при активних UPDATE/DELETE.
• Контроль: healthy autovacuum, моніторинг і за потреби VACUUM FULL/repack.

59. Яке призначення pg_stat_activity?

PostgreSQL

pg_stat_activity показує активні backend-процеси: хто підключений, який запит виконується, стан сесії, час старту, wait events.

SELECT pid, usename, state, query_start, wait_event_type, wait_event, query
FROM pg_stat_activity
ORDER BY query_start;

Коротко:

• pg_stat_activity - базовий live-огляд активності БД.
• Використовується для діагностики блокувань і довгих запитів.

60. Як знайти довготривалі запити?

PostgreSQL

SELECT
  pid,
  now() - query_start AS duration,
  state,
  wait_event_type,
  query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC
LIMIT 20;

Додатково фільтрують системні сесії та порівнюють із SLA/таймаутами застосунку.

Коротко:

• Для live-пошуку довгих запитів використовують pg_stat_activity.
• Оцінюють і час, і стан очікування (wait_event).

61. Що таке MVCC (Multi-Version Concurrency Control)?

PostgreSQL

MVCC - механізм багатоверсійності, за якого UPDATE/DELETE створюють нові версії рядків, а читачі бачать консистентний snapshot без блокування записів читанням.

Коротко:

• MVCC дає високий паралелізм читання і запису.
• Потребує регулярного VACUUM, щоб прибирати застарілі версії.

62. Як PostgreSQL обробляє конкурентний доступ?

PostgreSQL

PostgreSQL комбінує:

• MVCC для неблокуючих читань;
• lock manager для керування конфліктами доступу;
• рівні ізоляції транзакцій для контролю аномалій.

Для критичних секцій використовують SELECT ... FOR UPDATE/SHARE.

Коротко:

• Конкурентність базується на MVCC + блокуваннях.
• Ізоляція та явні lock-и дозволяють контролювати конфлікти.

63. Що таке блокування на рівні рядків (row-level locking)?

PostgreSQL

Row-level lock блокує конкретні рядки, а не всю таблицю, щоб інші транзакції не змінювали ті самі записи одночасно.

BEGIN;
SELECT id, balance
FROM accounts
WHERE id = 42
FOR UPDATE;

Коротко:

• Row lock мінімізує контеншн порівняно з table lock.
• Часто застосовується для безпечних фінансових/інвентарних операцій.

64. Що таке deadlock?

PostgreSQL

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

PostgreSQL детектить deadlock і примусово завершує одну транзакцію з помилкою.

Коротко:

• Deadlock = циклічне очікування блокувань.
• Профілактика: однаковий порядок доступу до ресурсів і короткі транзакції.

65. Як виявити блокуючі запити?

PostgreSQL

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocker.pid AS blocker_pid,
  blocker.query AS blocker_query
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity blocked ON blocked.pid = bl.pid
JOIN pg_catalog.pg_locks kl
  ON kl.locktype = bl.locktype
 AND kl.database IS NOT DISTINCT FROM bl.database
 AND kl.relation IS NOT DISTINCT FROM bl.relation
 AND kl.page IS NOT DISTINCT FROM bl.page
 AND kl.tuple IS NOT DISTINCT FROM bl.tuple
 AND kl.pid <> bl.pid
JOIN pg_catalog.pg_stat_activity blocker ON blocker.pid = kl.pid
WHERE NOT bl.granted
  AND kl.granted;

Коротко:

• Комбінують pg_locks і pg_stat_activity, щоб знайти blocker/blockee.
• Далі вирішують: чекати, pg_cancel_backend або pg_terminate_backend.

66. Що таке pg_locks?

PostgreSQL

pg_locks - системне представлення про поточні lock-и: тип, об'єкт, режим блокування, ознака granted, pid власника.

Воно не містить повний текст запиту, тому зазвичай поєднується з pg_stat_activity.

Коротко:

• pg_locks показує технічну картину блокувань.
• Для діагностики майже завжди дивляться разом із pg_stat_activity.

67. У чому різниця між pg_cancel_backend і pg_terminate_backend?

PostgreSQL

• pg_cancel_backend(pid) скасовує поточний запит, але сесія лишається живою;
• pg_terminate_backend(pid) завершує весь backend-процес (сесію).

SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

Коротко:

• cancel м'якший варіант.
• terminate жорсткіший і застосовується, коли cancel не допоміг.

68. Як створити резервну копію бази даних PostgreSQL?

PostgreSQL

Поширені підходи:

• логічний backup: pg_dump/pg_dumpall;
• фізичний backup: pg_basebackup або файлові snapshots з WAL.

-- shell command

pg_dump -Fc -d app_db -f app_db.dump

Коротко:

• Логічний backup зручний для міграцій і вибіркового restore.
• Для DR-сценаріїв частіше будують фізичні backup + архівацію WAL.

69. Що таке pg_dump?

PostgreSQL

pg_dump - утиліта логічного backup однієї БД: вивантажує схему і/або дані у SQL або кастомний архівний формат.

pg_dump -Fc -d app_db -f app_db.dump

Restore зазвичай роблять через pg_restore.

Коротко:

• pg_dump створює логічний дамп для backup/міграції.
• Формат -Fc зручний для selective restore і паралельного відновлення.

70. Що таке pg_basebackup?

PostgreSQL

pg_basebackup робить фізичну копію data directory кластера PostgreSQL, зазвичай разом з потрібними WAL для консистентного відновлення.

pg_basebackup -D /backups/base -X stream -c fast

Часто використовується для налаштування реплік і disaster recovery.

Коротко:

• pg_basebackup - інструмент фізичного backup всього кластера.
• Ключовий для реплікації і відновлення після аварій.

71. Що таке реплікація в PostgreSQL?

PostgreSQL

Реплікація - це копіювання змін з primary на один або кілька standby-вузлів для:

• high availability;
• disaster recovery;
• масштабування читання.

Коротко:

• Реплікація підвищує відмовостійкість і доступність.
• У PostgreSQL є фізична і логічна моделі реплікації.

72. Що таке потокова реплікація (streaming replication)?

PostgreSQL

Streaming replication - це передача WAL-записів з primary на standby майже в реальному часі по мережі.

Режими:

• asynchronous (менша затримка запису на primary);
• synchronous (сильніші гарантії, але вища латентність коміту).

Коротко:

• Streaming replication базується на WAL-потоці.
• Використовується для HA і read replicas.

73. У чому різниця між фізичною та логічною реплікацією?

PostgreSQL

• Фізична реплікація копіює WAL-блоки на рівні сторінок/файлів кластера;
• Логічна реплікація передає зміни як SQL-логіку таблиць (publishing/subscription).

Фізична краще для standby/HA, логічна - для вибіркової реплікації таблиць, міграцій і інтеграцій.

Коротко:

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

74. Що таке WAL (Write-Ahead Logging)?

PostgreSQL

WAL - журнал змін, у який PostgreSQL спочатку записує інформацію про модифікацію, і лише потім застосовує її до сторінок даних.

Це забезпечує:

• crash recovery;
• реплікацію;
• point-in-time recovery (PITR).

Коротко:

• WAL гарантує надійність комітів і відновлення після збоїв.
• Реплікація і backup-стратегії PostgreSQL будуються навколо WAL.

75. Що таке tablespace?

PostgreSQL

Tablespace - це логічне ім'я, яке вказує на фізичний шлях зберігання даних на диску. Дозволяє розміщувати різні об'єкти БД на різних storage.

CREATE TABLESPACE fast_ssd LOCATION '/pg_tblspc/fast_ssd';

Типові кейси: винести великі індекси на швидший диск або "холодні" дані на дешевший storage.

Коротко:

• Tablespace керує фізичним розміщенням об'єктів PostgreSQL.
• Дає гнучкість у storage-стратегії.

76. Що таке партиціювання таблиць?

PostgreSQL

Партиціювання - це розбиття великої таблиці на менші фізичні частини (partitions) за ключем (RANGE, LIST, HASH) при єдиному логічному інтерфейсі таблиці.

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

Коротко:

• Партиціювання ділить великі таблиці на керовані сегменти.
• Запити можуть читати лише релевантні partition-и (partition pruning).

77. Які переваги дає партиціювання?

PostgreSQL

Переваги:

• швидші запити по ключу партиціювання через pruning;
• простіший lifecycle management (архівація/видалення старих партицій);
• менші індекси в межах кожної партиції;
• операційна зручність для великих time-series таблиць.

Коротко:

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

78. Що таке пулінг з’єднань (connection pooling) і навіщо він потрібен?

PostgreSQL

Connection pooling - це перевикористання невеликого пулу вже відкритих з'єднань замість створення нового DB-з'єднання на кожен запит.

Навіщо:

• менший overhead на TCP/SSL/auth;
• захист PostgreSQL від надмірної кількості бекендів;
• стабільніша латентність під піковим навантаженням.

Коротко:

• Pooling зменшує вартість конектів і підвищує стабільність.
• Особливо важливий для high-concurrency веб-застосунків.

79. Що таке PgBouncer?

PostgreSQL

PgBouncer - легкий зовнішній connection pooler для PostgreSQL. Працює між застосунком і БД, керує пулом конектів і підтримує режими pooling (session, transaction, statement).

Коротко:

• PgBouncer зменшує кількість одночасних backend-процесів PostgreSQL.
• Найпопулярніший інструмент pooling у production.

80. Як завершити сесію в PostgreSQL?

PostgreSQL

Сесію завершують через pg_terminate_backend(pid).

SELECT pg_terminate_backend(12345);

Перед цим зазвичай перевіряють сесію в pg_stat_activity, щоб не зупинити критичний процес.

Коротко:

• Для завершення сесії використовують pg_terminate_backend.
• Для м'якого варіанту спочатку пробують pg_cancel_backend.

81. Що таке планувальник запитів (query planner) у PostgreSQL і як він працює?

PostgreSQL

Query planner (optimizer) аналізує SQL-запит і будує найменш вартісний план виконання за моделлю cost-based optimization.

Він оцінює:

• можливі шляхи доступу (Seq Scan, Index Scan, Bitmap Scan);
• порядок і типи join-ів;
• сортування, агрегації, використання пам'яті та I/O;
• статистику таблиць і налаштування сервера.

Коротко:

• Planner обирає план із найменшою очікуваною вартістю.
• Якість плану напряму залежить від статистики і структури запиту.

82. Які фактори впливають на вибір плану виконання запиту?

PostgreSQL

Ключові фактори:

• статистика таблиць/колонок (ANALYZE);
• наявність і типи індексів;
• селективність умов у WHERE;
• обсяг даних і очікувана кардинальність;
• налаштування планувальника (random_page_cost, effective_cache_size, work_mem);
• форма SQL (CTE, підзапити, порядок join-ів).

Коротко:

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

83. Що таке статистика таблиць і як PostgreSQL її використовує?

PostgreSQL

Статистика - це метадані про розподіл значень у колонках (null fraction, distinct, histogram, most common values), які PostgreSQL зберігає в системних каталогах.

Planner використовує ці оцінки, щоб передбачити кількість рядків і обрати ефективний план.

Коротко:

• Статистика потрібна для коректних оцінок кардинальності.
• Без актуальної статистики оптимізатор частіше помиляється у виборі плану.

84. Як вручну оновити статистику таблиці?

PostgreSQL

Оновлення статистики виконують командою ANALYZE:

ANALYZE orders;

Для всієї БД:

ANALYZE;

Коротко:

• ANALYZE оновлює статистику для planner.
• Використовують після великих змін даних або перед аналізом повільних запитів.

85. Що таке параметр work_mem і як він впливає на продуктивність?

PostgreSQL

work_mem - обсяг пам'яті на одну операцію сортування/хешування в межах одного запиту.

Якщо work_mem замалий, PostgreSQL переносить операції на диск (spill), що уповільнює запит.

SET LOCAL work_mem = '128MB';

Коротко:

• work_mem впливає на Sort, Hash Join, Hash Aggregate.
• Завищення небезпечне: множиться на кількість одночасних операцій і сесій.

86. Що таке connection limit і як керувати кількістю з’єднань?

PostgreSQL

Connection limit - це обмеження на кількість одночасних підключень (на рівні кластера або ролі).

Керування:

• глобально через max_connections;
• для ролі через ALTER ROLE ... CONNECTION LIMIT;
• практично - через connection pooler (PgBouncer).

ALTER ROLE app_user CONNECTION LIMIT 50;

Коротко:

• Ліміт з'єднань захищає сервер від перевантаження процесами.
• У production зазвичай використовують pooler, а не високий max_connections.

87. Як налаштувати PostgreSQL для роботи під високим навантаженням?

PostgreSQL

Базовий практичний підхід:

• налаштувати пам'ять (shared_buffers, work_mem, maintenance_work_mem);
• оптимізувати WAL/checkpoint (wal_compression, checkpoint_timeout, max_wal_size);
• використовувати PgBouncer;
• прибрати повільні запити через pg_stat_statements + EXPLAIN ANALYZE;
• підтримувати healthy autovacuum;
• застосувати read replicas для масштабування читання.

Коротко:

• Під high-load критичні: query tuning, індекси, autovacuum, pooling.
• Налаштування параметрів без вимірювань дає слабкий ефект.

88. Що таке read replica і в яких сценаріях її використовують?

PostgreSQL

Read replica - standby-вузол, який приймає зміни з primary і обслуговує запити на читання.

Сценарії:

• масштабування read-навантаження;
• звітність/аналітика без навантаження primary;
• резерв для швидкого перемикання при збоях.

Коротко:

• Read replica розвантажує primary по читанню.
• Записи зазвичай виконуються тільки на primary.

89. Як організувати відмовостійкість (high availability) у PostgreSQL?

PostgreSQL

Типова HA-архітектура:

• primary + 1..N standby з streaming replication;
• менеджер failover (наприклад, Patroni/repmgr) + distributed consensus (часто etcd/Consul);
• health checks, автоматичне перемикання і віртуальна точка доступу (VIP/LB/DNS);
• регулярні backup + перевірка restore.

Коротко:

• HA це не тільки реплікація, а й керований failover.
• Працездатність підтверджується регулярними DR-тестами.

90. Що таке failover і як він реалізується?

PostgreSQL

Failover - автоматичне або ручне перемикання ролі primary на standby при відмові основного вузла.

Реалізація включає:

• детекцію недоступності primary;
• promotion standby до primary;
• переналаштування маршрутів трафіку застосунків;
• повторне підключення старого primary як репліки (після відновлення).

Коротко:

• Failover мінімізує downtime при аварії primary.
• Важливі чіткі runbooks і автоматизація перемикання.

91. Що таке логічне декодування (logical decoding)?

PostgreSQL

Logical decoding - це читання змін із WAL у вигляді логічних подій (INSERT/UPDATE/DELETE), а не фізичних блоків.

Працює через replication slots і output plugins (наприклад, pgoutput, wal2json).

Коротко:

• Logical decoding перетворює WAL у події рівня даних.
• Є базою для CDC та інтеграційних потоків.

92. Як використовувати PostgreSQL для CDC (Change Data Capture)?

PostgreSQL

Типовий шлях:

• увімкнути логічну реплікацію;
• створити publication для потрібних таблиць;
• читати потік змін через subscriber/конектор (наприклад, Debezium);
• доставляти події в Kafka/черги/інтеграційні сервіси.

CREATE PUBLICATION app_pub FOR TABLE orders, order_items;

Коротко:

• CDC у PostgreSQL зазвичай будується на logical decoding.
• Потрібні контроль lag, retention WAL і моніторинг replication slots.

93. Що таке розширення (extensions) у PostgreSQL?

PostgreSQL

Extensions - це модулі, які додають нові типи, функції, оператори, індексні можливості й утиліти без зміни ядра БД.

Вони встановлюються в конкретній базі даних командою CREATE EXTENSION.

Коротко:

• Extensions розширюють можливості PostgreSQL під конкретні задачі.
• Життєвий цикл extension треба контролювати так само, як схему БД.

94. Які популярні розширення PostgreSQL ви знаєте і для чого вони використовуються?

PostgreSQL

Поширені приклади:

• pg_stat_statements - статистика SQL для performance tuning;
• pg_trgm - триграмний пошук і індексація для LIKE/ILIKE;
• uuid-ossp або pgcrypto - генерація UUID/криптофункції;
• citext - case-insensitive текстовий тип;
• hstore - key-value структура;
• PostGIS - геопросторові дані та запити.

Коротко:

• Найчастіше в production: pg_stat_statements, pg_trgm, citext, pgcrypto, PostGIS.
• Extension вибирають під конкретний workload, а не "про запас".

95. Як встановити та підключити розширення в базі даних?

PostgreSQL

Після наявності extension у серверній інсталяції, в БД виконують:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Оновлення версії:

ALTER EXTENSION pg_trgm UPDATE;

Коротко:

• Підключення extension робиться на рівні конкретної БД.
• Версії extension потрібно контролювати через міграції.

96. Що таке PostGIS і для чого він використовується?

PostgreSQL

PostGIS - геопросторове extension для PostgreSQL, яке додає типи geometry/geography, просторові функції і індексацію.

Використовується для:

• пошуку об'єктів у радіусі;
• геоаналітики;
• маршрутів, зон, картографії.

Коротко:

• PostGIS перетворює PostgreSQL на повноцінну геопросторову БД.
• Дає ефективні spatial-операції й індекси для геоданих.

97. Як PostgreSQL забезпечує безпеку доступу на рівні ролей і привілеїв?

PostgreSQL

PostgreSQL використовує рольову модель доступу:

• ролі (CREATE ROLE/USER);
• системні і об'єктні привілеї (GRANT/REVOKE);
• розділення ownership і робочих ролей;
• принцип мінімально необхідних прав (least privilege).

GRANT SELECT, INSERT ON TABLE orders TO app_user;
REVOKE DELETE ON TABLE orders FROM app_user;

Коротко:

• Контроль доступу будується на ролях і привілеях.
• Безпечна практика: мінімальні права за замовчуванням.

98. Що таке Row-Level Security (RLS)?

PostgreSQL

RLS - політики доступу на рівні рядків таблиці, які автоматично обмежують, які записи роль може бачити або змінювати.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_tenant_policy ON orders
USING (tenant_id = current_setting('app.tenant_id')::uuid);

Коротко:

• RLS реалізує fine-grained access control по рядках.
• Часто використовується в multi-tenant системах.

99. Як моніторити стан і продуктивність PostgreSQL у production?

PostgreSQL

Базовий стек моніторингу:

• pg_stat_activity, pg_stat_statements, pg_locks;
• метрики ресурсів (CPU, RAM, I/O, disk latency);
• replication lag, WAL generation rate, checkpoints;
• autovacuum-метрики, bloat, довгі транзакції;
• централізовані логи та алерти.

Коротко:

• Моніторинг має покривати SQL, блокування, реплікацію і ресурси ОС.
• Ключ до стабільності: алерти з порогами та регулярний review трендів.

100. Які основні best practices для роботи з PostgreSQL у production-середовищі?

PostgreSQL

Практичний checklist:

• schema design з чіткими PK/FK/constraints;
• індекси тільки під реальні запити;
• постійний аналіз top SQL через pg_stat_statements;
• регулярні VACUUM/ANALYZE (здоровий autovacuum);
• backup + регулярний test restore;
• HA/failover з перевіреними runbooks;
• connection pooling (PgBouncer);
• безпека: least privilege, TLS, аудит змін;
• контроль версій і міграцій.

Коротко:

• Production-стабільність = правильна модель даних + дисципліна операцій.
• Найбільше проблем запобігаються моніторингом, backup/restore тестами і регулярним тюнінгом SQL.

101. Які типи індексів існують у PostgreSQL?

PostgreSQL

Основні типи індексів у PostgreSQL:

• B-tree (за замовчуванням): =, діапазони (<, >, BETWEEN), ORDER BY.
• GIN: JSONB, масиви, full-text (tsvector), пошук по елементах/ключах.
• GiST: геодані (PostGIS), діапазони, nearest-neighbor (залежно від типу).
• BRIN: дуже великі таблиці, де дані фізично корельовані (часто time-series).
• HASH: вузький кейс для =; використовується рідко.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_events_payload_gin ON events USING GIN (payload);

Коротко:

• Тип індексу обирають під тип даних і конкретні умови запитів.
• Для більшості OLTP-кейсів достатньо B-tree, для JSONB часто потрібен GIN.

102. У яких випадках доречно використовувати HASH index у PostgreSQL?

PostgreSQL

HASH-індекс доречний у вузькому сценарії: коли переважна більшість запитів робить точний пошук по рівності (=) і немає потреби в діапазонах чи сортуванні.

Важливі обмеження/нюанси:

• HASH не допомагає для ORDER BY, BETWEEN, префіксних/діапазонних умов.
• У більшості практичних випадків B-tree дає таку саму (або кращу) користь для = і при цьому універсальніший.
• Рішення варто приймати лише після вимірювання через EXPLAIN (ANALYZE, BUFFERS) і реального профілю запитів.

CREATE INDEX idx_sessions_token_hash ON sessions USING HASH (token);

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM sessions
WHERE token = '...';

Коротко:

• HASH має сенс майже виключно для частих = по одному полю.
• Зазвичай B-tree простіший і практичніший вибір, якщо немає доведеного виграшу.

103. Як PostgreSQL визначає, коли потрібно запускати autovacuum для таблиці?

PostgreSQL

Autovacuum запускає VACUUM/ANALYZE, коли кількість змін у таблиці перетинає пороги.

Поріг для vacuum (спрощено):

• dead_tuples >= autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

Поріг для analyze (спрощено):

• changed_tuples >= autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

Де дивитися поточний стан:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Тюнінг часто роблять per-table (для “гарячих” таблиць):

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.02
);

Коротко:

• Autovacuum тригериться порогами: базове значення + відсоток від розміру таблиці.
• Для write-heavy таблиць зазвичай зменшують scale factor і моніторять n_dead_tup.