База данный: как устроены хранилища информации изнутри

Один SELECT может поднять несколько страниц в память, а UPDATE способен изменить копию страницы в буферном пуле и записать сведения об изменении в WAL. Физическая таблица на диске в этот момент может оставаться прежней.

Поэтому одна и та же база данных ведёт себя по-разному после рестарта, деплоя, изменения объёма данных или задержки на диске. Для диагностики полезно разделять четыре контура: CPU, кэш страниц, запись WAL и сброс страниц на диск. Без этого разбиения поиск причины легко превращается в подбор параметров наугад.

Страница служит единицей обмена с хранилищем. Транзакция фиксируется через журнал, а табличные страницы могут обновляться позже.

Страница как реальная единица хранения

Обычная SQL-команда не обращается к отдельным байтам файла. Планировщик выбирает план доступа, исполнитель запрашивает страницы, а менеджер буферов проверяет, есть ли они в памяти.

Физический путь чтения выглядит так:

1. Планировщик выбирает индексное или последовательное сканирование.

2. Исполнитель вычисляет идентификатор нужной страницы.

3. Менеджер буферов ищет соответствующий фрейм в памяти.

4. При промахе страница читается через файловую систему и систему хранения.

5. СУБД проверяет страницу по контрольной сумме, извлекает строку и применяет правила видимости транзакций.

Страница СУБД и блок операционной системы относятся к разным уровням. Страница может попасть в кэш файловой системы ещё до обращения СУБД к собственному буферному пулу. Отсюда разница между холодным и прогретым состоянием сервера: после рестарта один и тот же запрос сначала может читать страницы с диска, а затем обслуживаться из памяти.

Эти размеры являются настройками по умолчанию, а не универсальной спецификацией. Меньшая страница сокращает объём одного чтения, но при последовательной обработке данных может понадобиться больше операций. Большая страница позволяет прочитать больше данных за раз, однако способна повысить цену неполного заполнения. Итог зависит от размера ключей, типа запросов, распределения данных и характеристик накопителя.

В одном наборе данных могут одновременно находиться страницы разного назначения: страницы таблицы, листья индекса, bitmap-индексы, служебные страницы и данные больших объектов. В PostgreSQL heap таблицы физически отделён от индексов. InnoDB хранит записи в кластерном индексе. SQLite обычно размещает данные в B-дереве по rowid, а вариант WITHOUT ROWID меняет организацию таблицы, используя первичный ключ.

Буферный пул и WAL: где возникает задержка

Буферный пул — область оперативной памяти, в которой СУБД хранит страницы. InnoDB управляет выделенным buffer pool. PostgreSQL использует shared_buffers, но часть чтений может обслуживаться кэшем операционной системы. SQLite применяет собственный page cache, размер которого задаётся конфигурацией.

Страница, изменённая в памяти, называется грязной. Пока её не сбросили на диск, актуальная версия находится в буферном пуле. Сброс выполняют фоновые процессы, его также запускают давление на дисковую подсистему и механизм checkpoint.

Путь записи в типичной транзакционной СУБД выглядит так:

1. Транзакция находит страницу и при необходимости блокирует её.

2. Страница закрепляется в буферном пуле, чтобы её не вытеснили.

3. Изменение вносится в память.

4. Для операции создаётся запись в журнал упреждающей записи.

5. COMMIT фиксирует транзакцию и запускает сброс журнала согласно настройкам долговечности.

6. Грязная страница попадает на диск позже, во время фонового сброса или checkpoint.

Именно журнал, а не основной файл таблицы, помогает восстановиться после сбоя процесса или сервера. В PostgreSQL механизм называется WAL, в InnoDB для аналогичной роли служит redo log. В обоих случаях журнал работает как последовательный поток записей, пока старые сегменты не будут переработаны после checkpoint.

WAL фиксирует изменение, а страница таблицы хранит его текущее состояние. Записи в журнал и страницы обслуживаются по разным расписаниям.

Настройка COMMIT критична. В PostgreSQL поведение задаёт synchronous_commit, в MySQL — innodb_flush_log_at_trx_commit, в SQLite — параметр synchronous и режим журналирования. От этих настроек зависит, когда запись считается устойчивой после сбоя питания.

Последовательная запись журнала обычно дешевле случайного сброса страниц. Но сама по себе последовательность не гарантирует долговечность. Нужна фактическая фиксация данных в устойчивом хранилище, часто с участием fsync или эквивалентной операции. Медленный fsync способен создать длинный хвост задержки COMMIT, даже когда среднее время выглядит приемлемым.

