Наполняем до краев: влияние порядка столбцов в таблицах на размеры баз данных PostgresQL

в 12:00, , рубрики: 8 байтов, alignment, выравнивание, порядок столбцов, сжатие таблиц

8 + 2 = 16 или откуда берутся лишние байты

В языках низкого уровня, таких как C, на котором написан Postgres, для обращения к данным в памяти всегда используются стандартные размеры, независимо от того, сколько места на самом деле занимают элементы данных. Например, стандартное 32-битное целое число, которое может хранить значение немного более четырёх миллиардов, всегда считывается как четыре байта. То есть, даже если значение числа равно нулю, под него выделяется четыре байта памяти. Это называется выравнивание (alignment).

Postgres тоже использует размер выравнивания в восемь байтов. Как следствие, в ряде случаев данные в идущих подряд столбцах фиксированного размера дополняются пустыми байтами для выравнивания. Пример:

SELECT 
  pg_column_size(row()) AS empty, 
  pg_column_size(row(0::SMALLINT)) AS byte2, 
  pg_column_size(row(0::BIGINT)) AS byte8, 
  pg_column_size(row(0::SMALLINT, 0::BIGINT)) AS byte16
;
--------------------------------
 empty | byte2 | byte8 | byte16 
-------+-------+-------+--------
    24 |    26 |    32 |     40

Пустая строка занимает 24 байта (заголовок записи), SMALLINT занимает два байта, BIGINT — восемь байтов, а вместе они дают… 16 байтов? Это не ошибка: меньший по размеру столбец дополняется пустыми байтами для соответствия размеру следующего столбца в целях выравнивания и вместо 2 + 8 = 10 получаем 8 + 8 = 16.

Пока что это не является проблемой, но представим себе некую систему работы с заказами, в которой используется вот такая таблица:

CREATE TABLE user_order (
  is_shipped    BOOLEAN NOT NULL DEFAULT false,
  user_id       BIGINT NOT NULL,
  order_total   NUMERIC NOT NULL,
  order_dt      TIMESTAMPTZ NOT NULL,
  order_type    SMALLINT NOT NULL,
  ship_dt       TIMESTAMPTZ,
  item_ct       INT NOT NULL,
  ship_cost     NUMERIC,
  receive_dt    TIMESTAMPTZ,
  tracking_cd   TEXT,
  id            BIGSERIAL PRIMARY KEY NOT NULL
);

Выглядит, конечно, странно, но так и задумано. Допустим, таблица была набросана в спешке разработчиком, а может быть ему помогла ORM. Нам таблица нужна для иллюстрации. Вот как видит таблицу Postgres:

SELECT a.attname, t.typname, t.typalign, t.typlen
  FROM pg_class c
  JOIN pg_attribute a ON (a.attrelid = c.oid)
  JOIN pg_type t ON (t.oid = a.atttypid)
WHERE c.relname = 'user_order'
   AND a.attnum >= 0
 ORDER BY a.attnum;

-----------------------------------------------
   attname   |   typname   | typalign | typlen 
-------------+-------------+----------+--------
 is_shipped  | bool        | c        |      1
 user_id     | int8        | d        |      8
 order_total | numeric     | i        |     -1
 order_dt    | timestamptz | d        |      8
 order_type  | int2        | s        |      2
 ship_dt     | timestamptz | d        |      8
 item_ct     | int4        | i        |      4
 ship_cost   | numeric     | i        |     -1
 receive_dt  | timestamptz | d        |      8
 tracking_cd | text        | i        |     -1
 id          | int8        | d        |      8

В столбце typalign показан тип выравнивания (значения перечислены в документации для pg_type). Для каждого типа с фиксированным размером используется его стандартный размер в байтах. Для NUMERIC и TEXT ситуация чуть сложнее; к ним вернемся попозже. А пока посмотрим как порядок полей фиксированного размера отражается на таблице.

