В мире СУБД общепринятая догма гласит: «Индексы ускоряют запросы». Но что, если в погоне за производительностью мы создали себе проблему? В этой статье, на практике показан парадоксальный сценарий, при котором удаление первичного ключа у таблицы pgbench_branch и последующее увеличение стоимости запроса привели к впечатляющему росту общей производительности PostgreSQL под нагрузкой. СУБД не так просты, как может показаться.

Цель эксперимента

Используя инструментарий на основе свободного расширения pg_expecto^[1], в процессе нагрузочного тестирования^[2],  оценить влияние удаление индекса первичного ключа в таблице на производительность СУБД.

Дополнительная информация и контакты по теме:
https://dzen.ru/kznalp
kznalp@yandex.ru

Конфигурация тестовой виртуальной машины

• CPU: 8 ядер

• RAM: 8GB

• ОС: RED OS MUROM (7.3.4)

• PostgreSQL: Postgres Pro (enterprise certified) 17.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Soft 11.4.0-1), 64-bit

• Размер тестовой БД: 10GB

Конфигурационные параметры СУБД

shared_buffers = 1919MB

Используемые термины и определения

Операционная скорость

Сумма завершенных SQL операций и числа строк полученных или затронутых оператором за промежуток времени^[7]

Методика расчета метрик оценки производительности и ожиданий СУБД

Корреляционный анализ ожиданий СУБД PostgreSQL^[6]

Нагрузка на СУБД

План нагрузочного тестирования

Для проведения нагрузочного тестирования используются три сценария с разным распределением нагрузки:

· Select only (чтение данных): вес 0.5 (50%)
· Select + Update (чтение и обновление): вес 0.35 (35%)
· Insert only (добавление записей): вес 0.15 (15%)

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

PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL^[2]

Тестовая таблица pgbench_branches

Table "public.pgbench_branches"
  Column  |     Type      | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
 bid      | integer       |           | not null |
 bbalance | integer       |           |          |
 filler   | character(88) |           |          |
Indexes:
    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)
Referenced by:
    TABLE "pgbench_accounts" CONSTRAINT "pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
    TABLE "pgbench_history" CONSTRAINT "pgbench_history_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
    TABLE "pgbench_tellers" CONSTRAINT "pgbench_tellers_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)

Планы выполнения тестовых запросов , в который участвует таблица pgbench_branches

Сценарий-1 "Select only" (для формирования плана, задан фактический параметр запроса)

Nested Loop (cost=0.84..5.28 rows=1 width=4)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4)
Index Cond: (aid = 1000)
-> Index Scan using pgbench_branches_pkey on pgbench_branches br (cost=0.28..2.49 rows=1 width=8)
Index Cond: (bid = acc.bid)

Сценарий-2 "Select + Update" (для формирования плана, задан фактический параметр запроса)

Update on pgbench_branches (cost=0.28..2.49 rows=0 width=0)
-> Index Scan using pgbench_branches_pkey on pgbench_branches (cost=0.28..2.49 rows=1 width=10)
Index Cond: (bid = 469)

Эксперимент - удаление ограничения первичного ключа в таблице pgbench_branches

ALTER TABLE pgbench_branches DROP CONSTRAINT pgbench_branches_pkey CASCADE

pgbench_db=# ALTER TABLE pgbench_branches DROP CONSTRAINT pgbench_branches_pkey CASCADE ;
NOTICE: удаление распространяется на ещё 3 объекта
DETAIL: удаление распространяется на объект ограничение pgbench_tellers_bid_fkey в отношении таблица pgbench_tellers
удаление распространяется на объект ограничение pgbench_accounts_bid_fkey в отношении таблица pgbench_accounts
удаление распространяется на объект ограничение pgbench_history_bid_fkey в отношении таблица pgbench_history

Изменение планов выполнения

Сценарий-1 "Select only" (для формирования плана, задан фактический параметр запроса)

Hash Join (cost=2.80..372.68 rows=1 width=4)
Hash Cond: (br.bid = acc.bid)
-> Seq Scan on pgbench_branches br (cost=0.00..366.78 rows=1178 width=8)
-> Hash (cost=2.79..2.79 rows=1 width=4)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4)
Index Cond: (aid = 1000)

