Навіщо СУБД

Навіщо це

Найпростіше сховище для замовлень «Крамниці» — текстовий файл: рядок на замовлення, поля через кому. Його відкриває Excel, читає будь-яка мова, а дописати рядок можна однією командою. Поки замовлення оформляє один скрипт, цього досить.

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

Кожну проблему можна залатати кодом. Модуль показує, як виглядає латка і що з цієї роботи бере на себе система керування базами даних (СУБД). Потім дає карту курсу: з яких частин СУБД складається і де кожну з них розібрано.

Передумови. Немає. Корисно знати, що таке процес і кеш сторінок: модуль 6 і модуль 13 курсу «Операційні системи».

Що ламається, коли дані лежать у файлах

Курс увесь час працює з навчальним датасетом «Крамниця». Він має три розміри: small, medium і full (що в ньому і навіщо три розміри, описано на сторінці Датасет). Числа цього розділу виміряно на найбільшому, full: orders.csv має 2 009 466 рядків і важить 177 МБ. Міряли в Docker з PostgreSQL 18.6 на настільній машині з 24 ядрами. Файл лежав у кеші сторінок, а для бази ті самі рядки завантажено в таблицю orders_big. На ноутбуці абсолютні числа будуть більшими, співвідношення ті самі.

Пошук

Щоб знайти замовлення покупця 20559, файл треба прочитати від початку до кінця:

grep -c '^[0-9]*,20559,' orders.csv

Один прохід займає близько 0.1 с. Сто пошуків за різними покупцями — 6.4 с, тобто 64 мс на пошук. Для одного менеджера це терпимо, але вартість росте лінійно з розміром файлу й сплачується за кожен запит.

У orders_big немає індексу, крім первинного ключа. Запит WHERE customer_id = 20559 читає всю таблицю (паралельно, у трьох процесах): 24 тисячі сторінок за 24 мс. Після CREATE INDEX по customer_id (0.8 с на два мільйони рядків) він читає 37 сторінок і виконується за 0.19 мс. pgbench із випадковим покупцем дав 0.067 мс у середньому, близько 15 тисяч пошуків за секунду з одного з’єднання.

Структуру, що так працює, розбирає модуль 8. А коли її застосувати, вирішує планувальник (модуль 9).

Друга половина прохання, «згрупувати за областями», у файлах вимагає скрипту на awk з трьома словниками в пам’яті: пункт видачі, населений пункт, область. В SQL це один запит (пісочниця працює на розмірі small):

СпробуйPostgreSQLCtrl+Enter — виконати

          region          | orders |   revenue
--------------------------+--------+-------------
 Дніпропетровська область |   2427 | 10961941.78
 м. Київ                  |   1834 |  6605648.83
 Харківська область       |   1240 |  6414507.90
 Одеська область          |   1122 |  5402870.82
 Полтавська область       |   1060 |  4361423.66

Запит описує результат, а не спосіб обчислення. Цю властивість принесла реляційна модель (історія нижче). Як SQL обчислює такий запит, розбирає модуль 3.

Два процеси дописують одночасно

Кожне замовлення потребує номера. У файлі найпростіше взяти останній номер, додати одиницю й дописати рядок:

# add.sh: процес 500 разів читає останній order_id і дописує наступний
for i in $(seq 1 500); do
  last=$(tail -n 1 o.csv | cut -d, -f1)
  echo "$((last + 1)),$1,100.00" >> o.csv
done

Два процеси на файлі, де вже є заголовок і замовлення 1:

bash add.sh 11 & bash add.sh 22 & wait
wc -l o.csv                                                    # 1002 разом із заголовком
tail -n +2 o.csv | cut -d, -f1 | sort -n | uniq -d | wc -l    # 215 номерів трапляються двічі
tail -n 1 o.csv | cut -d, -f1                                  # останній номер: 786

Усі 1001 рядок на місці, і жоден не розірвався: кожен має рівно три поля. Для коротких рядків на локальному диску дописування саме собою безпечне (на мережевих файлових системах гарантії немає, про це попереджає man 2 open у розділі про O_APPEND). Ламає пара «прочитати, потім записати»: між читанням і записом другий процес встиг взяти той самий номер. Замовлення різних покупців отримали однакові номери, а останній номер 786 замість 1001.

Лікує це блокування файлу (flock). Але тоді ви пишете менеджер блокувань: що робити з процесом, який узяв блокування й помер, і що робити, коли потрібні два файли.

