NetBash.ru

Слои объекта

Каждому объекту БД, хранящему данные ( таблица, индекс, последовательность, представление ), соответствует несколько слоев (forks). Каждый слой содержит определенный вид данных
В начале слой содержит один единственный файл, в имени которого содержится числовой идентификатор, к которому может быть добавлено окончание, в соответствии с именем слоя
Когда размер такого файла доходит до 1 Гб, создается следующий файл этого же слоя. Такие файлы иногда называются сегментами. Порядковый номер сегмента добавляется в конец имени файла. Общий размер любого слоя показывает функция pg_relation_size
Таким образом одному объекту базы данных может соответствовать несколько файлов
Все файлы объектов, принадлежащих одному табличному пространству и одной БД, будут помещены в один каталог
Это необходимо учитывать, потому что файловые системы могут не очень работать с большим кол-вом файлов в каталоге

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

---

Слой инициализаци, используется при сбое, только для нежурналируемых таблиц,файлы оканчиваются на «_init», в случае сбоя их содержимое невозможно восстановить
При восстановлении PostgreSQL просто удаляет все слои таких объектов и записывает слой инициализации на место основного слоя. В результате получается пустая таблица
https://postgrespro.ru/docs/postgresql/16/storage-init

---

Карта видимости (vm) - битовая карта видимости, существует только для таблиц, Имена файлов этого слоя оканчиваются на «_vm»
Слой существует только для таблиц; для индексов не поддерживается отдельная версионность
https://postgrespro.ru/docs/postgresql/16/storage-vm

---

Карта свободного пространства - карта свободного пространства. Имена файлов этого слоя оканчиваются на «_fsm». Этот слой существует и для таблиц, и для индексов
https://postgrespro.ru/docs/postgresql/16/storage-fsm

---

Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_11_data_lowlevel.html

---

Расположение файлов

postgres=# CREATE DATABASE data_lowlevel; CREATE DATABASE postgres=# postgres=# \c data_lowlevel You are now connected to database "data_lowlevel" as user "postgres". data_lowlevel=# data_lowlevel=# --Создадим таблицу и посмотрим на файлы, которые ей принадлежат data_lowlevel=# data_lowlevel=# CREATE TABLE t( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, n numeric ); CREATE TABLE data_lowlevel=# data_lowlevel=# INSERT INTO t(n) SELECT id FROM generate_series(1,10000) AS id; INSERT 0 10000 data_lowlevel=# data_lowlevel=# VACUUM t; VACUUM data_lowlevel=# data_lowlevel=# --Путь до основного файла относительно PGDATA можно получить функцией: data_lowlevel=# SELECT pg_relation_filepath('t'); pg_relation_filepath ---------------------- base/16498/16500 (1 row) data_lowlevel=#

Поскольку таблица находится в табличном пространстве pg_default, путь начинается с base
Затем идет имя каталога для базы данных

data_lowlevel=# data_lowlevel=# SELECT oid FROM pg_database WHERE datname = 'data_lowlevel'; oid ------- 16498 (1 row)

Затем — собственно имя файла. Его можно узнать следующим образом:

data_lowlevel=# data_lowlevel=# SELECT relfilenode FROM pg_class WHERE relname = 't'; relfilenode ------------- 16500 (1 row) data_lowlevel=#

Тем и удобна функция pg_relation_filepath, что выдает готовый путь без необходимости выполнять несколько запросов к системному каталогу Посмотрим на файлы. Доступ к каталогу PGDATA имеет только пользователь ОС postgres, поэтому команда ls выдается от его имени или от имени root Мы видим три слоя: основной слой, карту свободного пространства (fsm) и карту видимости (vm)

ls -la /var/lib/postgresql/14/main/base/16498/16500* -rw------- 1 postgres postgres 450560 ноя 5 00:57 /var/lib/postgresql/14/main/base/16498/16500 -rw------- 1 postgres postgres 24576 ноя 5 00:57 /var/lib/postgresql/14/main/base/16498/16500_fsm -rw------- 1 postgres postgres 8192 ноя 5 00:57 /var/lib/postgresql/14/main/base/16498/16500_vm

