PostgreSQL 16. Организация данных. Часть 1

• > PostgreSQL 16. Организация данных. Часть 1

• PostgreSQL 16. Изоляция транзакций. Часть 2

• PostgreSQL 16. Страницы и версии строк. Часть 3

• PostgreSQL 16. Снимки данных. Часть 4

Введение

Идея написания статьи возникла во время чтения книги «PostgreSQL 16 изнутри». В ней рассматривается внутреннее устройство СУБД PostgreSQL со ссылками на документацию, в которой все еще более подробно, однако слишком сложно для восприятия.

Данная статья является объединением книги и официальной документации с моими рисунками, объясняющими написанное в более наглядном (надеюсь простом) варианте.

Информация взята из книги Егора Рогова PostgreSQL 16 изнутри, а также из документации PostgreSQL 16.2.

Кластер

Каждый работающий экземпляр сервера PostgreSQL обслуживает одну или несколько баз данных. Поэтому базы данных представляют собой вершину иерархии SQL-объектов («объектов базы данных»). ссылка на документацию

Каталог, в котором размещаются все файлы, относящиеся к кластеру, обычно называют словом PGDATA, по имени переменной окружения, указывающей на этот каталог.

При инициализации в PGDATA создаются три одинаковые базы данных (рис. 1.1):

• template0 используется, например, для восстановления из логической резервной копии или для создания базы в другой кодировке и никогда недолжна меняться;

• template1 служит шаблоном для всех остальных баз данных, которые может создать пользователь в этом кластере;

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

Рис. 1.1 Базы данных имеющиеся по умолчанию после инициализации кластера.

Поскольку для выполнения команды CREATE DATABASE необходимо подключение к серверу базы данных, возникает вопрос как создать самую первую базу данных. Первая база данных всегда создаётся командой initdb при инициализации пространства хранения данных (см. документацию.) Эта база данных называется postgres. Далее для создания первой «обычной» базы данных можно подключиться к postgres.

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

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

Шаблоны баз данных

Ссылка на документацию

По факту команда CREATE DATABASE выполняет копирование существующей базы данных. По умолчанию копируется стандартная системная база template1.

CREATE DATABASE users;

Рис. 1.2. Создание новой базы данных по умолчанию.

Однако команда CREATE DATABASE не копирует права GRANT уровня базы из исходной БД. Новая база получает права уровня базы по умолчанию.

Также существует вторая системная база template0. При инициализации она содержит те же самые объекты, что и template1, предопределённые в рамках устанавливаемой версии PostgreSQL. В template0 не следует вносить никакие изменения после инициализации кластера.

Рис. 1.3. Создание новой базы данных на основе шаблона.

CREATE DATABASE users TEMPLATE template0;

Если в команде CREATE DATABASE указать в качестве шаблона template0 вместо template1, вы сможете получить «чистую» пользовательскую базу данных (в которой никаких пользовательских объектов нет, есть только системные объекты в первозданном виде), не содержащую ничего, что могло быть добавлено на месте в template1 (Рис. 1.3).

Можно создавать дополнительные шаблоны баз данных, и, более того, можно копировать любую базу данных кластера, если указать её имя в качестве шаблона в команде CREATE DATABASE (Рис. 1.4).

CREATE DATABASE new TEMPLATE users;

Рис. 1.4. Создание новой базы данных на основе прошлой базы данных.

Системный каталог

Ссылка на документацию

Системные каталоги — это место, где система управления реляционной базой данных хранит метаданные схемы, в частности информацию о таблицах и столбцах, а также служебные сведения. Системные каталоги PostgreSQL представляют собой обычные таблицы. Поэтому вы можете удалить и пересоздать их, добавить столбцы, изменить и добавить строки, т. е. разными способами вмешаться в работу системы. Обычно модифицировать системные каталоги вручную не следует, для всего этого, как правило, есть команды SQL. (Например, CREATE DATABASE вставляет строку в каталог pg_database — и фактически создаёт базу данных на диске.)

Рис. 1.5. Системные каталоги имеются у каждой базы данных.

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

К системному каталогу можно обращаться с помощью обычных запросов SQL, а изменения в нем происходят при выполнении команд DDL. Клиентp sql располагает целым рядом специальных команд для просмотра системного каталога.

Все имена таблиц системного каталога имеют префикс pg_, например pg_database. Во всех таблицах системного каталога столбец с первичным ключом называется oid и имеет одноименный тип oid (object identifier) — целое 32-битное число.

pg_database

Рассмотрим один из каталогов подробнее.

Рис. 1.6. Положение pg_database в кластере.

В отличие от большинства системных каталогов, pg_database разделяется всеми базами данных кластера: есть только один экземпляр pg_database в кластере, а не отдельные в каждой базе данных.

