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

Індекс - окрема відсортована структура (найчастіше B-дерево), яка зберігає значення колонки й посилання на рядки таблиці. Як предметний покажчик у книжці: щоб знайти сторінку, не треба гортати всю книгу.

Без індексу запит WHERE email = 'a@b.com' змушує базу переглянути всю таблицю (Seq Scan / full table scan). З індексом пошук займає кілька кроків по дереву навіть на мільйонах рядків.

CREATE INDEX users_email_idx ON users (email);

Ціна індексу:

• Запис сповільнюється: кожен INSERT, UPDATE індексованої колонки й DELETE змушує базу оновити ще й індекс. Десять індексів на таблиці - десять додаткових записів на кожну вставку.
• Місце на диску і в пам'яті.

Що індексувати: колонки з WHERE, JOIN і ORDER BY у частих запитах, зовнішні ключі. Не варто - колонки, за якими не шукають, і таблиці на кілька сотень рядків.

Важливо: індекс корисний, коли умова відсіює більшість рядків. Колонка is_active, де 95% значень true, мало що дасть - базі все одно читати майже всю таблицю.

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

Складений індекс (a, b, c) впорядковує рядки спершу за a, всередині однакових a - за b, далі за c. Як телефонний довідник: за прізвищем, потім за ім'ям.

CREATE INDEX orders_user_status_idx ON orders (user_id, status, created_at);

Правило лівого префікса: індекс допомагає, коли умова використовує колонки з початку:

• WHERE user_id = 5 - так.
• WHERE user_id = 5 AND status = 'paid' - так.
• WHERE user_id = 5 AND status = 'paid' ORDER BY created_at - так, і сортування береться з індексу.
• WHERE status = 'paid' - ні (або лише дуже повільним повним обходом індексу): шукати за ім'ям у довіднику, впорядкованому за прізвищем, не вийде.

Як обирати порядок:

1. Спершу колонки з рівністю (=), потім ті, що в діапазоні (>, BETWEEN) чи в ORDER BY. Після колонки з діапазоном наступні колонки для пошуку вже майже не працюють.
2. Серед колонок з рівністю - ті, що використовуються в більшості запитів.

Наслідок: індекс (user_id, status) робить окремий індекс на user_id зайвим - лівий префікс уже покриває такі запити. Зайвий індекс лише сповільнює запис.

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

ACID - чотири гарантії, які дає транзакція в реляційній базі:

• Atomicity (атомарність) - усі операції транзакції виконуються або всі разом, або жодна. Переказ грошей не може зняти суму з одного рахунку й не зарахувати на інший.
• Consistency (узгодженість) - транзакція переводить базу з одного коректного стану в інший: обмеження (NOT NULL, унікальність, зовнішні ключі, CHECK) виконуються після кожної завершеної транзакції.
• Isolation (ізольованість) - паралельні транзакції не бачать проміжних станів одна одної. Наскільки суворо - визначає рівень ізоляції.
• Durability (довговічність) - після COMMIT дані не зникнуть навіть при падінні сервера: їх уже записано в журнал (WAL у PostgreSQL, redo log в InnoDB) на диск.

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

Якщо між двома UPDATE сервер впаде, після перезапуску жодної зміни не буде - атомарність.

Важливо для співбесіди: ізольованість не означає «транзакції виконуються по черзі». На рівнях за замовчуванням (Read Committed у PostgreSQL, Repeatable Read у MySQL) деякі аномалії паралельного виконання можливі, і їх треба враховувати в коді.

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

ROLLBACK скасовує всі зміни, зроблені в поточній транзакції, і завершує її. Так, ніби нічого не було.

BEGIN;
DELETE FROM orders WHERE created_at < '2020-01-01';
-- ой, не та дата
ROLLBACK;

Коли транзакція відкочується без явного ROLLBACK:

