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

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

1. Маленькие тексты. Имена и фамилии людей, заголовки страниц, имена пользователей, адреса электронной почты и прочее подобное. Обычно на размер таких полей накладываются определённые ограничения, довольно сильные. Возможно, это даже не поля типа text, а поля типа varchar(n).
2. Большие тексты. Это, например, содержимое публикаций в блогах, тексты статей, HTML-код. Такие данные представляют собой большие фрагменты текста неограниченной длины, хранящиеся в базе данных.
3. Тексты среднего размера. Это — описания, комментарии, отзывы о товарах, данные о трассировке стека и так далее. В сущности это — любые текстовые поля, размер которых находится между размерами «маленьких» и «больших» текстов. Обычно размер таких полей не ограничен, но их содержимое, по естественным причинам, меньше, чем содержимое полей категории «большие тексты».

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

В данный момент 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-таблицу.

Существует и ещё одна полезная функция — pg_size_pretty, которая выводит данные в удобочитаемом виде.

▍Сжатие данных в 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, используемые по умолчанию.

Производительность 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 id = 6000;
                                    QUERY 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 id = 6000;
Execution Time: 25.865 ms

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

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

db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_medium WHERE id = 6000;
Execution Time: 321.965 ms

db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_medium WHERE id = 6000;
Execution Time: 173.058 ms

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

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

db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_large WHERE id = 6000;
Execution Time: 49.867 ms

db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_large WHERE id = 6000;
Execution 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 id = 6000;
                                QUERY 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 id = 6000;
                                QUERY 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 id = 6000;
                                QUERY 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 <= 250000))
Time: 60.766 ms
db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_small WHERE id BETWEEN 0 AND 250000;
Time: 59.705 ms

db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_medium WHERE id BETWEEN 0 AND 250000;
Time: 3198.539 ms (00:03.199)
db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_medium WHERE id BETWEEN 0 AND 250000;
Time: 284.339 ms

db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_large WHERE id BETWEEN 0 AND 250000;
Time: 85.747 ms
db=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_large WHERE id BETWEEN 0 AND 250000;
Time: 70.364 ms

Мы выполнили запрос на получение половины данных, хранящихся в таблице. Это, с одной стороны, достаточно малая часть таблицы, поэтому 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-системах обычно выполняется чтение одной строки или нескольких строк. Поэтому хранение данных, ориентированное на строки, лучше подходит для этих систем.

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

Автор: ru_vds

Источник