Зменшення обсягу, займаного даних PostgreSQL на диску

Зазвичай при складанні структур даних і таблиць ніхто не заморочується порядком стовпців. Власне, який у цьому сенс? При необхідності можна змінити порядок стовпців в SELECT, так про що турбуватися? Так от, є про що турбуватися, так як порядок стовпців може відчутно впливати на розмір таблиці. Так-так, розмір таблиці може залежати від порядку стовпців, навіть якщо дані одні і ті ж.

Чим це пояснюється? Існує така річ, як вирівнювання даних CPU, і від неї залежить низькорівневий розмір структури даних. Усвідомлений вибір порядку стовпців дає можливість оптимізувати розмір даних. Не вірите? Давайте спробуємо:

test=# CREATE TABLE t_test (
int i1,
i2 int,
i3 int,
v1 varchar(100),
v2 varchar(100),
v3 varchar(100)
);
CREATE TABLE

У цьому прикладі 6 стовпців. 3 цілочисельних стовпця один за іншим і 3 стовпця varchar, також один за іншим. Додамо в цю таблицю 10 мільйонів рядків:

test=# INSERT INTO t_test SELECT 10, 20, 30, 'abcd', 'abcd', 'abcd'
FROM generate_series(1, 10000000);
INSERT 10000000 0

Загальний розмір таблиці — 574 МБайт:

test=# SELECT pg_size_pretty(pg_relation_size('t_test'));
pg_size_pretty
----------------
574 MB
(1 row)

Спробуємо змінити розташування цих стовпців. У наступному прикладі після стовпця varchar йде стовпець integer. Це повторюється тричі:

test=# CREATE TABLE t_test (
v1 varchar(100),
int i1,
v2 varchar(100),
i2 int,
v3 varchar(100),
i3 int
);
CREATE TABLE

Тепер додамо 10 мільйонів рядків…

test=# INSERT INTO t_test SELECT 'abcd', 10, 'abcd', 20, 'abcd', 30
FROM generate_series(1, 10000000);
INSERT 10000000 0

… і таблиця неабияк збільшиться:

test=# SELECT pg_size_pretty(pg_relation_size('t_test'));
pg_size_pretty
----------------
651 MB
(1 row)

Дані в таблиці не змінилися – просто вони спеціально підібрані для ілюстрації цього ефекту. Якби я написав «abc» замість «abcd», ми не побачили б різниці в розмірі, але рядок з 4 символів вже не поміщається в буфері меншого розміру.

Висновок
Важливий висновок, який можна зробити з цього експерименту – упаковка однакових типів даних поруч один з одним, безумовно, має сенс. Крім того, я виявив, що має сенс пакувати стовпці integer на початку таблиць. Часто це може додати ще пару відсотків до продуктивності, просто з-за того, що структура даних трохи менше, ніж якщо цього не робити.

Від перекладача:

Автор статті: Hans-Jürgen Schönig. Оригінал доступний з посилання.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.