[Из песочницы] Уменьшение объема, занимаемого данными PostgreSQL на диске10.01.2017 14:18

Обычно при составлении структур данных и таблиц никто не заморачивается порядком столбцов. Собственно, какой в этом смысл? При необходимости можно поменять порядок столбцов в 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. Оригинал доступен по ссылке.

Комментарии (3)

  • 10 января 2017 в 13:40 (комментарий был изменён)

    +1

    ↑

    ↓

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

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

    • 10 января 2017 в 14:00 (комментарий был изменён)

      0

      ↑

      ↓

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

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

      • 10 января 2017 в 14:13

        0

        ↑

        ↓

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

© Habrahabr.ru