Sysadminium
Разберем что хранится в файлах данных. Каждая таблица состоит из нескольких слоёв. Слои данных в PostgreSQL это как минимум 1 файл. Подробнее про физическое хранение базы данных можете почитать тут.
Слои
Каждый файл занимает не больше 1 GB и кратен 8 KB. Поэтому если таблица больше 1 GB, то она хранится в нескольких файлах. Файлы состоят из 8 KB страниц, которые в случае необходимости помещаются в буферный кэш.
Существуют следующие слои:
- Основной слой(main) — сами данные. Этот слой существует у всех объектов;
- Слой инициализации (init) — существует только для нежурналируемых таблиц. Содержит пустую копию таблицы. В случае сбоя PostgreSQL не пытается восстановить нежурналируемую таблицу, а перезаписывает её пустой таблицей из этого слоя. Поэтому после сбоя нежурналируемые таблицы окажутся пустыми.
- Карта свободного пространства (fsm) — хранит информацию о том, где внутри файлов есть свободное пространство.
- Карта видимости (vm) — отмечает страницы, в которых все версии строк видны. Другими словами VACUUM уже их почистил от неактуальных версий строк. Такой слой существует только для таблиц. Он нужен для оптимизации, чтобы VACUUM знал, какие страницы чистить уже не нужно.
Работа с большими строками (TOAST)
В PostgreSQL одна строка должна помещаться в одну страницу, то есть не быть больше 8 КБ. Чтобы поместить большую строку у PostgreSQL есть следующие стратегии:
- сжать большие атрибуты;
- вынести большие атрибуты в отдельную служебную TOAST таблицу;
- можно объединить оба способа.
Механизм работы с большими строками называется — TOAST. Внешняя таблица в которую по кусочкам помещают длинную строку называют TOAST-таблица.
TOAST-таблица имеет собственную версионность. Например, хранится у вас в такой табличке фотография сотрудника. Вы изменяете сотруднику фамилию, появляется новая версия длинной строки, но фотография в новую версию не копируется. Фотография в TOAST табличке остаётся в той-же версии. Просто новая версия строки (из обычной таблички) ссылается на туже самую фотографию. Это экономит место на диске и увеличивает скорость работы.
Разделение и склеивание длинных строк PostgreSQL делает самостоятельно, то есть вам не нужно обо всем этом задумываться. Вы просто пишите запрос (SELECT), а PostgreSQL склеивает из нескольких частей длинную строку.
Но про них нужно знать. Так как длинные строки обрабатываются отдельно, то это замедляет работу базы данных, но только при запросах к длинному атрибуту, например к фотографии.
TOAST-таблица имеет свою схему pg_toast. А если это временная таблица, то pg_toast_temp_N.
Если в табличке есть поле с типом, куда может поместиться большое значение (numeric, text и т.д.), то TOAST-таблица создается сразу (как бы на всякий случай). Но до помещения больших атрибутов в TOAST-таблицу, она будет пустой.
Практика
Создадим базу данных и подключимся к ней. Затем сделаем там таблицу «t» и в неё вставим 10000 строк:
postgres@s-pg13:~$ psql Timing is on. psql (13.3) Type "help" for help. postgres@postgres=# CREATE DATABASE data_lowlevel; CREATE DATABASE Time: 66,523 ms postgres@postgres=# \c data_lowlevel You are now connected to database "data_lowlevel" as user "postgres". postgres@data_lowlevel=# CREATE TABLE t(id serial PRIMARY KEY, n numeric); CREATE TABLE Time: 4,791 ms postgres@data_lowlevel=# INSERT INTO t(n) SELECT g.id FROM generate_series(1,10000) AS g(id); INSERT 0 10000 Time: 30,785 ms
Посмотрим на файлы таблицы
С помощью функции pg_relation_filepath() можно узнать в каком файле находится объект. Узнаем в каком файле находится наша табличка (относительно каталога PGDATA):
postgres@data_lowlevel=# SELECT pg_relation_filepath('t'); pg_relation_filepath ---------------------- base/16494/16497 (1 row) Time: 0,284 ms
Первое число (16494) — это идентификатор базы, второе (16497) — идентификатор таблички.
Этот путь можно было найти вручную. Так как таблица находится в табличном пространстве pg_default, то файл должен лежать в каталоге $PGDATA/base. Дальше нужно найти идентификаторы базы и таблицы следующими способами:
postgres@data_lowlevel=# SELECT OID FROM pg_database WHERE datname = 'data_lowlevel'; oid ------- 16494 (1 row) Time: 0,904 ms postgres@data_lowlevel=# SELECT relfilenode FROM pg_class WHERE relname = 't'; relfilenode ------------- 16497 (1 row) Time: 0,350 ms
Идентификатор базы мы вытаскиваем из таблички pg_database, а идентификатор таблички из pg_class.
Теперь посмотрим на сами файлы, предварительно закрыв psql:
postgres@data_lowlevel=# \q postgres@s-pg13:~$ ls -1 $PGDATA/base/16494/16497* /usr/local/pgsql/data/base/16494/16497 /usr/local/pgsql/data/base/16494/16497_fsm /usr/local/pgsql/data/base/16494/16497_vm
- Первый файл (16497) — это основной слой.
- Второй файл (16497_fsm) — карта свободного пространства.
- Третий файл (16497_vm) — карта видимости. Если этот файл появился, значит AUTOVACUUM уже отработал.
Каждый файл не может быть больше 1 GB и кратен 8 KB. Если таблица не помещается в 1 GB, создается дополнительный файл.
Посмотрим на файлы индексов
Теперь посмотрим на индексы. Так как создавая табличку «t» мы сделали поле «id» как публичный ключ, то под него создался индекс.
Подключимся снова к базе data_lowlevel и посмотрим на объект «t«:
postgres@s-pg13:~$ psql -d data_lowlevel Timing is on. psql (13.3) Type "help" for help. postgres@data_lowlevel=# \d t Table "public.t" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------- id | integer | | not null | nextval('t_id_seq'::regclass) n | numeric | | | Indexes: "t_pkey" PRIMARY KEY, btree (id)
Выше мы видим что индекс называется «t_pkey«.
Теперь с помощью уже знакомой функции pg_relation_filepath() определим путь к файлу этого объекта. И посмотрим на файлы:
postgres@data_lowlevel=# SELECT pg_relation_filepath('t_pkey'); pg_relation_filepath ---------------------- base/16494/16504 (1 row) Time: 0,252 ms postgres@data_lowlevel=# \q postgres@s-pg13:~$ ls -1 $PGDATA/base/16494/16504* /usr/local/pgsql/data/base/16494/16504
Как видим пока есть только 1 файл. fsm у индекса может быть, а vm — нет.
Про расширение oid2name
Существует расширение oid2name, которое позволяет сопоставить объекты и файлы.
Скомпилируем это расширение:
postgres@s-pg13:~$ exit выход root@s-pg13:~# cd pg/postgresql-13.3/contrib/oid2name/ root@s-pg13:~/pg/postgresql-13.3/contrib/oid2name# make . root@s-pg13:~/pg/postgresql-13.3/contrib/oid2name# make install .
Без ключей выдаст список баз данных:
root@s-pg13:~/pg/postgresql-13.3/contrib/oid2name# su - postgres postgres@s-pg13:~$ oid2name All databases: Oid Database Name Tablespace ---------------------------------- 16465 appdb pg_default 16481 configdb pg_default 16448 data_catalog pg_default 16494 data_lowlevel pg_default 12664 postgres pg_default 12663 template0 pg_default 1 template1 pg_default
Можно посмотреть на объекты в базе данных. Например на табличные пространства в базе data_lowlevel:
postgres@s-pg13:~$ oid2name -d data_lowlevel -s All tablespaces: Oid Tablespace Name ----------------------- 1663 pg_default 1664 pg_global
Можно по имени таблицы узнать её идентификатор, или по идентификатору узнать имя таблицы:
postgres@s-pg13:~$ oid2name -d data_lowlevel -t t From database "data_lowlevel": Filenode Table Name ---------------------- 16497 t postgres@s-pg13:~$ oid2name -d data_lowlevel -f 16497 From database "data_lowlevel": Filenode Table Name ---------------------- 16497 t
Узнаем размер объектов
С помощью функции pg_relation_size(‘’, ‘’) можем узнать размер слоя:
postgres@s-pg13:~$ psql -d data_lowlevel Timing is on. psql (13.3) Type "help" for help. postgres@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) Time: 0,659 ms
Посмотреть размер таблицы без учета индексов можно с помощью функции pg_table_size():
postgres@data_lowlevel=# SELECT pg_table_size('t'); pg_table_size --------------- 491520 (1 row) Time: 0,625 ms
А размер индексов можно посмотреть с помощью функции pg_indexes_size():
postgres@data_lowlevel=# SELECT pg_indexes_size('t'); pg_indexes_size ----------------- 245760 (1 row) Time: 0,479 ms
И полный размер таблицы вместе с индексами можно посмотреть с помощью функции pg_total_relation_size():
postgres@data_lowlevel=# SELECT pg_total_relation_size('t'); pg_total_relation_size ------------------------ 737280 (1 row) Time: 0,247 ms
Таблицы TOAST
Тип данных numeric может работать с очень большими числами. Например число 123456789 в степени 12345 будет содержать 99907 цифр:
postgres@data_lowlevel=# SELECT length((123456789::numeric ^ 12345::numeric)::text); length -------- 99907 (1 row) Time: 173,803 ms
Такое число в одну строку (8 KB) не поместится. Значит для этого поля PostgreSQL создаст служебную TOAST-таблицу.
Теперь поместим это число в базу data_lowlevel в табличку «t»:
postgres@data_lowlevel=# INSERT INTO t(n) SELECT 123456789::numeric ^ 12345::numeric; INSERT 0 1 Time: 204,066 ms
Найдем эту TOAST-таблицу:
postgres@data_lowlevel=# SELECT reltoastrelid FROM pg_class WHERE relname='t'; reltoastrelid --------------- 16501 (1 row) Time: 0,428 ms postgres@data_lowlevel=# SELECT relname, relfilenode FROM pg_class WHERE OID = 16501; relname | relfilenode ----------------+------------- pg_toast_16497 | 16501 (1 row) Time: 0,277 ms
Выше мы из таблички pg_class вытащили все идентификаторы TOAST-таблиц, для таблицы «t». А дальше по идентификатору (16501) узнали имя таблички (pg_toast_16497).
Файл для TOAST таблицы можем тоже найти:
postgres@data_lowlevel=# SELECT pg_relation_filepath('t'); pg_relation_filepath ---------------------- base/16494/16497 (1 row) Time: 0,206 ms data_lowlevel=# \q postgres@data_lowlevel=# \q postgres@s-pg13:~$ du -sh $PGDATA/base/16494/16501* 57K /usr/local/pgsql/data/base/16494/16501 4,5K /usr/local/pgsql/data/base/16494/16501_fsm
Выше я узнал каталог нашей базы данных base/16494/. А затем используя идентификатор таблички (pg_toast_16497 | 16501) посмотрел на файлы. Как видим основной слой TOAST-таблицы весит 57 KB. У этого объекта может быть fsm и vm слои. В этой табличке пока только одно большое число, которое не поместилось в поле (8 KB) обычной таблички «t».
Стратегии работы с большими строками
В теоретической части этой статьи мы уже разобрали что большие строки либо сжимаются, либо помещаются в TOAST-таблицу. Какая текущая стратегия работы для таблицы «t«? Можем посмотреть так:
postgres@s-pg13:~$ psql -d data_lowlevel Timing is on. psql (13.3) Type "help" for help postgres@data_lowlevel=# \d+ t Table "public.t" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+-------------------------------+---------+--------------+------------- id | integer | | not null | nextval('t_id_seq'::regclass) | plain | | n | numeric | | | | main | | Indexes: "t_pkey" PRIMARY KEY, btree (id) Access method: heap
Стратегия указывается в поле Storage. Выше мы видим что поле id имеет стратегию «plain«. Так как это поле имеет тип integer, то оно не может иметь большое значение и TOAST таблицы отключены. А поле n имеет тип numeric, и стратегию main — приоритет сжатия.
Возможные типы стратегий:
- plain — TOAST не применяется;
- main — сжимает по максимуму и только если никак не может поместить, то создает TOAST таблицу;
- externed — сжимает кусочек данных, и если видит что не помещается, создаёт TOAST таблицу;
- external — не пытается сжать, сразу создает TOAST-таблицу.
Поменять стратегию для таблицы можно с помощью ALTER TABLE … ALTER COLUMN … SET STORAGE …:
postgres@data_lowlevel=# ALTER TABLE t ALTER COLUMN n SET STORAGE extended; ALTER TABLE Time: 0,856 ms
Но эта команда не изменит существующие данные, а повлияет на работу с новыми данными.
Неожиданное влияние текстов среднего размера на производительность PostgreSQL
В схеме любой базы данных, наверняка, будет множество текстовых полей. Я, для целей этой статьи, разделил текстовые поля на три категории:
- Маленькие тексты. Имена и фамилии людей, заголовки страниц, имена пользователей, адреса электронной почты и прочее подобное. Обычно на размер таких полей накладываются определённые ограничения, довольно сильные. Возможно, это даже не поля типа text , а поля типа varchar(n) .
- Большие тексты. Это, например, содержимое публикаций в блогах, тексты статей, HTML-код. Такие данные представляют собой большие фрагменты текста неограниченной длины, хранящиеся в базе данных.
- Тексты среднего размера. Это — описания, комментарии, отзывы о товарах, данные о трассировке стека и так далее. В сущности это — любые текстовые поля, размер которых находится между размерами «маленьких» и «больших» текстов. Обычно размер таких полей не ограничен, но их содержимое, по естественным причинам, меньше, чем содержимое полей категории «большие тексты».
В этом материале я хочу рассказать о неожиданном влиянии текстов среднего размера на производительность запросов в PostgreSQL. В частности, мы поговорим о TOAST (The Oversized-Attribute Storage Technique, Техника хранения больших атрибутов)
Знакомство с TOAST
Если говорить о больших фрагментах текста, или о любых других полях, способных хранить большие объёмы данных, сначала надо понять то, как именно PostgreSQL обрабатывает подобные данные. Можно подумать, что эти данные хранятся так же, как и остальные, в обычной таблице, но, на самом деле, это не так.
В PostgreSQL используется фиксированный размер страницы (обычно — 8 Кб), кортежам запрещено занимать несколько страниц. Поэтому нельзя непосредственно хранить очень большие значения полей.
Из пояснений, даваемых в документации, можно узнать, что PostgreSQL не может хранить строки (кортежи), разбивая их на несколько страниц. Как же база данных обращается с большими фрагментами данных?
[…] большие значения полей сжимают и/или разбивают на несколько физических строк. […]. Эта методика известна как TOAST (или как «величайшая вещь после хлеба в нарезке»).
А как работает TOAST?
Если данные любого столбца таблицы поддерживают TOAST-обработку, то у этой таблицы будет связанная с ней TOAST-таблица.
То есть — TOAST — это отдельная таблица, связанная с основной таблицей. Она используется для хранения больших фрагментов данных из колонок, поддерживающих TOAST-обработку (например, к типам данных, поддерживающих TOAST, относится text ).
Какие значения считаются «большими»?
Код обработки TOAST вызывается только тогда, когда значение строки, которое должно быть сохранено в таблице, длиннее, чем TOAST_TUPLE_THRESHOLD байт (обычно — 2 Кб). Этот код сжимает и/или перемещает значения поля за пределы таблицы до тех пор, пока значение строки не окажется короче TOAST_TUPLE_TARGET байт (тоже обычно 2 Кб, поддаётся настройке) или до тех пор, пока это не принесёт никаких улучшений.
Получается, что PostgreSQL попытается сжать большие значения строк, а если эти данные оказываются больше заданного лимита, значения сохраняются за пределами основной таблицы, в таблице TOAST.
▍Поиск TOAST-таблицы
Теперь, когда мы немного разобрались в том, что такое TOAST, посмотрим на этот механизм в действии. Начнём с создания таблицы с текстовым полем:
db=# CREATE TABLE toast_test (id SERIAL, value TEXT); CREATE TABLE В этой таблице есть столбец id и поле value , имеющее тип TEXT . Обратите внимание на то, что мы не меняли стандартные параметры хранения данных.
Текстовое поле, которое мы добавили в таблицу, поддерживает TOAST, или TOAST-обработку. Поэтому СУБД PostgreSQL должна создать TOAST-таблицу. Попробуем найти такую таблицу, связанную с нашей таблицей toast_test . Для этого воспользуемся pg_class:
db=# SELECT relname, reltoastrelid FROM pg_class WHERE relname = 'toast_test'; relname │ reltoastrelid ────────────┼─────────────── toast_test │ 340488 db=# SELECT relname FROM pg_class WHERE oid = 340488; relname ───────────────── pg_toast_340484 Как и можно было ожидать, была создана TOAST-таблица pg_toast_340484 .
▍TOAST в действии
Посмотрим на таблицу TOAST:
db=# \d pg_toast.pg_toast_340484 TOAST table "pg_toast.pg_toast_340484" Column │ Type ────────────┼───────── chunk_id │ oid chunk_seq │ integer chunk_data │ bytea Она состоит из трёх столбцов:
- chunk_id : ссылка на значение, подвергнутое TOAST-обработке.
- chunk_seq : последовательный номер порции данных, представляющей часть значения.
- chunk_data : порция данных.
В данный момент TOAST-таблица пуста:
db=# SELECT * FROM pg_toast.pg_toast_340484; chunk_id │ chunk_seq │ chunk_data ──────────┼───────────┼──────────── (0 rows) Это и понятно — ведь мы пока ничего не добавляли в основную таблицу. Давайте добавим в неё какое-нибудь значение небольшого размера:
db=# INSERT INTO toast_test (value) VALUES ('small value'); INSERT 0 1 db=# SELECT * FROM pg_toast.pg_toast_340484; chunk_id │ chunk_seq │ chunk_data ──────────┼───────────┼──────────── (0 rows) После того, как мы добавили в основную таблицу небольшое значение, таблица TOAST осталась пустой. Это означает, что это значение было достаточно маленьким для сохранения его в основной таблице. Его не понадобилось перемещать в TOAST-таблицу.
Маленький текст хранится в основной таблице
А теперь давайте добавим в таблицу большой текст и посмотрим, что произойдёт.
db=# INSERT INTO toast_test (value) VALUES ('n0cfPGZOCwzbHSMRaX8 . WVIlRkylYishNyXf'); INSERT 0 1 Я, чтобы не перегружать статью, сократил текст, вставляемый в таблицу. На самом деле это — строка, состоящая из 4096 случайных символов. Посмотрим на то, что теперь хранится в TOAST-таблице:
db=# SELECT * FROM pg_toast.pg_toast_340484; chunk_id │ chunk_seq │ chunk_data ──────────┼───────────┼────────────────────── 995899 │ 0 │ \x30636650475a4f43. 995899 │ 1 │ \x50714c3756303567. 995899 │ 2 │ \x6c78426358574534. (3 rows) Как оказалось, большое значение хранится за пределами основной таблицы, в TOAST-таблице. Так как это значение слишком велико и не помещается в одну строку, PostgreSQL разделила это значение на три порции. Записи вида \x3063… — это то, как psql выводит двоичные данные.
Большой текст сохранён за пределами основной таблицы в связанной с ней TOAST-таблице
Теперь выполним следующий запрос для того чтобы получить сведения о данных, хранящихся в TOAST-таблице:
db=# SELECT chunk_id, COUNT(*) as chunks, pg_size_pretty(sum(octet_length(chunk_data)::bigint)) FROM pg_toast.pg_toast_340484 GROUP BY 1 ORDER BY 1; chunk_id │ chunks │ pg_size_pretty ──────────┼────────┼──────────────── 995899 │ 3 │ 4096 bytes (1 row) Результаты этого запроса подтверждают уже известный нам факт, заключающийся в том, что текст, сохранённый в TOAST-таблице, разбит на 3 фрагмента.
Существует несколько способов узнать размер объектов баз данных при работе с PostgreSQL. А именно, речь идёт о следующих функциях:
- pg_table_size : даёт размер таблицы, включая размер связанной с ней TOAST-таблицы, но не включая размер индексов.
- pg_relation_size : даёт лишь размер таблицы.
- pg_total_relation_size : даёт размер таблицы, включая индексы и связанную с ней TOAST-таблицу.
▍Сжатие данных в TOAST-таблицах
До сих пор я не занимался классификацией текстовых полей по их размерам. Причина этого была в том, что размер самого текста неважен. Важен его размер после сжатия.
Создавать длинные строки для тестирования мы будем с помощью функции, которая возвращает строки заданной длины, состоящие из случайных символов.
CREATE OR REPLACE FUNCTION generate_random_string( length INTEGER, characters TEXT default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' ) RETURNS TEXT AS $$ DECLARE result TEXT := ''; BEGIN IF length < 1 then RAISE EXCEPTION 'Invalid length'; END IF; FOR __ IN 1..length LOOP result := result || substr(characters, floor(random() * length(characters))::int + 1, 1); end loop; RETURN result; END; $$ LANGUAGE plpgsql; Воспользуемся ей для создания строки, состоящей из 10 случайных символов:
db=# SELECT generate_random_string(10); generate_random_string ──────────────────────── o0QsrMYRvp Мы можем передать этой функции набор символов, из которого она будет выбирать символы при создании строк. Например, создадим строку, состоящую из 10 случайных цифровых символов:
db=# SELECT generate_random_string(10, '1234567890'); generate_random_string ──────────────────────── 4519991669 В TOAST-таблицах используются алгоритмы сжатия семейства LZ. Механизмы сжатия обычно работают, находя в строках повторяющиеся фрагменты и устраняя повторы. В результате длинные строки, составленные из небольшого набора символов, должны сжиматься гораздо лучше, чем строки, собранные из большого набора символов.
Для того чтобы продемонстрировать то, как в TOAST-таблицах используется сжатие, мы очистим таблицу toast_test и добавим в неё длинную строку, составленную из множества случайно подобранных символов:
db=# TRUNCATE toast_test; TRUNCATE TABLE db=# INSERT INTO toast_test (value) VALUES (generate_random_string(1024 * 10)); INSERT 0 1 Тут мы добавили в таблицу строку, представляющую собой 10 Кб случайных символов. Посмотрим на таблицу TOAST:
db=# SELECT chunk_id, COUNT(*) as chunks, pg_size_pretty(sum(octet_length(chunk_data)::bigint)) FROM pg_toast.pg_toast_340484 GROUP BY 1 ORDER BY 1; chunk_id │ chunks │ pg_size_pretty ──────────┼────────┼──────────────── 1495960 │ 6 │ 10 kB Это значение сохранено в TOAST-таблице, за пределами основной таблицы. Видно, что оно хранится в несжатом виде.
Теперь вставим в таблицу ещё одно значение такой же длины, но составленное из небольшого количества символов:
db=# INSERT INTO toast_test (value) VALUES (generate_random_string(1024 * 10, '123')); INSERT 0 1 db=# SELECT chunk_id, COUNT(*) as chunks, pg_size_pretty(sum(octet_length(chunk_data)::bigint)) FROM pg_toast.pg_toast_340484 GROUP BY 1 ORDER BY 1; chunk_id │ chunks │ pg_size_pretty ──────────┼────────┼──────────────── 1495960 │ 6 │ 10 kB 1495961 │ 2 │ 3067 bytes Мы добавили в таблицу строку размером 10 Кб, но в её состав входят лишь три цифровых символа — 1 , 2 и 3 . Такая строка, скорее всего, будет содержать повторяющиеся двоичные паттерны, а значит сжиматься она должна лучше, чем предыдущая строка. Взглянув на TOAST-таблицу, мы видим, что новая строка была сжата до размера в примерно 3 Кб, то есть — её представление, хранимое в таблице, в три раза меньше её несжатого представления. Надо сказать, что это — не такой уж и плохой уровень сжатия!
А теперь вставим в таблицу длинную строку, при составлении которой используется лишь один цифровой символ:
db=# insert into toast_test (value) values (generate_random_string(1024 * 10, '0')); INSERT 0 1 db=# SELECT chunk_id, COUNT(*) as chunks, pg_size_pretty(sum(octet_length(chunk_data)::bigint)) FROM pg_toast.pg_toast_340484 GROUP BY 1 ORDER BY 1; chunk_id │ chunks │ pg_size_pretty ──────────┼────────┼──────────────── 1495960 │ 6 │ 10 kB 1495961 │ 2 │ 3067 bytes Эту строку удалось сжать так хорошо, что СУБД смогла сохранить её в основной таблице.
▍Настройка TOAST-таблиц
Если вас интересуют вопросы настройки TOAST-таблиц, связанных с основными таблицами баз данных, то вы должны знать о том, что такие настройки делаются путём установки параметров хранения данных в запросах, использующих команды CREATE TABLE или ALTER TABLE . SET STORAGE . Речь идёт о следующих параметрах:
- toast_tuple_target . Минимальный размер кортежа, при достижении которого PostgreSQL пытается переместить длинные значения в TOAST-таблицу.
- storage . TOAST-стратегия. PostgreSQL поддерживает 4 TOAST-стратегии. По умолчанию используется стратегия EXTENDED . Её применение означает, что PostgreSQL пытается сжать значение и сохранить его в основной таблице, а если размер значения и после этого слишком велик, это значение сохраняется за пределами основной таблицы.
Производительность TOAST
Для того чтобы понять то, какой эффект на производительность БД оказывает работа с текстами разных размеров, и то, как на производительность влияет хранение данных за пределами основной таблицы, мы создадим три таблицы — по одной для каждой из интересующих нас категорий текстов:
db=# CREATE TABLE toast_test_small (id SERIAL, value TEXT); CREATE TABLE db=# CREATE TABLE toast_test_medium (id SERIAL, value TEXT); CREATE TABLE db=# CREATE TABLE toast_test_large (id SERIAL, value TEXT); CREATE TABLE Так же, как и в предыдущем разделе статьи, для каждой таблицы PostgreSQL создаёт TOAST-таблицу:
SELECT c1.relname, c2.relname AS toast_relname FROM pg_class c1 JOIN pg_class c2 ON c1.reltoastrelid = c2.oid WHERE c1.relname LIKE 'toast_test%' AND c1.relkind = 'r'; relname │ toast_relname ───────────────────┼───────────────── toast_test_small │ pg_toast_471571 toast_test_medium │ pg_toast_471580 toast_test_large │ pg_toast_471589 ▍Тестовые данные
Добавим в таблицу toast_test_small 500 тысяч строк, содержащих маленькие тексты, которые могут быть сохранены в основной таблице:
db=# INSERT INTO toast_test_small (value) SELECT 'small value' FROM generate_series(1, 500000); INSERT 0 500000 Теперь добавим в таблицу toast_test_medium 500 тысяч строк, содержащих тексты среднего размера. Размеры этих строк находятся близко к размерам строк, которые сохраняются за пределами основной таблицы, но они при этом такие строки всё ещё могут быть сохранены в основной таблице:
db=# WITH str AS (SELECT generate_random_string(1800) AS value) INSERT INTO toast_test_medium (value) SELECT value FROM generate_series(1, 500000), str; INSERT 0 500000 Я экспериментировал с разными значениями до тех пор, пока не подобрал такое значение, размер которого достаточно велик для того чтобы оно подходило бы для хранения за пределами основной таблицы. Секрет подбора таких значений заключается в том, чтобы соответствующая строка имела бы размер примерно в 2 Кб и при этом очень плохо сжималась бы.
Далее, добавим 500 тысяч строк, содержащих длинные тексты, в таблицу toast_test_large :
db=# WITH str AS (SELECT generate_random_string(4096) AS value) INSERT INTO toast_test_large (value) SELECT value FROM generate_series(1, 500000), str; INSERT 0 500000 Сейчас мы готовы к проведению эксперимента.
▍Сравнение производительности работы с разными таблицами
Обычно ожидается, что запросы к большим таблицам будут медленнее, чем запросы к маленьким таблицам. В данном случае вполне оправданно ждать того, что запрос к маленькой таблице окажется быстрее, чем запрос к таблице среднего размера. Точно так же, можно ожидать того, что запрос к таблице среднего размера окажется быстрее аналогичного запроса к большой таблице.
Для того чтобы сравнить производительность работы с различными таблицами, мы собираемся выполнить простой запрос на получение из таблицы одной строки. Так как индекса у нас нет, база данных вынуждена будет выполнить полное сканирование таблицы. Мы, кроме того, отключим параллельное выполнение запросов для того чтобы получить простые и понятные сведения о времени выполнения запроса. Для того чтобы учесть влияние кеширования мы выполним запрос много раз.
db=# SET max_parallel_workers_per_gather = 0; SET Начнём с маленькой таблицы:
db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_small WHERE PLAN ───────────────────────────────────────────────────────────────────────────────────── Gather (cost=1000.00..7379.57 rows=1 width=16) -> Parallel Seq Scan on toast_test_small (cost=0.00..6379.47 rows=1 width=16) Filter: (id = 6000) Rows Removed by Filter: 250000 Execution Time: 31.323 ms (8 rows) db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_small WHERE Time: 25.865 ms Я выполнил этот запрос много раз и, чтобы не перегружать статью, сократил выходные данные. Тут, как и ожидалось, СУБД выполняет полное сканирование таблицы. В итоге время выполнения запроса стабилизировалось примерно на 25 мс.
Теперь изучим производительность таблицы среднего размера:
db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_medium WHERE Time: 321.965 ms db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_medium WHERE Time: 173.058 ms Выполнение точно такого же запроса на таблице среднего размера заняло значительно больше времени. А именно — 173 мс, что примерно в 6 раз медленнее, чем в случае с маленькой таблицей. Это — серьёзное различие.
Завершим тесты, выполнив такой же запрос к большой таблице:
db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_large WHERE Time: 49.867 ms db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_large WHERE Time: 37.291 ms Результат выполнения этого запроса вызывает удивление. Время, необходимое на выполнение запроса к большой таблице, является почти таким же, как время, нужное на выполнение запроса к маленькой таблице. И запрос этот выполняется примерно в 6 раз быстрее, чем запрос к таблице среднего размера.
toast_test_small toast_test_medium toast_test_large А ведь здравый смысл подсказывает нам, что самая большая таблица должна быть и самой медленной. Что же тут происходит?
▍Осмысление результатов
Для того чтобы понять полученные результаты предлагаю взглянуть на размеры самих таблиц и связанных с ними TOAST-таблиц:
SELECT c1.relname, pg_size_pretty(pg_relation_size(c1.relname::regclass)) AS size, c2.relname AS toast_relname, pg_size_pretty(pg_relation_size(('pg_toast.' || c2.relname)::regclass)) AS toast_size FROM pg_class c1 JOIN pg_class c2 ON c1.reltoastrelid = c2.oid WHERE c1.relname LIKE 'toast_test_%' AND c1.relkind = 'r'; toast_test_small pg_toast_471571 toast_test_medium pg_toast_471580 toast_test_large pg_toast_471589 Проанализируем эти данные.
Таблица toast_test_small
Размер таблицы toast_test_small составляет 21 Мб. При работе с ней TOAST-таблица не используется. Это понятно — ведь в таблицу мы добавили маленькие тексты, которые сохраняются прямо в этой таблице.
Тексты маленького размера хранятся в основной таблице
Таблица toast_test_medium
Размер таблицы toast_test_medium значительно больше — 977 Мб. Мы заполнили её строками, которые имеют длину, близкую к той, после достижения которой значения сохраняются в TOAST-таблице, но не превышающую её. В результате таблица получилась очень большой, но при этом TOAST-таблица для хранения данных не использовалась.
Тексты среднего размера хранятся в основной таблице
Таблица toast_test_large
Размер таблицы toast_test_large практически такой же, как размер таблицы toast_test_small . Дело тут в том, что мы добавили в эту таблицу большие тексты, что привело к тому, что СУБД сохранила их за пределами основной таблицы, в TOAST-таблице. Именно поэтому в данном случае размеры TOAST-таблицы столь велики, а основная таблица получилась маленькой.
Большие тексты были сохранены в TOAST-таблице
При выполнении нашего запроса СУБД производит полное сканирование таблицы. При сканировании таблиц toast_test_large и toast_test_small системе приходится обрабатывать 21 Мб и 25 Мб данных. В результате запросы к этим таблицам выполняются достаточно быстро. Но когда мы выполняем запрос к таблице toast_test_medium , все данные которой хранятся в ней, системе надо прочитать с диска 977 Мб данных. В результате на выполнение запроса нужно больше времени.
Механизм TOAST, за счёт сохранения больших значений за пределами основных таблиц, способствует тому, что эти таблицы имеют компактные размеры.
▍Работа с содержимым текстовых полей
Выше мы исследовали производительность базы данных, выполняя запросы с использованием идентификаторов ( id ), а не значений, хранящихся в текстовых полях. Что произойдёт в том случае, если нужно работать с самими текстовыми значениями?
db=# \timing Timing is on. db=# SELECT * FROM toast_test_large WHERE value LIKE 'foo%'; Time: 7509.900 ms (00:07.510) db=# SELECT * FROM toast_test_large WHERE value LIKE 'foo%'; Time: 7290.925 ms (00:07.291) db=# SELECT * FROM toast_test_medium WHERE value LIKE 'foo%'; Time: 5869.631 ms (00:05.870) db=# SELECT * FROM toast_test_medium WHERE value LIKE 'foo%'; Time: 259.970 ms db=# SELECT * FROM toast_test_small WHERE value LIKE 'foo%'; Time: 78.897 ms db=# SELECT * FROM toast_test_small WHERE value LIKE 'foo%'; Time: 50.035 ms Мы выполнили запросы к трём таблицам, производя поиск по строковым значениям. При этом не ожидалось, что запрос вернёт какой-то результат. СУБД, чтобы выполнить этот запрос, нужно было просканировать всю таблицу. В этот раз результаты испытания лучше соответствуют тому, чего можно ожидать.
toast_test_small toast_test_medium toast_test_large Чем больше таблица — тем дольше выполняется запрос. Это вполне понятно, так как для выполнения запроса системе необходимо прочитать тексты, хранящиеся в таблицах. В случае с большой таблицей это означает и необходимость работы с её TOAST-таблицей.
▍Как насчёт индексов?
Индексы помогают базе данных минимизировать число страниц, которые ей необходимо загрузить для выполнения запроса. Например, давайте повторим наш первый эксперимент, когда мы искали строку по id , но в этот раз построим индекс по соответствующему полю:
db=# CREATE INDEX toast_test_medium_id_ix ON toast_test_small(id); CREATE INDEX db=# CREATE INDEX toast_test_medium_id_ix ON toast_test_medium(id); CREATE INDEX db=# CREATE INDEX toast_test_large_id_ix ON toast_test_large(id); CREATE INDEX Выполним к таблицам те же запросы, что выполняли ранее:
db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_small WHERE PLAN ───────────────────────────────────────────────────────────────────────────────────────────── Index Scan using toast_test_small_id_ix on toast_test_small(cost=0.42..8.44 rows=1 width=16) Index Cond: (id = 6000) Time: 0.772 ms db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_medium WHERE PLAN ───────────────────────────────────────────────────────────────────────────────────────────── Index Scan using toast_test_medium_id_ix on toast_test_medium(cost=0.42..8.44 rows=1 width=1808 Index Cond: (id = 6000) Time: 0.831 ms db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_large WHERE PLAN ───────────────────────────────────────────────────────────────────────────────────────────── Index Scan using toast_test_large_id_ix on toast_test_large(cost=0.42..8.44 rows=1 width=22) Index Cond: (id = 6000) Time: 0.618 ms Здесь, во всех трёх запросах, использовался индекс. Можно видеть, что скорость выполнения запросов во всех трёх случаях практически идентична.
Теперь мы уже знаем о том, что проблемы с производительностью начинаются тогда, когда базе данных приходится выполнять много операций ввода-вывода. Поэтому давайте составим запрос, при выполнении которого СУБД будет пользоваться индексом, но такой, чтобы для его выполнения нужно было бы прочесть большой объём данных:
db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_small WHERE id BETWEEN 0 AND 250000; QUERY PLAN ─────────────────────────────────────────────────────────────────────────────────────────────── Index Scan using toast_test_small_id_ix on toast_test_small(cost=0.4..9086 rows=249513 width=16 Index Cond: ((id >= 0) AND (id Мы выполнили запрос на получение половины данных, хранящихся в таблице. Это, с одной стороны, достаточно малая часть таблицы, поэтому PostgreSQL решает воспользоваться индексом, но, с другой стороны, данных тут достаточно много для того чтобы системе пришлось бы выполнить большой объём операций ввода-вывода.
Вот мой материал, из которого можно узнать о том, что использование индексов не всегда позволяет выполнять запросы самым быстрым из возможных способов.
Мы выполнили запросы к каждой таблице по два раза. Во всех случаях СУБД использует индекс для работы с таблицами. Учитывайте то, что индекс помогает лишь в снижении количества страниц, к которым нужно обращаться базе данных, но в данном случае системе нужно ещё и прочитать половину таблицы.
toast_test_small toast_test_medium toast_test_large Результаты этого испытания похожи на результаты первого проведённого нами теста. В тех случаях, когда базе данных нужно прочесть большой фрагмент таблицы, таблица, в которой тексты среднего размера хранятся без использования TOAST-таблицы, оказывается самой медленной.
Варианты решения проблемы
Если вы дочитали до этого места и убедились в том, что тексты среднего размера — это то, что вызывает проблемы с производительностью, вы, вероятно, размышляете о том, как бороться с этими проблемами. Именно об этом я и хочу тут рассказать.
▍Настройка toast_tuple_target
Параметр toast_tuple_target управляет минимальной длиной кортежа, по достижении которой PostgreSQL пытается переместить длинное значение в TOAST-таблицу. По умолчанию тут установлено 2 Кб, но это значение можно уменьшать — вплоть до 128 байт. Чем меньше это значение — тем больше шансов на то, что строка среднего размера будет храниться не в основной таблице, а в TOAST-таблице.
Для того чтобы продемонстрировать пример применения этой рекомендации, я создал пару таблиц. В одной параметры хранения данных оставлены в стандартном состоянии, а при создании другой я записал в toast_tuple_target значение 128 :
db=# CREATE TABLE toast_test_default_threshold (id SERIAL, value TEXT); CREATE TABLE db=# CREATE TABLE toast_test_128_threshold (id SERIAL, value TEXT) WITH (toast_tuple_target=128); CREATE TABLE db=# SELECT c1.relname, c2.relname AS toast_relname FROM pg_class c1 JOIN pg_class c2 ON c1.reltoastrelid = c2.oid WHERE c1.relname LIKE 'toast%threshold' AND c1.relkind = 'r'; relname │ toast_relname ──────────────────────────────┼────────────────── toast_test_default_threshold │ pg_toast_3250167 toast_test_128_threshold │ pg_toast_3250176 Далее, я заполнил таблицы значениями, размеры которых, в несжатом виде, превышают 2 Кб, а в сжатом оказываются меньше, чем 128 байт. После этого я проверил то, хранятся ли эти значения в основных таблицах или в соответствующих им TOAST-таблицах:
db=# INSERT INTO toast_test_default_threshold (value) VALUES (generate_random_string(2100, '123')); INSERT 0 1 db=# SELECT * FROM pg_toast.pg_toast_3250167; chunk_id │ chunk_seq │ chunk_data ──────────┼───────────┼──────────── (0 rows) db=# INSERT INTO toast_test_128_threshold (value) VALUES (generate_random_string(2100, '123')); INSERT 0 1 db=# SELECT * FROM pg_toast.pg_toast_3250176; ─[ RECORD 1 ]───────────── chunk_id │ 3250185 chunk_seq │ 0 chunk_data │ \x3408. В обе таблицы попали примерно одинаковые строки. При использовании стандартных параметров они были сохранены в основной таблице. При работе с таблицей, которая создавалась с использованием конструкции toast_tuple_target=128 , данные были сохранены в TOAST-таблице.
▍Создание отдельной таблицы
Если у вас имеется очень важная таблица, в которой хранятся тексты среднего размера, и вы заметили, что большинство таких текстов хранится в основной таблице, что, возможно, замедляет запросы, вы можете переместить столбец с полем, содержащим такие тексты, в отдельную таблицу:
CREATE TABLE toast_test_value (fk INT, value TEXT); CREATE TABLE toast_test (id SERIAL, value_id INT) В одной из моих статей я рассказываю о том, как мы пользуемся SQL для поиска аномалий. В одном из примеров рассматривается таблица со сведениями об ошибках, содержащая данные о трассировке Python-кода. Эти сообщения представляли собой тексты средних размеров, многие из них хранились в основной таблице. В результате размеры этой таблицы очень быстро выросли. На самом деле, таблица стала настолько большой, что мы заметили, как запросы к ней выполняются всё медленнее и медленнее. В итоге мы переместили данные об ошибках в другую таблицу, что позволило ускорить работу с системой.
Итоги
Главная проблема текстов среднего размера заключается в том, что при их сохранении в базе данных строки становятся слишком длинными. Это плохо, так как PostgreSQL, как и другие OLTP-ориентированные базы данных, хранят значения, организуя данные по строкам. Когда мы просим СУБД выполнить запрос по небольшому количеству столбцов, весьма вероятно то, что значения этих столбцов будут разбросаны по множеству блоков. Если строки таблицы достаточно длинны, это приводит к необходимости выполнения большого объёма операций ввода-вывода, что влияет на производительность запросов и на объём используемых системных ресурсов.
Для решения этой проблемы некоторые базы данных, не ориентированные на OLTP, используют другие схемы хранения данных: система при хранении данных ориентируется не на строки, а на столбцы. При таком подходе в ситуации, когда СУБД нужно просканировать некий столбец, то окажется, что значения, хранящиеся в этом столбце, находятся в последовательно расположенных блоках. Обычно это приводит к тому, что для работы с такими значениями приходится выполнять меньше операций ввода-вывода. Кроме того, данные, хранящиеся в одном столбце, вполне возможно, будут содержать повторяющиеся паттерны и значения, а значит — будут лучше поддаваться сжатию.
Базы данных, которые при хранении данных ориентируются на строки и на столбцы
Такую схему хранения данных имеет смысл использовать, например, в неких информационных хранилищах, не относящихся к OLTP-системам. Строки таблиц, используемых в таких системах, обычно очень длинны, запросы к ним часто задействуют небольшое подмножество столбцов и предусматривают чтение большого количества строк. В OLTP-системах обычно выполняется чтение одной строки или нескольких строк. Поэтому хранение данных, ориентированное на строки, лучше подходит для этих систем.
Сталкивались ли вы с необычными проблемами, касающимися производительности баз данных?
Pg toast что за таблица
В данном разделе рассматривается TOAST (The Oversized-Attribute Storage Technique, Методика хранения сверхбольших атрибутов).
Postgres Pro использует фиксированный размер страницы (обычно 8 КБ), и не позволяет кортежам занимать несколько страниц. Поэтому непосредственно хранить очень большие значения полей невозможно. Для преодоления этого ограничения большие значения полей сжимаются и/или разбиваются на несколько физических строк. Это происходит незаметно для пользователя и на большую часть кода сервера влияет незначительно. Этот метод известен как TOAST (тост, или « лучшее после изобретения нарезанного хлеба » ). Инфраструктура TOAST также применяется для оптимизации обработки больших значений данных в памяти.
Лишь определённые типы данных поддерживают TOAST — нет смысла производить дополнительные действия с типами данных, размер которых не может быть большим. Чтобы поддерживать TOAST , тип данных должен представлять значение переменной длины (varlena), в котором первое четырёхбайтовое слово любого хранящегося значения содержит общую длину значения в байтах (включая само это слово). Содержание оставшейся части значения TOAST не ограничивает. Специальные представления, в целом называемые значениями в формате TOAST , работают, манипулируя этим начальным словом длины и интерпретируя его по-своему. Таким образом, функции уровня C, работающие с типом данных, поддерживающим TOAST , должны аккуратно обращаться со входными значениями, которые могут быть в формате TOAST : входные данные могут и не содержать четырёхбайтовое слово длины и содержимое после него, пока не будут распакованы. (Обычно в таких ситуациях нужно использовать макрос PG_DETOAST_DATUM прежде чем что-либо делать с входным значением, но в некоторых случаях возможны и более эффективные подходы. За подробностями обратитесь к Подразделу 35.11.1.)
TOAST занимает два бита слова длины varlena (старшие биты на машинах с порядком байт от старшего к младшему, или младшие биты — при другом порядке байт), таким образом, логический размер любого значения в формате TOAST ограничивается 1 Гигабайтом (2 30 - 1 байт). Когда оба бита равны нулю, значение является обычным, не в формате TOAST , и оставшиеся биты слова длины задают общий размер элемента данных (включая слово длины) в байтах. Когда установлен старший (или младший, в зависимости от архитектуры) бит, значение имеет однобайтовый заголовок вместо обычного четырёхбайтового, а оставшиеся биты этого байта задают общий размер элемента данных (включая байт длины) в байтах. Этот вариант позволяет экономно хранить значения короче 127 байт и при этом допускает расширение значения этого типа данных до 1 Гбайта при необходимости. Значения с однобайтовыми заголовками не выравниваются по какой-либо определённой границе, тогда как значения с четырёхбайтовыми заголовками выравниваются по границе минимум четырёх байт; это избавление от выравнивания даёт дополнительный выигрыш в объёме, очень ощутимый для коротких значений. В качестве особого случая, если все оставшиеся биты однобайтового заголовка равны нулю (что в принципе невозможно с учётом включения размера длины), значением является указатель на отдельно размещённые данные, с несколькими возможными вариантами, описанными ниже. Тип и размер такого указателя TOAST определяется кодом, хранящимся во втором байте значения. Наконец, когда старший (или младший, в зависимости от архитектуры) бит очищен, а соседний бит установлен, содержимое данных хранится в упакованном виде и должно быть распаковано перед использованием. В этом случае оставшиеся биты четырёхбайтового слова длины задают общий размер сжатых, а не исходных данных. Заметьте, что сжатие также возможно и для отделённых данных, но заголовок varlena не говорит, имеет ли оно место — это определяется содержимым, на которое указывает указатель TOAST .
Как уже было сказано, существуют разные варианты использования указателя TOAST . Самый старый и наиболее популярный вариант — когда он указывает на отделённые данные, размещённые в таблице TOAST , которая отделена, но связана с таблицей, содержащей собственно указатель данных TOAST . Такой указатель на данные на диске создаётся кодом обработки TOAST (в access/heap/tuptoaster.c ), когда кортеж, сохраняемый на диск, оказывается слишком большим. Дополнительные подробности описаны в Подразделе 63.2.1. Кроме того, указатель TOAST может указывать на отделённые данные, размещённые где-то в памяти. Такие данные обязательно недолговременные и никогда не оказываются на диске, но этот механизм очень полезен для исключения копирования и избыточной обработки данные большого размера. Дополнительные подробности описаны в Подразделе 63.2.2.
В качестве метода сжатия внутренних и отделённых данных применяется довольно простой и очень быстрый представитель семейства алгоритмов LZ. Подробнее см. src/common/pg_lzcompress.c .
63.2.1. Отдельное размещение TOAST на диске
Если какие-либо столбцы таблицы хранятся в формате TOAST , у таблицы будет связанная с ней таблица TOAST , OID которой хранится в значении pg_class . reltoastrelid для данной таблицы. Размещаемые на диске TOAST -значения содержатся в таблице TOAST , что подробнее описано ниже.
Отделённые значения делятся на порции (после сжатия, если оно применяется) размером не более TOAST_MAX_CHUNK_SIZE байт (по умолчанию это значение выбирается таким образом, чтобы на странице помещались четыре строки порций, то есть размер одной составляет порядка 2000 байт). Каждая порция хранится как отдельная строка в таблице 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 Кб) или уменьшить объём станет невозможно. Во время операции UPDATE значения неизменённых полей обычно сохраняются как есть, поэтому модификация строки с отдельно хранимыми значениями не несёт издержек, связанных с TOAST , если все такие значения остаются без изменений.
Код обработки TOAST распознаёт четыре различные стратегии хранения столбцов, совместимых с TOAST , на диске:
PLAIN не допускает ни сжатие, ни отдельное хранение; кроме того, отключается использование однобайтовых заголовков для типов varlena. Это единственно возможная стратегия для столбцов типов данных, которые несовместимы с TOAST .
EXTENDED допускает как сжатие, так и отдельное хранение. Это стандартный вариант для большинства типов данных, совместимых с TOAST . Сначала происходит попытка выполнить сжатие, затем — сохранение вне таблицы, если строка всё ещё слишком велика.
EXTERNAL допускает отдельное хранение, но не сжатие. Использование EXTERNAL ускорит операции над частями строк в больших столбцах text и bytea (ценой увеличения объёма памяти для хранения), так как эти операции оптимизированы для извлечения только требуемых частей отделённого значения, когда оно не сжато.
Каждый тип данных, совместимый с TOAST , определяет стандартную стратегию для столбцов этого типа данных, но стратегия для заданного столбца таблицы может быть изменена с помощью ALTER TABLE SET STORAGE .
Эта схема имеет ряд преимуществ по сравнению с более простым подходом, когда значения строк могут занимать несколько страниц. Если предположить, что обычно запросы характеризуются выполнением сравнения с относительно маленькими значениями ключа, большая часть работы будет выполняться с использованием главной записи строки. Большие значения атрибутов в формате TOAST будут просто передаваться (если будут выбраны) в тот момент, когда результирующий набор отправляется клиенту. Таким образом, главная таблица получается гораздо меньше, и в общий кеш буферов помещается больше её строк, чем их было бы без использования отдельного хранения. Наборы данных для сортировок также уменьшаются, а сортировки чаще будут выполняться исключительно в памяти. Небольшой тест показал, что таблица, содержащая типичные HTML-страницы и их URL после сжатия занимала примерно половину объёма исходных данных, включая таблицу TOAST , и что главная таблица содержала лишь около 10% всех данных (URL и некоторые маленькие HTML-страницы). Время обработки не отличалось от времени, необходимого для обработки таблицы без использования TOAST , в которой размер всех HTML-страниц был уменьшен до 7 Кб, чтобы они уместились в строках.
63.2.2. Отдельное размещение TOAST в памяти
Указатели TOAST могут указывать на данные, размещённые не на диске, а где-либо в памяти текущего серверного процесса. Очевидно, что такие указатель не могут быть долговременными, но они, тем не менее, полезны. В настоящее время поддерживаются два подварианта: косвенные указатели на данные и указатели на развёрнутые данные.
Косвенный указатель TOAST просто указывает на значение varlena, хранящееся где-то в памяти. Этот вариант изначально был реализован просто как подтверждение концепции, но в настоящее время он применяется при логическом декодировании, чтобы не приходилось создавать физические кортежи больше одного 1 ГБ (что может потребоваться при консолидации всех отделённых значений полей в одном кортеже). Данный вариант имеет ограниченное применение, так как создатель такого указателя должен полностью понимать, что целевые данные будут существовать, только пока существует указатель, и никакой инфраструктуры для сохранения их нет.
Указатели на развёрнутые данные TOAST полезны для сложных типов, представление которых на диске плохо приспособлено для вычислительных целей. Например, стандартное представление в виде varlena массива Postgres Pro включает информацию о размерности, битовую карту элементов NULL (если они в нём содержатся), а затем значения всех элементов по порядку. Когда элемент сам по себе имеет переменную длину, единственный способ найти N -ый элемент — просканировать все предыдущие элементы. Это представление компактно, и поэтому подходит для хранения на диске, но для вычислительной обработки массива гораздо удобнее иметь « развёрнутое » или « деконструированное » представление, в котором можно определить начальные адреса всех элементов. Механизм указателей TOAST способствует решению этой задачи, допуская передачу по ссылке элемента Datum как указателя на стандартное значение varlena (представление на диске) или указателя TOAST на развёрнутое представление где-то в памяти. Детали развёрнутого представление определяются самим типом данных, хотя оно может иметь стандартный заголовок и удовлетворять другим требованиям API, описанным в src/include/utils/expandeddatum.h . Функции уровня C, работающие с этим типом, могут реализовать поддержку любого из этих представлений. Функции, не знающие о развёрнутом представлении, а просто применяющие PG_DETOAST_DATUM к своим входным данным, будут автоматически получать традиционное представление varlena; так что поддержка развёрнутого представления может вводиться постепенно, по одной функции.
Указатели TOAST на развёрнутые значения далее подразделяются на указатели для чтения/записи и указатели только для чтения. Представление, на которое они указывают, в любом случае одинаковое, но функции, получающей указатель для чтения/записи, разрешается модифицировать целевые данные прямо на месте, тогда как функция, получающая указатель только для чтения, не должна этого делать; если ей нужно получить изменённую версию значения, она должна сначала сделать копию. Это отличие и связанные с ним соглашения позволяют избежать излишнего копирования развёрнутых значений при выполнении запросов.
Для всех типов указателей TOAST на данные в памяти, код обработки TOAST гарантирует, что такие данные не окажутся случайно сохранены на диске. Указатели TOAST в памяти автоматически сворачиваются в обычные значения varlena перед сохранением — а затем могут преобразоваться в указатели TOAST на диске, если без этого не смогут уместиться в содержащем их кортеже.
| Пред. | Наверх | След. |
| 63.1. Размещение файлов базы данных | Начало | 63.3. Карта свободного пространства |
Записки программиста
Ранее мы разобрались, как PostgreSQL хранит страницы на диске, и как можно посмотреть их содержимое при помощи pageinspect. Но возникает вот какой вопрос. Если размер страницы составляет несколько килобайт, и кортежи не могут занимать несколько страниц, то как СУБД хранит атрибуты с типом TEXT ? Ведь строки явно могут быть длиннее пары тысяч символов. Это возможно благодаря штуке под названием The Oversized-Attribute Storage Technique, или сокращенно TOAST.
Идея простая. Если в таблице есть атрибуты с типом TEXT / JSONB / так далее, то рядом с таблицей создается служебная TOAST-таблица. Когда размер кортежа превышает 1/4 размера страницы, СУБД смотрит на атрибуты кортежа и пытается их сжать. Затем сжатые данные нарезаются на части (chunks) заведомо меньше размера страницы и складываются в TOAST-таблицу. Атрибуты в исходном кортеже заменяются на TOAST-указатели. Кортеж становится меньше и теперь помещается в страницу. Это если в двух словах. Есть еще ряд нюансов. О них будет рассказано ниже по тексту.
CREATE TABLE phonebook (
"id" SERIAL PRIMARY KEY NOT NULL ,
"name" TEXT NOT NULL ,
"phone" INT NOT NULL ) ;
Имеем атрибут с типом TEXT . Значит, где-то должна быть TOAST-таблица:
= # SELECT c1 . oid , c1 . reltoastrelid , c2 . relname
FROM pg_class AS c1
LEFT JOIN pg_class AS c2
ON c1 . reltoastrelid = c2 . oid
WHERE c1 . relname = 'phonebook' ;
- [ RECORD 1 ] -+ ---------------
oid | 39542
reltoastrelid | 39546
relname | pg_toast_39542
Что же это за таблица такая:
TOAST table "pg_toast.pg_toast_39542"
Column | Type
------------+---------
chunk_id | oid
chunk_seq | integer
chunk_data | bytea
Owning table: "public.phonebook"
Indexes:
"pg_toast_39542_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
Заметьте, что таблица живет в схеме pg_toast . По умолчанию схема отсутствует в search_path , поэтому ее нужно указывать явно.
Спрашивается, откуда PostgreSQL знает, какие типы являются TOAST-able, а какие — нет? Конечно же, из описания типа в таблице каталога pg_type:
= # SELECT typlen , typstorage
FROM pg_type
WHERE typname = 'text' ;
- [ RECORD 1 ] ----
typlen | - 1
typstorage | x
Значение typlen = -1 говорит о том, что TEXT является типом переменного размера. Или, как принято говорить в мире PostgreSQL, varlena типом. Данная информация нам еще пригодится. В typstorage может быть записано одно из четырех значений: p = PLAIN, x = EXTENDED, e = EXTERNAL, m = MAIN. Это называется стратегией хранения.
PLAIN означает, что атрибуты хранятся в кортежах как есть, и TOAST для них выключен. Это стратегия для типов INT4 , CHAR , и так далее. EXTENDED означает, что TOAST включен. Данная стратегия используется для TEXT , JSONB , и многих других типов. EXTERNAL — то же самое, что EXTENDED, только отключает сжатие данных. По умолчанию эта стратегия не используется ни для каких типов. Наконец, MAIN означает сжимать данные, но пытаться хранить их в самом кортеже. Сжатые данные будут помещены в TOAST-таблицу только если это единственный способ уместить кортеж в страницу. Это стратегия для типов INET , CIDR и NUMERIC .
Может показаться, что EXTERNAL — какая-то бесполезная стратегия, если она ни для чего не используется. На самом деле, это не так. Во-первых, пользователь может указать ее для самостоятельно объявленных типов: CREATE TYPE foo STORAGE = external . Во-вторых, стратегия хранения может быть переопределена для конкретной колонки конкретной таблицы:
ALTER TABLE phonebook ALTER COLUMN name SET STORAGE external;
EXTERNAL имеет смысл использовать, например, когда вы знаете, что данные будут плохо сжиматься. Также эта стратегия ускоряет выделение подстрок из длинных значений с типом TEXT и BYTEA .
Также можно переопределить и используемый алгоритм сжатия:
ALTER TABLE phonebook ALTER COLUMN name SET COMPRESSION lz4;
На момент написания этих строк поддерживалось два алгоритма — PGLZ и LZ4. Первый был в PostgreSQL испокон веков, и он используется по умолчанию. Алгоритм LZ4 доступен, если PostgreSQL был собран с флагом --with-lz4 . Обычно LZ4 сжимает данные похуже других алгоритмов, зато он очень быстрый. Из особенностей PGLZ стоит отметить тот факт, что если ему не удается сжать первые 1024 байта входных данных, алгоритм сдается и говорит, что эти данные несжимаемые. Если у вас какие-то особые данные, первые байты которых плохо сжимаются, может иметь смысл дописать к ним в начале нулевых байт.
Узнать, какие стратегии хранения и какие алгоритмы сжатия используются для колонок заданной таблицы, можно при помощи \d+ .
Когда срабатывает правило про 1/4 страницы, PostgreSQL не пытается сжать и нарезать сразу все атрибуты кортежа. На самом деле, используется алгоритм из четырех проходов:
- Только для EXTENDED и EXTERNAL атрибутов. EXTENDED атрибуты сжимаются. Если размер атрибута превышает 2 Кб, он нарезается в TOAST-таблицу;
- Только для EXTENDED и EXTERNAL атрибутов. В этот раз все атрибуты нарезаются в TOAST-таблицу, безотносительно их размера;
- Только для MAIN атрибутов. Они сжимаются, но не нарезаются в TOAST-таблицу;
- Только для MAIN атрибутов. Если дело дошло до этого прохода, значит мы в беде. MAIN атрибуты нарезаются в TOAST-таблицу;
На каждом проходе атрибуты рассматриваются начиная с самого длинного и заканчивая самым коротким. Алгоритм останавливается в любой момент, как только кортеж умещается в 1/4 страницы. Полную реализацию можно посмотреть в heap_toast_insert_or_update().
На самом деле, по поводу 1/4 страницы — это не совсем правда. Значение по умолчанию задается макросом TOAST_TUPLE_TARGET. Для стандартного размера страницы 8 Кб значение макроса равно 2032 байта:
>>> page_size = 8192 # размер страницы
>>> page_header_size = 24 # размер заголовка страницы
>>> itemiddata_size = 4 # размер ItemIdData
>>> tuples_per_page = 4 # желаемое число кортежей на страницу
>>>
>>> temp = page_size
>>> temp -= page_header_size + itemiddata_size * tuples_per_page
>>> temp /= tuples_per_page
>>> temp = int(temp / 8) * 8
>>> temp
2032
Это число можно переопределить на уровне таблицы:
-- Значение 4080 посчитано, как показано выше, с tuples_per_page = 2
ALTER TABLE phonebook SET ( toast_tuple_target = 4080 ) ;
-- Чтобы вернуть значение по умолчанию:
ALTER TABLE phonebook RESET ( toast_tuple_target ) ;
Но тут есть важный момент! Переопределенный toast_tuple_target лишь говорит, до какого размера TOAST должен пытаться ужать кортеж. Он не определяет, когда TOAST должен сработать. Это значение вычисляется так же, как было показано выше, но пользователь не может его переопределить. В коде оно задается макросом TOAST_TUPLE_THRESHOLD.
Ни TOAST_TUPLE_THRESHOLD, ни toast_tuple_target не являются жестким ограничением на максимальный размер кортежа. Например, такой запрос:
= # SELECT format (
'CREATE TABLE foo AS SELECT %s' ,
array_to_string (
array_agg ( format ( '1 AS c%s' , col_num ) ) ,
', '
)
) FROM generate_series ( 1 , 1024 ) col_num
\gexec
… завершится вполне успешно, несмотря на то, что 1024 столбца с типом INT4 занимают больше половины страницы.
Есть еще одна тонкость, заслуживающая внимания. Рассмотрим такой пример:
CREATE TABLE test ( val VARCHAR ( 16 ) ) ;
SELECT c1 . oid , c1 . reltoastrelid , c2 . relname
FROM pg_class AS c1
LEFT JOIN pg_class AS c2
ON c1 . reltoastrelid = c2 . oid
WHERE c1 . relname = 'test' ;
Здесь TOAST-таблица не будет создана, несмотря на то, что в исходной таблице есть EXTENDED атрибут. PostgreSQL видит, что размер кортежа не может превысить TOAST_TUPLE_THRESHOLD, а значит TOAST-таблица не нужна. Если вы смените тип столбца на VARCHAR ( 4096 ) , то TOAST-таблица появится. Если снова смените тип на VARCHAR ( 16 ) , СУБД удалит TOAST-таблицу. Констрейнты предсказуемым образом не влияют на наличие или отсутствие TOAST-таблицы.
Выше говорилось про TOAST-указатели. Давайте разберемся, что конкретно они собой представляют:
typedef struct varatt_external
{
int32 va_rawsize ; /* исходный размер данных */
uint32 va_extinfo ; /* размер сжатых данных + алгоритм сжатия */
Oid va_valueid ; /* chunk_id в TOAST-таблице */
Oid va_toastrelid ; /* Oid TOAST-таблицы */
} varatt_external ;
Алгоритм сжатия (или его отсутствие) кодируется двумя старшими битами в поле va_extinfo .
Указатели хранятся в кортеже не просто так. Они являются частным случаем представления varlena типа. Всего же возможных представлений четыре:
xxxxxx00 xxxxxxxx xxxxxxxx xxxxxxxx, несжатые данные
xxxxxx10 xxxxxxxx xxxxxxxx xxxxxxxx, сжатые данные
00000001 xxxxxxxx, TOAST-указатель
xxxxxxx1, несжатые данные длиной до 126 байт
Это иллюстрация для little endian машин. На big endian (например, MIPS) данные кодируются чуть иначе, но принцип тот же.
Здесь xxx… — это биты, хранящие длину атрибута. Длина включает длину varlena заголовка. В первых двух случаях, когда длина кодируется 30-ю битами, varlena заголовок хранится с выравниванием до четырех байт. Для выравнивания используются нулевые байты. Таким образом, данные трактуются однозначно. Во втором случае, который со сжатыми данными, первый четыре байта полезной нагрузки хранят длину распакованных данных. Поскольку длина кодируется 30-ю битами, в varlena типе можно сохранить не более 1 Гб данных, минус 4 байта на длину заголовка. На практике это число несколько меньше, поскольку раньше возникнет ошибка «invalid memory alloc request size».
Заинтересованным читателям предлагается взять pageinspect, и проверить, что varlena данные действительно хранятся так, как описано выше. Подсказка — воспользуйтесь функцией tuple_data_split(). Занимательный факт: PostgreSQL хранит атрибуты с типом CHAR как varlena значения. Убедитесь в этом при помощи pageinspect. Как вы думаете, почему так сделано?
Таким образом, вместе с varlena заголовком TOAST-указатель занимает 18 байт. В битах xxx… будет записано именно это значение. Вспомним, что по умолчанию размер страницы составляет 8 Кб, минус накладные расходы на заголовок страницы и тд. Легко видеть, что создавать таблицы с сотнями столбцов varlena типов — не лучшая затея. Если увлечься, то при вставке новой строки можно получить ошибку «row is too big».
В настоящее время предпринимаются различные попытки улучшения TOAST. Предлагается добавить возможность инкрементального обновления данных, частичной распаковки и переиспользования общих данных между атрибутами. Есть также предложение сделать TOAST расширяемым. Это серьезные изменения, и на данном этапе остается много открытых вопросов. Например, не совсем понятно, как все это должно работать с table access methods. Тем не менее, что-то из названного может появиться в будущих версиях PostgreSQL.
Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.