В каталоге pg_database хранится информация о доступных базах данных. Ссылка на документацию

SELECT * FROM pg_database;

Рис. 1.7. Вывод данных pg_database.

Рассмотрим некоторые поля:

• oid — идентификатор строки;

• datname  — имя базы данных;

• datdba (ссылается на pg_authid.oid) — владелец базы данных, обычно пользователь, создавший её.

Схемы

Ссылка на документацию

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

Рис. 1.8. Организация структуры схемы public.

База данных содержит одну или несколько именованных схем.

Одно и то же имя объекта можно свободно использовать в разных схемах, например и schema1, и public могут содержать таблицы с именем t1. Точно так же как и разные базы данных могут использовать одни и те же названия схем.

Рис. 1.9. Структура таблиц в схемах и базах данных

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

До этого мы создавали таблицы, не указывая никакие имена схем.

По умолчанию таблицы (и другие объекты) автоматически помещаются в схему «public».

Она содержится во всех создаваемых базах данных. Таким образом, команда:

CREATE TABLE products (...);

эквивалентна

CREATE TABLE public.products (...);

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

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

Чтобы узнать текущий тип поиска, выполните следующую команду:

SHOW search_path;

В конфигурации по умолчанию она возвращает:

 search_path
--------------
 "$user", public

Стоит отметить, что всегда просматривается схема временных таблиц текущего сеанса, pg_temp_nnn, если такая имеется. В случае отсутствия таблицы в пути, она будет просматриваться первой (даже перед pg_catalog, ссылка на документацию).

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

В итоге получаем следующее:

1. Временная таблица (если не указана явно на другой позиции).

2. Схема pg_catalog (если не указана явно на другой позиции).

3. "$user" ссылается на схему с именем текущего пользователя (вместо него подставляется схема с именем, возвращаемым функцией CURRENT_USER, если такая схема существует и пользователь имеет право USAGE для неё). Если такой схемы не существует, ссылка на неё игнорируется.

4. Схема public.

Рассмотрим пример. (Рис. 1.10)

• Имеется база данных db1, в которой созданы 2 дополнительные схемы: igor и anna (public имеется по умолчанию).

• Также созданы 2 пользователя, которые имеют равные и полные права на все схемы в базе db1.

• Каждый пользователь выполняет одни и те же SQL запросы:

  SELECT * FROM t1;
  SELECT * FROM t3;

Рис. 1.10. Поиск пути при неполном указании названия таблиц для разных пользователей.

Так как имеются две разные схемы с названиями пользователей, то первый запрос (из-за того, что таблица указана неполным именем) будет брать данные из таблицы igor.t1 для пользователя igor, а вот для Анны точно такой же запрос обратится в таблицу anna.t1.

Однако второй запрос (из-за отсутствия таблицы t3 в схемах пользователей) будет брать данные из public.t3.

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

Кроме пользовательских схем, имеется несколько специальных служебных:

Рис. 1.11. Служебные схемы по умолчанию.

• public — используется по умолчанию для пользовательских объектов, если не выполнены иные настройки;

• pg_catalog — используется для таблиц системного каталога;

• information_schema — дает альтернативное представление системного каталога, регламентируемое стандартом SQL;

• pg_toast — используется для объектов, относящихся к TOAST;

• pg_temp — объединяет временные таблицы (хотя временные таблицы разных пользователей создаются в разных схемах pg_temp_N, каждый обращается к своим объектам, используя имя pg_temp).

Посмотреть текущие схемы можно в каталоге pg_namespace.

SELECT * FROM pg_namespace;

Рис. 1.12. Список схем базы данных

Схемы существуют внутри базы данных, и все объекты базы принадлежат каким-либо схемам.

Табличные пространства

Ссылка на документацию

В отличие от логического распределения объектов по базам данных и схемам, табличные пространства определяют физическое расположение данных. Фактически табличное пространство — это каталог файловой системы.

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

Рис. 1.13. Табличные пространства по умолчанию

При инициализации кластера создаются два табличных пространства:

• pg_default располагается в каталоге PGDATA/base и используется как табличное пространство по умолчанию, если явно не выбрать для этой цели другое пространство;

• pg_global располагается в каталоге PGDATA/global и хранит общие для всего кластера объекты системного каталога.

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

Для создания табличного пространства используется команда CREATE TABLESPACE, например::

CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';

Таблицы, индексы и целые базы данных могут храниться в отдельных табличных пространствах. Для этого пользователь с правом CREATE на табличное пространство должен указать его имя в качестве параметра соответствующей команды (Рис. 1.14).

Создадим индекс по столбцу в табличном пространстве fastspace:

CREATE INDEX code_idx ON films (code) TABLESPACE fastspace;

Рис. 1.14. Новое табличное пространство на отдельном носителе.

Слои и файлы