Checkpoint решает другую задачу. Он сбрасывает грязные страницы и перемещает точку, от которой при восстановлении потребуется читать журнал. Если накопитель не успевает обслуживать поток грязных страниц, возникает долг по сбросу. Следующий checkpoint может затянуться, а задержки отдельных транзакций вырасти.

WAL не является резервной копией. Он не хранит всю историю данных и может перерабатываться после checkpoint. Для восстановления после повреждения диска или ошибки оператора нужны резервные копии, репликация и регулярная проверка восстановления.

Данные с длинным сроком хранения требуют проверяемой сохранности, а не одной декларации. Тот же инженерный принцип виден в подходе к пенсионным накоплениям и защите капитала: обязательство сохранения превращается в регулярно проверяемый процесс.

Внутри страницы: заголовок, кортежи и свободное место

Типичная реляционная страница использует структуру slotted page. Логически она состоит из заголовка, массива указателей на строки, самих кортежей и свободного пространства.

Заголовок содержит служебные метаданные. В зависимости от движка там могут находиться контрольная сумма, LSN, счётчики элементов и информация о состоянии страницы. Указатели располагаются в начале страницы и растут к её концу. Кортежи размещаются с противоположной стороны и растут навстречу указателям. Между ними остаётся свободное место.

При вставке новой строки движок резервирует место под кортеж, записывает его с конца страницы и добавляет указатель в массив слотов. При удалении указатель могут очистить, но физическое место строки освобождается не всегда сразу. При обновлении применяются разные стратегии: перезапись на месте, создание новой версии строки или использование журнала отката.

MVCC усложняет этот процесс. PostgreSQL хранит версии строк в heap и удаляет устаревшие версии при обслуживании, обычно с помощью VACUUM и autovacuum. InnoDB использует собственный механизм версий и undo-журналы. Долгая транзакция удерживает старые версии и мешает их удалению. Это приводит к росту bloat, дополнительному чтению страниц и может повлиять на планы запросов.

Большие значения могут выноситься за пределы основной страницы. В PostgreSQL для этого применяется TOAST с возможным сжатием. Размер основной страницы остаётся управляемым, но чтение такой записи требует дополнительных обращений.

При нехватке места страница разделяется. Дерево индексов перестраивает связи, а в таблице со временем могут появиться страницы с незаполненными участками. Физический порядок данных расходится с логическим. Перезапись и удаление сами по себе не гарантируют автоматического уплотнения.

Контрольная сумма обнаруживает повреждение данных, но не исправляет страницу. Для восстановления понадобятся WAL, реплика или резервная копия. Поэтому checksum, backup и recovery решают разные задачи.

Индексы и ключи: B+Tree против LSM

Индекс — отдельная структура для поиска. Он содержит ключ и ссылку на строку либо дополнительные поля. В PostgreSQL индекс обычно указывает на кортеж в heap. InnoDB хранит строки в кластерном индексе, а вторичные индексы ссылаются на первичный ключ. Поэтому обе СУБД используют индексы, но физическая организация у них различается.

В реляционных системах часто применяют B+Tree. Внутренние страницы маршрутизируют поиск, листья содержат данные и связаны между собой для последовательного сканирования диапазонов. Высокий коэффициент ветвления позволяет разместить много записей на одной странице. Фактическая глубина дерева зависит от размера ключей и данных, а также от заполненности страниц.

Порядок составного ключа определяет его полезность. Индекс (tenant_id, created_at) подходит для запроса по одному tenant_id и диапазону времени внутри него. Запрос только по created_at потребует другого плана доступа. B+Tree хранит ключи в лексикографическом порядке с учётом последовательности столбцов в индексе.

Покрывающий индекс содержит все поля, нужные запросу. PostgreSQL может выполнить index-only scan, если требуемые данные доступны в индексе и видимость строк подтверждается visibility map. В InnoDB вторичный индекс тоже может обслужить запрос без чтения кластерного индекса, когда он покрывает все необходимые запросу столбцы. Если хотя бы части нужных данных во вторичном индексе нет, СУБД обращается к кластерному индексу, чтобы получить строку. Запрос SELECT * обычно лишает такую оптимизацию смысла.

Системы с высокой интенсивностью записи часто используют LSM-деревья. Cassandra и RocksDB — типичные примеры. Запись попадает в memtable и журнал, затем сбрасывается в неизменяемые отсортированные runs. Со временем структура уплотняется через compaction.

Выбор структуры определяется профилем нагрузки. Сравнение «B+Tree медленнее» или «LSM всегда быстрее» не даёт полезного ответа. Нужно учитывать распределение чтения и записи, размер записи, требования к диапазонным запросам, объём данных и допустимую стоимость обслуживания.