Сценарий-2 "Select + Update" (для формирования плана, задан фактический параметр запроса)

Update on pgbench_branches (cost=0.00..369.73 rows=0 width=0)
-> Seq Scan on pgbench_branches (cost=0.00..369.73 rows=1 width=10)
Filter: (bid = 469)

Изменение производительности в ходе нагрузочного тестирования (Эксперимент-2) по сравнению с базовыми значениями (Эксперимент-1)

Операционная скорость

Для построения графиков используются отчеты по результатам нагрузочного тестирования ^[3][4]

Среднее увеличение операционной скорости в эксперименте-2 составило ~20%

Ожидания СУБД

Характерные события ожидания (wait_event)

Для формирования таблиц используются отчеты по результатам нагрузочного тестирования ^[5]

Итог эксперимента

Для данной виртуальной машины, при данном характере и сценарии нагрузки , операционная скорость в ходе Эксперимента-2 увеличилась в среднем на 20%.

В ходе Эксперимента-2 кардинально снизилась доля событий ожиданий CheckpointerComm и кардинально увеличилась доля событий ожиданий BufferContent и BufferMapping.

Таблица 27.12. События ожидания, относящиеся к типу LWLock^[8]

Ссылки на используемые материалы:

1.PG_EXPECTO : Статистический анализ производительности СУБД PostgreSQL

2.PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL

3.PG_EXPECTO : Показатели производительности и ожиданий СУБД в ходе нагрузочного тестирования

4.PG_EXPECTO : Построение графиков производительности и ожиданий по результатам нагрузочного тестирования СУБД

5.PG_EXPECTO : Диаграмма Парето по событиям ожидания СУБД

6.Корреляционный анализ ожиданий СУБД PostgreSQL - презентация по докладу, не попавшему на конференцию PGConf.СПб 2025

7.Словарь терминов , используемых при корреляционном анализе.

8.Таблица 27.12. События ожидания, относящиеся к типу LWLock

P.S. Мнение нейросети о причинах роста производительности СУБД в данном кейсе

? Почему это работает для pgbench_branches

Конкретный сценарий pgbench

В стандартном TPC-B-подобном сценарии pgbench:

• Таблица pgbench_branches содержит очень мало строк (по умолчанию = scale factor)

• При scale=685 - всего 685 строк в таблице

• Основная нагрузка - частые операции UPDATE на эту таблицу

Источник повышения производительности

-- Стандартная операция pgbench, которая выполняется часто:
UPDATE pgbench_branches SET bbalance = bbalance + 123 WHERE bid = 1;

Без индекса:

• PostgreSQL выполняет последовательное сканирование маленькой таблицы

• Нет затрат на обновление структуры B-дерева индекса

• Меньше операций ввода-вывода

С индексом:

• Каждый UPDATE требует обновления индекса

• Дополнительные блокировки и конфликты

• Больше нагрузки на WAL

⚙️ Технические детали

Ресурсы ВМ (8 CPU, 8GB RAM)

• При 8GB RAM таблица размером 685 строк полностью помещается в памяти

• Последовательное сканирование такой маленькой таблицы практически бесплатно

• 8 CPU позволяют эффективно обрабатывать высокую конкуренцию

? Ожидаемые результаты

Увеличение TPS (Transactions Per Second)

• Без PK: Выше TPS из-за уменьшения накладных расходов

• С PK: Ниже TPS, но гарантирована целостность данных

Снижение нагрузки

• Меньше операций ввода-вывода

Подтверждено в ходе эксперимента: Количество ожиданий DataFileRead снизилось на 13%

• Снижение использования CPU

• Уменьшение конфликтов блокировок

Подтверждено в ходе эксперимента: Количество ожиданий relation снизилось на 100%

? Вывод

Для данного конкретного случая (scale=685, 8 CPU, 8GB RAM) удаление индекса и PK с pgbench_branches может дать прирост производительности 15-25% в TPS за счет:

Подтверждено в ходе эксперимента: Производительность СУБД увеличилась на 20%

1. Устранения накладных расходов на обновление индекса

2. Уменьшения конфликтов блокировок при высокой конкуренции

3. Оптимизации использования ограниченных ресурсов RAM