Аналогично можно посмотреть и на файлы индекса:

data_lowlevel=# \d t Table "public.t" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------ id | integer | | not null | generated always as identity n | numeric | | | Indexes: "t_pkey" PRIMARY KEY, btree (id) data_lowlevel=# data_lowlevel=# data_lowlevel=# SELECT pg_relation_filepath('t_pkey'); pg_relation_filepath ---------------------- base/16498/16505 (1 row) data_lowlevel=# # ls -la /var/lib/postgresql/14/main/base/16498/16505* -rw------- 1 postgres postgres 245760 ноя 5 00:57 /var/lib/postgresql/14/main/base/16498/16505

И на файлы последовательности, созданной для первичного ключа:

SELECT pg_relation_filepath(pg_get_serial_sequence('t','id')); pg_relation_filepath ---------------------- base/16498/16499 (1 row)

Временные таблицы харнятся также как и постоянные

postgres=# \c data_lowlevel You are now connected to database "data_lowlevel" as user "postgres". data_lowlevel=# data_lowlevel=# CREATE TEMP TABLE temp AS SELECT * FROM t; SELECT 10000 data_lowlevel=# data_lowlevel=# VACUUM temp; VACUUM data_lowlevel=# data_lowlevel=# SELECT pg_relation_filepath('temp'); pg_relation_filepath ---------------------- base/16498/t4_16509 (1 row) data_lowlevel=#

oid2name

Существует полезное расширение oid2name, входящее в стандартную поставку, с помощью которого можно легко связать объекты БД и файлы

--посмотреть все базы данных: /usr/lib/postgresql/16/bin/oid2name --посмотреть все объекты в базе: /usr/lib/postgresql/16/bin/oid2name -d data_lowlevel --Или все табличные пространства в базе: /usr/lib/postgresql/16/bin/oid2name -d data_lowlevel -s --Можно по имени таблицы узнать имя файла: /usr/lib/postgresql/16/bin/oid2name -d data_lowlevel -t t --Или наоборот, по номеру файла узнать таблицу: /usr/lib/postgresql/16/bin/oid2name -d data_lowlevel -f 16388

Размер слоев

Размер файлов, входящих в слой, можно, конечно, посмотреть в файловой системе, но существует специальная функция для получения размера каждого слоя в отдельности:

data_lowlevel=# SELECT pg_relation_size('t','main') main, pg_relation_size('t','fsm') fsm, pg_relation_size('t','vm') vm; main | fsm | vm --------+-------+------ 450560 | 24576 | 8192 (1 row)

TOAST

TOAST - The Oversized-Attribute Storage Technique, Методика хранения сверхбольших атрибутов
Фиксированный размер страницы составляет 8 Кб. Postgres не позволяет занимать кортежам несколько страниц сразу.
Большие значения сжимаются или разбиваются на несколько физических строк
TOAST применяется для оптимизации обработки больших значений данных в памяти.
TOAST поддерживает определенные типы данных, которые поддерживают значения переменной длины (varlena), в котором первое четырёхбайтовое слово любого хранящегося значения содержит общую длину значения в байтах (включая само это слово)
Содержание оставшейся части значения TOAST не ограничивает
Специальные представления, называемые значениями в формате TOAST, манипулируют этим начальным словом длины и интерпретируя его по-своему
Метод, который будет применяться для сжатия данных при внутреннем и внешнем хранении, можно выбрать для каждого отдельного столбца, задав параметр COMPRESSION в команде CREATE TABLE или ALTER TABLE. Если метод сжатия для столбца не задан явным образом, по умолчанию при вставке данных будет использоваться метод из параметра default_toast_compression
Существуют разные варианты использования указателя TOAST, самый старый и наиболее популярный вариант — когда он указывает на отделённые данные, размещённые в TOAST-таблице, которая отделена, но связана с таблицей, содержащей собственно указатель данных TOAST
Такой указатель на данные на диске создаётся кодом обработки TOAST, когда кортеж, сохраняемый на диск, оказывается слишком большим
Указатель TOAST может указывать на отделённые данные, размещённые где-то в памяти. Такие данные обязательно недолговременные и никогда не оказываются на диске, но этот механизм очень полезен для исключения копирования и избыточной обработки данных большого размера
Источник: https://postgrespro.ru/docs/postgresql/16/storage-toast