Ровняем под стандарт

Для упрощения работы с данными, Postgres дополнит каждый столбец, меньший чем стандартный размер выравнивания (8 байтов) так, чтобы он соответствовал размеру столбца, следующего за ним. В результате в данной таблице между каждой парой столбцов используются пустые байты в роли наполнителя.

Зальём миллион записей и проверим размеры таблицы:

INSERT INTO user_order (
    is_shipped, user_id, order_total, order_dt, order_type,
    ship_dt, item_ct, ship_cost, receive_dt, tracking_cd
)
SELECT true, 1000, 500.00, now() - INTERVAL '7 days',
       3, now() - INTERVAL '5 days', 10, 4.99,
       now() - INTERVAL '3 days', 'X5901324123479RROIENSTBKCV4'
FROM generate_series(1, 1000000);

SELECT pg_relation_size('user_order') AS size_bytes,
       pg_size_pretty(pg_relation_size('user_order')) AS size_pretty;

--------------------------
 size_bytes | size_pretty 
------------+-------------
  141246464 | 135 MB

Берём эти цифры за отправную точку. Вопрос: сколько байтов таблицы использованы для выравнивания, то есть бесполезны? Вот оценка количества пустых байтов по данным выравнивания:

  • 7 байт между is_shipped и user_id

  • 4 байта между order_total и order_dt

  • 6 байт между order_type и ship_dt

  • 4 байта между receive_dt и id

Получается 21 пустой байт в каждой записи; при этом используемые данные занимают 59 байтов, а общая длина записи составляет 80 байтов без учета заголовка. Пока используем только данные выравнивания для типов фиксированного размера. Данные с переменным размером: NUMERIC и TEXT меняют эту оценку, но для первого приближения она годится. Если эта оценка даже приблизительно верна, то мы можем уменьшить размер таблицы на (21/80 =) 26% или на 37 Мб. Стоит копать дальше.

Данные с переменным размером

Теперь посмотрим на типы NUMERIC и TEXT. Поскольку это типы переменного размера, они обрабатываются по‑другому. Возьмем NUMERIC: 

SELECT pg_column_size(row()) AS empty_row,
       pg_column_size(row(0::NUMERIC)) AS zero_val,
       pg_column_size(row(1::NUMERIC)) AS no_dec,
       pg_column_size(row(1.1::NUMERIC)) AS with_dec,
       pg_column_size(row(1::INT4)) AS int4,
       pg_column_size(row(1::NUMERIC, 1::INT4)) AS num_int4,
       pg_column_size(row(1::INT4, 1::NUMERIC)) AS int4_num,
       pg_column_size(row(1.1::NUMERIC, 1::INT4)) AS num_dec_int4,
       pg_column_size(row(1::INT4, 1.1::NUMERIC)) AS int4_num_dec
;

------------------------------------------------------------------------------------------------------
 empty_row | zero_val | no_dec | with_dec | int4 | num_int4 | int4_num | num_dec_int4 | int4_num_dec | 
------------------------------------------------------------------------------------------------------
        24 |       27 |     29 |       31 |   28 |       36 |       33 |           36 |           35 |

Похоже, что данные в столбце NUMERIC не влияют на выравнивание предыдущего столбца, но влияют на выравнивание фиксированного столбца, следующего за NUMERIC.

Теперь TEXT:

SELECT pg_column_size(row()) AS empty_row,
       pg_column_size(row(''::TEXT)) AS no_text,
       pg_column_size(row('a'::TEXT)) AS min_text,
       pg_column_size(row('a'::TEXT, 1::INT4)) AS txt_smint,
       pg_column_size(row('ab'::TEXT, 1::INT4)) AS txt2_smint,
       pg_column_size(row('abc'::TEXT, 1::INT4)) AS txt3_smint,
       pg_column_size(row('abcd'::TEXT, 1::INT4)) AS txt4_smint,
       pg_column_size(row('abcde'::TEXT, 1::INT4)) AS txt5_smint,
       pg_column_size(row(1::INT4, 'a'::TEXT)) AS smint_txt,
       pg_column_size(row(1::INT4, 'ab'::TEXT)) AS smint_txt2,
       pg_column_size(row(1::INT4, 'abc'::TEXT)) AS smint_txt3,
       pg_column_size(row(1::INT4, 'abcd'::TEXT)) AS smint_txt4,
       pg_column_size(row(1::INT4, 'abcde'::TEXT)) AS smint_txt5
;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 empty_row | no_text | min_text | txt_smint | txt2_smint | txt3_smint | txt4_smint | txt5_smint | smint_txt | smint_txt2 | smint_txt3 | smint_txt4 | smint_txt5 | 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
        24 |      25 |       26 |        32 |         32 |         32 |         36 |         36 |        30 |         31 |         32 |         33 |         34 |

Отсюда видно, что столбец TEXT дополнится до следующего ближайшего числа, кратного размеру, следующего за ним столбца фиксированного размера (четыре байта для INT4, восемь байтов для INT8). А вот если TEXT идет после столбца с фиксированным размером, то его размер не округляется.

То есть столбцы с данными переменного размера не оказывают влияния на выравнивание, если находятся в конце списка столбцов.

Данные с фиксированным размером

Если к столбцу фиксированного размера добавляются пустые байты для выравнивания по размеру следующего столбца, это означает, что самые «широкие» фиксированные типы должны идти первыми. Но, можно и комбинировать столбцы так, чтобы их суммарная ширина составляла восемь байтов.

SELECT pg_column_size(row()) AS empty_row,
       pg_column_size(row(1::SMALLINT)) AS int2,
       pg_column_size(row(1::INT)) AS int4,
       pg_column_size(row(1::BIGINT)) AS int8,
       pg_column_size(row(1::SMALLINT, 1::BIGINT)) AS padded,
       pg_column_size(row(1::INT, 1::INT, 1::BIGINT)) AS not_padded
;

------------------------------------------------------
 empty_row | int2 | int4 | int8 | padded | not_padded 
-----------+------+------+------+--------+------------
        24 |   26 |   28 |   32 |     40 |         40

Как вам?

Тетрис со столбцами

Задача оптимизации хранения заключается в подборе такого порядка столбцов, который минимизирует количество пустых байтов. И из всего вышесказанного следует рекомендация: «Используйте порядок столбцов по убыванию ширины хранящегося в них типа данных из pg_type». Этот порядок получаем, изменив сортировку в запросе для вывода столбцов и их типов:

SELECT a.attname, t.typname, t.typalign, t.typlen
  FROM pg_class c
  JOIN pg_attribute a ON (a.attrelid = c.oid)
  JOIN pg_type t ON (t.oid = a.atttypid)
 WHERE c.relname = 'user_order'
   AND a.attnum >= 0
 ORDER BY t.typlen DESC
;

-----------------------------------------------
   attname   |   typname   | typalign | typlen 
-------------+-------------+----------+--------
 id          | int8        | d        |      8
 user_id     | int8        | d        |      8
 order_dt    | timestamptz | d        |      8
 ship_dt     | timestamptz | d        |      8
 receive_dt  | timestamptz | d        |      8
 item_ct     | int4        | i        |      4
 order_type  | int2        | s        |      2
 is_shipped  | bool        | c        |      1
 tracking_cd | text        | i        |     -1
 ship_cost   | numeric     | i        |     -1
 order_total | numeric     | i        |     -1

Применим этот порядок к нашей таблице:

DROP TABLE user_order;

