JSONB && TOAST in PostgreSQL

В предыдущей статье мы разобрали как устроен TOAST.

Следующий эксперимент – убедиться, что JSONB в TOAST довольно медленный и подвержен bloating. Сгенерируем таблицу 10 тысяч строк из JSONB объектов размером по 10 тысяч элементов:

\timing

CREATE TABLE t AS

SELECT i AS id, (SELECT jsonb_object_agg(j, j) FROM generate_series(1, 1000) j) js

FROM generate_series(1, 10000) i;

Посмотрим на размер сгенерированных данных:

SELECT oid::regclass AS heap_rel,

       pg_size_pretty(pg_relation_size(oid)) AS heap_rel_size,

       reltoastrelid::regclass AS toast_rel,

       pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast_rel_size

FROM pg_class WHERE relname = ‘t’;

Сама таблица будет занимать 512 Кб, а хранилище TOAST — 78 Мб. JSON будет 19 Кб, и он сжимается в 6 Кб, которые займут 4 чанка в TOAST (чанки размером по 2 Кб – параметр TOAST_MAX_CHUNK_SIZE).

Дальше давайте проапдейтим колонку id — она маленькая, находится отдельно и никогда не попадет в TOAST, заодно посмотрим LpostgresqloSN журнала WAL:

\d+ t

SELECT pg_current_wal_lsn();

UPDATE t SET id = id + 1;

SELECT pg_current_wal_lsn();

Всего 23 мс и посмотрим на размер сгенерированного журнала WAL:

SELECT pg_size_pretty(pg_wal_lsn_diff(‘0/1FADE3B0′,’0/1F96A060’)) AS wal_size;

WAL  будет всего 1,5 Мб, то есть 150 байт на запись.

При этом размер TOAST не изменится — как был 78 Мб, так и останется.

И теперь давайте проапдейтим JSON и посмотрим на скокрость и размер журнала:

SELECT pg_current_wal_lsn();

UPDATE t SET js = js::jsonb || ‘{“a”:1}’;

SELECT pg_current_wal_lsn();

SELECT pg_size_pretty(pg_wal_lsn_diff(‘0/26818180′,’0/1FADE3B0’)) AS wal_size;

Это займет 23 секунды, то есть станет в 1000 раз медленней (в среднем от 300 до 500 раз), а размер  WAL увеличится до 106 Мб вместо 1,5 Мб.

Хранилище TOAST соответственно увеличилось в 2 раза: 

То есть, мы сделали маленький апдейт и у нас сразу же возникли проблемы.

Имеем распухание – bloating. Простые методы борьбы с этим не помогут, только VACUUM FULL, который нереально применить на рабочей системе из-за блокировок. Какие есть более эффективные методы борьбы с bloating разберём на курсе.

---