Главная

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

2017-01-10 в 10:19, admin, рубрики: postgresql, размер данных

Обычно при составлении структур данных и таблиц никто не заморачивается порядком столбцов. Собственно, какой в этом смысл? При необходимости можно поменять порядок столбцов в SELECT, так о чем беспокоиться? Так вот, беспокоиться есть о чем, так как порядок столбцов может ощутимо влиять на размер таблицы. Да-да, размер таблицы может зависеть от порядка столбцов, даже если данные одни и те же.

Чем это объясняется? Существует такая вещь, как выравнивание данных CPU, и от нее зависит низкоуровневый размер структуры данных. Осознанный выбор порядка столбцов дает возможность оптимизировать размер данных. Не верите? Давайте попробуем:

test=# CREATE TABLE t_test (
            i1        int,
            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 0 10000000

Общий размер таблицы — 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),
            i1        int,
            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 0 10000000

… и таблица изрядно увеличится:

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. Оригинал доступен по ссылке.

Автор: tuffnatty

Источник