---

Размещение TOAST на диске

Если какие-либо столбцы таблицы хранятся в формате TOAST, у таблицы будет связанная с ней таблица TOAST, OID которой хранится в значении pg_class.reltoastrelid
Размещаемые на диске TOAST-значения содержатся в таблице TOAST
Отделённые значения делятся на порции (после сжатия, если оно применяется) размером не более TOAST_MAX_CHUNK_SIZE байт
Каждая порция хранится как отдельная строка в таблице TOAST, принадлежащей исходной таблице-владельцу
Каждая таблица TOAST имеет столбцы chunk_id (OID, идентифицирующий конкретное TOAST-значение), chunk_seq (последовательный номер для порции внутри значения) и chunk_data (фактические данные порции)
Уникальный индекс по chunk_id и chunk_seq обеспечивает быструю выдачу значений
Таким образом, в указателе, представляющем отдельно размещаемое на диске значение TOAST, должно храниться OID таблицы TOAST, к которой нужно обращаться, и OID определённого значения (его chunk_id)
Для удобства в данных указателя также хранится логический размер элемента данных (исходных данных без сжатия), фактический размер хранимых данных (отличающийся, если было применено сжатие) и используемый метод сжатия, если он задан
Учитывая байты заголовка varlena, общий размер указателя на хранимое на диске значение TOAST составляет 18 байт
Код обработки TOAST срабатывает, только когда значение строки, которое должно храниться в таблице, по размеру больше, чем TOAST_TUPLE_THRESHOLD байт (обычно это 2 Кб).
Код TOAST будет сжимать и/или выносить значения поля за пределы таблицы до тех пор, пока значение строки не станет меньше TOAST_TUPLE_TARGET байт (переменная величина, так же обычно 2 Кб) или уменьшить объём станет невозможно

---

Код обработки TOAST распознаёт четыре различные стратегии хранения столбцов, совместимых с TOAST, на диске:
PLAIN не допускает ни сжатия, ни отдельного хранения ( несовместимы с TOAST )
EXTENDED допускает как сжатие, так и отдельное хранение. Это стандартный вариант для большинства типов данных, совместимых с TOAST. Сначала происходит попытка выполнить сжатие, затем — сохранение вне таблицы, если строка всё ещё слишком велика
EXTERNAL допускает отдельное хранение, но не сжатие. Использование EXTERNAL ускорит операции над частями строк в больших столбцах text и bytea (ценой увеличения объёма памяти для хранения), так как эти операции оптимизированы для извлечения только требуемых частей отделённого значения, когда оно не сжато.
MAIN допускает сжатие, но не отдельное хранение. (Фактически для таких столбцов будет тем не менее применяться отдельное хранение, но лишь как крайняя мера, когда нет другого способа уменьшить строку так, чтобы она помещалась на странице.)

---

Каждый тип данных, совместимый с TOAST, определяет стандартную стратегию для столбцов этого типа данных, но стратегия для заданного столбца таблицы может быть изменена с помощью ALTER TABLE ... SET STORAGE.
TOAST_TUPLE_TARGET можно задавать на уровне таблиц с помощью команды ALTER TABLE ... SET (toast_tuple_target = N)
Большие значения атрибутов в формате TOAST будут просто передаваться (если будут выбраны) в тот момент, когда результирующий набор отправляется клиенту.
Таким образом, главная таблица получается гораздо меньше, и в общий кеш буферов помещается больше её строк, чем их было бы без использования отдельного хранения
Наборы данных для сортировок также уменьшаются, а сортировки чаще будут выполняться исключительно в памяти.

Размещение TOAST в памяти

Указатели TOAST могут указывать на данные, размещённые не на диске, а где-либо в памяти текущего серверного процесса
Такие указатели не могут быть долговременными, но они тем не менее полезны
Поддерживаются два подварианта: косвенные указатели на данные и указатели на развёрнутые данные
Косвенный указатель TOAST просто указывает на значение varlena, хранящееся где-то в памяти
Указатели на развёрнутые данные TOAST полезны для сложных типов, представление которых на диске плохо приспособлено для вычислительных целей

