В жизни каждого шарписта наступает момент, когда надо разобраться, наконец, как работает СУБД, которой он каждый день пользуется.
Прежде чем написать этот короткий пост, я погрузился в тему баз данных и в какой-то степени они стали для меня открытой книгой, буквально, потому что в них слишком много аналогий с книгами. Оказывается, что концепция, на которой стоит вообще всё хранение данных, одна и та же что в 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 #инженерныештучки

👈 В прошлый раз мы выяснили, что обычно данные в СУБД лежат в страницах фиксированного размера. А как по этим страницам что-то быстро найти?

🔍 Если искать в лоб
Представь, что ты хочешь найти в толстой книге по базам данных упоминание слова «дерево». Если в конце нет предметного указателя — придётся листать страницу за страницей и глазами выискивать нужное слово.
СУБД без индекса делает ровно то же самое: она прочитает все страницы таблицы подряд, проверит на каждой, есть ли подходящая строка, и оставит то, что нашла. Этот режим работы называется Sequential Scan, или просто Seq Scan — последовательное чтение.
Увидеть, что СУБД делает именно так, можно через EXPLAIN — команду, которая покажет, как планируется выполнить запрос. Видишь в плане Seq Scan — значит СУБД проходит таблицу целиком.
На таблице в 10 млн строк это будет довольно долго, потому что на каждый запрос будут выполняться сотни тысяч чтений страниц с диска ради одной нужной строки 💽

📖 Предметный указатель
В книге это решается просто: там есть предметный указатель — отсортированный по алфавиту список слов с номерами страниц. Открываешь, ищешь слово, прыгаешь на нужную страницу.
В СУБД роль такого указателя играет индекс. Это отдельная структура на диске, в которой ключи (например, значения id) лежат отсортированно, а рядом с каждым — указатель на конкретную страницу таблицы 🧿
Чаще всего первый индекс появляется в таблице автоматически: когда ты добавляешь, например, в свою таблицу users PRIMARY KEY на колонку id, Postgres под капотом создаёт уникальный индекс по этой колонке — users_pkey; в psql его видно через метакоманду \d users как btree (id).

🌳 Что такое btree
В большинстве СУБД (PostgreSQL, MySQL/InnoDB, SQL Server, MongoDB через WiredTiger) индекс по умолчанию — не просто отсортированный список, а B-дерево (точнее, его вариант B+tree).
Почему не список? Главное — число чтений с диска. Бинарный поиск по отсортированному списку из миллиона страниц — это около 20 чтений. И если ключ не монотонный (email, uuid, имя автора), любая вставка в середину означает переписать целый хвост.
B-дерево — это обычное дерево из корня сверху, внутренних узлов в середине и листьев снизу. Оно устроено так:

🟢 В каждом узле много ключей — не один-два, а сотни.
🟢 Все листья на одной глубине. Любой путь от корня до листа одинаковой длины.
🟢 За счёт большого ветвления глубина дерева смешная. Для индекса по сотне миллионов записей это 3–4 уровня. Найти строку = 3–4 чтения. Не миллионы, не тысячи. Три-четыре 🎉
🟢 Листья связаны в список. Поэтому Range-запросы (>, <, BETWEEN) тоже летают: спустился до начала диапазона и пробежал листья подряд.
Аналогия — толстая энциклопедия: тома → главы → разделы. Три шага, и ты на месте 👍

📦 Как это лежит в Postgres (см. дикпик 3)
Структура B-tree один в один ложится на структуру страниц из прошлого поста:
⚪️ Один узел дерева = одна страница 8 KB.
⚪️ Внутренние узлы хранят пары (ключ, ссылка на дочернюю страницу). В одну страницу таких пар влезают сотни.
⚪️ Листья хранят пары (ключ, ctid). Помнишь ctid из прошлого поста? Это указатель «страница такая-то, позиция такая-то» — он ведёт прямо на нужную строку в куче.
⚪️ Индекс лежит отдельным файлом со своим pg_relation_filepath — это не часть таблицы, это самостоятельная сущность.

🔬 Пощупаем (см. дикпики 1 и 2)
Берём таблицу users из прошлого поста (6000 строк, без PRIMARY KEY) и ищем по id. Seq Scan ... Rows Removed by Filter: 5999: прочитали всё, отбросили 5999, оставили одну.

Добавляем первичный ключ. Тот же запрос — план сменился на Index Scan using users_pkey. Сначала Postgres прошёл по B-дереву от корня до листа, там нашёл ctid, по нему сходил в нужную страницу таблицы и достал строку.

🅰️ Что с этим знанием делать
Индекс — это отдельные страницы на диске. Каждый INSERT/UPDATE/DELETE правит и таблицу, и все её индексы. Поэтому вешать их «на всякий случай» на каждое поле — плохая идея: платишь местом на диске, замедлением записи, обслуживанием дерева. При хорошо выбранном индексе взамен этих накладных расходов получаешь быстрый поиск⚡️

#бд #postgresql #инженерныештучки