Меня зовут Якупов Азат, я дата-архитектор Quadcode. Параллельно с работой читаю лекции в Казанском университете и веду курсы по дата-инженерии, дата-архитектуре и дата-аналитике. В индустрии я больше 20 лет, из них больше 6 — в архитектуре. Хочу поделиться багажом опыта, который накопил за это время. 

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

Мы рассмотрим: 

• Heap-таблицы, они же таблицы из песочницы. 

• Метаданные и то, каким образом они располагаются в Postgres.

• Что такое магический Table page и его физическое представление на диске. 

• Параметры Fillfactor, которые влияют на производительность системы.

• OIDS.

• TOAST- таблицы.

Эта статья — немного сокращенный пересказ моего выступления на митапе по теме «Heap Table в PostgreSQL». Если вам комфортнее смотреть видео, чем читать текст, можно сделать это на YouTube. 

Heap-таблицы

Начнем с обычной таблицы. Как она хранится и логически представляется на диске? Многие думают, что это реальная структура, которая хранится в виде набора файлов, набора упорядоченных строк. Но на самом деле есть небольшой рандом в Postgres, который представляет нашу таблицу в порядке, где данные могут храниться на разных пейджах (pages) и разных местах внутри самого пейджа (page). 

В Postgres есть механизм, который называется вакуум (VACUUM). Он перераспределяет данные (вычищая мертвые записи) и привнося небольшой хаос в строки.

Чтобы предотвратить случайный набор данных, есть полезная конструкция ORDER BY. Если вы хотите упорядочить данные, чтобы они не появлялись рандомно, лучше использовать ORDER BY, зная, что вам нужна точная сортировка по каким-то атрибутам и данным. Он поможет, например, если час назад был один слепок без ORDER BY, а сейчас появляется другой слепок, из-за того что какой-то внутренний процесс Postgres запустился как VACUUM и сделал некоторые преобразования в pages.

Обычный синтаксис таблички выглядит так:

Называю табличку как-нибудь просто, например, (t). У таблицы три столбца: A, B, C. Создав таблицу, мы можем обратиться к ее метаданным.

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

OID (object identifier) — это идентификатор объекта, который создается внутри Postgres. Он основывается на системной последовательности. Эта последовательность каждый раз возвращает нечто уникальное для нового объекта, который мы создаем:столбца, функции, триггера, виртуальной таблицы и прочего. Тем самым, у нас есть уникальный идентификатор, по которому мы можем обратиться к объекту. 

На что еще можно обратить внимание в запросе? 

• Здесь есть Tablespace по умолчанию пространство таблиц, он — pg_default для Postgres. 

• Схема (Schema) — паблик (public). 

• Есть также хозяин таблицы (owner) — человек, который создал эту таблицу и ответственен за раздачу доступов к ней. 

• Есть столбцы, отвечающие за количество страниц и таплов (строк) — tuples.

• Есть понятие TOAST-таблиц или таблиц-спутников. Они позволяют функционировать основной таблице и разрешают сплитить (to split) ваши длинные строки, если они не помещаются на основании стратегии или выбранной политики в конкретный пейдж.  

• Есть разные типы (types) таблицы. Мы сейчас рассматриваем обычную (permanent — постоянную) журналируемую таблицу, которая создается на основании команды CREATE TABLE.

• Есть также путь к этой таблице (File Path) — путь, который маппится (to map) на уровне операционной системы в pg_home, где находится ваш инстанс Postgres. 

В строке пути можно посмотреть, что у нас получился файл 16387. Это бинарный файл, который содержит контент вашей таблицы (если вы будете делать заполнение какими нибудь инсертами и потом делать UPDATEs или DELETEs). Заметьте, что этот файл совпадает с OID таблицы. Таким образом мы можем выстроить взаимно однозначное соответствие между тем, как назван файл на диске, с точки зрения его уникального идентификатора. Так как мы никаких данных не вставляли в таблицу, Relation size — 0 байт.

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

В результате у нас есть некоторые изменения, на рисунке 4 они выделены бирюзовым цветом. 

Количество страниц теперь 1, количество записей – 1, и объем отношений резко стал 8 килобайт. С чем это связано? Дело в том, что когда мы вставляем 1 строку, выделяется сразу минимальный атомарный элемент. В рамках хранения Postgres — это пейдж , и он всегда равен 8 килобайтам. Исключение — те случаи, когда вы полностью пересобрали Postgres и увеличили размер пейджа или уменьшили его в степени двойки. 

