pg toast что за таблица

Неожиданное влияние текстов среднего размера на производительность PostgreSQL

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

В этом материале я хочу рассказать о неожиданном влиянии текстов среднего размера на производительность запросов в 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, посмотрим на этот механизм в действии. Начнём с создания таблицы с текстовым полем:

▍TOAST в действии

Посмотрим на таблицу TOAST:

Она состоит из трёх столбцов:

В данный момент TOAST-таблица пуста:

Это и понятно — ведь мы пока ничего не добавляли в основную таблицу. Давайте добавим в неё какое-нибудь значение небольшого размера:

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

Маленький текст хранится в основной таблице

А теперь давайте добавим в таблицу большой текст и посмотрим, что произойдёт.

Я, чтобы не перегружать статью, сократил текст, вставляемый в таблицу. На самом деле это — строка, состоящая из 4096 случайных символов. Посмотрим на то, что теперь хранится в TOAST-таблице:

Как оказалось, большое значение хранится за пределами основной таблицы, в TOAST-таблице. Так как это значение слишком велико и не помещается в одну строку, PostgreSQL разделила это значение на три порции. Записи вида \x3063… — это то, как psql выводит двоичные данные.

Большой текст сохранён за пределами основной таблицы в связанной с ней TOAST-таблице

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

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

Существует несколько способов узнать размер объектов баз данных при работе с PostgreSQL. А именно, речь идёт о следующих функциях:

▍Сжатие данных в TOAST-таблицах

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

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

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

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

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

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

Тут мы добавили в таблицу строку, представляющую собой 10 Кб случайных символов. Посмотрим на таблицу TOAST:

Это значение сохранено в TOAST-таблице, за пределами основной таблицы. Видно, что оно хранится в несжатом виде.

Теперь вставим в таблицу ещё одно значение такой же длины, но составленное из небольшого количества символов:

А теперь вставим в таблицу длинную строку, при составлении которой используется лишь один цифровой символ:

Эту строку удалось сжать так хорошо, что СУБД смогла сохранить её в основной таблице.

▍Настройка TOAST-таблиц

Производительность TOAST

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

Так же, как и в предыдущем разделе статьи, для каждой таблицы PostgreSQL создаёт TOAST-таблицу:

▍Тестовые данные

Добавим в таблицу toast_test_small 500 тысяч строк, содержащих маленькие тексты, которые могут быть сохранены в основной таблице:

Теперь добавим в таблицу toast_test_medium 500 тысяч строк, содержащих тексты среднего размера. Размеры этих строк находятся близко к размерам строк, которые сохраняются за пределами основной таблицы, но они при этом такие строки всё ещё могут быть сохранены в основной таблице:

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

Далее, добавим 500 тысяч строк, содержащих длинные тексты, в таблицу toast_test_large :

Сейчас мы готовы к проведению эксперимента.

▍Сравнение производительности работы с разными таблицами

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

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

Начнём с маленькой таблицы:

Я выполнил этот запрос много раз и, чтобы не перегружать статью, сократил выходные данные. Тут, как и ожидалось, СУБД выполняет полное сканирование таблицы. В итоге время выполнения запроса стабилизировалось примерно на 25 мс.

Теперь изучим производительность таблицы среднего размера:

Выполнение точно такого же запроса на таблице среднего размера заняло значительно больше времени. А именно — 173 мс, что примерно в 6 раз медленнее, чем в случае с маленькой таблицей. Это — серьёзное различие.

Завершим тесты, выполнив такой же запрос к большой таблице:

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

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

▍Осмысление результатов

Для того чтобы понять полученные результаты предлагаю взглянуть на размеры самих таблиц и связанных с ними TOAST-таблиц:

Проанализируем эти данные.

Таблица toast_test_small

Размер таблицы toast_test_small составляет 21 Мб. При работе с ней TOAST-таблица не используется. Это понятно — ведь в таблицу мы добавили маленькие тексты, которые сохраняются прямо в этой таблице.

Тексты маленького размера хранятся в основной таблице

Таблица toast_test_medium

Размер таблицы toast_test_medium значительно больше — 977 Мб. Мы заполнили её строками, которые имеют длину, близкую к той, после достижения которой значения сохраняются в TOAST-таблице, но не превышающую её. В результате таблица получилась очень большой, но при этом TOAST-таблица для хранения данных не использовалась.

Тексты среднего размера хранятся в основной таблице

Таблица toast_test_large

Большие тексты были сохранены в TOAST-таблице

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

▍Работа с содержимым текстовых полей