Ссылка на документацию

В каталоге PGDATA содержится несколько подкаталогов и управляющих файлов.

Рис. 1.15. Структура каталога PGDATA.

Для каждой базы данных в кластере существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database (Рис. 1.16). Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги.

Рис. 1.16. Хранение баз данных на диске.

⚠️ Внимание

Заметьте, что хотя номер файла таблицы часто совпадает с её OID, так бывает НЕ всегда; некоторые операции, например, TRUNCATE, REINDEX, CLUSTER и некоторые формы команды ALTER TABLE могут изменить номер файла, но при этом сохранят OID. Не следует рассчитывать, что номер файлового узла и OID таблицы совпадают. Кроме того, для некоторых системных каталогов, включая и pg_class, в pg_class.relfilenode содержится ноль. Фактический номер файлового узла для них хранится в низкоуровневой структуре данных, и его можно получить при помощи функции pg_relation_filenode().

Каждая таблица и индекс хранятся в отдельном файле. Для обычных отношений, эти файлы получают имя по номеру файлового узла таблицы или индекса, который содержится в pg_class.relfilenode (Рис. 1.17).

Воспользуемся командой ниже, чтобы посмотреть какие таблицы хранятся на диске в папке базы данных (для этого укажем условие relfilenode > 0):

SELECT oid, relname, relfilenode FROM pg_catalog.pg_class WHERE relfilenode > 0 ORDER BY relfilenode;

Рис. 1.17. Хранение таблиц на диске.

Можно заметить, что для каждой таблицы системного каталога (начинаются на pg_), пользовательских таблиц, а также индексов (начинаются на pk__) имеются свои собственные файлы.

Если relfilenode равно нулю, местоположение данных для этой таблицы определяется другими способами:

1. Системные таблицы (pg_catalog): Некоторые таблицы, могут иметь relfilenode равное 0. Для таких таблиц данные фактически могут храниться в системных каталогах базы данных, а не в отдельных файлах на диске.

2. Временные таблицы: Если таблица создана как временная (TEMPORARY), то её данные могут храниться в специальном сегменте, выделенном для временных данных, а не в постоянных файлах.

3. "Unlogged" таблицы: Таблицы, созданные как «непротоколируемые» (UNLOGGED), могут также иметь relfilenode равное 0. Для них данные хранятся во временном пространстве, и эти таблицы не записывают свои изменения в журнал транзакций.

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

В общем, при relfilenode равном 0, данные таблицы могут храниться в специальных областях памяти или структурах PostgreSQL, а не в стандартных файлах таблиц на диске.

Рис. 1.18. Слои таблицы представлены отдельными файлами.

Помимо главного файла (также называемого основным слоем), у каждой таблицы и индекса есть карта свободного пространства (документация). Но появляется она не сразу, а только при необходимости. Имя файла карты свободного пространства образуется из номера файлового узла с суффиксом _fsm.

Также таблицы имеют карту видимости, хранящуюся в слое с суффиксом _vm, она существует для таблиц, но не для индексов (документация).

Нежурналируемые таблицы и индексы имеют третий слой, так называемый слой инициализации, имя которого содержит суффикс _init (документация).

Когда объём таблицы или индекса превышает 1 GB, они делятся на сегменты размером в один гигабайт (Рис. 1.19). Файл первого сегмента называется по номеру файлового узла (filenode); последующие сегменты получают имена filenode.1, filenode.2 и т. д.

Рис. 1.19. Слои таблицы разделены на сегменты не более 1ГБ.

Табличное пространство делает сценарий более сложным (Рис. 1.20). Каждое пользовательское табличное пространство имеет символическую ссылку внутри каталога PGDATA/pg_tblspc, указывающую на физический каталог табличного пространства (т. е., положение, указанное в команде табличного пространства CREATE TABLESPACE). Эта символьная ссылка получает имя по OID табличного пространства. Внутри физического каталога табличного пространства имеется подкаталог, имя которого зависит от версии сервера PostgreSQL, как например PG_16_202307071. (Этот подкаталог используется для того, чтобы последующие версии базы данных могли свободно использовать одно и то же местоположение, заданное в CREATE TABLESPACE.) Внутри каталога конкретной версии находится подкаталог для каждой базы данных, которая имеет элементы в табличном пространстве, названный по OID базы данных. Таблицы и индексы хранятся внутри этого каталога, используя схему именования файловых узлов.

Табличное пространство pg_default недоступно через pg_tblspc, но соответствует PGDATA/base. Подобным же образом, табличное пространство pg_global недоступно через pg_tblspc, но соответствует PGDATA/global.

Рис. 1.20. Структура хранения файла таблицы в отдельном хранилище.

Функция pg_relation_filepath() показывает полный путь (относительно PGDATA) для любого отношения.

