В жизни каждого шарписта наступает момент, когда надо разобраться, наконец, как работает СУБД, которой он каждый день пользуется.
Прежде чем написать этот короткий пост, я погрузился в тему баз данных и в какой-то степени они стали для меня открытой книгой, буквально, потому что в них слишком много аналогий с книгами. Оказывается, что концепция, на которой стоит вообще всё хранение данных, одна и та же что в PostgreSQL, в MySQL, и даже в MongoDB — страницы 📄
📖
Что такое страница и зачем онаКогда ты делаешь
INSERT INTO users (...), в голове картина обычно простая: «строчка просто легла куда-то в таблицу». Физически — нет. Ни одна нормальная СУБД (что реляционная, что документная) не пишет каждую строку или документ отдельно. Вместо этого данные складываются в страницы (pages) — блоки фиксированного размера.
У PostgreSQL и SQL Server страница — 8 KB, у MySQL/InnoDB — 16 KB, у MongoDB через WiredTiger — настраивается, но того же порядка. Любая операция чтения или записи — это работа с целой страницей, а не с одной строкой.
Почему так? Потому что диск и RAM физически работают блоками, а не байтами. Один поход на диск всё равно вытаскивает целый блок килобайт на восемь — хочешь ты того или нет. СУБД этим пользуется: складывает в одну страницу столько строк или документов, сколько влезет. Берёшь одну строку — получаешь рядом «соседей» бесплатно 💋
🔬
Давай потрогаем рукамиСамое классное — это всё можно пощупать, если поднять, например, PostgreSQL в Docker. Полный список команд для последовательного выполнения сможешь найти
👉вот здесь👈 (а то пост получится уже не таким коротким) 🥖
На пояснительном дикпике 1 можно увидеть, где физически лежит созданная таблица и сколько весит: файл занимает ровно 409600 байт =
50 × 8192, то есть страницы ровно по 8 KB. И таких страниц в одном файле подряд — 50 штук, это и есть вся таблица.
На дикпике 2 можно увидеть, как заглянуть внутрь файла прямо из контейнера и что записи лежат сырыми байтами в одной из 50 страниц.
📍
Где какая строка лежитВ Postgres у каждой строки есть служебный «адрес» —
ctid, пара
(номер страницы, номер слота на странице).
На дикпике 3 запрос, который покажет адреса выбранных строк, и там же видно, что в нулевую страницу 8 KB у нас влезла 121 строка, в первую — следующие 120, и так далее. На 6000 строк ушло 50 страниц — ровно то, что мы видели по размеру файла. Когда СУБД хочет прочитать запись с
id = 5999, она не «ищет в таблице» — она поднимает с диска страницу №49 и достаёт из неё нужный слот.
🧑💻
Что с этим знанием делать в работе1.
Чтение одной строки = чтение целой страницы. Запросом за одной строчкой ты всё равно поднимаешь с диска 8 (или 16) KB. Если в
WHERE id IN (...) много значений и они физически рядом — это почти бесплатно. Если разбросаны по таблице — каждое чтение отдельная страница.
2.
Узкие таблицы — это не только про место. Чем шире строка, тем меньше их влезает в страницу. Большое поле
text на килобайт-другой может уронить плотность в 20 раз — и для того же количества строк понадобится в 20 раз больше страниц.
3.
shared_buffers / buffer pool — это кэш страниц, не строк. Когда тюнят память под СУБД, имеют в виду «сколько страниц влезет в RAM». И именно поэтому случайные чтения по большой таблице болят: каждая страница приходит с диска заново.
4.
В Postgres UPDATE — это DELETE + INSERT. Старая версия строки помечается мёртвой и продолжает лежать на той же странице, новая дописывается рядом. Поэтому после массовых апдейтов файл таблицы пухнет, пока не пройдёт VACUUM. Та же логика —
ctid после
UPDATE меняется, потому что физически это уже другая запись на другом слоте.
А если в таблице миллионы записей, как БД так быстро находит нужную? Для этого нужны индексы, но о них — в другом посте👉
#бд #postgresql #инженерныештучки
