Как PostgreSQL хранит строки и WAL

На уровне SQL таблица выглядит как набор строк. На диске PostgreSQL работает с файлами, страницами и версиями строк. Эта модель объясняет, почему UPDATE занимает новое место, зачем нужен VACUUM и как индекс может прочитать данные без обращения к таблице.

Обычная таблица PostgreSQL физически хранится как куча (heap): строки не обязаны лежать в порядке первичного ключа. Файл таблицы разбит на страницы, обычно по 8 КиБ. Страница — блок, который сервер читает с диска и держит в памяти.

Внутри страницы указатель ведёт к версии строки (tuple version). Служебное поле ctid показывает физический адрес версии как (номер блока, место внутри блока).

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

Каждая версия содержит сведения MVCC, включая xmin и xmax. Видимость зависит от снимка и состояния транзакций, а не от простого сравнения этих чисел. Поэтому бизнес-логику на них тоже не строят.

Обычный UPDATE создаёт новую версию строки. Если изменился индексируемый столбец, PostgreSQL обычно добавляет новые записи и в соответствующие индексы.

Оптимизация HOT (Heap-Only Tuple) позволяет не создавать новые индексные записи, когда одновременно выполнены два условия:

Параметр fillfactor оставляет часть страницы свободной для будущих обновлений. Это может увеличить долю HOT, но увеличивает начальный размер таблицы. Эффект измеряют по n_tup_hot_upd, чтениям с диска и разрастанию таблицы, а не назначают одинаково всем объектам.

Для больших строк, JSONB и других переменных значений PostgreSQL использует механизм TOAST. Сервер пытается сжать значение и при необходимости переносит его части в связанную служебную таблицу.

Нельзя считать, что любое значение больше определённой цифры обязательно вынесено: решение зависит от размера всей строки и настройки хранения. SELECT * может незаметно потребовать чтения и распаковки большого содержимого, хотя приложению нужен только id.

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

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

Карта видимости (visibility map) отмечает страницы, все строки которых видимы всем текущим транзакциям. Благодаря этой отметке Index Only Scan может получить данные из индекса и не проверять таблицу для каждой найденной строки.

WAL — журнал предварительной записи. Сначала PostgreSQL надёжно записывает в журнал описание изменения, а изменённая страница таблицы может попасть в файл позже. После сбоя журнал позволяет повторить недостающие изменения.

Изменённую в памяти, но ещё не записанную страницу называют грязной (dirty page). Контрольная точка (checkpoint) постепенно записывает такие страницы и отмечает место, от которого нужно начинать восстановление.

Слишком частые контрольные точки создают много ввода-вывода и полных образов страниц в WAL. Слишком редкие увеличивают объём журнала и время восстановления. Настройку оценивают по pg_stat_wal, pg_stat_bgwriter, сообщениям о контрольных точках и задержке хранилища.

В лаборатории 19 вы сравните HOT и обычное обновление, увидите карту видимости и различие между размером файла и местом, которое PostgreSQL уже может использовать повторно.

Подробнее: физическое хранение, журнал WAL.