Выбор типа данных для первичного ключа и способ его заполнения влияет на производительность и эффективность индекса ключа. В статье описываются особенности вставки в таблицу с первичным ключем для целочисленных полей (bigint) и uuid. Патч с функцией, генерирующей uuidv7 был принят в PostgreSQL 31 января 2025. У последовательностей и identity столбцов есть параметр CACHE. Параметр определяет сколько значений из последовательности будет кэшировать серверный процесс в своей локальной памяти для будущих вставок. Увеличение значения CACHE часто уменьшает производительность, рассматриваются причины
Быстрый путь вставки в индексы
Оптимизация вставки, позволяющая не спускаться с корня дерева индекса типа btree называется fastpath. Серверный процесс, который выполнил вставку в правый листовой блок, запоминает ссылку на него и при последующей вставке, если новое значение больше предыдущего (или пусто) и не проходит путь от корня до листового блока. "The fastpath optimization avoids most of the work of searching the tree repeatedly when a single backend inserts successive new tuples on the rightmost leaf page of an index" (nbtinsert.c).
Столбец с первичным ключом обычно делают автоинкрементальным, то есть используется монотонно возрастающая последовательность целых чисел. В таком случае, при вставке новой строки она всегда будет вставляться в самый правый листовой блок и при вставке нет смысла проходить блоки начиная с корня. Также при вставке в датавременной столбец, заполняемый по DEFAULT временем вставки или заполняемые другим способом.
Если по какой-либо причине выполнил вставку (в индекс записи только вставляются, они не меняются, а удаляются только вакуумом) не в самый правый блок, то процесс забывает адрес блока и снова начинает поиск с корня.
Fastpath используется при числе уровней в индексе начиная с 2 (BTREE_FASTPATH_MIN_LEVEL). Оптимизация хорошо работает, если один процесс вставляет строки в таблицу. Если процесс столкнется на блокировке правого листового блока с другим процессом (проверяется что буфер не закреплен другими процессами), этот процесс перестает использовать fastpath. Другой процесс может продолжать, так как он не сталкивался. Получается, что процесс "отправляется на штрафной круг": прочтет несколько блоков от корня до правого листового блока. За время пока процесс проходит "штрафной круг" он не сталкивается с другими процессами и не мешает другим процессам работать с правым листовым блокам. Это увеличивает общую производительность, позволяя какому-то другому процессу без конкуренции за доступ к правому блоку с большой скоростью вставлять в него записи. Пока другие процессы спустятся с корня дерева индекса, то этот блок с большой вероятностью уже не будет самым правым листовым. Другими словами, правый блок перестаёт (снижается вероятность) быть "горячим".
По этой же причине не стоит распараллеливать в таблицу вставки строк, предполагая что это всегда быстрее: процессы могут встретиться с конкуренцией за "горячий" блок.
Тестирование вставок в индекс типа btree
При использовании в качестве уникального ключа типа uuid (за исключением uuidv7) размер поля для этого типа 16 байт и не будет работать оптимизация fastpath, так как вставляются случайные значения, а не возрастающие. Также встака в разные листовые блоки индекса небольшого числа записей приводит к увеличению объема журнала засчет записи в журнал большего числа полных образов страниц (full page images, FPI).
Вместо функции gen_random_uuid() может использоваться функция uuid_generate_v4() или функция uuidv7() расширения pg_uuidv7. Для тестирования я использовал СУБД Tantor 16.6, где присутствут эта функция. В ванильном PostgreSQL uuidv7 пока отсутствует. Патч с uuidv7 Андрея Бородина был принят 31 января 2025 года https://commitfest.postgresql.org/47/4388/ и появится в следующей версии PostgreSQL.
Тест приведён полностью, чтобы его можно было легко повторить. Он состоит из итераций, различающихся только числом сессий и способом генерации значений для ключевого столбца.
Можно переставить порядок создания таблиц и при большом числе ядер процессора увеличить число сессий. Возможно, при большом числе ядер процессоров удастся подобрать число процессов и значение CACHE, при которых увеличится производительность. Время контрольной точки увеличено, чтобы работа процесса контрольной точки не повлияла на результат.
psql -c "alter system set checkpoint_timeout='60min';"
pgbench -i > /dev/null 2> /dev/null
sudo systemctl restart postgresql-tantor
sleep 10
psql -c 'create extension if not exists "uuid-ossp";' > /dev/null 2> /dev/null
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id bigint generated by default as identity (cache 50) primary key, data bigint);" > /dev/null
echo "insert into tt1(data) values(1);" > txn.sql
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 64 -f txn.sql 2> /dev/null | grep tps
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "reindex table tt1;" > /dev/null
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id uuid default gen_random_uuid() primary key, data bigint);" > /dev/null
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 64 -f txn.sql 2> /dev/null | grep tps
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "reindex table tt1;" > /dev/null
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id bigint generated by default as identity primary key, data bigint);" > /dev/null
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 64 -f txn.sql 2> /dev/null | grep tps
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "reindex table tt1;" > /dev/null
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "create extension if not exists pg_uuidv7;" > /dev/null 2> /dev/null
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id uuid default uuidv7() primary key, data bigint);" > /dev/null
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 64 -f txn.sql 2> /dev/null | grep tps
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "reindex table tt1;" > /dev/null
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id bigint generated by default as identity (cache 50) primary key, data bigint);" > /dev/null
echo "insert into tt1(data) values(1);" > txn.sql
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 32 -f txn.sql 2> /dev/null | grep tps
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "reindex table tt1;" > /dev/null
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id uuid default gen_random_uuid() primary key, data bigint);" > /dev/null
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 32 -f txn.sql 2> /dev/null | grep tps
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "reindex table tt1;" > /dev/null
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id bigint generated by default as identity primary key, data bigint);" > /dev/null
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 32 -f txn.sql 2> /dev/null | grep tps
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "reindex table tt1;" > /dev/null
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id uuid default uuidv7() primary key, data bigint);" > /dev/null
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 32 -f txn.sql 2> /dev/null | grep tps
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "reindex table tt1;" > /dev/null
psql -c "select count(*), pg_indexes_size('tt1') from tt1;"
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id bigint generated by default as identity (cache 50) primary key, data bigint);" > /dev/null
echo "insert into tt1(data) values(1);" > txn.sql
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 16 -f txn.sql 2> /dev/null | grep tps
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id uuid default gen_random_uuid() primary key, data bigint);" > /dev/null
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 16 -f txn.sql 2> /dev/null | grep tps
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id bigint generated by default as identity primary key, data bigint);" > /dev/null
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 16 -f txn.sql 2> /dev/null | grep tps
psql -c "drop table if exists tt1;" > /dev/null
psql -c "create table tt1 (id uuid default uuidv7() primary key, data bigint);" > /dev/null
psql -c "vacuum analyze tt1;" > /dev/null
sleep 1
pgbench -T 30 -c 16 -f txn.sql 2> /dev/null | grep tpsКомментарии к командам теста
Устанавливаются расширения c функциями для типа данных uuid: "uuid-ossp" и pg_uuidv7.
Таблица из двух столбцов create table tt1 (id primary key, data bigint). Второй столбец и его тип не влияет на результаты теста и добавлен для приближения к реальности. На первый столбец создаётся уникальный индекс для первичного ключа.
В сессиях вставляются строки одиночной командой insert into tt1(data) values(1). Команда самая простая, расширенный протокол не используется.
Каждая сессия создаёт примерно 100 строк в секунду.
Тестируются:bigint заполняемый последовательностю со свойством cache со значением 50.uuid заполняемый функцией gen_random_uuid()bigint заполняемый последовательностю со свойством cache по умолчанию 1uuid заполняемый функцией uuidv7()
Результаты выполнения команд теста:
tps = 6033.348007
count | pg_indexes_size
--------+-----------------
180018 | 5488640
(1 row)
count | pg_indexes_size
--------+-----------------
180018 | 4063232
(1 row)
tps = 5989.641340
count | pg_indexes_size
--------+-----------------
178711 | 7315456
(1 row)
count | pg_indexes_size
--------+-----------------
178711 | 5660672
(1 row)
tps = 6041.427014
count | pg_indexes_size
--------+-----------------
180261 | 4071424
(1 row)
count | pg_indexes_size
--------+-----------------
180261 | 4071424
(1 row)
tps = 5995.067495
count | pg_indexes_size
--------+-----------------
178730 | 5652480
(1 row)
count | pg_indexes_size
--------+-----------------
178730 | 5660672
(1 row)
tps = 3290.625242
count | pg_indexes_size
-------+-----------------
98463 | 2940928
(1 row)
count | pg_indexes_size
-------+-----------------
98463 | 2228224
(1 row)
tps = 3266.906578
count | pg_indexes_size
-------+-----------------
97711 | 4341760
(1 row)
count | pg_indexes_size
-------+-----------------
97711 | 3104768
(1 row)
tps = 3312.672801
count | pg_indexes_size
-------+-----------------
99050 | 2236416
(1 row)
count | pg_indexes_size
-------+-----------------
99050 | 2236416
(1 row)
tps = 3247.920498
count | pg_indexes_size
-------+-----------------
97172 | 3088384
(1 row)
count | pg_indexes_size
-------+-----------------
97172 | 3088384
(1 row)
tps = 1717.792820
tps = 1717.162280
tps = 1734.506311
tps = 1707.292961Результаты
Для первого набора из 4 тестов подобраны значения, при которых размер индексов после перестройки различен. Различные значения указывают на то, что части команд insert не использовалась оптимизация fastpath. Для bigint с cache 1 и uuidv7() это означает, что работа серверный процесс испытывает задержки, не связанные с последовательностью и функцией. В примере слишком много сессий (-с 64) при небольшом числе ядер процессора (тест проводился на процессоре с числом ядер 4).
Во втором и третьем наборе тестов уменьшено число сессий и для bigint с cache 1 и uuidv7()размер индексов после перестройки не изменился: 2236416 и 3088384. Вставка строк таким образом, что не нужно перестраивать индексы, так как их структура оптимальна является важным свойством. Это соблюдается для столбцов, заполняемых последовательностью с CACHE 1, а также для uuidv7. Cо свойством cache=1 индекс по столбцу bigint остался эффективным, так как размер индекса после перестройки не уменьшился.
Для оценки TPS нужен более аккуратный тест. Длительность тестов небольшая (30 секунд), TPS для bigint с cache 1 немного больше остальных тестов, но укладывается в погрешность. Скорость генерации uuidv7() достойна при том, что размерность типа данных uuid больше bigint.
Для bigint с достаточно увеличенным значением cache, для uuid заполняемых gen_random_uuid() и uuid_generate_v4() размеры индексов до перестройки и после перестройки индекса будут различаться. Это указывает на неэффективность вставки.
Причина неэффективности в том, что если несколько сессий кэшируют диапазоны значений, велика вероятность, что вставки пойдут не в самый правый листовой блок индекса. Если вставки идут не в самый правый блок индекса, то структура индекса становится неэффективной, индекс становится большего размера, чем мог бы быть. Выборка значений из последовательности не является узким местом и быстрая операция. Во многих случаях увеличение cache не даст прироста производительности.
Использование параметра cache было бы оправдано, если бы кластер баз данных обслуживало несколько экземпляров на разных узлах (как в Oracle RAC) и между экземплярами надо было бы синхронизировать доступ к последовательности.
При меньшем числе строк (столбец count) размеры индексов по столбцу типа uuid в 1,38 раз больше, чем размер индекса по столбцу типа bigint. Например:
для bigint число строк 99050, а размер индекса 2236416 байт.
для uuidv7 число строк 97172, а размер индекса 3088384 байт.
Причина в том, что размер поля типа uuid 16 байт, что в два раза больше, чем размер поля bigint (8 байт).
Для генерации уникальных значений для первичного ключа можно использовать последовательности (столбцыidentity) с параметром CACHE по умолчанию, так как PostgreSQL оптимизирован для их использования.