Та сама пара процесів проти PostgreSQL, де номер видає identity-колонка (pgbench, два клієнти по 500 вставок):

 rows | distinct_ids |  max
------+--------------+------
 1000 |         1000 | 1000

Повторів немає, але це заслуга лічильника, який база дає готовим. Код «прочитав quantity, перевірив, записав» ламається в базі так само, як у файлі. Це тема модуля 10.

Збій посеред запису

Замовлення складається з рядка в orders і рядків у order_items. У файлах це дві команди. Уб’ємо процес між ними, як це зробила б аварія:

( echo "5001,7,300.00" >> ord.csv; sleep 3; echo "5001,1,3,100.00" >> items.csv ) &
sleep 1; kill -9 $!
cat ord.csv items.csv

5001,7,300.00

Замовлення 5001 є, позицій немає, і ніщо не підкаже, що воно половинчасте. У PostgreSQL обидва рядки вставляються в одній транзакції з паузою посередині. Клієнта psql убито командою kill -9 під час паузи:

BEGIN;
INSERT INTO o VALUES (5001, 7, 300.00);
SELECT pg_sleep(3);
INSERT INTO oi VALUES (5001, 1, 3, 100.00);
COMMIT;

 orders | items
--------+-------
      0 |     0

Транзакція не закомітилась, тому не лишилось нічого. Це атомарність, яку разом з рештою ACID розбирає модуль 10.

Живлення, що зникло посеред запису, може залишити половину сектора, і в контейнері цього не відтворити. Що саме встигає потрапити на диск і що гарантує fsync, розповідає модуль 11. З боку ОС див. модуль 13 (кеш сторінок) і модуль 15 (журналювання).

Цілісність

У файлі кожен скрипт пише що хоче: позиція неіснуючого замовлення чи від’ємна сума пройдуть мовчки. База відмовляє, якщо правило описано в схемі:

INSERT INTO order_items (order_id, line_no, product_id, quantity, unit_price, discount_pct)
VALUES (99999999, 1, 1, 1, 100, 0);

ERROR:  insert or update on table "order_items" violates foreign key constraint "order_items_order_id_fkey"
DETAIL:  Key (order_id)=(99999999) is not present in table "orders".

UPDATE orders SET total_amount = -5 WHERE order_id = 198 теж закінчується помилкою, через CHECK. Правило лежить у схемі, а не в кожному скрипті, тож його не обійти жодним із них. Обмеження розбирають модулі 2 і 4.

Що виходить із цього

Кожна латка — окрема частина СУБД: індекс, менеджер блокувань, транзакція з журналом, обмеження цілісності, мова запитів. Система керування базами даних бере на себе зберігання, одночасний доступ, відновлення після збою й відповіді на запити, щоб кожен застосунок не писав це заново.

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

З чого складається СУБД

Запит проходить через кілька шарів, і більшість систем, реляційних чи ні, влаштовані схоже.

Шари СУБД і модулі, де курс їх розбирає. Запит іде зверху вниз; менеджер транзакцій і журнал діють на кількох шарах одразу.

Мова запитів приймає текст і перевіряє його за схемою: модулі 2–4. Застосунок підключається через драйвер (модуль 6). Планувальник вибирає найдешевший з еквівалентних способів виконання, виконавець виконує обраний (модуль 9).

Сховище тримає дані в сторінках фіксованого розміру, кешує їх у власному buffer pool і будує індекси (модулі 7 і 8). Менеджер транзакцій вирішує, що побачить кожна з одночасних транзакцій і хто кого дочекається (модуль 10). Журнал записує зміни раніше, ніж вони потраплять у файли даних, і дає змогу відновитися після збою та збудувати репліку (модулі 11 і 12).

Під ними файлова система й диск із курсу ОС. Коли даних більше, ніж вміщає один вузол, додаються шардинг і консенсус (модуль 13).

Нереляційні бази міняють верхні шари: власний API замість SQL, заздалегідь визначені шляхи доступу замість планувальника. Нижні лишаються схожими: Cassandra має журнал (commit log), MongoDB має журнал і менеджер транзакцій. Тому курс іде від однієї реляційної СУБД до її розмноження, і лише потім до нереляційних.

Два види навантаження: OLTP і OLAP

Навантаження бувають двох типів, майже протилежних. Транзакційне (OLTP, online transaction processing) — тисячі коротких запитів на секунду, кожен чіпає кілька рядків: оформити замовлення, списати залишок. Аналітичне (OLAP, online analytical processing) — рідкісні важкі запити, що читають мільйони рядків і підсумовують їх.