• Розірвалося з'єднання або впав процес застосунку посеред транзакції - база відкотить її сама.
• Помилка в PostgreSQL: після будь-якої помилки транзакція переходить у стан «aborted», і всі наступні команди відхиляються з current transaction is aborted, доки не буде ROLLBACK.
• Deadlock: база обирає одну з транзакцій-«жертв» і відкочує її.

Відмінність MySQL: помилка однієї команди (наприклад, порушення унікальності) за замовчуванням відкочує лише цю команду, а не всю транзакцію. Тому покладатися на «база сама все відкотить» небезпечно - застосунок має явно робити ROLLBACK при помилці.

Ще є SAVEPOINT - точка, до якої можна відкотити частину транзакції, не скасовуючи все. Laravel використовує їх для вкладених DB::transaction().

Пастка: DDL у MySQL (CREATE TABLE, ALTER TABLE) неявно комітить поточну транзакцію, тож відкотити міграцію з кількома змінами схеми не вийде. У PostgreSQL DDL транзакційний.

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

EXPLAIN показує, як база збирається виконати запит: у якому порядку читати таблиці, чи використовувати індекси, як з'єднувати. Сам запит при цьому не виконується.

EXPLAIN SELECT * FROM orders WHERE user_id = 5;

Index Scan using orders_user_id_idx on orders  (cost=0.43..8.45 rows=3 width=64)
  Index Cond: (user_id = 5)

Що шукати новачку:

• Seq Scan (PostgreSQL) / type: ALL (MySQL) на великій таблиці - повний перегляд таблиці. Часто ознака відсутнього індексу.
• Index Scan / Index Only Scan (PostgreSQL), type: ref, range (MySQL) - використано індекс.
• rows - скільки рядків база очікує. Якщо оцінка сильно відрізняється від реальності, план може бути поганим.
• Sort з великою кількістю рядків чи Using filesort у MySQL - сортування, яке не взяли з індексу.

cost - умовні одиниці вартості, не мілісекунди. Перше число - вартість до першого рядка, друге - до останнього.

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

Наступний крок - EXPLAIN ANALYZE, який виконує запит і показує реальний час і кількість рядків.

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

SELECT * повертає всі колонки, навіть ті, що коду не потрібні. На маленьких таблицях різниці немає, але проблеми з'являються з ростом:

• Зайві дані через мережу й у пам'ять. Колонка body TEXT чи payload JSON на сотні кілобайтів, вибрана для списку із 50 заголовків, множить обсяг у рази.
• Не працює покривний індекс. Якщо запиту потрібні лише id і status, які є в індексі, база могла б не звертатися до таблиці. З * мусить.
• Крихкість. Додали колонку - і запит раптом повертає більше даних; у INSERT ... SELECT * чи UNION зміна схеми ламає запит.
• Читабельність. З коду не видно, які дані реально використовуються.

-- Для списку статей
SELECT id, title, published_at FROM posts ORDER BY published_at DESC LIMIT 20;

У Laravel Eloquent за замовчуванням робить select *. Для важких таблиць варто явно обмежувати колонки: Post::select(['id', 'title', 'published_at']), а для зв'язків - with('author:id,name').

Коли * нормальний: разові запити в консолі, EXISTS (SELECT * ...) (там колонки не читаються взагалі), і COUNT(*), який з вибором колонок узагалі не пов'язаний.

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

Найпростіший спосіб - логічний дамп:

# PostgreSQL: власний стиснений формат, зручний для вибіркового відновлення
pg_dump -Fc -d app > app.dump
pg_restore -d app_restored app.dump

# MySQL
mysqldump --single-transaction --routines app > app.sql

pg_dump бере узгоджений знімок і не блокує роботу застосунку. Для MySQL на InnoDB той самий ефект дає --single-transaction.

Бекап, який ніхто не пробував відновити, - не бекап. Мінімум:

• Регулярно відновлювати копію на окремому сервері й перевіряти, що застосунок з нею працює. Автоматично, за розкладом.
• Зберігати копії не там, де база: інший сервер, інший дата-центр, об'єктне сховище. Пожежа чи видалений акаунт не мають забрати і базу, і бекапи.
• Кілька поколінь: щоденні за тиждень, щотижневі за місяць. Помилку в даних часто помічають не одразу.
• Моніторинг: сповіщення, якщо бекап не створився чи раптом став набагато меншим.