Любая версия строки в PostgreSQL должна целиком помещаться на одну страницу. Для «длинных» версий строк применяется технология TOAST — The Oversized Attributes Storage Technique. Она подразумевает несколько стратегий работы с «длинными» полями. Значение поля может быть сжато так, чтобы версия строки поместилась на страницу. Значение может быть убрано из версии и перемещенов отдельную служебную таблицу. Могут применяться и оба подхода: какие-то значения будут сжаты, какие-то — перемещены, какие-то — сжаты и перемещены одновременно.
Для каждой основной таблицы при необходимости создается отдельная toast-таблица (и к ней специальный индекс). Такие таблицы и индексы располагаются в отдельной схеме pg_toast и поэтому обычно не видны
Версии строк в toast-таблице тоже должны помещаться на одну страницу, поэтому длинные значения хранятся порезанными на фрагменты. Из этих фрагментов PostgreSQL прозрачно для приложения «склеивает» необходимое значение.
Toast-таблица используется только при обращении к длинному значению. Кроме того, для toast-таблицы поддерживается своя версионность: если обновление данных не затрагивает «длинное» значение, новая версия строки будет ссылаться на то же самое значение в toast-таблице — это экономит место.
https://postgrespro.ru/docs/postgresql/16/storage-toast

---

Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_11_data_lowlevel.html

---

data_lowlevel=# --В таблице t есть столбец типа numeric, который может работать с очень большими числами data_lowlevel=# \d t Table "public.t" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------ id | integer | | not null | generated always as identity n | numeric | | | Indexes: "t_pkey" PRIMARY KEY, btree (id) data_lowlevel=# data_lowlevel=# SELECT length( (123456789::numeric ^ 12345::numeric)::text ); length -------- 99907 (1 row) data_lowlevel=# data_lowlevel=# data_lowlevel=# -- Если вставить такое значение в таблицу, размер файлов не изменится data_lowlevel=# SELECT pg_relation_size('t','main'); pg_relation_size ------------------ 450560 (1 row) data_lowlevel=# data_lowlevel=# INSERT INTO t(n) SELECT 123456789::numeric ^ 12345::numeric; INSERT 0 1 data_lowlevel=# data_lowlevel=# SELECT pg_relation_size('t','main'); pg_relation_size ------------------ 450560 (1 row) data_lowlevel=# data_lowlevel=# data_lowlevel=# --Имя и идентификатор такой таблицы можно найти следующим образом: data_lowlevel=# SELECT relname, relfilenode FROM pg_class WHERE oid = ( SELECT reltoastrelid FROM pg_class WHERE oid = 't'::regclass ); relname | relfilenode ----------------+------------- pg_toast_16500 | 16503 (1 row) data_lowlevel=# data_lowlevel=# --Название стратегии показывается в поле Storage: data_lowlevel=# \d+ t Table "public.t" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+---------+-----------+----------+------------------------------+---------+-------------+--------------+------------- id | integer | | not null | generated always as identity | plain | | | n | numeric | | | | main | | | Indexes: "t_pkey" PRIMARY KEY, btree (id) Access method: heap data_lowlevel=#

plain — TOAST не применяется (тип имеет фиксированную длину);
extended — применяется как сжатие, так и отдельное хранение;
external — сжатие не используется, только отдельное хранение;
main — такие поля обрабатываются в последнюю очередь и выносятся в toast-таблицу, только если сжатия недостаточно

---

При необходимости стратегию можно впоследствии изменить. Например, если известно, что в столбце хранятся уже сжатые данные, разумно поставить стратегию external.
Просто для примера:
=> ALTER TABLE t ALTER COLUMN n SET STORAGE external;

Найти по названию тоста название связанной таблицы

data_lowlevel=# --Найти по названию ТОСТА название таблицы: data_lowlevel=# SELECT relname FROM pg_class WHERE reltoastrelid = ( SELECT oid FROM pg_class WHERE relname = 'pg_toast_16500' ); relname --------- t (1 row)

