Уменьшение объема, занимаемого данными PostgreSQL на диске

[@Melkij]

Как внезапно кончилась статья. Я-то думал, дальше пойдёт описание заголовка строки в 23 байта, битовая маска NULL-значений, описание по какой границе данные выравниваются (хотя бы касательно только x86_64) и т.д.
Немножко по этой теме было на pgday'16, вот этот доклад начиная с 34 слайда. (выложено ли видео доклада — честно, не в курсе)

Об этом любопытно знать. Но проблема — добавить новое поле можно только в конец списка полей. В итоге табличка всё равно будет кучкой разных полей и заново разметить её можно только перезаписью всей таблицы, что весьма неудобно.

[@afiskon]

Я бы еще добавил к возможным подходам ZSON.

В случае с текстовыми данными нужно учитывать, что в TOAST уже есть LZ-подобное сжатие, поэтому все большие объемы данных и так жмутся. На выравнивании можно выиграть еще 1-2 процента от силы.

[@Melkij]

Если говорить именно об экономии места, то есть ещё не самый очевидный вариант: вместо много строк (23 байта заголовок каждой строки, сжатия нет) упаковать все похожие строки в массив (можно и массив композитных типов использовать! Константный заголовок на весь массив, остальное — чистые данные). И массив пойдёт в toast и дополнительно будет сжиматься. И по этому всё ещё можно адекватно искать.

[@tuffnatty]

ZSON — идея неплоха, но, к сожалению, работает только с JSONB-столбцами, а еще мне показалось, что 16 битов маловато.

Сжатие TOAST в постгресе практически никакое, в погоне за процессорными тактами был выбран алгоритм, эффективно сжимающий только совсем однообразные данные. Основная надежда тут — что появится сжатие на уровне страниц, да еще желательно с pluggable алгоритмами

В синтетическом примере в статье автор выигрывает на выравнивании 12%, а не 1-2.

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

[@homm]

Бесполезная статья. «Столбцы нужно упаковывать». Ну ок. А как?

[@tuffnatty]

Статья несколько сумбурная, но мне показался важным и неожиданным сам факт, на который указывает автор — что в дисковом формате данных используется data alignment.
Как паковать — как правило, пересозданием таблицы. Есть текст https://wiki.postgresql.org/wiki/Alter_column_position, в котором также упоминается, что physical layout can be optimized by putting fixed size columns at the start of the table, но без подробностей.

[@erwins22]

типы с фиксированным размером вначале, с переменным в конце.

[@nvv]

Сколько раз повторялся эксперимент, есть ли влияние упреждающего выделения места/страниц БД и др. параметров базы, такие как кодировка?

[@tuffnatty]

Ну, поведение вполне стабильно и естественно, но явным образом не описано в документации, или я не нашел — вот только есть тут (искать typalign) и в вики немного.

[@tuffnatty]

Но еще у автора поста есть книга, в которой делается предположение, что может влиять тип CPU.

[@baza906]

Справедливо только для Postgre? Есть у кого информация?

[@fifthsound]

В Oracle 12c второй вариант получился на 2мегабайта больше (320мб и 322мб).

[@silvercaptain]

В SQL Server вторая больше:
--415,563 MB
--425,536 MB

[@Timosha]

немного мякотки

( 
	select
		encode((select substring(get_raw_page('t_test2', 'main', 0) from (lp_off + t_hoff + 1) for (lp_len - t_hoff))), 'hex') as user_data
	from heap_page_items(get_raw_page('t_test2', 'main', 0)) as h limit 1
)
union all
(
	select
		encode((select substring(get_raw_page('t_test', 'main', 0) from (lp_off + t_hoff + 1) for (lp_len - t_hoff))), 'hex') as user_data
	from heap_page_items(get_raw_page('t_test', 'main', 0)) as h limit 1
)

и мы получаем как физически выглядят данные в тупле
t_test : 0a000000 14000000 1e000000 0b61626364 0b61626364 0b61626364
t_test2: 0b616263 64000000 0a000000 0b616263 64000000 14000000 0b616263 64000000 1e000000

в случае когда varchar'ы идут подряд не производится дополнительного выравнивания