Привет, Хабр! Меня зовут Александр Гришин, я руководитель по развитию продуктов хранения данных в Selectel. Сегодня я предлагаю продолжить разбираться с PostgreSQL и заглянуть еще глубже в эту кроличью нору. Посмотрим, что происходит под капотом СУБД во время записи строк, поверхностно разберем, как устроены страницы памяти, что такое tuple, tuple chain, fillfactor, VM и FSM. Эти знания помогут разработчикам не только понимать, как работает база данных, но и эффективно управлять ее производительностью в продакшене.

Если вы начинающий DBA, разработчик, инженер или архитектор облачной инфраструктуры, эта статья для вас. Погнали!

В серии статей о PostgreSQL я уже рассказал:

• как оптимально подобрать аппаратную конфигурацию под ожидаемый профиль нагрузки,

• как оптимизировать работу с PostgreSQL, чтобы разработчик не лишился сна,

• что умеет и где может пригодиться инженерам pgvector,

• как перестать раздувать таблицы в PostgreSQL.

Почему важно знать, как PostgreSQL хранит данные

Представьте типичную ситуацию: вы обновили 10 миллионов строк в таблице, ожидая, что база останется примерно того же размера. Но на диске внезапно прибавилось несколько гигабайт. Казалось бы, баг? Нет, это ожидаемое поведение продукта и естественный результат внутренней архитектуры PostgreSQL, построенной на MVCC и механизме цепочек tuple.

В PostgreSQL данные таблицы физически хранятся в страницах — это базовые единицы хранения. Каждая страница содержит одну или несколько строк физических записей с данными колонок и служебной информацией для управления версиями. При UPDATE старая версия строки не перезаписывается — она сохраняется и к ней добавляется новая. Старая строка превращается в мертвую (dead tuple), которая продолжает занимать место на странице, пока ее не удалит VACUUM.

Такой механизм позволяет реализовать изолированность транзакций: каждая транзакция видит свой «снимок» данных на момент начала работы. Благодаря этому несколько транзакций могут одновременно читать и писать одни и те же строки без конфликтов: одна транзакция видит старую строку до фиксации другой, а новая строка становится доступной только после коммита. Такой простой и элегантный механизм обеспечивает согласованность данных и позволяет PostgreSQL обходиться без блокировок на чтение в большинстве сценариев.

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

Страницы, фундамент хранения

На диске PostgreSQL работает со страницами — это базовые блоки данных размером 8 Кб, из которых состоят все таблицы и индексы. Любая таблица в файловой системе PostgreSQL — это просто множество таких страниц, размещенных подряд.

Когда вы выполняете SELECT, INSERT или UPDATE, база не читает отдельные строки, она загружает целые страницы в буфер, изменяет их в памяти и только потом сбрасывает обратно на диск. Поэтому производительность ввода-вывода, кэш и даже работа VACUUM завязаны именно на производительность на уровне страниц.

Heap pages и index pages

Heap pages — это неупорядоченные страницы с наборами строк. Внутри такой страницы находится заголовок, служебная таблица смещений (line pointer array) и область данных, где хранятся сами строки. Свободное пространство между ними используется для вставки новых строк или обновленных версий.

Отдельно существуют index pages, которые принадлежат индексам. Их структура зависит от типа индекса (например, B-Tree или GIN), но принцип примерно один: каждая страница содержит ключи и ссылки (ctid) на tuple в heap. При поиске PostgreSQL сначала идет по дереву индекса, а затем по ctid извлекает нужную строку из heap-страницы.

Простой способ убедиться, что таблица действительно состоит из страниц:

SELECT pg_relation_size('users') / 8192 AS pages_count;

Промежуточный вывод: PostgreSQL работает со страницами. Все операции ввода-вывода, кэширование и очистка памяти происходят на уровне этих 8 КБ блоков.

Облачные базы данных

Создайте готовую базу данных в облаке за 5 минут. Поддерживаем PostgreSQL, MySQL, Redis и не только.

Подробнее →

Tuple, физическая строка

На логическом уровне таблица в PostgreSQL состоит из строк. Но если копнуть глубже, то каждая строка — это tuple. Это даже не строка, а отдельная запись в памяти, включающая не только данные колонок, но и служебную информацию, необходимую для работы многоверсионности (MVCC).

В состав tuple входят:

• данные колонок — содержимое полей таблицы;

• xmin — идентификатор транзакции, которая создала строку;

• xmax — идентификатор транзакции, которая удалила или заменила строку;

• ctid — физический адрес tuple в таблице;

• флаги и длины полей — служебная информация для чтения и навигации по странице.