Выше мы исследовали производительность базы данных, выполняя запросы с использованием идентификаторов ( id ), а не значений, хранящихся в текстовых полях. Что произойдёт в том случае, если нужно работать с самими текстовыми значениями?

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

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

▍Как насчёт индексов?

Выполним к таблицам те же запросы, что выполняли ранее:

Здесь, во всех трёх запросах, использовался индекс. Можно видеть, что скорость выполнения запросов во всех трёх случаях практически идентична.

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

Мы выполнили запрос на получение половины данных, хранящихся в таблице. Это, с одной стороны, достаточно малая часть таблицы, поэтому PostgreSQL решает воспользоваться индексом, но, с другой стороны, данных тут достаточно много для того чтобы системе пришлось бы выполнить большой объём операций ввода-вывода.

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

Мы выполнили запросы к каждой таблице по два раза. Во всех случаях СУБД использует индекс для работы с таблицами. Учитывайте то, что индекс помогает лишь в снижении количества страниц, к которым нужно обращаться базе данных, но в данном случае системе нужно ещё и прочитать половину таблицы.

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

Варианты решения проблемы

Если вы дочитали до этого места и убедились в том, что тексты среднего размера — это то, что вызывает проблемы с производительностью, вы, вероятно, размышляете о том, как бороться с этими проблемами. Именно об этом я и хочу тут рассказать.

▍Настройка toast_tuple_target

Параметр toast_tuple_target управляет минимальной длиной кортежа, по достижении которой PostgreSQL пытается переместить длинное значение в TOAST-таблицу. По умолчанию тут установлено 2 Кб, но это значение можно уменьшать — вплоть до 128 байт. Чем меньше это значение — тем больше шансов на то, что строка среднего размера будет храниться не в основной таблице, а в TOAST-таблице.

Для того чтобы продемонстрировать пример применения этой рекомендации, я создал пару таблиц. В одной параметры хранения данных оставлены в стандартном состоянии, а при создании другой я записал в toast_tuple_target значение 128 :

Далее, я заполнил таблицы значениями, размеры которых, в несжатом виде, превышают 2 Кб, а в сжатом оказываются меньше, чем 128 байт. После этого я проверил то, хранятся ли эти значения в основных таблицах или в соответствующих им TOAST-таблицах:

▍Создание отдельной таблицы

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

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

Итоги

Главная проблема текстов среднего размера заключается в том, что при их сохранении в базе данных строки становятся слишком длинными. Это плохо, так как PostgreSQL, как и другие OLTP-ориентированные базы данных, хранят значения, организуя данные по строкам. Когда мы просим СУБД выполнить запрос по небольшому количеству столбцов, весьма вероятно то, что значения этих столбцов будут разбросаны по множеству блоков. Если строки таблицы достаточно длинны, это приводит к необходимости выполнения большого объёма операций ввода-вывода, что влияет на производительность запросов и на объём используемых системных ресурсов.

Для решения этой проблемы некоторые базы данных, не ориентированные на OLTP, используют другие схемы хранения данных: система при хранении данных ориентируется не на строки, а на столбцы. При таком подходе в ситуации, когда СУБД нужно просканировать некий столбец, то окажется, что значения, хранящиеся в этом столбце, находятся в последовательно расположенных блоках. Обычно это приводит к тому, что для работы с такими значениями приходится выполнять меньше операций ввода-вывода. Кроме того, данные, хранящиеся в одном столбце, вполне возможно, будут содержать повторяющиеся паттерны и значения, а значит — будут лучше поддаваться сжатию.

Базы данных, которые при хранении данных ориентируются на строки и на столбцы

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

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

Источник

Sysadminium

База знаний системного администратора

Слои данных и TOAST таблицы в PostgreSQL

Разберем что хранится в файлах данных. Каждая таблица состоит из нескольких слоёв. Слои данных в PostgreSQL это как минимум 1 файл. Подробнее про физическое хранение базы данных можете почитать тут.

Каждый файл занимает не больше 1 GB и кратен 8 KB. Поэтому если таблица больше 1 GB, то она хранится в нескольких файлах. Файлы состоят из 8 KB страниц, которые в случае необходимости помещаются в буферный кэш.

Существуют следующие слои:

Работа с большими строками (TOAST)

В PostgreSQL одна строка должна помещаться в одну страницу, то есть не быть больше 8 КБ. Чтобы поместить большую строку у PostgreSQL есть следующие стратегии:

Механизм работы с большими строками называется – TOAST. Внешняя таблица в которую по кусочкам помещают длинную строку называют TOAST-таблица.

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

