PostgreSQL: Загальний
20 питань · ~18 хв · Версія v3.0
Увійдіть, щоб продовжити
Діалект і типи PostgreSQL, JSONB, індекси, EXPLAIN, MVCC і VACUUM, транзакції й блокування, CTE та віконні функції.
- За спробу
- 20
- У пулі
- 100
- Проходжень
- 1
- Середній бал
- 50%
- Пройшли на 70%+
- 0%
Питання для підготовки
116 питаньІндекс - окрема відсортована структура (найчастіше 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, мало що дасть - базі все одно читати майже всю таблицю.
DELETE FROM orders видаляє рядки по одному: позначає кожен як видалений (MVCC), запускає тригери ON DELETE, перевіряє зовнішні ключі, може мати WHERE. Місце звільняється пізніше, вакуумом.
TRUNCATE orders звільняє таблицю цілком - фактично підміняє файли таблиці порожніми. Миттєво навіть на мільярді рядків, і місце повертається одразу.
Відмінності:
DELETE |
TRUNCATE |
|
|---|---|---|
Умова WHERE |
так | ні, лише вся таблиця |
| Швидкість на великій таблиці | повільно, пропорційно кількості рядків | миттєво |
| Тригери на рядки | спрацьовують | ні (є окремі ON TRUNCATE) |
| Блокування | рядків | ексклюзивне на таблицю (ACCESS EXCLUSIVE) |
Лічильник identity/serial |
не скидає | скидає з RESTART IDENTITY |
| Зовнішні ключі | перевіряються по рядках | помилка, якщо на таблицю посилаються, без CASCADE |
Чи можна відкотити: у PostgreSQL так - TRUNCATE транзакційний:
BEGIN;
TRUNCATE orders;
ROLLBACK; -- дані на місці
У MySQL TRUNCATE - DDL-команда з неявним комітом, і відкотити її не можна.
Застереження:
TRUNCATEбере найсильніше блокування: поки транзакція з ним не завершилася, таблицю не може читати ніхто.TRUNCATE ... CASCADEочистить і всі таблиці, що посилаються на цю, - легко знищити більше, ніж планували.- У тестах
TRUNCATEміж тестами - швидкий спосіб очищення (так працюєDatabaseTruncationу Laravel), але транзакція з відкатом (RefreshDatabase) ще швидша.
Складений індекс (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'- ні (або лише дуже повільним повним обходом індексу): шукати за ім'ям у довіднику, впорядкованому за прізвищем, не вийде.
Як обирати порядок:
- Спершу колонки з рівністю (
=), потім ті, що в діапазоні (>,BETWEEN) чи вORDER BY. Після колонки з діапазоном наступні колонки для пошуку вже майже не працюють. - Серед колонок з рівністю - ті, що використовуються в більшості запитів.
Наслідок: індекс (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) деякі аномалії паралельного виконання можливі, і їх треба враховувати в коді.
Найпростіший спосіб - логічний дамп:
# 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).
Докладніше в документації: Резервне копіювання і відновлення
Прочитати - ще не значить знати
20 питань, по одному на екран, ~18 хв. Після завершення - розбір кожної помилки з посиланням на питання.