У последовательностей есть параметр cache, который определяет сколько значений из последовательности будет кэшировать серверный процесс в своей локальной памяти для будущих вставок в течение сессии. Последовательности используются первичными и уникальными ключами. По умолчанию значения последовательностей не кэшируются. Кэширование может снизить произвдительность и сделать структуру индекса не оптимальной.
Быстрый путь вставки в индексы
В PostgreSQL есть оптимизация вставки в индекс типа btree, позволяющая не спускаться с корня дерева индекса. Серверный процесс, который выполнил вставку в правый листовой блок, запоминает ссылку на него и при последующей вставке, если новое значение больше предыдущего (или пусто) и не проходит путь от корня до листового блока. Оптимизация используется при числе уровней в индексе начиная со второго (макрос BTREE_FASTPATH_MIN_LEVEL).
Столбец с первичным ключом обычно делают автоинкрементальным. В таком случае, при вставке новой строки она всегда будет вставляться в самый правый листовой блок и при вставке нет смысла проходить блоки начиная с корня. Оптимизация также поддерживается при вставке любых возрастающих значений, заполняемых выражением DEFAULT.
Если процесс по какой-либо причине не может выполнить вставку в самый правый блок, то процесс забывает адрес блока и снова начинает поиск с корня. Одна из причин: столкновение на блокировке правого листового блока с другим процессом (проверяется что буфер не закреплен другими процессами). Другой процесс может продолжать, так как он не сталкивался. Получается, что процесс "отправляется на штрафной круг": читает блоки от корня до правого листового блока. За время пока процесс проходит "штрафной круг" он не сталкивается с другими процессами и не мешает другим процессам работать с правым листовым блоком. Это увеличивает общую производительность, позволяя какому-то другому процессу без конкуренции за доступ к правому блоку с большой скоростью вставлять в блок записи. Пока другие процессы спустятся с корня дерева индекса, этот блок с большой вероятностью уже не будет самым правым листовым. Другими словами, правый блок перестаёт (снижается вероятность) быть "горячим". Это элегантное решение, снижающее конкуренцию за блок.
Кэширование значений последовательности (cache больше 1) приводит к следующему эффекту. Процесс выбирает из последовательности и кэширует несколько значений под будущие вставки строк, а также запоминает адрес самого правого листового блока. Если вставку следующей строки процесс делает через некоторое время, а не вставляет сроки без задержки, то за это время другие процессы скорее всего заполнят правый листовой блок и разделят его. В этом случае, все последующие вставки от первого процесса с закэшированными значениями будут вставляться не в самый правый листовой блок, а в блоки левее него.
Если процесс не кэширует значения последовательности (cache 1), то всегда выбирается самое свежее, а значит наибольшее, значение из последовательности и вставка строки пойдёт в самый правый блок. Исключением может быть случай, когда между выборкой значения из последовательности и получением доступа к правому листовому блоку блок успеет разделиться. Такое возможно при большой нагрузке. Нужна ли оптимизация при небольшой скорости вставки? При небольшой скорости вставки оптимизация быстрой вставки не важна, главное что строка будет вставляться в самый правый листовой блок. При большой скорости вставки оптимизация зааботает и ускорит вставки.
Почему желательно, чтобы вставки выполнялись в правый листовой блок? Потому, что в индексах типа btree PostgreSQL вставка записей в правый листовой блок оставляет структуру индекса наиболее оптимальной, структура индекса растёт равномерно и остаётся такой же эффективной, как если бы индекс был перестроен.
При использовании в качестве уникального ключа типа uuid (за исключением uuidv7, с ним проблем нет, он оптимален для вставок) оптимизации быстрой вставки не будет, так как вставляются случайные значения, а не возрастающие. Вставка неупорядоченных значений в разные листовые блоки индекса приводит к увеличению объема журнала засчет записи в журнал большего числа полных образов страниц (full page images, FPI). Возрастающие значения генерирует uuidv7().
Патч с поддержкой uuidv7 был принят 31 января 2025 года https://commitfest.postgresql.org/47/4388/ и появится в следующей версии PostgreSQL.
Тесты выполнялись на СУБД Tantor SE 17.2, так как в ней uuidv7присутствует начиная с версии 16.8.
Скрипт теста приведён полностью, чтобы его можно было легко повторить:
Скрытый текст
psql -c "alter system set checkpoint_timeout='60min';"
pgbench -i > /dev/null 2> /dev/null
sudo systemctl restart postgresql
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). Второй столбец и его тип не влияет на результаты теста и добавлен для приближения к реальности. На первый столбец создаётся уникальный индекс для первичного ключа.
В сессиях утилиты pgbench вставляются строки одиночной командой insert into tt1(data) values(1). Тестируются вставки в поля типов:bigint, заполняемые последовательностю со свойством cache 50.uuid, заполняемые функцией gen_random_uuid()bigint, заполняемые последовательностю без кэширования значенийuuid, заполняемые функцией 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
Результаты
Для первого набора комнд из четырёх групп подобраны значения, при которых размер индексов после перестройки различен. Это ситуация, когда после выбора значения из последовательности процесс не может получить доступ к правому листовому блоку, проходит путь от корня индекса и оказывается, что за это время правый блок разделился и значение последовательности должно быть вставлено не в самый правый листовой блок. Вставка не в правый листовой блок приводит к неэффективности структуры индекса, на что указывает уменьшение размера индекса после его перестройки. Причина такой ситуации: в слишком много сессий (-с 64) при небольшом числе ядер процессора.
Во втором и третьем наборе команд уменьшено число сессий до 32 и 16. Для bigint без кэширования и uuidv7() размер индексов после перестройки не изменился и во втором примере составил: 2236416 и 3088384 байт. При использовании bigint без кэширования и uuidv7() структура индекса остаётся оптимальной. Для bigint с кэшированием, для uuid, заполняемых gen_random_uuid() и uuid_generate_v4() размеры индексов до перестройки и после перестройки индекса всегда будут различаться. Это основной результат.
Размер поля типа uuid 16 байт, что в два раза больше, чем размер поля bigint (8 байт). Размер индекса по столбцу типа uuid больше, чем размер индекса по столбцу типа bigint. Пример разницы размеров индексов:
для bigint число строк 99050, а размер индекса 2236416 байт.
для uuidv7 число строк 97172, а размер индекса 3088384 байт.
Оптимизация структуры хранения для uuidv7 возможна: по аналогии с 64-битным счетчиком транзаций верхние 32 бита хранятся в одном месте блока таблицы, что не увеличивает его размер, так же и в листовом блоке индекса хранение uuidv7 можно сделать компактным.