Сжатие данных в PostgreSQL: как различные методы влияют на хранение TOAST

[SATORI-LAB]

Спасибо за статью. А есть графики сравнения разных алгоритмов сжатия для разных задач?

[dub0v]

Данный тест проводил только с текстовыми данными. Возможно в дальнейшем сделаю более расширенный тест.

[suprimex]

Почему бы просто не использовать файловую систему с возможностью сжатия "на-лету" и не размещать там базу с текстовыми данными?

[AshBlade]

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

[dim2r]

Уточненые формулировки, которые мы с коллегами выявили

EXTERNAL: Хранение вне строки без сжатия. Опция компрессии игнорируется.

EXTENDED: Позволяет как сжатие, так и хранение вне строки. Oпция колонки compression lz4, pglz влияет на размер.

MAIN: Хранит данные вне строки только в том случае, если они не могут быть уменьшены до допустимого размера. Oпция колонки compression lz4, pglz влияет на размер.

PLAIN: Хранение в основном файле как есть. Не допускает сжатия и хранения вне строки. Не создается доп файл для TOAST.

-----------EXTERNAL
drop table my1;
create  table                my1 (a text storage EXTERNAL/*no compression*/ )  ;
insert into my1 select repeat('1234567890',10000000);
vacuum full my1;
checkpoint;
select pg_size_pretty( pg_total_relation_size('my1'));


drop table my2;
create  table                my2 (a text storage EXTERNAL compression lz4 /*не влияет*/)  ;
insert into my2 select repeat('1234567890',10000000);
vacuum full my2;
checkpoint;
select pg_size_pretty( pg_total_relation_size('my2'));
select pg_size_pretty( pg_total_relation_size('my1')-pg_total_relation_size('my2')) diff;
-- показывает одинаковые размеры

-------------EXTENDED
drop table my1;
create  table                my1 (a text storage EXTENDED/*no compression*/ )  ;
insert into my1 select repeat('1234567890',10000000);
vacuum full my1;
checkpoint;
select pg_size_pretty( pg_total_relation_size('my1'));


drop table my2;
create  table                my2 (a text storage EXTENDED compression lz4 /*влияет*/)  ;
insert into my2 select repeat('1234567890',10000000);
vacuum full my2;
checkpoint;
select pg_size_pretty( pg_total_relation_size('my2'));
select pg_size_pretty( pg_total_relation_size('my1')-pg_total_relation_size('my2')) diff;
-- показывает разные размеры

------------ MAIN
drop table my1;
create  table                my1 (a text storage MAIN/*no compression*/ )  ;
insert into my1 select repeat('1234567890',10000000);
vacuum full my1;
checkpoint;
select pg_size_pretty( pg_total_relation_size('my1'));


drop table my2;
create  table                my2 (a text storage MAIN compression lz4 /*влияет*/)  ;
insert into my2 select repeat('1234567890',10000000);
vacuum full my2;
checkpoint;
select pg_size_pretty( pg_total_relation_size('my2'));
select pg_size_pretty( pg_total_relation_size('my1')-pg_total_relation_size('my2')) diff;
-- показывает разные размеры

[y_u_h]

Из документации https://postgrespro.ru/docs/postgresql/17/storage-toast

TOAST занимает два бита слова длины varlena (старшие биты на машинах с порядком байт от старшего к младшему, или младшие биты — при другом порядке байт), таким образом, логический размер любого значения в формате TOAST ограничивается 1 Гигабайтом (230 - 1 байт).

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