Каждый индекс добавляет собственные страницы и записи в журнал, а также обновляется при INSERT, DELETE и UPDATE. Низкоселективный индекс может почти не отсекать строки, но повышать стоимость каждой записи. Для производительности важнее сбалансированный набор индексов, чем их максимальное количество.

Транзакции и восстановление по ARIES

Современные транзакционные базы используют принципы, описанные в ARIES: Algorithms for Recovery and Isolation Exploiting Semantics. Конкретные реализации различаются, но базовая последовательность восстановления узнаваема.

1. Analysis. СУБД читает журнал от checkpoint, определяет активные транзакции, грязные страницы и точку, с которой нужно начинать redo.

2. Redo. СУБД повторяет записи журнала, которых ещё нет на страницах. Повторяются и изменения завершённых транзакций, поскольку после сбоя соответствующие страницы могли не успеть сброситься.

3. Undo. Незавершённые транзакции откатываются. Для компенсации создаются записи CLR, чтобы повторный запуск восстановления не отменял уже выполненную компенсацию.

Page LSN позволяет сравнить состояние страницы с журналом и не повторять уже применённые изменения. Checkpoint сокращает объём анализа при запуске. Одни системы сбрасывают все грязные страницы, другие записывают их постепенно, сохраняя согласованную точку отсчёта.

Четыре гарантии ACID опираются на разные механизмы:

• Atomicity обеспечивается откатом изменений и восстановлением через журнал.
• Durability зависит от записи и сброса WAL согласно настройкам COMMIT.
• Isolation реализуется блокировками, MVCC, уровнями изоляции или их сочетанием.
• Consistency поддерживается ограничениями, правилами транзакций и согласованным состоянием после recovery.

Уровень Read Committed обычно показывает снимок на уровне отдельного оператора. Repeatable Read может удерживать снимок дольше, но точная семантика зависит от СУБД. Serializable добавляет обнаружение конфликтов или более строгие блокировки и тем самым повышает цену параллельной работы.

Локальный COMMIT и подтверждение реплики — разные события. При асинхронной репликации клиент может получить успешный ответ до того, как последняя транзакция попадёт на реплику. Переключение на отстающий узел приведёт к потере этих записей. Для систем с требованиями к сохранности это напрямую влияет на допустимый RPO.

Практический разбор узкого места

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

Порядок диагностики

1. Зафиксировать симптом. Разделить p95 и p99 задержки, отдельно проверить SELECT, INSERT, UPDATE и COMMIT. Среднее время скрывает редкие задержки checkpoint и fsync.

2. Проверить фактический план. Оценка планировщика может расходиться с числом обработанных строк. В PostgreSQL EXPLAIN (ANALYZE, BUFFERS) выполняет запрос. Для DML это изменяет данные, а volatile-функции могут иметь побочные эффекты и в SELECT. В неизвестной продовой сессии сначала используют EXPLAIN без выполнения.

3. Сопоставить план с чтениями. Сравнить estimated rows и actual rows, число loops, index scan с sequential scan, попадания shared hit и чтения shared read, использование временных файлов. Последовательное сканирование большой доли таблицы может быть правильным решением.

4. Проверить кэш. Посмотреть hit ratio буферного пула, объём холодных чтений, число промахов и поведение запроса после прогрева. Один холодный запуск нельзя считать стабильным бенчмарком.

5. Разобрать путь записи. Проверить задержку WAL и redo log, время сброса журнала, объём грязных страниц, длительность checkpoint и очередь записи к накопителю.

6. Исключить блокировки и раздувание. Посмотреть lock waits, deadlocks, возраст долгих транзакций, число мёртвых версий и размер индексов. Долгий SELECT способен удерживать снимок данных и мешать VACUUM.

7. Сверить репликацию и восстановление. Измерить lag и задержку подтверждения, проверить состояние WAL и дату последней успешной проверки восстановления. Резервная копия без теста восстановления остаётся предположением.

Что фиксировать в эксплуатации

• Размер страницы и параметры её хранения.
• Buffer hit и page read с разделением чтения и записи.
• WAL bytes per second, задержку сброса и p99 fsync.
• Длительность checkpoint и объём страниц, ожидающих сброса.
• Lock wait, deadlock и возраст транзакций.
• Число мёртвых версий, bloat и рост индексов.
• Replication lag и фактическое окно возможной потери данных.
• Время восстановления из резервной копии, включая проверку целостности.

Рабочая модель СУБД проста: страница попадает в буфер, изменение фиксируется в WAL, грязные страницы сбрасываются во время checkpoint, а recovery повторяет и отменяет записи. Индекс ускоряет поиск, но занимает место, добавляет записи и требует обслуживания. Когда эти процессы разделены в голове, «медленная база данных» перестаёт быть диагнозом и становится набором измеряемых узких мест.