SELECT pg_relation_filepath('db1.public.new1');

 pg_relation_filepath 
--------------------------------------------
pg_tblspc/24581/PG_16_202307071/3321/24585

Слои

Имеется несколько стандартных типов слоев. Для удобства организации ввода-вывода файлы логически поделены на страницы (или блоки) — это минимальный объем данных, который считывается или записывается.

Обычно страница имеет размер 8 Кбайт.

Рис. 1.21. Основные слои таблицы.

1. Основной слой (main fork) — это собственно данные: те самые табличные или индексные строки. Данные разделены на страницы.

2. Слой инициализации (init fork) существует только для нежурналируемых таблиц (созданных с указанием UNLOGGED) и их индексов. Такие объекты ничем не отличаются от обычных, кроме того, что действия с ними не записываются в журнал предзаписи. За счет этого работа с ними происходит быстрее, но в случае сбоя невозможно восстановить данные в согласованном состоянии. Поэтому при восстановлении PostgreSQL просто удаляет все слои таких объектов и записывает слой инициализации на место основного слоя. В результате получается «пустышка».

3. Карта свободного пространства (free space map) — слой, в котором отслеживается примерный объем свободного места внутри страниц. Этот объем постоянно меняется: при добавлении новых версий строк уменьшается, при очистке — увеличивается. Карта свободного пространства представляет собой дерево страниц FSM. Cтраницы FSM нижнего уровня хранят информацию о свободном пространстве, доступном на каждой странице таблицы (или индекса), используя один байт для представления каждой такой страницы. Верхние уровни агрегируют информацию нижних уровней. Подробнее про FSM

4. Карта видимости (visibility map) — слой, который позволяет быстро определить, требует ли страница очистки или заморозки. Для этого на каждую табличную страницу в этом слое отведено два бита. Карта видимости (VM) представляет собой большую битовую маску. Для каждой страницы в куче она хранит два бита. Первый бит называется all-visible, а второй бит — all-frozen. Для индексов VM не строится. VM хранится отдельно от кучи.

TOAST

Ссылка на документацию

TOAST (The Oversized-Attribute Storage Technique, Методика хранения сверхбольших атрибутов).

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

Но что делать, если нужно хранить содержимое статьи размером 2Мб?

Для преодоления этого ограничения большие значения полей сжимаются и/или разбиваются на несколько физических строк.

Лишь определённые типы данных поддерживают TOAST, которые представляют значение переменной длины:

• VARCHAR(n) и CHAR(n) с размером более 2000 байт;

• TEXT;

• BYTEA;

• JSON и JSONB;

• XML.

Подробнее про TOAST

TOAST подразумевает несколько стратегий. Длинные значения атрибутов можно отправить в отдельную служебную таблицу, предварительно нарезав на небольшие фрагменты-«тосты». Другой вариант — сжать длинное значение так, чтобы строка все-таки поместилась на одну страницу. А можно и тои другое: сначала сжать, а уже потом нарезать и отправить.

Код обработки TOAST распознаёт четыре различные стратегии хранения столбцов, совместимых с TOAST, на диске:

• PLAIN не допускает ни сжатия, ни отдельного хранения. Это единственно возможная стратегия для столбцов типов данных, которые несовместимы с TOAST.

• EXTENDED допускает как сжатие, так и отдельное хранение. Это стандартный вариант для большинства типов данных, совместимых с TOAST. Сначала происходит попытка выполнить сжатие, затем — сохранение вне таблицы, если строка всё ещё слишком велика.

• EXTERNAL допускает отдельное хранение, но не сжатие. Использование EXTERNAL ускорит операции над частями строк в больших столбцах text и bytea (ценой увеличения объёма памяти для хранения), так как эти операции оптимизированы для извлечения только требуемых частей отделённого значения, когда оно не сжато.

• MAIN допускает сжатие, но не отдельное хранение. (Фактически для таких столбцов будет тем не менее применяться отдельное хранение, но лишь как крайняя мера, когда нет другого способа уменьшить строку так, чтобы она помещалась на странице.)

Рис. 1.22. Хранение длинных атрибутов в отдельной toast-таблице.

Если какие-либо столбцы таблицы хранятся в формате TOAST, у таблицы будет связанная с ней таблица TOAST, OID которой хранится в значении pg_class.reltoastrelid для данной таблицы (фиолетовая стрелочка на рис. 1.22).

При обращении к атрибуту, который хранится в toast‑таблице PostgreSQL автоматически восстанавливает исходное значение и возвращает его клиенту. Если же такие атрибуты не участвуют в запросе, то toast‑таблица не читается. Это одна из причин не использовать «звездочку» в производственном коде.

Заключение

В данной статье был рассмотрен пункт «1.1. Организация данных» из книги PostgreSQL 16 изнутри «Глава 1».