Посмотреть эту информацию для условной таблицы users можно напрямую:

SELECT xmin, xmax, ctid, * 
FROM users;

Все строки в ответе имеют одинаковый xmin. Это значит, что все они были созданы одной и той же транзакцией. Обычно так выглядит результат массовой вставки или загрузки данных. xmax равен нулю у всех строк, что говорит о том, что эти строки все еще активны. Если бы хоть одна из них была заменена (UPDATE) или удалена (DELETE), в xmax стоял бы номер транзакции, которая их закрыла, а текущая tuple стала dead tuple.

Так же мы видим, что ctid увеличивается: (30,2), (30,3), (30,4):

• первая цифра — это номер страницы в файле таблицы;

• вторая цифра — смещение внутри страницы (line pointer).

Это означает, что все эти tuple лежат на одной и той же heap-странице 30 и занимают последовательные слоты. Абсолютно нормальная картина, типичная для свежей таблицы без обновлений.

WAL и фиксация изменений

Когда PostgreSQL создает новую версию строки, изменения происходят не напрямую на диске. Сначала они попадают в буферный кеш, а затем в журнал предзаписи, Write-Ahead Log.

WAL — это последовательность записей о том, что именно изменилось: добавлена строка, удален tuple, обновлена страница и т. д. PostgreSQL сначала записывает эти изменения в WAL, а уже потом фиксирует их в основном файле таблицы. Такой порядок гарантирует, что при сбое система сможет восстановить состояние базы, проиграв журнал до последнего успешного COMMIT.

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

SELECT * FROM pg_stat_bgwriter;

Пример вывода: 

pg_stat_bgwriter — одна из тех системных вьюх PostgreSQL, которая помогает понять, как эффективно работает подсистема записи и фоновые процессы (в частности, background writer и checkpointer). Мы не будем разбирать подробно каждый параметр, эта важная тема по объему займет целую главу в книге. Поэтому если действительно нужно, можно обратиться к документации PostgreSQL.

Этот механизм отвечает за durability в модели ACID: если транзакция зафиксирована и COMMIT выполнен, изменения не потеряются даже при внезапном выключении сервера.

Роль tuple в MVCC и изолированности

MVCC (Multi-Version Concurrency Control) — это система, которая позволяет нескольким транзакциям одновременно читать и изменять данные без конфликтов. PostgreSQL не блокирует строки на чтение, а создает новую версию tuple для каждой операции UPDATE или DELETE.

Когда вы выполняете UPDATE, база данных не перезаписывает существующую tuple. Вместо этого:

1. создается новая tuple с новыми значениями и собственным xmin;

2. в старой tuple проставляется xmax — номер текущей транзакции;

3. новая tuple добавляется на страницу (или в свободное место другой страницы).

Таким образом, старая и новая версии строки существуют одновременно. Это и есть реализация изоляции транзакций:

• активные транзакции читают только те tuple, которые были зафиксированы на момент их начала;

• изменения других транзакций становятся видимы только после коммита;

• если транзакция откатывается, ее tuple просто игнорируются.

Демонстрационный пример

Разберем  минимальный воспроизводимый сценарий, который демонстрирует появление новой версии и поведение ctid, xmin, xmax. Используем две сессии SQL: в одной эмулируем чтение, в другой — запись.

Сначала откроем терминал А. Убедимся, что у таблицы есть строка:

SELECT ctid, xmin, xmax, * FROM users WHERE id = 1;

Теперь откроем терминал B. Начинаем транзакцию, обновляем строку в другой сессии, но не делаем коммит:

BEGIN;
UPDATE users SET age = age + 1 WHERE id = 1;

Проверяем строку в терминале B:

Видим новый tuple: ctid = (0,8) и age = 25.

Проверяем строку в терминале А.

Сессия A все еще увидит старую версию (старый ctid), пока мы не завершим транзакцию. Обратите внимание на xmax — оно указывает на значение новой строки.

Обе версии физически присутствуют в файле таблицы до тех пор, пока VACUUM не удалит мертвые tuple и не отметит место доступным.

Теперь делаем COMMIT в терминале B.

Проверяем строку в терминале А.

Мертвые tuple не исчезают с диска сразу после UPDATE/DELETE. Их уборку выполняет VACUUM (ручной или autovacuum). Я недавно довольно подробно описывал нюансы эксплуатации раздутых таблиц и расширение pg_repack, рекомендую эту статью к прочтению, если вы этого еще не сделали.

Как PostgreSQL выбирает, куда записать новую строку

Когда в таблицу вставляется новая запись, PostgreSQL должен решить, в какую именно страницу ее положить. Для этого он не сканирует всю таблицу подряд, а использует специальные служебные карты.

