Порядок столбцов в таблицах влияет на компактность и производительность. При небольшом числе строк на это не обращают внимание. Если размер таблиц большой, то полезно даже небольшое уменьшение объема хранения. У столбцов в таблицах 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)typlen = -1 это типы переменной длины. (ещё есть один единственный тип со значением -2, но я о нём ничего не знаю). Все используемые в столбцах таблиц 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и редактирую, меняя порядок столбцов:
Скрытый текст
\d aircrafts_data
Table "bookings.aircrafts_data"
Column | Type | Collation | Nullable | Default
---------------+--------------+-----------+----------+---------
range | integer | | not null |
aircraft_code | character(3) | | not null |
model | jsonb | | not null |
\d airports_data
Table "bookings.airports_data"
Column | Type | Collation | Nullable | Default
--------------+--------------+-----------+----------+---------
coordinates | point | | not null |
airport_code | character(3) | | not null |
airport_name | jsonb | | not null |
city | jsonb | | not null |
timezone | text | | not null |
\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 |
\d bookings
Table "bookings.bookings"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
book_date | timestamp with time zone | | not null |
book_ref | character(6) | | not null |
total_amount | numeric(10,2) | | not null |
\d flights
Table "bookings.flights"
Column | Type | Collation | Nullable | Default
---------------------+--------------------------+-----------+----------+--------------------------------------------
scheduled_departure | timestamp with time zone | | not null |
scheduled_arrival | timestamp with time zone | | not null |
actual_departure | timestamp with time zone | | |
actual_arrival | timestamp with time zone | | |
flight_id | integer | | not null | nextval('flights_flight_id_seq'::regclass)
flight_no | character(6) | | not null |
departure_airport | character(3) | | not null |
arrival_airport | character(3) | | not null |
status | character varying(20) | | not null |
aircraft_code | character(3) | | not null |
\d seats
Table "bookings.seats"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
aircraft_code | character(3) | | not null |
seat_no | character varying(4) | | not null |
fare_conditions | character varying(10) | | not null |
\d ticket_flights
Table "bookings.ticket_flights"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
flight_id | integer | | not null |
amount | numeric(10,2) | | not null |
fare_conditions | character varying(10) | | not null |
ticket_no | character(13) | | not null |
\d tickets
Table "bookings.tickets"
Column | Type | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+---------
book_ref | character(6) | | not null |
contact_data | jsonb | | |
passenger_id | character varying(20) | | not null |
passenger_name | text | | not null |
ticket_no | character(13) | | not null |
Учтя предыдущую проблему с индексами, создаю две копии отредактированного файла со структурой объектов. В одном из файлов, в самом конце удаляю последние строки, которые и создают индексы, начиная со строки:
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
real 0m9,990s
user 0m1,881s
sys 0m0,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
real 0m28,332s
user 0m0,056s
sys 0m0,008sВ начале выдаются ошибки, что таблицы уже есть, но это нормально. В конце командами ALTER TABLE добавляются первичные ключи, создавая индексы, что и требуется от этого скрипта.
Стоит всомнить про упущение. Загрузка данных до создания индексов заняла 10+28=38 секунд. Загрузка данных в таблицы с индексами заняла 3 минуты 28 секунд, то есть медленнее в 5,5 раз. Плюс индексы были неоптимальны и по размеру и по производительности при их использовании. Перестройка индексов заняла 23 секунды. Более того, если не перестроить индексы, то всё будет работать, но только медленно. Разница в длительности 6 раз - это цена упущения.
Параметров у утилиты 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 с точки зрения оптимизации места хранения и рассмотрен пример перестановки столбцов.