Зберігання
Навіщо це
Section titled “Навіщо це”SELECT avg(product_id) FROM view_events на розмірі medium (Датасет) потребує чотирьох байтів на рядок, усього 2.3 МіБ. Прочитала база 6784 сторінки, тобто 53 МіБ. Менше прочитати не вийде: так влаштовано файл.
Або інший приклад: дві пробні таблиці мають однакові шість колонок і однакові 100 000 рядків. Різниця лише в порядку колонок у CREATE TABLE: перша займає 7 659 520 байтів, друга 6 029 312, на п’яту частину менше.
Обидва факти випливають з одного: СУБД бачить не рядки, а сторінки фіксованого розміру. Спершу про те, що видно ззовні: скільки сторінок прочитав запит і де лежить рядок. Потім про те, що всередині сторінки й рядка, куди відкладаються довгі значення і як сторінка потрапляє в пам’ять через два шари кешу.
Передумови. Процеси PostgreSQL і шари СУБД: модуль 1. Типи колонок: модуль 4. Сторінки пам’яті й кеш сторінок ОС: модулі 11 і 13 курсу «Операційні системи».
Сторінка, а не рядок
Section titled “Сторінка, а не рядок”Диск, SSD і файлова система обмінюються блоками, набагато більшими за рядок (модуль 14 курсу ОС), а кожен запит до пристрою має затримку, яка майже не залежить від кількості байтів. Тому PostgreSQL групує все у сторінки даних по 8 КіБ. З цим розміром він читає, кешує, блокує, захищає контрольною сумою та пише журнал.
SHOW block_size; block_size------------ 8192Більша сторінка дешевша для послідовного читання, але для точкового запиту дорожча. PostgreSQL задає розмір під час збірки, InnoDB у MySQL має 16 КіБ. Сторінка PostgreSQL дорівнює двом сторінкам пам’яті ОС по 4 КіБ, тож запис може дійти до диска наполовину: чим це загрожує, розповідає модуль 11.
Ціну видно на одному рядку. Якщо адресу рядка (ctid, про нього далі) відомо, база читає рівно одну сторінку. Вивід після скидання buffer pool, про який теж далі: shared read означає, що сторінку довелося взяти поза пам’яттю бази, shared hit що вона вже там була.
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT * FROM orders WHERE ctid = '(100,5)'; Tid Scan on orders (actual rows=1.00 loops=1) TID Cond: (ctid = '(100,5)'::tid) Buffers: shared read=1Рядок важить 87 байтів, прочитано 8192. Зате ще 81 сусідній рядок тієї ж сторінки тепер у пам’яті безкоштовно: через це послідовний доступ дешевий, а розкиданий дорогий.
Файл таблиці: heap і ctid
Section titled “Файл таблиці: heap і ctid”Таблиця PostgreSQL зберігається як heap-файл: масив сторінок без порядку. Новий рядок іде на сторінку, де вистачає місця, і знайти її допомагає карта вільного місця. Порядок вставки зберігається, лише поки нічого не видаляли й не оновлювали.
Адресою рядка слугує ctid, пара «номер сторінки, номер вказівника»:
SELECT order_id, ctid FROM orders WHERE order_id IN (1, 3, 83, 84); order_id | ctid----------+------- 1 | (0,1) 3 | (0,3) 83 | (1,1) 84 | (1,2)На сторінці 82 рядки, тож 83-є замовлення відкриває сторінку 1. Індекс з модуля 8 зберігає саме такі ctid. UPDATE створює нову версію рядка з новою адресою, тому ctid не годиться за ідентифікатор рядка.
Файл таблиці названо числом filenode, яке не завжди збігається з OID (після TRUNCATE чи VACUUM FULL воно змінюється). Поруч лежать додаткові файли, для orders на розмірі medium:
SELECT pg_relation_filepath('orders') AS path, pg_relation_size('orders') AS main, pg_relation_size('orders', 'fsm') AS fsm, pg_relation_size('orders', 'vm') AS vm; path | main | fsm | vm------------------+----------+-------+------ base/16385/37362 | 21618688 | 24576 | 8192На medium головний файл має 2639 сторінок. _fsm (free space map) зберігає, скільки вільно на кожній. _vm (visibility map) тримає два біти на сторінку: чи всі рядки на ній видимі всім транзакціям і чи їх заморожено. За цими бітами VACUUM пропускає сторінки, де нема чого чистити, а Index Only Scan (модуль 9) не заходить у heap.
Файл, що перевищив 1 ГіБ, ріжеться на сегменти. Пробна таблиця на 1172 МіБ, зібрана з копій view_events, лежить у 37574 (рівно 1 073 741 824 байти) і 37574.1 (154 935 296).
Що всередині сторінки
Section titled “Що всередині сторінки”Заголовок займає 24 байти. Далі йде масив вказівників на рядки (line pointers, lp) по 4 байти: зсув і довжина рядка на сторінці. Самі рядки складаються з кінця сторінки назад, вільне місце лишається посередині. Коли pd_lower (кінець масиву) зустрівся з pd_upper (початок рядків), новий рядок іде на іншу сторінку.
Навіщо окремий масив? Рядки різної довжини залишають діри, і після ущільнення зсуви змінилися б. Вказівник цьому запобігає: ззовні рядок названо «сторінка й номер вказівника», а де він лежить усередині, знає лише сторінка. pageinspect показує ці числа (потрібен -U postgres):
SELECT lower, upper, special FROM page_header(get_raw_page('orders', 0));SELECT count(*), min(lp_off) FROM heap_page_items(get_raw_page('orders', 0)); lower | upper | special-------+-------+--------- 352 | 424 | 8192
count | min-------+----- 82 | 424Сторінка має 82 рядки: 24 + 82 × 4 = 352. Останній рядок починається в 424, де закінчується вільне місце. Між 352 і 424 лишилося 72 байти, для нового рядка замало.
Що адреса переживає ущільнення, видно на тимчасовій таблиці: видалимо середній рядок, запустимо VACUUM і вставимо новий.
CREATE TEMP TABLE lp_demo (id int, note text);INSERT INTO lp_demo VALUES (1, repeat('a', 100)), (2, repeat('b', 100)), (3, repeat('c', 100));DELETE FROM lp_demo WHERE id = 2;VACUUM lp_demo;SELECT lp, lp_off, lp_flags FROM heap_page_items(get_raw_page('lp_demo', 0));INSERT INTO lp_demo VALUES (4, repeat('d', 50));SELECT ctid, id FROM lp_demo; lp | lp_off | lp_flags----+--------+---------- 1 | 8056 | 1 2 | 0 | 0 3 | 7920 | 1
ctid | id-------+---- (0,1) | 1 (0,2) | 4 (0,3) | 3Рядок 3 лежав у 7784, після VACUUM у 7920, а адреса (0,3) та сама. Вказівник 2 звільнився (lp_flags = 0), і новий рядок його зайняв. Чому видалений рядок пролежав до VACUUM: модуль 10.
Рядок на сторінці і порядок колонок
Section titled “Рядок на сторінці і порядок колонок”Рядок на сторінці починається із заголовка. Поля t_xmin, t_xmax, t_ctid з модуля 10 задають видимість версії. Сам заголовок має 23 байти, але дані мусять починатися з адреси, кратної 8, тож t_hoff дорівнює 24. Коли в рядку є хоч один NULL, між заголовком і даними з’являється бітова карта по біту на колонку. У orders дев’ять колонок, карта займає два байти, і t_hoff стає 32 (t_bits = 1111111000000000: дві останні колонки, promo_code і customer_note, порожні). Сам NULL даних не займає, лише біт.
Далі йдуть значення в порядку CREATE TABLE. Кожен тип вимагає вирівнювання (alignment): boolean 1 байт, smallint 2, integer 4, bigint і timestamptz 8. Якщо наступному значенню потрібна адреса, кратна 8, а позиція не кратна, база пропускає зайві байти. Це й дало різницю на початку модуля.
Без таблиць це видно з pg_column_size конструктора рядка: він дає довжину рядка із заголовком.
bad_order | good_order-----------+------------ 72 | 51Різниця в 21 байт повторюється в кожному рядку. У сторінці рядок вирівнюється до 8 байтів: перший займає 72, другий 56, тож на сторінку вміщається 107 рядків проти 136. На таблицях:
CREATE TABLE col_order_bad (a boolean, b bigint, c boolean, d bigint, e boolean, f bigint);CREATE TABLE col_order_good (b bigint, d bigint, f bigint, a boolean, c boolean, e boolean);INSERT INTO col_order_bad SELECT true, i, false, i, true, i FROM generate_series(1, 100000) AS i;INSERT INTO col_order_good SELECT i, i, i, true, false, true FROM generate_series(1, 100000) AS i;SELECT pg_relation_size('col_order_bad') AS bad, pg_relation_size('col_order_good') AS good; bad | good---------+--------- 7659520 | 6029312Порядок не виправити без переписування таблиці. У «Крамниці» він вдалий майже випадково: навмисно погано переставлений order_items (розмір small) виріс лише на 1.4%, бо numeric і text коротші за 127 байтів мають однобайтний заголовок і вирівнювання не потребують. На практиці 8-байтові типи (bigint, timestamptz, double precision) ставлять першими, далі integer, smallint, boolean, змінної довжини наприкінці.
Довгі значення: TOAST
Section titled “Довгі значення: TOAST”Значення text чи jsonb може бути більшим за сторінку. Механізм TOAST спрацьовує, коли рядок перевищує приблизно 2 КіБ: спершу база стискає найбільші значення, а якщо цього мало, виносить їх у службову таблицю, лишаючи в рядку вказівник. Там значення ріжеться на шматки по 1996 байтів, по чотири шматки на сторінку. Межа одного значення — 1 ГіБ.
Описи товарів до цього не доходять: найдовший має 706 байтів, тож у products TOAST порожній. pg_column_size(description) для нього дає 710: чотири байти понад octet_length — заголовок значення (для коротших за 127 байтів він займає один). Щоб побачити TOAST, склеїмо по 30 сусідніх описів у кожного з перших 3500 товарів (це весь small):
CREATE TABLE products_long ASSELECT p.product_id, p.title, (SELECT string_agg(d.description, E'\n' ORDER BY d.product_id) FROM products d WHERE d.product_id BETWEEN p.product_id AND p.product_id + 29) AS descriptionFROM products p WHERE p.product_id <= 3500;
SELECT length(description) AS chars, octet_length(description) AS bytes, pg_column_size(description) AS stored, pg_column_compression(description) AS methodFROM products_long WHERE product_id = 1; chars | bytes | stored | method-------+-------+--------+-------- 6854 | 12050 | 4266 | pglzКирилиця займає по два байти на літеру, тому 6854 символи дають 12 050 байтів. Після стиснення типовим pglz лишилося 4266, що вище порога в 2 КіБ, і значення пішло в TOAST-таблицю. У середньому по таблиці 11 689 байтів стиснулися до 4067. Метод lz4 (default_toast_compression) на цих текстах стискає гірше, 5441, зате швидше розпаковує: сума довжин описів читалася 69 мс проти 98 мс.
Запит, який не чіпає довгу колонку, читає лише основну таблицю, а той, що чіпає, ще й шматки з TOAST:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT sum(length(title)) FROM products_long;EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT sum(length(description)) FROM products_long; Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=42 -> Seq Scan on products_long (actual rows=3500.00 loops=1) Buffers: shared hit=42
Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=11591 -> Seq Scan on products_long (actual rows=3500.00 loops=1) Buffers: shared hit=42Основна таблиця — 42 сторінки в обох запитах. Решта 11 549 звернень у другому запиті припадає на TOAST-таблицю та її індекс. Отже, SELECT * по таблиці з довгими колонками платить за кожну з них, а UPDATE іншої колонки значення в TOAST не переписує.
Рядкове і колонкове зберігання
Section titled “Рядкове і колонкове зберігання”Усе вище — рядкове зберігання (row store): значення одного рядка лежать поруч, бо так дешево вставити, оновити й прочитати рядок цілком. Запит на одну колонку платить за всі. У view_events (medium) середній рядок важить 83 байти, на сторінку їх лягає 90, колонка product_id разом займає 2 440 044 байти, а таблиця 55 574 528, у 22.8 раза більше. Аналітичний запит по кількох колонках мільярдів рядків платить цю різницю щоразу.
У колонковому зберіганні (column store) кожна колонка лежить окремо: такий запит читає лише потрібне, а однорідні значення поруч добре стискаються. Ціна: збирання рядка з кількох файлів і дорога одинична вставка. Так влаштовані DuckDB і ClickHouse, їх розбирає модуль 19.
Buffer pool
Section titled “Buffer pool”Запит не читає файл напряму. Кожне звернення до сторінки йде через buffer pool: область спільної пам’яті розміром shared_buffers, розбиту на буфери по 8 КіБ. У нашому docker-compose.yml це 256 МБ, тобто 32 768 буферів (типово 128 МБ). Для виділеного сервера документація радить близько чверті оперативної пам’яті й не очікує виграшу від понад 40%: решту з користю займає кеш ОС.
Пул має хеш-таблицю «файл і номер сторінки → буфер». Якщо сторінка є (shared hit), процес закріплює буфер (pin), щоб його не витіснили під час читання, збільшує лічильник використання й працює. Якщо немає (shared read), треба звільнити буфер.
Тут працює алгоритм годинника (clock sweep): стрілка обходить буфери по колу. Лічильник використання кожного зростає з кожним зверненням, але не вище 5. Стрілка зменшує ненульовий лічильник і йде далі, а буфер із нулем і без pin витісняється. Змінену сторінку спершу записують на диск (це роблять background writer, checkpointer або сам процес), і не раніше за її журнальний запис (модуль 11).
Чому не точний LRU? Список за давністю довелося б блокувати при кожному зверненні, а з пулом одночасно працюють усі процеси. Годинник змінює лише лічильник у самому буфері, як і годинниковий алгоритм ОС (модуль 12 курсу ОС).
Одноразові проходи не мають знищувати гарячі сторінки. Для великих послідовних сканувань і VACUUM виділяється невелике кільце буферів, і вони витісняють лише один одного. Заміряно: таблиця на 26 944 сторінки (211 МіБ) після повного сканування залишила в пулі 188 буферів, тобто 1.5 МіБ. Це більше за класичні 256 КіБ: у PostgreSQL 18 до кільця додається місце під асинхронні читання (за freelist.c).
Два кеші і прямий ввід-вивід
Section titled “Два кеші і прямий ввід-вивід”Коли в shared_buffers сторінки немає, PostgreSQL просить її в ОС. Ядро дивиться у кеш сторінок і, якщо сторінка там, копіює її без звернення до диска (модуль 13 курсу ОС).
Звідси два наслідки. Одна сторінка може лежати в пам’яті двічі, в пулі й у кеші ОС: це подвійне кешування (double buffering). А shared read в EXPLAIN не гарантує, що диск працював. Час вводу-виводу (I/O Timings при track_io_timing = on) показує, скільки чекали, але кеш ОС від швидкого диска теж не відрізнить.
Прямий ввід-вивід (direct I/O, прапорець O_DIRECT) просить ядро не кешувати файл, читати й писати повз кеш. СУБД з власним кешем часто ним користуються, щоб не дублювати: InnoDB у нашому контейнері MySQL 8.4 працює з innodb_flush_method = O_DIRECT. PostgreSQL за замовчуванням ні. Практична причина в тому, що кеш ОС слугує безкоштовним другим рівнем, і разом із ним приходить випереджувальне читання для послідовних сканувань. Документація ж каже прямо: debug_io_direct (значення data, wal, wal_init) у цій версії знижує продуктивність і призначений для тестування розробниками.
Що змінилося в PostgreSQL 18. Release notes описують нову підсистему асинхронного вводу-виводу: запити ставляться в чергу, не чекаючи один одного. Параметр io_method має три значення: worker (типове: окремі процеси io worker, які видно в ps у модулі 1), io_uring (потрібна збірка з liburing) і sync зі старою поведінкою. За анонсом версії, асинхронно працюють послідовні сканування, bitmap heap scan і VACUUM. Це ще не прямий ввід-вивід: читання лишаються буферизованими й ідуть через кеш ОС.
Чому СУБД не користуються mmap. Спокуса: відобразити файл у пам’ять і віддати сторінки ОС (модуль 12 курсу ОС). Стаття Crotty, Leis і Pavlo (CIDR 2022) називає чотири проблеми.
- Транзакційна безпека. ОС може записати змінену сторінку на диск будь-коли, навіть до коміту транзакції. СУБД ні не заборонить цього, ні не дізнається.
- Затримки вводу-виводу.
mmapне має асинхронних читань, а звернення до витисненої сторінки тихо стає блокуючим сторінковим винятком. - Обробка помилок. Контрольну суму доводиться перевіряти при кожному зверненні, бо сторінку могли витіснити. Помилка вводу-виводу приходить сигналом
SIGBUS, не кодом повернення. - Продуктивність. На швидких дисках заважають суперечка за таблицю сторінок, однопотокове витіснення й
TLB shootdowns(модуль 11 курсу ОС пояснює TLB).
InnoDB інакше
Section titled “InnoDB інакше”Сторінка InnoDB має 16 КіБ, але головна різниця глибша: таблиця MySQL є кластерним індексом (clustered index), B+-деревом за первинним ключем, і рядки лежать у його листових сторінках. Heap-файлу й ctid немає.
Heap-файл без порядку, первинний ключ — звичайний індекс із ctid у листових сторінках. Порядок колонок у CREATE TABLE змінює розмір рядка через вирівнювання.
Рядки лежать у листових сторінках дерева первинного ключа, DATA_LENGTH у information_schema.tables — розмір цього дерева. Вторинний індекс зберігає значення первинного ключа й веде за рядком у кластерний індекс. Довгі text і blob у форматі DYNAMIC лежать на сторінках переповнення, а в рядку лишається 20-байтовий вказівник. Порядок колонок на розмір не впливає: аналоги col_order_bad і col_order_good зі 100 000 рядків (з додатковим id bigint як первинним ключем) мали однакові 6 832 128 байтів DATA_LENGTH (MySQL 8.4.11).
Buffer pool InnoDB теж не чистий LRU: список ділиться на «молоду» і «стару» частини (стара типово 37%). Нова сторінка потрапляє на межу між ними й стає молодою, лише якщо до неї звернулися знову після затримки innodb_old_blocks_time (1000 мс). Мета та сама, що в кілець PostgreSQL: одноразове сканування не повинно вимити гарячі сторінки. Лічильники Innodb_buffer_pool_read_requests (логічні читання) і Innodb_buffer_pool_reads (з диска) приблизно відповідають shared hit і shared read. Наслідки кластеризації для індексів: модуль 8.
Як це насправді
Section titled “Як це насправді”PostgreSQL 18.6 із нашого docker compose, розмір medium (див. setup/README.md і Датасет). pageinspect і pg_buffercache потребують суперкористувача: docker compose exec postgres psql -U postgres -d shop, потім CREATE EXTENSION pageinspect; CREATE EXTENSION pg_buffercache;. У пісочниці (розмір small) їх немає (PGlite без цих розширень), але працюють ctid, pg_column_size, pg_relation_size і EXPLAIN:
Запит показує, скільки рядків лягло на кожну сторінку orders. Скільки сторінок прочитав запит, каже EXPLAIN (ANALYZE, BUFFERS); у пісочниці це завжди shared hit:
У виводі Buffers: shared hit=272, рівно стільки, скільки дає pg_relation_size('orders') / 8192 на small. На Docker пул можна скинути: pg_buffercache_evict_all() (нова в PostgreSQL 18) виштовхує з нього всі сторінки без pin. Двічі порахуємо рядки view_events (medium):
SELECT pg_buffercache_evict_all();SET max_parallel_workers_per_gather = 0;EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT count(*) FROM view_events;EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT count(*) FROM view_events; Aggregate (actual rows=1.00 loops=1) Buffers: shared read=6784 I/O Timings: shared read=0.757 -> Seq Scan on view_events (actual rows=610011.00 loops=1) Buffers: shared read=6784 Execution Time: 26.496 ms
Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=6784 -> Seq Scan on view_events (actual rows=610011.00 loops=1) Buffers: shared hit=6784 Execution Time: 15.885 msСпершу 6784 сторінки з shared read, потім усі 6784 shared hit. Різниця в часі невелика, 26.5 проти 15.9 мс, бо в контейнері «читання» пройшло з кешу ОС віртуальної машини: I/O Timings менший за мілісекунду на 53 МіБ.
Що лежить у пулі, покаже pg_buffercache:
SELECT usagecount, count(*) FROM pg_buffercache WHERE relfilenode IS NOT NULL GROUP BY 1 ORDER BY 1; usagecount | count------------+------- 1 | 60 2 | 6803 3 | 13 4 | 11 5 | 33Сторінки view_events мають usagecount = 2, бо їх прочитали двічі, а системні каталоги, до яких звертається кожен запит, дійшли до 5. За цим лічильником стрілка відрізняє гарячі сторінки від холодних.
Типові помилки розуміння
Section titled “Типові помилки розуміння”«База читає рядки». Читаються сторінки. Запит, який знайшов один рядок за ctid, заплатив за 8192 байти, а запит по одній колонці заплатив за всі колонки всіх рядків.
«Порядок колонок у CREATE TABLE — справа смаку». У PostgreSQL він визначає вирівнювання, розмір рядка й кількість рядків на сторінці: 21% на нашій таблиці з трьох boolean і трьох bigint. В InnoDB порядок не впливає.
«Чим більше shared_buffers, тим швидше». Решта пам’яті працює кешем ОС, а пул більший за робочий набір лише відбирає її в кеша. Тому документація радить близько чверті, а не всю.
«shared read — це читання з диска». Це промах у buffer pool. Сторінка могла бути в кеші ОС, тоді диск не працював.
Перевір себе
Лабораторна
Section titled “Лабораторна”Окремої лабораторної модуль не має. Сторінки, BUFFERS і розміри знадобляться в L4. Прискорити повільні запити разом із модулями 8 і 9: кожне прискорення там підтверджується числом прочитаних сторінок.
Джерела
Section titled “Джерела”- PostgreSQL 18, документація: Database Physical Storage (файли, TOAST, сторінка), ресурси й асинхронний ввід-вивід,
debug_io_direct, pageinspect, pg_buffercache. - PostgreSQL 18, release notes і анонс версії.
- A. Crotty, V. Leis, A. Pavlo, Are You Sure You Want to Use MMAP in Your Database Management System?, CIDR 2022.
- MySQL 8.4 Reference Manual: Clustered and Secondary Indexes, The InnoDB Buffer Pool, InnoDB Row Formats.
- J. Hellerstein, M. Stonebraker, J. Hamilton, Architecture of a Database System, Foundations and Trends in Databases, 2007.