Free Space Map хранит сведения о свободном пространстве в каждой странице.
При вставке система обращается к FSM, находит страницу, где хватает места под новый tuple, и пишет туда.

Если таких страниц нет, таблица расширяется — добавляется новая страница на 8 КБ.

Visibility Map служит для VACUUM. В ней помечаются страницы, где все tuple видимы (то есть в них нет «мертвых» версий строк). VACUUM может пропускать эти страницы, не тратя время на их проверку.

Благодаря этим структурам PostgreSQL избегает полного сканирования таблицы при вставках и очистке, а операции INSERT и VACUUM работают значительно быстрее даже на больших объемах данных.

Промежуточный вывод: в PostgreSQL каждая операция UPDATE или DELETE — это создание новой версии tuple. Этот механизм делает систему устойчивой к конкуренции транзакций, но со временем порождает мертвые строки, которые необходимо очищать.

Как цепочка версий влияет на производительность 

Как мы разобрали выше, каждое изменение строки добавляет в таблицу новую версию tuple, а старая остается на месте до очистки. Если обновления происходят часто, для одной логической строки может образоваться цепочка версий. Это называется tuple chain — последовательность связанных между собой tuple, каждая из которых указывает на следующую через внутренний указатель.

Когда запрос выполняет чтение, PostgreSQL должен пройти по этой цепочке, чтобы найти актуальную версию строки, подходящую под видимость конкретной транзакции. Чем длиннее цепочка, тем, разумеется, больше страниц приходится читать и тем выше накладные расходы на IO и CPU. При этом старые tuple продолжают занимать место в файле и участвуют в последовательных сканированиях, даже если не видимы пользователю.

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

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

Промежуточный вывод: чем больше версий строк остается в таблице, тем выше издержки на чтение и планирование запросов. Все это отражается в утилизации IO и CPU кластера.

Fillfactor, резерв свободного места 

По умолчанию PostgreSQL старается использовать место на странице максимально плотно: каждая страница заполняется tuple до предела, пока не останется буквально несколько байт свободного пространства для служебных нужд. Это логично для статичных данных таблиц, где строки редко меняются. Но как мы уже поняли, при частых UPDATE или DELETE такая плотность становится проблемой.

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

Чтобы этим управлять, существует параметр fillfactor — коэффициент заполнения страниц. Он задает, какую долю страницы PostgreSQL может использовать под данные, оставляя остальное как резерв под будущие обновления.

Например, если fillfactor = 80, то при вставке новых строк PostgreSQL будет заполнять страницу только на 80%, оставляя примерно 20% свободного места. Когда позже произойдут обновления, новые версии tuple смогут разместиться рядом со старыми на той же странице, без перемещения и без увеличения числа страниц таблицы.

Посмотреть или изменить fillfactor можно напрямую:

-- Изменить коэффициент заполнения
ALTER TABLE users SET (fillfactor = 80);
-- Узнать текущие настройки
SELECT relname, reloptions FROM pg_class WHERE relname = 'users';
-- Применить изменения физически
VACUUM FULL users;

После VACUUM FULL таблица будет перестроена с новым уровнем заполнения страниц. Теперь у каждой страницы останется небольшой запас свободного пространства, который PostgreSQL сможет использовать для новых версий tuple при UPDATE.

Fillfactor для индексов

Аналогично таблицам, индексы тоже имеют свой fillfactor. Для B-tree по умолчанию он равен 90, чтобы уменьшить вероятность page split ситуации, когда страница индекса переполняется и вынужденно делится на две. Это дорогостоящая операция, сопровождающаяся дополнительными записями и перераспределением ключей.

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

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

Заключение

Если смотреть на PostgreSQL не как на черный ящик, а как на инженерную систему, становится понятно, почему она ведет себя именно так, как ведет. Каждая операция INSERT, UPDATE или DELETE — это физическая работа программы с 8-килобайтными страницами на дисках, размещением tuple и регистрацией изменений в WAL. Каждое обновление создает новую версию строки, а старая превращается в мертвую и ждет очистки.

Сегодня мы увидели, что производительность зависит не только от индексов и запросов, но и от того, как страничный уровень хранения взаимодействует с буфером, FSM и Visibility Map. А еще — как параметры вроде fillfactor помогают управлять поведением системы, и почему цепочки tuple напрямую влияют на реальную скорость чтения.

Понимание этих механизмов расширяет взгляд на работу с PostgreSQL, а тонкие настройки превращаются в инструменты для поддержания кластера в готовности к любой нагрузке.

Как обычно, пишите в комментариях, что бы вы хотели узнать о PostgreSQL.