Обидва запити нижче виконано на orders_big (розмір full, у таблиці 188 МБ):

SELECT * FROM orders WHERE order_id = 1234567;

SELECT date_trunc('month', placed_at) AS month, count(*), sum(total_amount)
FROM orders
WHERE status = 'delivered'
GROUP BY 1
ORDER BY 1;

Перший читає 7 сторінок і виконується за 0.5 мс. Другий обробляє 1.74 мільйона рядків і читає близько 24 тисяч сторінок, бо рядок береться цілком, хоча потрібні лише три колонки з дев’яти. Він займає 643 мс.

Обидва запити нормальні. Але коли одна база обслуговує обидва, аналітичний запит забирає пам’ять і диск, а транзакційні чекають. Тому аналітику часто виносять в окрему систему з колонковим зберіганням і копіюють туди дані (модуль 19).

Як до цього дійшли

Кожен крок нижче розв’язував проблему попереднього.

Ієрархічні й мережеві моделі. Перші СУБД з’явилися в 1960-х. IBM IMS зберігала записи деревом: у кожного один батько, програма спускається від кореня. Це пасувало обліку комплектуючих, для якого IMS і створювали. Але замовлення, що належить і покупцю, і пункту видачі, в дереві без дублювання не виразити.

Мережева модель (CODASYL) дозволяла запису мати кількох «батьків», а програма ходила за вказівниками. Чарлз Бахман, автор перших систем цього типу, назвав таку роботу навігацією: його лекція до премії Тюрінга називається «Програміст як навігатор». Ціною навігації була залежність програми від фізичної структури. Запит «замовлення покупця Ковальчука» був процедурою: знайди покупця, піди за вказівником, дійди до кінця списку. Нова структура даних означала переписані програми.

Кодд. У 1970 році Едгар Кодд опублікував у Communications of the ACM статтю «A Relational Model of Data for Large Shared Data Banks». Головна ідея — незалежність даних: дані подано як таблиці (теорія називає їх відношеннями), а програма описує, що їй потрібно, і не знає про індекси та вказівники. Додали індекс, програма не змінилась.

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

System R і SQL. Відповіддю став проєкт System R в IBM Research у середині 1970-х. Мову запитів для нього розробили Дональд Чемберлін і Реймонд Бойс під назвою SEQUEL, звідси SQL. System R показала, що декларативний запит можна скомпілювати в ефективний план. Вибір плану за вартістю описали Селінджер зі співавторами 1979 року, і ця ідея досі лежить в основі планувальників (модуль 9).

Перший стандарт SQL ухвалив ANSI в 1986 році. Транзакції й журнал виросли з тих самих систем: абревіатуру ACID запровадили Theo Härder і Andreas Reuter у 1983 році.

NoSQL-хвиля. Наприкінці 2000-х веб-компанії впиралися в те, що одна машина не тримає їхнього запису. Статті Google про Bigtable (2006) і Amazon про Dynamo (2007) описали бази, що розкладають дані на багато звичайних серверів. Dynamo проєктували під кошик покупок: запис має проходити завжди, навіть коли частина вузлів недоступна. У 2009 році назву «NoSQL» використали для зустрічі розробників таких систем у Сан-Франциско, і вона прижилася. Тоді ж вийшли перші версії MongoDB і Redis.

Відмовитись довелося від того, що реляційна СУБД давала безкоштовно: JOIN, транзакцій на кілька записів, довільних запитів без продуманого заздалегідь ключа. Система швидко виконувала заплановані запити й важко незаплановані. Ціну розбирають модулі 12–13 і частина IV.

NewSQL і «Postgres для всього». Google у 2012 році описав Spanner: розподілену базу з SQL, де транзакції між вузлами поводяться так, ніби виконуються по черзі. Ціна: точні годинники (TrueTime) і затримка кворуму на кожному коміті. Схожий напрям (CockroachDB, YugabyteDB, TiDB) називають NewSQL. Що в ньому справді розв’язано, розбирає модуль 13.

З іншого боку, реляційні системи засвоювали чужі можливості. Повнотекстовий пошук з’явився в ядрі PostgreSQL 8.3, jsonb у версії 9.4 (2014), векторний пошук додає розширення pgvector. MongoDB, навпаки, отримала транзакції на кілька документів у версії 4.0.

У «Крамниці» колонка products.attributes має тип jsonb, і в одній таблиці лежать товари з різним набором атрибутів:

СпробуйPostgreSQLCtrl+Enter — виконати

 product_id |                                     attributes