Разделение и склеивание длинных строк PostgreSQL делает самостоятельно, то есть вам не нужно обо всем этом задумываться. Вы просто пишите запрос (SELECT), а PostgreSQL склеивает из нескольких частей длинную строку.

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

TOAST-таблица имеет свою схему pg_toast. А если это временная таблица, то pg_toast_temp_N.

Если в табличке есть поле с типом, куда может поместиться большое значение (numeric, text и т.д.), то TOAST-таблица создается сразу (как бы на всякий случай). Но до помещения больших атрибутов в TOAST-таблицу, она будет пустой.

Практика

Создадим базу данных и подключимся к ней. Затем сделаем там таблицу “t” и в неё вставим 10000 строк:

Посмотрим на файлы таблицы

С помощью функции pg_relation_filepath( ) можно узнать в каком файле находится объект. Узнаем в каком файле находится наша табличка (относительно каталога PGDATA):

Первое число (16494) – это идентификатор базы, второе (16497) – идентификатор таблички.

Этот путь можно было найти вручную. Так как таблица находится в табличном пространстве pg_default, то файл должен лежать в каталоге $PGDATA/base. Дальше нужно найти идентификаторы базы и таблицы следующими способами:

Идентификатор базы мы вытаскиваем из таблички pg_database, а идентификатор таблички из pg_class.

Теперь посмотрим на сами файлы, предварительно закрыв psql:

Каждый файл не может быть больше 1 GB и кратен 8 KB. Если таблица не помещается в 1 GB, создается дополнительный файл.

Посмотрим на файлы индексов

Теперь посмотрим на индексы. Так как создавая табличку “t” мы сделали поле “id” как публичный ключ, то под него создался индекс.

Подключимся снова к базе data_lowlevel и посмотрим на объект “t“:

Выше мы видим что индекс называется “t_pkey“.

Теперь с помощью уже знакомой функции pg_relation_filepath() определим путь к файлу этого объекта. И посмотрим на файлы:

Как видим пока есть только 1 файл. fsm у индекса может быть, а vm – нет.

Про расширение oid2name

Существует расширение oid2name, которое позволяет сопоставить объекты и файлы.

Скомпилируем это расширение:

Без ключей выдаст список баз данных:

Можно посмотреть на объекты в базе данных. Например на табличные пространства в базе data_lowlevel:

Можно по имени таблицы узнать её идентификатор, или по идентификатору узнать имя таблицы:

Узнаем размер объектов

С помощью функции pg_relation_size(‘ ’, ‘ ’) можем узнать размер слоя:

Посмотреть размер таблицы без учета индексов можно с помощью функции pg_table_size():

А размер индексов можно посмотреть с помощью функции pg_indexes_size():

И полный размер таблицы вместе с индексами можно посмотреть с помощью функции pg_total_relation_size():

Таблицы TOAST

Тип данных numeric может работать с очень большими числами. Например число 123456789 в степени 12345 будет содержать 99907 цифр:

Такое число в одну строку (8 KB) не поместится. Значит для этого поля PostgreSQL создаст служебную TOAST-таблицу.

Теперь поместим это число в базу data_lowlevel в табличку “t”:

Найдем эту TOAST-таблицу:

Выше мы из таблички pg_class вытащили все идентификаторы TOAST-таблиц, для таблицы “t”. А дальше по идентификатору (16501) узнали имя таблички (pg_toast_16497).

Файл для TOAST таблицы можем тоже найти:

Выше я узнал каталог нашей базы данных base/16494/. А затем используя идентификатор таблички (pg_toast_16497 | 16501) посмотрел на файлы. Как видим основной слой TOAST-таблицы весит 57 KB. У этого объекта может быть fsm и vm слои. В этой табличке пока только одно большое число, которое не поместилось в поле (8 KB) обычной таблички “t”.

Стратегии работы с большими строками

В теоретической части этой статьи мы уже разобрали что большие строки либо сжимаются, либо помещаются в TOAST-таблицу. Какая текущая стратегия работы для таблицы “t“? Можем посмотреть так:

Стратегия указывается в поле Storage. Выше мы видим что поле id имеет стратегию “plain“. Так как это поле имеет тип integer, то оно не может иметь большое значение и TOAST таблицы отключены. А поле n имеет тип numeric, и стратегию main – приоритет сжатия.

Возможные типы стратегий:

Поменять стратегию для таблицы можно с помощью ALTER TABLE … ALTER COLUMN … SET STORAGE …:

Но эта команда не изменит существующие данные, а повлияет на работу с новыми данными.

Источник