Найти по названию таблицы название ее тоста

data_lowlevel=# --Найти по названию таблицы название ТОСТА: data_lowlevel=# select oid::regclass AS heap_rel, pg_size_pretty(pg_relation_size(oid)) AS heap_rel_size, reltoastrelid::regclass AS toast_rel, pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast_rel_size FROM pg_class WHERE relname = 't' ; heap_rel | heap_rel_size | toast_rel | toast_rel_size ----------+---------------+-------------------------+---------------- t | 440 kB | pg_toast.pg_toast_16500 | 56 kB (1 row) data_lowlevel=#

Определение использования диска

Для каждой таблицы создается первичный дисковый файл кучи (heap), в которой хранится большая часть данных. Если в таблице есть столбцы с потенциально большими значениями, то с этой таблицей может ассоциироваться файл TOAST, который используется для хранения слишком больших значений.
На каждую таблицу TOAST будет существовать один индекс. Каждая таблица и индекс хранятся в отдельном дисковом файле или в нескольких файлах(если размер привышает 1 Гб)
Осуществлять мониторинг дискового простанства можно тремя способами: используя SQL-функции, используя модуль oid2name, просматривая системные каталоги вручную

--Посмотреть сколько дискового пространства использует таблица SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'customer';

relpages обновляется только командами VACUUM, ANALYZE, и несколькими командами DDL, такими как CREATE INDEX

--посмотреть пространство таблицы customer, используемое таблицами TOAST SELECT relname, relpages FROM pg_class, (SELECT reltoastrelid FROM pg_class WHERE relname = 'customer') AS ss WHERE oid = ss.reltoastrelid OR oid = (SELECT indexrelid FROM pg_index WHERE indrelid = ss.reltoastrelid) ORDER BY relname; -- посмотреть размеры индексов SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = 'customer' AND c.oid = i.indrelid AND c2.oid = i.indexrelid ORDER BY c2.relname; --самые большие таблицы и индексы SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Размер таблицы

pg_table_size показывает размер таблицы и ее toast-части. Несмотря на название, функция принимает любое отношение
pg_indexes_size суммирует размеры всех индексов таблицы, кроме индекса toast-таблицы
pg_total_relation_size показывает полный размер таблицы, вместесо всеми ее индексами
Для получения размера отдельного индекса можно воспользоваться функцией pg_table_size. Toast-части у индексов нет, поэтому функция покажет только размер всех слоев индекса (main, fsm)

---

postgres=# postgres=# \c data_lowlevel You are now connected to database "data_lowlevel" as user "postgres". data_lowlevel=# data_lowlevel=# data_lowlevel=# --Размер таблицы data_lowlevel=# SELECT pg_table_size('t'); pg_table_size --------------- 581632 (1 row) data_lowlevel=# data_lowlevel=# --Общий размер всех индексов таблицы: data_lowlevel=# SELECT pg_indexes_size('t'); pg_indexes_size ----------------- 245760 (1 row) data_lowlevel=# --Размер индекса совпадает со значением pg_indexes_size, так как у таблицы есть только индекс по первичному ключу data_lowlevel=# SELECT pg_table_size('t_pkey') AS t_pkey; t_pkey -------- 245760 (1 row) data_lowlevel=# data_lowlevel=# --Общий размер таблицы, включающий TOAST и все индексы: data_lowlevel=# SELECT pg_total_relation_size('t'); pg_total_relation_size ------------------------ 827392 (1 row) data_lowlevel=#

Найти по названию таблицы toast.toast.... название родительской таблицы

SELECT relname FROM pg_class WHERE reltoastrelid = ( SELECT oid FROM pg_class WHERE relname = 'pg_toast_209964173' );

Найти по названию родительской таблицы название таблицы toast.toast...

select oid::regclass AS heap_rel, pg_size_pretty(pg_relation_size(oid)) AS heap_rel_size, reltoastrelid::regclass AS toast_rel, pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast_rel_size FROM pg_class WHERE relname = 'events_queue_listener_event' ;

---

Источники