------------+------------------------------------------------------------------------------------
          2 | {"brand": "Kvantis", "colour": "білий", "weight_g": 115, "warranty_months": 12}
         70 | {"cover": "тверда", "pages": 480, "author": "Людмила Кузьменко", "language": "uk"}

Це документ усередині реляційного рядка. Коли цього вистачає, а коли потрібна окрема документна база, вирішує модуль 16. Матриця вибору — в модулі 21.

Карта моделей даних

Модель даних задає, з чого складається одиниця зберігання й які операції над нею природні. Кожна прискорює той патерн доступу, під який створена, і ускладнює інші.

Вісім моделей, система-приклад для кожної й модулі курсу. Пошукова модель пов'язана з векторною: обидві в модулі 20.

Реляційна модель тримає рядки таблиць, зв’язані ключами. Вона підходить, коли патерни доступу наперед невідомі, а цілісність важлива. Key-value зводиться до значення за ключем, документна зберігає вкладений документ цілком, wide-column розкладає рядки за ключем партиції по багатьох вузлах.

Графова ставить на перше місце зв’язки. Колонкова зберігає таблицю по колонках для аналітики. Векторна шукає найближчих сусідів за ембедингами.

Як це насправді

Підніміть PostgreSQL з датасетом (якщо порт 5432 зайнятий, змінна PG_PORT задає інший).

docker compose --profile postgres up -d --wait
docker compose exec postgres psql -U shop -d shop

Які процеси працюють

СУБД — звичайна програма, і в контейнері її видно через ps. Залиште в psql відкритий запит (SELECT pg_sleep(60)), щоб з’явився клієнтський процес (вивід скорочено):

docker compose exec postgres ps -eo pid,ppid,cmd --sort=pid

    PID    PPID CMD
      1       0 postgres -c shared_preload_libraries=pg_stat_statements -c shared_buffers=256MB ...
     27       1 postgres: io worker 0
     30       1 postgres: checkpointer
     31       1 postgres: background writer
     33       1 postgres: walwriter
     34       1 postgres: autovacuum launcher
     35       1 postgres: logical replication launcher
     67       1 postgres: shop shop 127.0.0.1(56436) SELECT

Головний процес (PID 1) породжує решту, зокрема окремий процес на кожне з’єднання клієнта (останній рядок): тому в модулі 6 з’являється пул з’єднань. Решта службові. checkpointer і background writer скидають змінені сторінки на диск, walwriter пише журнал, autovacuum launcher запускає прибирання мертвих версій рядків (модуль 10), io worker виконують асинхронний ввід-вивід, новий у PostgreSQL 18. Те саме видно з SQL: SELECT pid, backend_type, state FROM pg_stat_activity.

Які файли лежать на диску

Каталог даних ($PGDATA) містить base з файлами таблиць, pg_wal із журналом, pg_xact зі статусами транзакцій і конфігурацію. Таблиця orders — один файл:

SELECT pg_relation_filepath('orders'), pg_relation_size('orders'), pg_relation_size('orders') / 8192 AS pages;

 pg_relation_filepath | pg_relation_size | pages
----------------------+------------------+-------
 base/16385/17006     |          2228224 |   272

Це orders розміру small: 2 228 224 байти, розділені на 8192, дають 272 сторінки. Таблицю в сторінках розбирає модуль 7, а сегменти журналу по 16 МБ у pg_wal — модуль 11. Це звичайний файл у файловій системі ОС, тож усе з модуля 15 курсу ОС діє й тут.

Що буде, якщо вбити сервер

Закомітьте рядок у новій таблиці й убийте сервер сигналом SIGKILL: у нього не лишається шансу дописати сторінки на диск.

docker compose exec postgres psql -U shop -d shop \
  -c "CREATE TABLE crash_demo (id int, note text)" \
  -c "INSERT INTO crash_demo VALUES (1, 'до збою')"
docker compose kill -s KILL postgres
docker compose --profile postgres up -d --wait
docker compose logs --no-log-prefix postgres

LOG:  database system was interrupted; last known up at 2026-09-30 21:51:11 UTC
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/17568FC8
LOG:  invalid record length at 0/1758A130: expected at least 24, got 0
LOG:  redo done at 0/1758A0F8 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOG:  checkpoint starting: end-of-recovery immediate wait
LOG:  database system is ready to accept connections