Каким образом работают Heap-таблицы или даже обычные таблицы другого вида, которые являются журналируемыми на уровне Postgres? Представим, что у нас есть набор табличек: Таблица 1 и Таблица 2 весят меньше гигабайта и Postgres через свои метаданные понимает, к каким файлам относятся эти таблицы через логический слой. Это сильно напоминает архитектуру ANSI-SPARK, когда предполагается, что у нас есть внешний и внутренний концептуальный слой данных работы со структурами. Здесь же есть внутренний слой, на рисунке 5 он выделен зеленым контуром и внешний слой — таблицы. 

Таблица 1 соответствует красному файлу, с таким же именем, как и ее внутренний идентификатор. Таблица 2 соответствует синему квадрату. Но есть нюанс. Если табличка больше гигабайта, то Postgres начинает сплититить (to split) ее на отдельные файлы размером 1 гигабайт. Тем самым появляются суффиксы, указывающие на уровне операционной системы, в каком порядке читать части нашей таблицы). Одна таблица на уровне метаданных соответствует нескольким файлам на уровне операционной системы, а суффикс помогает упорядочить назначение контента. 

Давайте теперь рассмотрим сам пейдж. 

Структура пейджа сильно напоминает используемую в Oracle и в MysQL. Эти структуры одновременно растут сверху вниз и снизу вверх. Когда вы делаете инсерты, рождаются T1, T2, Т3 или tuples, которые записываются снизу налево. Они растут до тех пор, пока не встретятся где-то ближе к началу пейджа со значениями поинтеров (I1, I2, I3). 

Поинтеры — это указатели на конкретные tuples, которые хранятся ниже в файле. Эта структура прошла огонь, воду и медные трубы с точки зрения оптимизации и правильного хранения информации. Она помогает оптимизатору Postgres использовать ее преимущества в плане работы с оффсетами и указателями. 

У пейджа есть header — это метаслой, который хранит интересную структуру, показывающую общее количество места в пейдже. Также в структуре пейджа есть указатели по транзакциям, метаслой для каждого tuple, для каждой строки. Есть special-зона, но она не используется для стандартных таблиц, так как не необходима для индексных структур. Почему? Потому что этот пейдж является атомарным элементом не только для таблиц, но также и для индексов. Одна общая структура подведена под всевозможные хранения. 

У пейджа есть Fillfactor, о нем мы подробнее поговорим дальше. По умолчанию Fillfactor стоит на 100% и означает, что Postgres будет максимально заполнять ваш пейдж до конца. Что значит максимально заполнять? Грубо говоря, пока нижняя зона (lower) не встретится с верхней (upper). Как только они соприкоснутся, будет рожден новый пейдж. Ситуация будет повторяться до тех пор, пока не заполнятся 8 килобайт, потом родятся следующие 8 килобайт и так далее. Так растет табличка. 

Рассмотрим примеры.

Я использовал pgstattuple. Это экстеншен (extension), который позволяет смотреть статистику, не прибегая к массивным селектам о метаданных. Используя API экстеншена, я передаю внутренний идентификатор моей таблички. В итоге получается срез статистики: по длине таблички, по количеству живых записей, мертвых записей — или зомби, как я их называю — и т. д. 

Также я хочу изучить сам пейдж и посмотреть, что находится внутри. 

Здесь я хочу посмотреть соответствует ли пейдж тому, что мы увидим сейчас через pageinspect. Например, изучить header. Передаю имя таблицы (t), ставлю номер пейджа — 0, потому что у нас всего один page. Посмотрим на саму табличку, тут есть интересности. Номер lsn — это уникальный sequence number, который был выделен моему изменению при инсерте. Этот номер используется как и при восстановлении данных, так и при репликации. 

Если рассматривать хедер пейджа, который весит 24 байта, то какие структуры там находятся?

Здесь структуры представлены и размечены побайтно: сколько и что занимает. Хедер четко описан с точки зрения того, какие элементы входят в него, чтобы оптимизатор Postgres мог зайти в header перед тем, как читать весь пейдж , и понять, с чем он вообще имеет дело. И когда вы только начинаете читать, то сможете сразу определить сколько места есть в этом пейдже. 

На рисунке 10 можно посмотреть на наш header, и pointer (I1), который указывает на наш tuple (Т1), который начинается с оффсета 8144 байта. Этот Т1 и содержит строку, которую мы сгенерировали на уровне инсерта. 

Если посмотреть не на метаданные, а на саму строку, которая находится внутри, мы можем увидеть интересные атрибуты относительно не только длины самого тапла (tuple), но также по номеру транзакции, которая была применена. 