Правило 3-2-1: три копії, на двох різних носіях, одна - поза основним майданчиком.

Обмеження дампу: відновлення - лише на момент створення копії. Усе, що сталося після, втрачено. Для відновлення на довільний момент потрібне безперервне архівування журналу (WAL, binlog).

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

Репліка - копія бази на іншому сервері, яка безперервно отримує зміни з основного сервера (primary) і застосовує їх у себе.

Як це працює: primary записує всі зміни в журнал (WAL у PostgreSQL, binlog у MySQL), а репліка отримує цей журнал по мережі й програє його. Зазвичай реплікація асинхронна: primary не чекає підтвердження від репліки.

Навіщо:

• Відмовостійкість. Якщо primary впав, репліку можна підвищити до нового primary і продовжити роботу за хвилини, а не за години відновлення з бекапу.
• Масштабування читання. Важкі звіти, аналітику й частину запитів на читання відправляють на репліки, розвантажуючи primary.
• Обслуговування без простою. Оновлення чи перевірки можна спершу робити на репліці.

Чого репліка НЕ замінює - бекапів. DELETE FROM users без WHERE за мілісекунди реплікується на всі копії. Від логічних помилок рятує лише бекап чи відновлення на момент у часі.

Головний нюанс для розробника - затримка реплікації: щойно записані дані на репліці можуть з'явитися із запізненням. У Laravel є опція sticky: після запису в межах того самого запиту читання йде на primary.

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

PostgreSQL має кілька видів індексів, кожен під свій тип запитів:

• B-tree (за замовчуванням) - рівність, діапазони (<, >, BETWEEN), сортування, LIKE 'початок%'. Покриває більшість випадків.
• Hash - лише рівність (=). З PostgreSQL 10 надійний (записується в WAL), але виграш над B-tree рідко помітний.
• GIN (Generalized Inverted Index) - для значень, що містять багато елементів: масиви, jsonb, повнотекстовий пошук (tsvector), триграми (pg_trgm). Шукає «рядки, де є цей елемент».
• GiST - для геометрії й діапазонів: «перетинається», «містить», «найближчий» (PostGIS, tsrange, exclusion constraints, триграми з пошуком найближчих).
• SP-GiST - для нерівномірно розподілених даних: IP-адреси, телефонні префікси, квадродерева.
• BRIN - крихітний індекс для дуже великих таблиць, де значення фізично впорядковані на диску (час вставки в журналі подій).

CREATE INDEX orders_user_idx ON orders (user_id);                       -- B-tree
CREATE INDEX products_tags_idx ON products USING gin (tags);            -- масив
CREATE INDEX docs_search_idx ON docs USING gin (to_tsvector('simple', body));
CREATE INDEX bookings_period_idx ON bookings USING gist (period);       -- tsrange
CREATE INDEX events_created_brin ON events USING brin (created_at);

Як обирати: почати з B-tree. Інші типи - коли запит використовує оператори, які B-tree не підтримує (@>, &&, @@, <->), або коли таблиця настільки велика, що розмір індексу стає проблемою (BRIN).

Нюанс GIN: швидкий на читання, але дорожчий на запис. Тому він накопичує зміни в «відкладеному списку» (fastupdate), який потім вливається в індекс - на таблицях з інтенсивним записом це варто враховувати.

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

Які індекси є:

-- у psql
\d orders

-- або запитом
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

Чи використовуються - статистика pg_stat_user_indexes, яку PostgreSQL збирає з моменту останнього скидання статистики:

SELECT relname AS table,
       indexrelname AS index,
       idx_scan AS scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

idx_scan = 0 означає, що індекс жодного разу не використали для пошуку. Такий індекс лише сповільнює запис і займає місце.

