Порядок следования столбцов в таблицах PostgreSQL

Порядок столбцов в таблицах влияет на компактность и производительность. При небольшом числе строк на это не обращают внимание. Если размер таблиц большой, то полезно даже небольшое уменьшение объема хранения. У столбцов в таблицах PostgreSQL есть оптимальный  порядок и менее оптимальные с точки зрения размера физического хранения. Причина в выравнивании (aligning) и появлении пустых мест (padding). В блоке данных выравниваются все структуры: заголовки, поля, сами строки.

На 64-разраядных операционных системах, длина любой строки и заголовка строки  выравниваются по 8 байт. То есть если строка занимает 28 байт, то физически она займёт 32 байта. В конец строки будут добавлены пустые байты. Поля выравниваются сложнее — по 4, 8, 16 байт. В статье рассматривается перестановка столбцов на примере демонстрационной базы.

При изучении PostgreSQL многие доходят до вопроса перестановок столбцов. С виду задача простая. Найдя на просторах интернет скрипт, который выдаёт оптимальный порядок столбцов, я решил проверить какой эффект может быть. Скрипт написан специалистом, который дальше всех продвинулся в изучении перестановок столбцов («column tetris»). Воспользоваться скриптом просто:

wget https://raw.githubusercontent.com/NikolayS/postgres_dba/refs/heads/master/sql/p1_alignment_padding.sql ‘p1_alignment_padding.sql’ saved [6598/6598]

Встроенной демонстрационной базы в PostgreSQL нет. Существует база авиаперевозок, которой я решил воспользоваться. Это чрезвычайно аккуратная база, приближенная к реальным схемам хранения. Она является лучшим примером того, как стоит разрабатывать структуры схем хранения (ER modeling). Демобаза поставляется на двух языках и в трёх вариантах размеров: small, medium, big. Разные размеры позволяют тестировать запросы разной степени сложности. Я выбрал вариант medium, «как у всех». Скачивание и установка базы элегантна:

wget https://edu.postgrespro.com/demo-medium-en.zip Saving to: ‘demo-medium-en.zip’ ‘demo-medium-en.zip’ saved [64544920/64544920] time zcat demo-medium-en.zip | psql real    1m3.339s user    0m2.168s sys     0m0.255s

Создание базы заняло 1 минуту. Проверяю скрипт в деле:

psql -d demo demo=# \i p1_alignment_padding.sql          Table           | Table Size |     Comment      |      Wasted *      |      Suggested Columns Reorder        --------------------------+------------+------------------+--------------------+--------------------------------------  bookings.ticket_flights  | 154 MB     | Includes VARLENA | ~18 MB (11.68%)    | amount, fare_conditions, flight_id  +                           |            |                  |                    | ticket_no  bookings.boarding_passes | 109 MB     | Includes VARLENA | ~14 MB (13.25%)    | boarding_no, flight_id, seat_no     +                           |            |                  |                    | ticket_no  bookings.tickets         | 109 MB     | Includes VARLENA | ~6477 kB (5.81%)   | book_ref, contact_data, passenger_id+                           |            |                  |                    | passenger_name, ticket_no  bookings.bookings        | 30 MB      | Includes VARLENA |                    |   bookings.flights         | 6688 kB    | Includes VARLENA |                    |   bookings.seats           | 96 kB      | Includes VARLENA |                    |   bookings.airports_data   | 48 kB      | Includes VARLENA | ~832 bytes (1.69%) | airport_code, airport_name, city    +                           |            |                  |                    | timezone, coordinates  bookings.aircrafts_data  | 8192 bytes | Includes VARLENA |                    |  (8 rows)

Скрипт обещает, что переставив столбцы можно сэкономить примерно 11% места. О ужас, неужели создатели демонстрационной базы использовали неоптимальный порядок столбцов?! Предвкушая, как я расскажу об этом создателям демобазы, потирая ручки, я чуть было не принялся поверять рекомендации теста. Но до проверки руки дошли только спустя пару месяцев.

Скрипт выдал рекомендуемый порядок столбцов в столбце Suggested Columns Reorder. Как поменять порядок следования столбцов? Над этим бились лучшие умы, но ни к чему не пришли. Я поступил простым способом до которого смог додуматься. Выгрузил определения столбцов:

pg_dump -d demo -s -f demo.sql

и отредактировал порядок следования в текстовом редакторе, переставляя строки в полученном файле в mcedit:

Дальше создал базу для новых таблиц и выполнил скрипт, создающий структуру объектов:

postgres=# create database demo1; \c demo1 \\ \i demo.sql CREATE DATABASE You are now connected to database "demo1" as user "postgres". SET ...