Наша строка (T1) весит 42 байта. Как это все хранится? Дело в том, что каждая строка также хранит метаслой, метаданные. Эти 42 байта разделяются на две части. Первая часть — это структура, нарисованная слева на рисунке 11. , Она определяет размер байт для хранения каждого поля этих метаданных. С другой стороны у нас есть tuple-данные (пользовательские данные), и они равны 18 байтам. Получается, у нас 18 из 42 байт — это пользовательских данные. Обратите внимание, что метаданные настолько важны, что отжирают столько места. 

Другой срез метаданных - это CTID — пойнтер (pointer), который содержит адрес страницы плюс адрес tuple (строки) внутри этой страницы. 

Эта структура очень напоминает массив, рекорд (запись), который указывает что эта строка “1 1 string #1” хранится в нулевом пейдже в первом tuple. Если мы добавим сюда OID-отношения, то по сути получим координаты поиска информации. С одной стороны, у нас есть дата-файл, с другой — у нас есть пейдж в этом дата-файле, и, с третьей стороны, у нас есть tuple, куда нужно точечно получить эту информацию из конкретного пейджа. На этом принципе строятся btree-индексы, когда листья в этом btree-индексе указывают на конкретный ctid, указанный в конкретном файле. 

Fillfactor

Переходим к Fillfactor.

Fillfactor — это состояние пейджа хранить информацию только до определенного уровня. Его дефолтное значение в 100% , что означает, что ваш page будет заполнен до конца. В примере на рисунке я установил Fillfactor = 50%. Он означает, что через приведение типа ALTER TABLE  таблички к 50%, новые pages будут рождаться так, что 50% нельзя будет трогать инсертами. То есть когда вы будете инсертить новую строку, то новый tuple не будет пересекать красные границы на рисунке: новый page будет рождаться каждый раз, когда tuples будут достигать тонкой красной линии. 

Fillfactor нужен для будущих UPDATEs таблицы. Дело в том, что когда вы проводите UPDATE, Postgres пытается максимально сохранить его в том же page, где хранилась оригинальная строка, а не мутировать ее куда-нибудь в другой. Для Postgres это хорошая практика. 

Чтобы помочь Postgres и освободить место в рамках page, можно оставить ему пространство для каждого page и сказать: «Давай установим Fillfactor 90%, он будет заполняться на 90%, а 10% останется для будущих возможных UPDATEs». Но нужно понимать, что если это операционные таблицы, которые очень часто апдейтятся и изменяются, тогда да, стоит попробовать с порогом 90% . Если это статичные таблицы типа справочников или dimensions, то стоит сразу выставить Fillfactor на 100%, потому что данные сначала очищаются, а потом перезаполняются. Но если они апдейтятся, то тут надо сразу подумать о правильной выставленной политике для Fillfactor.

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

Но как только я установил на существующей таблице Fillfactor, и хочу пересчитать и исторические пейджи тоже, то надо сделать операцию vacuum full. По сути эта операция сделает копирование данных из одного файла в другой.

Обратите внимание, что VACUUM FULL — операция, довольно критичная для HighLoad, потому что она блокирует read and write трафик для таблицы, пока сама операция не будет закончена. Советую быть с этим осторожными и почитать документацию о том, что VACUUM может привести к блокировкам. Блокировка таблицы в свою очередь вызывает connection refused и connection lost, если таймаут у вас выставлен в достаточно небольшое значение. 

OID

Использовать OID для указаний в табличке — плохо. 

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

Кажется, что ничего страшного здесь нет. Можно сказать: «Почему бы не воспользоваться внутренним системным идентификатором, по сути primary key, первичным ключом, который каждый раз генерируется для каждой строки?» Но есть некоторая проблемка. OID у нас — конкретный тип. И проблема приходит, когда заканчивается системный sequence и счетчику ничего не остается, как начать с нуля. 

Дело в том, что у вас в базе данных уже существуют объекты, начинающиеся с нуля. В таком случае Postgres сколлапсирует, и все свернется и закроется. И тут надо решать: 

1. Каким образом и подходами восстановить эти данные; 

2. каким образом вести с себя с таким кластером. 

OID используется только для генерации номера объекта структуры, но никак не для контента. 

TOAST-таблицы

Посмотрим на TOAST-таблицы или так называемые таблицы-спутники. Смысл в том, что это табличка-спутник, которая помогает вам сплитить (SPLIT) данные с точки зрения длины строк. TOAST-таблица — это обычная Heap-таблица, и она, по сути, наследует в себе те свойства, которые вы указываете на оригинальной таблице. 

Если мы рассмотрим нашу табличку (t), мы увидим слева ссылку на нашу табличку 16559 — это идентификатор таблицы, сохраняющейся в метаслое. 