CREATE TABLE user_order (
  id            BIGSERIAL PRIMARY KEY NOT NULL,
  user_id       BIGINT NOT NULL,
  order_dt      TIMESTAMPTZ NOT NULL,
  ship_dt       TIMESTAMPTZ,
  receive_dt    TIMESTAMPTZ,
  item_ct       INT NOT NULL,
  order_type    SMALLINT NOT NULL,
  is_shipped    BOOLEAN NOT NULL DEFAULT false,
  order_total   NUMERIC NOT NULL,
  ship_cost     NUMERIC,
  tracking_cd   TEXT
);

И повторим заливку миллиона записей

INSERT INTO user_order (
    is_shipped, user_id, order_total, order_dt, order_type,
    ship_dt, item_ct, ship_cost, receive_dt, tracking_cd
)
SELECT true, 1000, 500.00, now() - INTERVAL '7 days',
       3, now() - INTERVAL '5 days', 10, 4.99,
       now() - INTERVAL '3 days', 'X5901324123479RROIENSTBKCV4'
FROM generate_series(1, 1000000);

SELECT pg_relation_size('user_order') AS size_bytes,
       pg_size_pretty(pg_relation_size('user_order')) AS size_pretty;

--------------------------
 size_bytes | size_pretty 
------------+-------------
  117030912 | 112 MB

Просто поменяв порядок столбцов, мы сэкономили 21% дискового пространства. От себя добавлю, что вставка прошла почти в два раза быстрее: 5,2 с против 8,7 с в первоначальном варианте.

Для одной таблицы это может быть и несущественно, но повторите это для всех таблиц и это, наверняка, приведет к существенному сокращению требований к емкости дисков и размеру оперативной памяти. В больших хранилищах данных можно рассчитывать на уменьшение объема на 10–20%. Я видел базы Postgres объемом под 60 ТБ; представьте что вам подарили дисков на 6–12 ТБ.

Есть старая притча о том как наполнить пустую банку: сначала камни, потом песок, потом вода. Примерно так же работает создание таблицы в Postgres с учетом выравнивания: сначала идут широкие столбцы, затем средние, маленькие столбцы в последнюю очередь, а столбцы переменного размера, такие как NUMERIC и TEXT, в самом конце. Таковы правила, которые диктуют указатели.

Но, допустим, нам захотелось использовать другой, более наглядный, порядок, например, такой:

CREATE TABLE user_order (
  id            BIGSERIAL PRIMARY KEY NOT NULL,
  user_id       BIGINT NOT NULL,
  order_type    SMALLINT NOT NULL,
  order_total   NUMERIC NOT NULL,
  order_dt      TIMESTAMPTZ NOT NULL,
  item_ct       INT NOT NULL,
  ship_dt       TIMESTAMPTZ,
  is_shipped    BOOLEAN NOT NULL DEFAULT false,
  ship_cost     NUMERIC,
  tracking_cd   TEXT,
  receive_dt    TIMESTAMPTZ
);

В этом случае получим на 8 МБ или 7,7% больше чем в идеале. Реальная таблица, вероятно, будет находиться где‑то между лучшим и худшим сценариями, и единственным способом настройки баланса эффективности и наглядности остаётся изменение порядка столбцов вручную.

Законный вопрос и вывод

Почему это до сих пор не встроено в Postgres? Наверняка можно обеспечить разделение физического и логического представлений порядка столбцов: физическое — в том порядке, который нужен для минимизации пустых байтов, логическое — в том, который хочет видеть разработчик. Вопрос хороший, но ответа на него нет.

Видимо, дело в приоритетах. Еще в 2006 году существовал пункт TODO для этой проблемы. С тех пор патчи и релизы выходили постоянно, но проблема остается. Вероятно, решить ее довольно сложно, а в пруду с TODO плавает рыба и покрупнее. (От себя — все скрипты проверены на Postgres 15.1 в 2023; цифры полностью совпали).

Но, уж если эта особенность существует, то учесть её несложно: всего один запрос покажет идеальный порядок столбцов, что может обозначить приличную прибавку к эффективности.

Автор:
tba

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js