Я что-то упустил? Опытные администраторы СУБД, думаю, догадались.

Осталось перегрузить данные из исходных таблиц в новые (упустил я не это, про упущенное будет дальше), с рекомендованным скриптом порядком следования столбцов:

time pg_dump -d demo -a | psql -d demo1 real    3m38.040s user    0m1.981s sys     0m0.259s

Перегрузка выполнялась довольно долго ~3 минуты 38 секунд. Создание базы demo скриптом от разработчиков длилась 1 минуту, то есть существенно быстрее. Что я упустил или недосмотрел? Упустил (самомнение поправило: банально забыл) то, что скрипт дампа создал индексы на таблицах. Именно их наличие существенно замедлило загрузку данных командами COPY, которые использует утилита pg_dump. Ну да ладно, не повторять же всё заново. Посмотрим размеры баз:

postgres=# select pg_size_pretty(a), pg_size_pretty(b), 100*(a-b)/(a+b) "%" from (select pg_database_size('demo') a , pg_database_size('demo1') b);  pg_size_pretty | pg_size_pretty | %   ----------------+----------------+----   703 MB        | 812 MB         | -7 (1 row)

Это что такое? Размер базы данных увеличился на 7%! Стало хуже. Вспомнив, что загрузка выполнялась при наличии индексов на таблицах я предположил, что виноваты индексы — их структура не оптимальна. Перестройка индексов:

\timing on \\ REINDEX SCHEMA bookings; Time: 23059,135 ms (00:23,059)

Перестройка индексов прошла быстро — за 23 секунды. Проверим размеры баз:

demo1=# select pg_size_pretty(a), pg_size_pretty(b), 100*(a-b)/(a+b) "%" from (select pg_database_size('demo') a , pg_database_size('demo1') b);  pg_size_pretty | pg_size_pretty | %  ----------------+----------------+---  703 MB         | 703 MB         | 0 (1 row)

Стало лучше, но разницы, обещанной скриптом, нет. Что скажет скрипт на этот раз:

demo1=# \i p1_alignment_padding.sql           Table           | Table Size |     Comment      | Wasted * | Suggested Columns Reorder  --------------------------+------------+------------------+----------+---------------------------  bookings.ticket_flights  | 154 MB     | Includes VARLENA |          |   bookings.boarding_passes | 109 MB     | Includes VARLENA |          |   bookings.tickets         | 109 MB     | Includes VARLENA |          |   bookings.bookings        | 30 MB      | Includes VARLENA |          |   bookings.flights         | 6688 kB    | Includes VARLENA |          |   bookings.seats           | 96 kB      | Includes VARLENA |          |   bookings.airports_data   | 48 kB      | Includes VARLENA |          |   bookings.aircrafts_data  | 8192 bytes | Includes VARLENA |          |  (8 rows)

Скрипт сказал танцуют все, что не видит лишнего места (Wasted *) и порядок столбцов оптимален. Я пригорюнился (но не приуныл), восхитился мудростью разработчиков демобазы, укорил себя за малодушные сомнения в создателях базы и слепую веру скриптам из интернет. Но червь сомнения глодал — а может, скрипт ошибается и есть в жизни счастье оптимальный порядок следования столбцов?

Padding и aligning

Придётся изучить теорию. Сами по себе перестановки столбцов просты и до сих пор сложность статьи несложная. Но выбор оптимального порядка столбцов сложен и предсказать, что получится сложно, так как результат частично зависит и от самих данных, хранящихся в таблице. Я без иронии писал, что создатель скрипта дальше всех продвинулся в изучении перестановок столбцов, это действительно так. Прогресс (как и постгрес) не стоит на месте, у нас есть шанс углубить и расширить знания. В этом разделе сложность статьи — «сложная». В столбце typalign таблицы системного каталога pg_type и attalign таблицы pg_attribute указано выравнивание для полей разных типов. Все типы:

select distinct typname, typlen, typalign from pg_type where typname not like 'pg_%' and typname not like '\_%' order by typname;                 typname                | typlen | typalign  ---------------------------------------+--------+----------  aclitem                               |     16 | d  administrable_role_authorizations     |     -1 | d ...  void                                  |      4 | i  xid                                   |      4 | i  xid8                                  |      8 | d  xml                                   |     -1 | i  yes_or_no                             |     -1 | i (179 rows)

Все используемые в столбцах таблиц PostgreSQL типы:

select distinct atttypid::regtype, attlen, attalign from pg_attribute order by attalign;               atttypid              | attlen | attalign  ------------------------------------+--------+----------  name                               |     64 | c  boolean                            |      1 | c  "char"                             |      1 | c  cstring                            |     -2 | c  ...  smallint                           |      2 | s  tid                                |      6 | s (51 rows)

Выравнивание может быть:
c (char), 1 байт, то есть без выравнивания
s (short), 2 байта
i (int), 4 байта
d (double) по 8 байт

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

create table t1 (c1 varchar(1), c2 bigserial , c3 date, c4 timestamp); create table t2 (c1 bigserial , c2 timestamp, c3 date, c4 varchar(1)); insert into t1 values('A', 1, now(), current_timestamp); insert into t2 values(1, current_timestamp, now(), 'A');

Строки будут храниться в виде последовательности байт в HEX:

Пример в виде картинки с другими типами данных:

В примере на картинке перестановка столбцов не даёт результата — длина строк одинакова. Причина? Вся строка выравнивается до 8 байт, поэтому без разницы в конец строки или после первого поля будут добавлены пустые байты.

Причина почему в PostgreSQL используется выравнивание: улучшение производительности работы при доступе к строкам в блоках данных. Выравнивание снижает нагрузку на процессор.

Перестановка столбцов в демонстрационной базе

 Пробовать все варианты перестановок накладно, поэтому решил воспользоваться простыми правилами: столбцы фиксированной ширины сделать первыми; чем шире столбец, тем ближе к началу. То есть расположу столбцы фиксированной ширины в следующем порядке их typalign: d, i, s, c. Поля переменной ширины (varlena) начинают выравниваются по их typalign (attalign), только если их длина больше 126 байт (мало кто задастся вопросом почему). Длинных полей в демобазе не должно быть, поэтому не буду это учитывать. Снова выгружаю файл:

pg_dump -d demo -s -f demo.sql

и редактирую, меняя порядок столбцов:

Учтя предыдущую проблему с индексами, создаю две копии отредактированного файла со структурой объектов. В одном из файлов, в самом конце удаляю последние строки, которые и создают индексы, начиная со строки:

ALTER TABLE ONLY bookings.aircrafts_data     ADD CONSTRAINT aircrafts_pkey PRIMARY KEY (aircraft_code);

Создаю новую базу данных, выполняю скрипт без создания индексов:

postgres=# create database demo2; \c demo2 \\ \i demo2.sql CREATE DATABASE You are now connected to database "demo1" as user "postgres". SET ...

Перегружаю данные:

time pg_dump -d demo -a | psql -d demo1 real0m9,990s user0m1,881s sys0m0,344s

Перегрузка данных заняла 10 секунд, вместо 3 минут 38 секунд.
Запускаю скрипт с созданием индексов:

time psql -d demo2 -f demo2copy.sql ... SET psql:demo1a.sql:24: ERROR:  schema "bookings" already exists ... ALTER TABLE ALTER TABLE  real0m28,332s user0m0,056s sys0m0,008s

В начале выдаются ошибки, что таблицы уже есть, но это нормально. В конце командами ALTER TABLE добавляются первичные ключи, создавая индексы, что и требуется от этого скрипта.

Стоит всомнить про упущение. Загрузка данных до создания индексов заняла 10+28=38 секунд. Загрузка данных в таблицы с индексами заняла 3 минуты 28 секунд, то есть медленнее в 5,5 раз. Плюс индексы были неоптимальны и по размеру и по производительности при их использовании. Перестройка индексов заняла 23 секунды. Это цена упущения. Более того, если не перестроить индексы, то всё будет работать, но только медленно.

Параметров у утилиты pg_dump, которые позволяли бы при загрузке данных (режим pg_dump -a) удалять индексы, загружать данные, а потом добавлять индексы отсутствуют. Такой параметр сделал бы работу утилиты более удобной.

Проверяю размеры баз данных:

demo2=# select pg_size_pretty(a), pg_size_pretty(b), 100*(a-b)/(a+b) "%" from (select pg_database_size('demo') a , pg_database_size('demo2') b);  pg_size_pretty | pg_size_pretty | %  ----------------+----------------+---  703 MB         | 697 MB         | 0 (1 row)

После перестановки столбцов размер базы уменьшился на 6Мб. Уменьшение небольшое, но всё-таки уменьшение. Размеры индексов не уменьшились. Уменьшились размеры таблиц ticket_flights со 154Мб до 148Мб (на 3.9%), flights с 6688Кб до 6048Кб (9.57%) или после полного вакуумирования с 6336Кб до 5976Кб (на 9.9%):