Если мы перепишем запрос WHERE class.oid = 16559, указав идентификатор, то увидим нашу TOAST-таблицу с именем “pg_toast_16556”. То есть паттерн ее создания — это pg_toast плюс суффикс определяющий OID парент-таблицы. 

Еще стоит обратить внимание, что TOAST-таблица не содержит ссылку на другой TOAST. То есть нет бесконечного числа тостов, есть первый уровень и все. 

Для понимания, что такое TOAST-таблица, проведем тест. Я ставлю размер строки больше 2 килобайт и вставляю мою пользовательскую табличку (t). Я нагенерил здесь номер, эта строка получилась очень длинная. 

Посмотрим, что произойдет. Если мы вернемся и заглянем на page #0 моей оригинальной таблички, мы увидим что page практически пуст. Куда делись данные размером 2 КБ? 

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

 Они разделены на чанки (chunk), и каждый чанк содержит свою часть метаданных. По сути они разделены. Что это значит? Делая запрос “SELECT ctid, * FROM table”, вы заставляете Postgres (если у вас есть длинные строки) обращаться к TOAST-таблице, вычитывать эти чанки, склеивать их для вас, и возвращать эту информацию наружу. Мое личное мнение: если вы делаете SELECT ctid со звездочкой — остерегайтесь звездочки и указывайте, какие столбцы вы хотите. Скорее всего, в большинстве случаев вам не понадобятся длинные строки. Но когда нужны именно длинные строки, то Postgres работает так: он сплитит ваши данные и сохраняет их в отдельной структуре, помогающей функционировать оригинальной таблице. 

Если посмотреть на TOAST-таблицу с точки зрения метаданных, используя pageinspect расширение, то абсолютно та же самая история: есть sequence number, lower- и upper-кейс, оффсеты. То есть все так же, как и в обычной Heap-таблице.

TOAST-стратегии

Дефолтная стратегия Postgres’а — extended. Она говорит о том, что у вас есть TOAS- таблица и вы еще и архивируете данные. Postgres же старается ужать эти данные внутри page. 

Стратегию можно поменять. Кроме extended есть: 

• plain; 

• external; 

• main. 

Они применяются в зависимости от комбинаций параметров, которых вы хотите добиться. Например, нужна компрессия или нет, нужно  хранить в TOAST-таблице или не нужно?

Например, есть табличка (t). Я делаю запрос к метаданным и получаю историю по атрибутам, которые насыщают мою табличку.

Розовым я выделил системные атрибуты, которые маппятся в моей табличке. Зеленым — те, которые я сам создал, пользовательские (мои столбцы a, b, c. Как вы видите в последней колонке таблицы на рисунке, в основном это plain-стратегия именно для типов не из семейства строковых (плюс они должны быть фиксированной длины). 

Атрибут С — это тип VARCHAR, по дефолту у него выстроено extended. Это означает, что стратегия позволяет компрессию и еще хранение в TOAST. Грубо говоря, написав запрос SELECT * вы получаете небольшой негатив, так как вам нужно пойти в TOAST, склеить строки, которые предварительно надо разархивировать.

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

Если вы используете external-стратегию, означающую, что вы можете хранить в TOAST-таблице, но компрессии при этом не будет, то минус тут в том, что будут отжираться диски. Но в современном мире диски стоят дешево, все должно быть быстро, оптимально, и ответы должны получаться в рамках миллисекунд. С external-стратегии можно начать исследование.

Каким образом можно изменить стратегию? Команда обычно: 

• ALTER TABLE (название таблицы)

• ALTER COLUMN (колонка)

• SET STORAGE PLAIN;

TOAST-ные типы — это обычные типы работы со строками, форматы текста, CHAR и VARCHAR. Возможно, вы создадите свои пользовательские типы, которые будут являтся так называемой оберткой от системного типа. 

Выбирая ту или иную стратегию, нужно обязательно ее протестить с помощью бенчмарка, tps-метрики (transaction per second), latency, и только после этого делать выводы о том, переключать стратегию или нет. 

Рекомендации по книгам

Напоследок хочу порекомендовать вам интересные книги с точки зрения того, что находится внутри базы данных. Здесь не только Postgres, но будет полезно:

• “Database Internals” — Alex Petrov.

• “Readings in Database Systems” — Peter Bailis, Joseph M. Hellerstein, Michael Stonebraker.

• “PostgreSQL Notes for Professionals”.

• “Understanding EXPLAIN”.

• “SQL Базовый курс” — Е. П. Моргунов.