Перш ніж видаляти «невикористаний» індекс:

• Перевірити термін статистики: якщо її скинули вчора, щомісячний звіт ще не встиг скористатися індексом.
• Перевірити всі репліки: статистика збирається окремо на кожному сервері. Індекс може не використовуватися на primary, але бути критичним для звітів, що йдуть на репліку.
• Унікальні індекси й первинні ключі не видаляють, навіть з idx_scan = 0: вони забезпечують цілісність, а не швидкість.
• Видаляти через DROP INDEX CONCURRENTLY, щоб не блокувати таблицю.

Окремо корисно: seq_scan і seq_tup_read у pg_stat_user_tables показують таблиці, які часто читаються повністю, - кандидати на новий індекс.

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

Autocommit - режим, у якому кожна команда - окрема транзакція: вона або виконується й одразу фіксується, або відкочується при помилці. Це поведінка PostgreSQL і більшості драйверів за замовчуванням.

UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- уже закомічено
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- окрема транзакція

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

Явна транзакція об'єднує команди:

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

Що варто знати:

• Одна команда атомарна й без BEGIN: UPDATE ... WHERE id IN (1, 2) або INSERT з кількома рядками виконається повністю або ніяк.
• Тригери й каскадні дії виконуються в межах тієї самої транзакції, що й команда, яка їх запустила.
• Незавершена транзакція (забули COMMIT) тримає блокування й заважає вакууму. Сесія в стані idle in transaction - поширена проблема на проді.
• У PHP: PDO працює в autocommit, доки не викликати beginTransaction(). Laravel DB::transaction(fn () => ...) робить BEGIN / COMMIT і ROLLBACK при винятку.

Неявні транзакції в інших СУБД: в Oracle й у SQL Server з IMPLICIT_TRANSACTIONS транзакція починається сама з першою командою й триває до явного COMMIT. А в MySQL DDL-команди (ALTER TABLE) неявно комітять поточну транзакцію - у PostgreSQL DDL транзакційний і відкочується разом з рештою.

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

DELETE FROM orders видаляє рядки по одному: позначає кожен як видалений (MVCC), запускає тригери ON DELETE, перевіряє зовнішні ключі, може мати WHERE. Місце звільняється пізніше, вакуумом.

TRUNCATE orders звільняє таблицю цілком - фактично підміняє файли таблиці порожніми. Миттєво навіть на мільярді рядків, і місце повертається одразу.

Відмінності:

Чи можна відкотити: у PostgreSQL так - TRUNCATE транзакційний:

BEGIN;
TRUNCATE orders;
ROLLBACK;   -- дані на місці

У MySQL TRUNCATE - DDL-команда з неявним комітом, і відкотити її не можна.

Застереження:

• TRUNCATE бере найсильніше блокування: поки транзакція з ним не завершилася, таблицю не може читати ніхто.
• TRUNCATE ... CASCADE очистить і всі таблиці, що посилаються на цю, - легко знищити більше, ніж планували.
• У тестах TRUNCATE між тестами - швидкий спосіб очищення (так працює DatabaseTruncation у Laravel), але транзакція з відкатом (RefreshDatabase) ще швидша.

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

У PostgreSQL text, varchar і varchar(n) зберігаються однаково й працюють з однаковою швидкістю. Різниця лише в тому, що varchar(n) додає перевірку довжини.

• text - рядок довільної довжини.
• varchar(n) - рядок не довший за n символів (не байтів). Довший - помилка.
• varchar без довжини - те саме, що text.
• char(n) - доповнюється пробілами до n. Майже ніколи не потрібен: займає не менше місця і має дивну семантику порівнянь.

Звідки звичка до varchar(255): у MySQL та інших СУБД довжина впливає на зберігання, індекси й тимчасові таблиці. У PostgreSQL такої причини немає.

Як обирати:

• text - для більшості рядків. Обмеження довжини - якщо воно справді є правилом домену.
• Бізнес-обмеження - через CHECK, його легше змінити:

title text NOT NULL CHECK (char_length(title) <= 200)

Збільшити ліміт varchar(n) у сучасних версіях можна без переписування таблиці, але зменшення вимагає повної перевірки даних.

Що варто знати:

• Довгі значення (понад ~2 КБ) автоматично стискаються й виносяться в TOAST - таблиця не роздувається.
• Laravel $table->string('name') створює varchar(255) - на PostgreSQL це лише обмеження, а не оптимізація. Для довгих текстів - $table->text().
• Валідація довжини в застосунку все одно потрібна, щоб користувач отримав зрозуміле повідомлення, а не помилку бази.

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

Обидва способи створюють автоінкрементний цілочисельний ключ на основі послідовності (sequence).

serial / bigserial - старий спосіб, по суті скорочення:

id bigserial PRIMARY KEY
-- розгортається в:
-- id bigint NOT NULL DEFAULT nextval('orders_id_seq')
-- + окрема послідовність, «прив'язана» до колонки

Identity (PostgreSQL 10+, стандарт SQL):

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- або
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Чому identity краще:

• Стандарт SQL - переноситься між СУБД.
• Послідовність - частина колонки: права, видалення таблиці, копіювання структури працюють коректно. З serial треба окремо давати права на послідовність, і CREATE TABLE ... (LIKE ...) може поділити послідовність між таблицями.
• GENERATED ALWAYS забороняє вставляти власні значення (можна лише явно з OVERRIDING SYSTEM VALUE). Захищає від ручних ID, що потім конфліктують з послідовністю.
• Керування через ALTER TABLE ... ALTER COLUMN id RESTART WITH 1000.

Спільні нюанси:

• Дірки в номерах - норма. Значення з послідовності береться до коміту й не повертається при відкаті. ID 5, 6, 8 - не баг. Для «безперервних» номерів рахунків послідовність не підходить.
• bigint, а не int: 2,1 мільярда закінчуються несподівано швидко в таблицях подій і логів, а зміна типу первинного ключа на великій таблиці - болюча операція.
• Після ручного імпорту даних з явними ID послідовність треба зсунути: SELECT setval('orders_id_seq', (SELECT max(id) FROM orders));.

Laravel $table->id() на PostgreSQL створює bigserial-ключ - для більшості застосунків різниця непомітна.

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

PostgreSQL дозволяє зберігати в колонці масив значень будь-якого типу: text[], int[], uuid[].

CREATE TABLE posts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    tags text[] NOT NULL DEFAULT '{}'
);

INSERT INTO posts (title, tags) VALUES ('Черги в Laravel', ARRAY['laravel', 'queues']);

SELECT * FROM posts WHERE 'queues' = ANY(tags);       -- містить елемент
SELECT * FROM posts WHERE tags @> ARRAY['laravel'];   -- містить усі
SELECT * FROM posts WHERE tags && ARRAY['vue', 'react'];  -- є хоча б один спільний
SELECT unnest(tags) AS tag, count(*) FROM posts GROUP BY tag;   -- розгорнути в рядки

Для швидкого пошуку - GIN-індекс: CREATE INDEX posts_tags_idx ON posts USING gin (tags); (працює з @>, &&, але не з = ANY).

Індексація з одиниці: tags[1] - перший елемент.

Коли масив доречний:

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

Коли краще окрема таблиця:

• Елементи посилаються на інші сутності - масив ID не має зовнішніх ключів, і видалення пов'язаного запису не прибере його з масиву.
• Потрібні атрибути зв'язку (хто й коли додав тег).
• Потрібно часто змінювати окремі елементи чи масив великий.
• Потрібні звичайні JOIN і агрегати - на масивах вони можливі, але громіздкіші.

Пастки: NULL і порожній масив '{}' - різні речі; = ANY(NULL) дає NULL; багатовимірні масиви мусять бути «прямокутними». Laravel не має типу масиву в схемі - колонку додають через $table->addColumn(...) чи сирий SQL, а читають з кастом.

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