Сервер прочитав журнал від точки, з якої зміни могли ще не дійти до файлів даних, повторив їх і дійшов до кінця журналу. Повідомлення «invalid record length … got 0» означає саме кінець, а не пошкодження. SELECT * FROM crash_demo повертає рядок 1 | до збою.

У CSV-варіанті цю роботу мав би робити ваш код. Як і чому журнал пишуть раніше за дані, розповідає модуль 11, там же про fsync і те, що буває, коли він бреше. Наприкінці: docker compose --profile "*" down -v.

Типові помилки розуміння

«База даних — це файл, тільки з SQL». У PostgreSQL є процеси, buffer pool, журнал і менеджер блокувань, а файли даних лише найнижчий шар. Зміна потрапляє у файли даних пізніше, ніж COMMIT повертає успіх: спершу її записано в журнал.

«NoSQL означає без SQL» і «NoSQL швидший». Назву придумали для зустрічі, а не як технічне визначення, а Cassandra має мову CQL, схожу на SQL. Швидкість залежить від патерну доступу: система, заточена під запис за ключем, швидко виконає саме це й гірше впорається з довільним запитом.

«Реляційні бази не масштабуються». Один вузол PostgreSQL з індексами витримує тисячі простих запитів на секунду (вище одне з’єднання дало 15 тисяч пошуків за ключем на full), а для більшого є репліки й шардинг (модулі 12 і 13). Перехід на іншу модель виправдовує патерн доступу, а не репутація.

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

Перевір себе

1. Два процеси по 500 разів читають останній order_id з файлу, додають 1 і дописують рядок. У файлі 1001 рядків, усі цілі, але order_id повторюються. Що сталося?Запис розірвав кілька рядків, і номери злиплисьФайлова система перевпорядкувала записи двох процесівМіж читанням останнього номера й записом наступного другий процес прочитав той самий номерОперація `>>` не працює для двох процесів одночасно

2. Замовлення записують у два CSV-файли двома командами, процес убито між ними. Що лишилось і чому з базою інакше?Нічого: ОС відкотить записи процесу, який померПозиції без замовлення: база вставляє спершу дочірній рядокВ обох системах лишиться половина, бо збій не можна обробитиЗамовлення без позицій; у базі обидва `INSERT` стоять в одній транзакції, і без `COMMIT` не лишиться жодного

3. Програми для IMS і мережевих баз ламалися, коли змінювали структуру даних. Що Кодд запропонував проти цього?Незалежність даних: програма описує, що їй потрібно, а не шлях до данихОбов’язкове шардування за ключемЗберігання всіх даних у пам’ятіЗаборону індексів у базі

4. Що насамперед розв’язувала NoSQL-хвиля кінця 2000-х і чим за це заплатили?Повільний синтаксис SQL; ціною стала втрата типів данихМасштабування запису й доступність на багатьох звичайних серверах; ціна: `JOIN`, транзакції на кілька записів і довільні запитиНеможливість зберігати JSON; ціна: складніший парсерБрак безкоштовних СУБД; ціна: відсутність підтримки

5. Запит «виручка за місяцями по всіх замовленнях» читає 24 тисячі сторінок (на `full`), хоча потрібні три колонки з дев’яти. Що це означає?Система зламана: індекс мав би зменшити читанняПотрібно більше оперативної пам’яті, і запит стане OLTPЦе OLAP-запит на рядковому зберіганні: кожен рядок читається цілком, а для такого навантаження придумано колонкові базиPostgreSQL не вміє агрегувати

Лабораторна

Лабораторної для цього модуля немає. Першою практикою курсу буде L1 після модуля 3: запити до того самого датасету.

Джерела

E. F. Codd, A Relational Model of Data for Large Shared Data Banks, Communications of the ACM, 1970.
C. W. Bachman, The Programmer as Navigator (лекція до премії Тюрінга), Communications of the ACM, 1973.
P. Selinger та ін., Access Path Selection in a Relational Database Management System, SIGMOD, 1979.
T. Haerder, A. Reuter, Principles of Transaction-Oriented Database Recovery, ACM Computing Surveys, 1983.
F. Chang та ін., Bigtable: A Distributed Storage System for Structured Data, OSDI, 2006.
G. DeCandia та ін., Dynamo: Amazon’s Highly Available Key-value Store, SOSP, 2007.
J. Corbett та ін., Spanner: Google’s Globally-Distributed Database, OSDI, 2012.
M. Kleppmann, Designing Data-Intensive Applications, розділи 2 і 3.
PostgreSQL 18, документація: Database File Layout.
Linux man-pages, open(2): опис O_APPEND.