demo2=# \dt+ bookings.*                                                  List of relations   Schema  |      Name       | Type  |  Owner   | Persistence | Access method |  Size   |        Description         ----------+-----------------+-------+----------+-------------+---------------+---------+---------------------------  bookings | aircrafts_data  | table | postgres | permanent   | heap          | 16 kB   | Aircrafts (internal data)  bookings | airports_data   | table | postgres | permanent   | heap          | 56 kB   | Airports (internal data)  bookings | boarding_passes | table | postgres | permanent   | heap          | 109 MB  | Boarding passes  bookings | bookings        | table | postgres | permanent   | heap          | 30 MB   | Bookings  bookings | flights         | table | postgres | permanent   | heap          | 6048 kB | Flights  bookings | seats           | table | postgres | permanent   | heap          | 96 kB   | Seats  bookings | ticket_flights  | table | postgres | permanent   | heap          | 148 MB  | Flight segment  bookings | tickets         | table | postgres | permanent   | heap          | 109 MB  | Tickets (8 rows)

Можно ли что-то улучшить?

Посмотрим, почему не уменьшилcя размер таблицы boarding_passes. До перестановки столбцов:

demo=# \d boarding_passes                   Table "bookings.boarding_passes"    Column    |         Type         | Collation | Nullable | Default  -------------+----------------------+-----------+----------+---------  ticket_no   | character(13)        |           | not null |   flight_id   | integer              |           | not null |   boarding_no | integer              |           | not null |   seat_no     | character varying(4) |           | not null |  ...  demo=# select t_data, lp_len, t_hoff from heap_page_items(get_raw_page('boarding_passes','main',0)) limit 3;                           t_data                          | lp_len | t_hoff  ----------------------------------------------------------+--------+--------  \x1d3030303534333532303832323900003bed000001000000073148 |     51 |     24  \x1d3030303534333532303832323400003bed000002000000073241 |     51 |     24  \x1d3030303534333532303831393100003bed000003000000073244 |     51 |     24 (3 rows)

После перестановки столбцов размер строки уменьшился с 51 байта до 49 байт:

demo2=# \d boarding_passes                   Table "bookings.boarding_passes"    Column    |         Type         | Collation | Nullable | Default  -------------+----------------------+-----------+----------+---------  boarding_no | integer              |           | not null |   flight_id   | integer              |           | not null |   seat_no     | character varying(4) |           | not null |   ticket_no   | character(13)        |           | not null |  ...  demo2=# select t_data, lp_len, t_hoff from heap_page_items(get_raw_page('boarding_passes','main',0)) limit 3;                         t_data                        | lp_len | t_hoff  ------------------------------------------------------+--------+--------  \x010000003bed00000731481d30303035343335323038323239 |     49 |     24  \x020000003bed00000732411d30303035343335323038323234 |     49 |     24  \x030000003bed00000732441d30303035343335323038313931 |     49 |     24 (3 rows)

Место не уменьшилось, так как вся строка выравнивается до 8 байт и для 51 байт, 49 байт ближайшее значение одинаково: 56 байт. А вот, если уменьшить строку на один байт, то длина строки была бы 48 байт.

Какие столбцы можно было бы сделать меньше при проектировании схемы хранения? Номер посадочного талона (boarding_no) отведено 4 байта. В нём хранится последовательность создания посадочных талонов, начиная с 1. Даже с учетом перевыпуска посадочных талонов, значение вряд ли превысит 32 тысячи. Поменяем тип integer на int2:

demo=# create table boarding_passes2 (flight_id integer, boarding_no int2, seat_no text, ticket_no text); insert into boarding_passes2 select flight_id, boarding_no, seat_no, ticket_no from boarding_passes; select t_data, lp_len, t_hoff from heap_page_items(get_raw_page('boarding_passes2','main',0)) limit 1; \dt+ boarding_pass*  CREATE TABLE INSERT 0 1894295                       t_data                      | lp_len | t_hoff  --------------------------------------------------+--------+--------  \x3bed000001000731481d30303035343335323038323239 |     47 |     24 (1 row)                                              List of relations   Schema  |       Name       | Type  |  Owner   | Persistence | Access method |  Size  |   Description    ----------+------------------+-------+----------+-------------+---------------+--------+-----------------  bookings | boarding_passes  | table | postgres | permanent   | heap          | 109 MB | Boarding passes  bookings | boarding_passes2 | table | postgres | permanent   | heap          | 94 MB  |  (2 rows)

Размер строки уменьшился на 2 байта и стал 47 байт. Строка будет занимать 48 байт.
Место, занимаемое таблицей уменьшилось на 13.7%. Это хороший результат.

Заключение

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