Postgres DBA

Предисловие

В мире высоконагруженных баз данных выбор метода пагинации может стать решающим фактором для производительности системы. Эксперимент, проведённый с двумя подходами — классическим ROW_NUMBER и отложенным соединением (Deferred Join) — показал, что даже архитектурно более совершенный метод не гарантирует победы без тонкой настройки СУБД. Исследование раскрывает, как правильная конфигурация памяти PostgreSQL перевесила преимущества Deferred Join и позволила ROW_NUMBER добиться превосходства на параллельной нагрузке до 22 сессий.

Начало экспериментов с методом пагинации ROW_NUMBER

Прогноз нейросети

Вопрос

Используя информацию о размерах таблиц, схеме баз данных и тестовых запросах, дай прогноз и обоснование - какой тестовый запрос покажет большую производительность для нагрузочного тестирования с параллельной нагрузкой от 5 до 22 сессий для СУБД PostgreSQL 17 c ресурсами CPU=8 RAM=8GB

Прогноз:
Наибольшую производительность при параллельной нагрузке (5–22 сессии) покажет запрос plan-1 (ROW_NUMBER()) после оптимизации индексами.

Экспериментальная проверка

Анализ ситуации нейросетью

Почему не оправдался прогноз нейросети о большей эффективности метода ROW_NUMBER для заданных условий ?

5. Условия, при которых ROW_NUMBER() действительно был бы эффективнее:

Сценарий 2: Адекватный размер work_mem

Если бы work_mem был ≥ 256MB для сортировки в памяти

какие значение work_mem необходимо установить для получения преимущества метода row_number при максимальной нагрузке 22 параллельные сессии при общем размере RAM=8GB ?

-- Для получения преимущества ROW_NUMBER():
SET work_mem = '180MB';
SET max_parallel_workers_per_gather = 1;
SET max_parallel_workers = 16;

-- В postgresql.conf:
work_mem = 180MB
max_parallel_workers_per_gather = 1
max_parallel_workers = 16
max_worker_processes = 16
shared_buffers = 2GB
effective_cache_size = 6GB

Задача

Экспериментальная проверка рекомендации нейросети по оптимизации метода пагинации ROW_NUMBER

Изменение конфигурационных параметров СУБД

ALTER SYSTEM SET work_mem = '180MB';

ALTER SYSTEM SET max_parallel_workers_per_gather = 1;

ALTER SYSTEM SET max_parallel_workers = 16;

ALTER SYSTEM SET max_worker_processes = 16 ;

ALTER SYSTEM SET shared_buffers = '2GB';

ALTER SYSTEM effective_cache_size = '6GB';

Производительность и ожидания СУБД в ходе нагрузочного тестирования

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

Результат

Среднее превышение операционной скорости , при использовании метода ROW_NUMBER составило 12.59%

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

Результат

Среднее снижение ожиданий СУБД, при использовании метода ROW_NUMBER составило 18.06%

Характерные особенности тестовых запросов и планов выполнения

1. Общая цель запросов

Оба запроса решают задачу случайной пагинации (выбор случайной "страницы" из 100 строк) для отфильтрованных данных (билеты с бизнес-классом).

2. Основные различия в подходах

Первый запрос (ROW_NUMBER()):

• Использует оконную функцию ROW_NUMBER() для нумерации всех строк

• Фильтрует по диапазону номеров строк после нумерации

• Ключевая проблема: Выполняет полную сортировку и нумерацию всех 2.15 млн строк

Второй запрос (Deferred Join):

• Использует отложенное соединение (deferred join)

• Сначала выбирает только ticket_no с помощью OFFSET/LIMIT

• Затем соединяет остальные данные по отобранным ключам

• Преимущество: Сортирует только ключи, а не все данные

3. Производительность

Первый запрос:

• Время выполнения: 12.65 секунд

• Основные затраты:
  Merge Semi Join: 9.3 секунды
  Сортировка 2.15 млн строк: 850-1000 мс (quicksort, 170 МБ памяти)
  WindowAgg (оконная функция): 1723 мс

Второй запрос:

• Время выполнения: ~16.1 секунды (неполные данные в выводе)

• Основные затраты:
  Merge Semi Join: 8.75 секунды
  Сортировка для OFFSET: 5.5 секунды (top-N heapsort, 15 МБ памяти)
  Hash Join: 5575 мс

4. Критические различия в планах

Сортировка:

1. Первый запрос: Сортирует все столбцы (ticket_no, book_ref, passenger_name) - 92 байта на строку

2. Второй запрос: Сортирует только ticket_no - 32 байта на строку

Использование памяти:

1. Первый: 170 МБ для сортировки всех данных

2. Второй: 15 МБ для сортировки только ключей

Фильтрация:

1. Первый: Сначала нумерует все строки, затем фильтрует 100

2. Второй: Сначала выбирает 100 ключей, затем соединяет данные

5. Проблемы обоих подходов

1. Общая проблема: floor(random() * COUNT(*)/100) требует полного сканирования для подсчета строк

2. Производительность OFFSET: Во втором запросе OFFSET должен "пропустить" много строк (случайная страница * 100)

6. Рекомендации

Для оптимизации:

1. Второй подход лучше архитектурно (deferred join), но в данном случае медленнее из-за:
   Необходимости вычислять OFFSET
   Hash Join вместо более эффективного соединения

2. Потенциальные улучшения:
   Использовать keyset pagination вместо OFFSET
   Кэшировать общее количество строк
   Рассмотреть материализованные представления для filtered_tickets

В текущей реализации:

• Первый запрос быстрее (12.65с vs ~16.1с), но использует больше памяти

• Второй запрос масштабируется лучше при увеличении размера данных, так как сортирует меньше данных

7. Вывод

Оба запроса имеют проблемы с производительностью из-за необходимости обработки всех 2.15 млн строк для случайной выборки. Deferred Join (второй подход) - более правильная архитектурная паттерн, но требует оптимизации OFFSET и соединения для повышения эффективности в данном конкретном случае.

Характерные особенности производительности СУБД

1. Производительность (SPEED)

ROW_NUMBER подход:

• Начальная производительность: 60

• Стабильная после 45 итерации: 96

• Максимум: 96 (после адаптации)

• Прирост: 60 → 96 (+60%)

Deferred Join подход:

• Начальная производительность: 52

• Стабильная после 55 итерации: 84

• Максимум: 84

• Прирост: 52 → 84 (+61.5%)

Вывод: ROW_NUMBER показывает на 14.3% выше абсолютную производительность (96 vs 84).

2. Динамика адаптации

ROW_NUMBER:

• Быстрый рост до 96 за 55 итераций

• Более резкие скачки производительности

• Ранняя стабилизация (с 55 итерации)

Deferred Join:

• Более плавный рост до 84

• Дольше адаптируется (до 55 итерации)

• Стабильнее на низких нагрузках

3. Ожидания и блокировки

ROW_NUMBER:

• TIMEOUT появляются с итерации 46 (при нагрузке 10 соединений)

• LWLOCK стабилизируются на уровне 3-6

• WAITINGS: 4-7

Deferred Join:

• IO ожидания появляются с итерации 73 (при нагрузке 15+ соединений)

• LWLOCK: 4-8 (выше, чем у ROW_NUMBER)

• WAITINGS: до 21 пикового значения

• Более выраженные скачки в ожиданиях (74 итерация: 21 ожидание)

4. Корреляция с нагрузкой

• Нагрузка растет от 5 до 22 соединений

• Критические точки:
  10 соединений (итерация 46): ROW_NUMBER начинает показывать TIMEOUT
  15 соединений (итерация 73): Deferred Join показывает IO ожидания
  18+ соединений: оба подхода стабилизируются на максимальных значениях ожиданий

5. Ключевые различия в поведении

ROW_NUMBER:

1. Выше пиковая производительность (96 vs 84)

2. Раннее появление TIMEOUT (с 10 соединений)

3. Меньше LWLOCK в среднем (3-6 vs 4-8)

4. Более предсказуемые паттерны ожиданий

Deferred Join:

1. Лучше масштабируется при низких нагрузках

2. Более стабильные WAITINGS (кроме пиков)

3. Появление IO ожиданий вместо TIMEOUT

4. Сильнее страдает от LWLOCK

6. Анализ проблемных точек

ROW_NUMBER:

• Проблема: TIMEOUT при 10+ соединениях

• Причина: WindowAgg + сортировка больших данных конкурируют за ресурсы

• Симптом: Конкуренция за CPU/память

Deferred Join:

• Проблема: IO ожидания при 15+ соединениях

• Причина: Hash Join + сортировка для OFFSET требуют disk I/O

• Симптом: Конкуренция за disk I/O и LWLOCK

7. Рекомендации по оптимизации

Для ROW_NUMBER:

1. Увеличить work_mem для уменьшения disk spills

2. Рассмотреть материализованные представления для filtered_tickets

3. Кэшировать COUNT(*) для random_page

Для Deferred Join:

1. Оптимизировать индексы для сортировки ticket_no

2. Увеличить shared_buffers для кэширования hash таблиц

3. Рассмотреть использование keyset pagination вместо OFFSET

8. Общий вывод

1. ROW_NUMBER лучше для:
   Максимальной производительности (14.3% выше)
   Систем с достаточными CPU/памятью
   Сценариев с <10 конкурентных соединений

2. Deferred Join лучше для:
   Систем с ограничениями по памяти
   Сценариев с предсказуемыми IO паттернами
   Приложений, чувствительных к TIMEOUT

3. Оба подхода требуют оптимизации при >10 конкурентных соединений

4. Критическая нагрузка: 10-15 соединений (точка деградации производительности)

Метрики производительности инфраструктуры

1. Использование CPU

ROW_NUMBER:

• Начало: 61% user, 3% system, 36% idle

• Критическая точка (итерация 50): резкий переход на 95-96% user, 4% system, 0% idle

• Стабильная нагрузка: 96% user, 4% system, 0% idle

• Вывод: Полная загрузка CPU с преобладанием пользовательского времени

Deferred Join:

• Начало: 61% user, 2% system, 36% idle

• Критическая точка (итерация 52): переход на 97% user, 3% system, 0% idle

• Стабильная нагрузка: 97% user, 3% system, 0% idle

• Вывод: Аналогичная полная загрузка, но меньше системного времени

Сравнение: Deferred Join показывает на 1% выше user CPU и на 1% ниже system CPU.

2. Использование памяти

ROW_NUMBER:

• Свободная память: 1791 → 2753 KB (+53% рост)

• Буферы: 47 → 6 KB (-87% снижение)

• Кэш: 4955 → 2751 KB (-44% снижение)

• Своп: 70 → 67 KB (минимальное изменение)

• Вывод: Активное использование кэша и буферов

Deferred Join:

• Свободная память: 4567 → 2207 KB (-51% снижение)

• Буферы: 18 → 6 KB (-67% снижение)

• Кэш: 2211 → 2728 KB (+23% рост)

• Своп: 69 → 73 KB (незначительный рост)

• Вывод: Увеличение использования кэша, снижение свободной памяти

Сравнение: ROW_NUMBER активнее использует кэш в начале, Deferred Join наращивает кэш в процессе.

3. Ввод-вывод (IO)

ROW_NUMBER:

• io_bo: 52 → 58 (+11.5% рост)

• Пиковые значения: до 59

• Вывод: Умеренный рост IO при увеличении нагрузки

Deferred Join:

• io_bo: 59 → 65 (+10% рост)

• Пиковые значения: до 68

• Вывод: Более высокий базовый уровень IO

Сравнение: Deferred Join имеет на 10-15% выше активность IO, что соответствует ожиданиям из предыдущего анализа (IO ожидания).

4. Системные метрики

ROW_NUMBER:

• Прерывания (system_in): 5687 → 8279 (+45% рост)

• Переключения контекста (system_cs): 657 → 1053 (+60% рост)

• Процессы в run queue (procs_r): 6 → 15 (+150% рост)

Deferred Join:

• Прерывания (system_in): 5652 → 8274 (+46% рост)

• Переключения контекста (system_cs): 652 → 1063 (+63% рост)

• Процессы в run queue (procs_r): 5 → 15 (+200% рост)

Сравнение: Оба подхода показывают схожий рост системных метрик, Deferred Join имеет немного больше переключений контекста.

5. Критические точки перехода

ROW_NUMBER:

• Итерация 50 (нагрузка 10): переход на 0% idle CPU

• Сопровождается: Ростом procs_r до 9

• Симптом: Резкое изменение паттерна использования памяти

Deferred Join:

• Итерация 52 (нагрузка 10): переход на 0% idle CPU

• Сопровождается: Ростом procs_r до 9

• Симптом: Снижение свободной памяти до 4000 KB

6. Корреляция с нагрузкой

• 5-8 соединений: Оба подхода работают с простоями CPU (36% idle)

• 10 соединений: Критическая точка (0% idle)

• 12-15 соединений: Стабилизация на максимальной нагрузке

• 18-22 соединений: Дальнейший рост системных метрик

7. Ключевые различия по метрикам

ROW_NUMBER преимущества:

1. Меньше IO операций (58 vs 68)

2. Лучшее использование памяти (растущая свободная память)

3. Более стабильный кэш (меньше колебаний)

Deferred Join преимущества:

1. Меньше системного времени CPU (3% vs 4%)

2. Более предсказуемое использование кэша (постепенный рост)

3. Меньше начальных буферов (более эффективное использование)

8. Проблемные паттерны

ROW_NUMBER:

• Проблема: Резкое падение кэша с 4955 до 2751

• Причина: Оконные функции требуют больших рабочих наборов

• Риск: Потенциальные page faults при недостатке памяти

Deferred Join:

• Проблема: Высокий базовый уровень IO (59 vs 52)

• Причина: Hash Join и сортировка для OFFSET

• Риск: Disk I/O bottlenecks при высокой нагрузке

9. Общие выводы по метрикам

1. Оба подхода CPU-bound: 0% idle при нагрузке ≥10 соединений

2. Deferred Join более IO-intensive: на 10-15% выше IO активность

3. ROW_NUMBER более memory-intensive: активнее использует кэш и буферы

4. Критическая нагрузка: 10 соединений для обоих подходов

5. Масштабирование: Deferred Join лучше сохраняет кэш, но требует больше IO

10. Рекомендации по оптимизации системы

Для ROW_NUMBER:

1. Увеличить work_mem для сортировки в памяти

2. Настроить shared_buffers для кэширования рабочих наборов

3. Мониторить page faults и swap активность

Для Deferred Join:

1. Оптимизировать индексы для уменьшения IO

2. Рассмотреть SSD для уменьшения latency IO операций

3. Настроить effective_io_concurrency для параллельного IO

Итог:

Выбор между подходами зависит от конфигурации системы -

• ROW_NUMBER для CPU-обеспеченных архитектур

• Deferred Join для систем с хорошим IO.

Cчитается, что индексы — это панацея для производительности СУБД, своего рода «волшебная таблетка» для ускорения запросов. Но что, если под давлением конкуренции и стремительно растущей нагрузки классические подходы дают сбой?

В этой статье показаны результаты нагрузочного тестирования PostgreSQL в условиях нагрузочного тестирования, которые привели к парадоксальному на первый взгляд выводу. Когда количество одновременных операций растет, а данные активно читаются, дорогостоящие индексы могут не справиться, превратившись из помощников во вредителей. И как оказалось, старый добрый Seq Scan — метод полного сканирования таблицы — неожиданно стал в итоге самым эффективным решением в этом сценарии.

Постановка экспериментов

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

CREATE TABLE pgbench_test

(

aid integer PRIMARY KEY ,

bid integer,

abalance integer,

filler character(84)

);

ALTER TABLE pgbench_test ADD CONSTRAINT "pgbench_test_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid);

INSERT INTO pgbench_test ( aid , bid , abalance , filler )

SELECT

id ,

floor(random() * 685 ) + 1 ,

floor(random() * (68500000 - 1 + 1)) + 1 ,

md5(random()::text)

FROM generate_series(1,1000000) id;

Тестовый запрос

select test.abalance

from pgbench_accounts acc

join pgbench_test test on (test.bid = acc.bid )

where acc.aid = current_aid ;

Эксперимент-1 : Метод доступа Seq Scan

План выполнения тестового запроса

Стоимость плана = 21620.62

Gather (cost=1002.80..21620.62 rows=1460 width=4) (actual time=4.088..329.017 rows=1468 loops=1)

Workers Planned: 3

Workers Launched: 3

-> Hash Join (cost=2.80..20474.62 rows=471 width=4) (actual time=1.821..300.589 rows=367 loops=4)

Hash Cond: (test.bid = acc.bid)

-> Parallel Seq Scan on pgbench_test test (cost=0.00..19619.81 rows=322581 width=8) (actual time=0.692..263.390 rows=250000 loops=4)

-> Hash (cost=2.79..2.79 rows=1 width=4) (actual time=0.488..0.489 rows=1 loops=4)

Buckets: 1024 Batches: 1 Memory Usage: 9kB

-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4) (actual time=0.472..0.474 rows=1 loops=4)

Index Cond: (aid = 51440641)

Planning Time: 1.977 ms

Execution Time: 329.301 ms

Эксперимент-2 : Метод доступа Bitmap Index Scan

CREATE INDEX pgbench_test_idx ON pgbench_test ( bid );

План выполнения тестового запроса

Стоимость плана = 1546.55

Nested Loop (cost=14.51..1546.55 rows=1460 width=4) (actual time=0.894..366.050 rows=1468 loops=1)

-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4) (actual time=0.217..0.227 rows=1 loops=1)

Index Cond: (aid = 51440641)

-> Bitmap Heap Scan on pgbench_test test (cost=13.94..1529.17 rows=1460 width=8) (actual time=0.669..365.312 rows=1468 loops=1)

Recheck Cond: (bid = acc.bid)

Heap Blocks: exact=1407

-> Bitmap Index Scan on pgbench_test_idx (cost=0.00..13.57 rows=1460 width=0) (actual time=0.355..0.356 rows=1468 loops=1)

Index Cond: (bid = acc.bid)

Planning Time: 2.634 ms

Execution Time: 366.419 ms

Эксперимент-3 : Метод доступа Index Only Scan

CREATE INDEX idx_pgbench_test_bid_abalance ON pgbench_test(bid) INCLUDE (abalance);

План выполнения тестового запроса

Стоимость плана = 48.86

Nested Loop (cost=0.99..48.86 rows=1460 width=4) (actual time=0.771..1.186 rows=1432 loops=1)

-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4) (actual time=0.736..0.737 rows=1 loops=1)

Index Cond: (aid = 51440641)

-> Index Only Scan using idx_pgbench_test_bid_abalance on pgbench_test test (cost=0.42..31.47 rows=1460 width=8) (actual time=0.029..0.289 rows=1432 loops=1)

Index Cond: (bid = acc.bid)

Heap Fetches: 0

Planning Time: 4.949 ms

Execution Time: 1.302 ms

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

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

1. До 8-ми соединений - наибольшая производительность получена с использованием метода доступа Bitmap Index Scan.

2. После 8-ми соединений и до 22-х - наибольшая производительность получена с использованием метода доступа Seq Scan.

Причины эффективности Seq Scan при параллельной нагрузке

1. Увеличение нагрузки на подсистему ввода-вывода

• Без индекса: Sequential Scan читает данные большими последовательными блоками

• С индексом: Index Scans создают случайный доступ к диску

2. Конкуренция за буферный кэш

• Индекс занимает место в shared_buffers

• Вытесняет полезные данные из кэша

• Каждое соединение читает разные части индекса → больше промахов кэш

3. Блокировки в системных каталогах

• При использовании индекса PostgreSQL обращается к системным каталогам

• Увеличивается конкуренция за pg_index, pg_class

• Особенно заметно при многих одновременных соединениях

4. CPU overhead

• Обработка индекса требует больше CPU операций

• Bitmap Index Scan + Bitmap Heap Scan сложнее чем простой Seq Scan

• При росте соединений CPU становится узким местом

Предисловие

В сценариях с параллельными запросами и острой конкуренцией за ресурсы паттерн EXISTS показал себя как однозначно более эффективное решение для PostgreSQL.

ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Эксперименты с Демобазой 2.0

"Демобаза 2.0" нагрузочное тестирование : СУБД оказалась устойчива к выбору между Join и коррелированным подзапросом.

Начало экспериментов "IN vs EXISTS"

Очередной раунд тестов: EXISTS против IN в условиях параллелизма.

Тестовый запрос-1 : IN

SELECT DISTINCT a.country

FROM airports_data a

JOIN routes r ON (r.arrival_airport = a.airport_code)

WHERE duration IN

(

'09:45:00' , '11:50:00' , '02:40:00' , '05:50:00' , '15:25:00' , '04:30:00' , '11:00:00' , '07:15:00' , '12:40:00' , '03:40:00' , '05:15:00' , '08:35:00' , '10:35:00' , '07:30:00' , '09:35:00' , '04:15:00' , '11:45:00' , '04:05:00' , '01:10:00' , '19:50:00' , '07:55:00' , '01:35:00' , '16:05:00' , '08:15:00' , '04:00:00' , '08:45:00' , '12:25:00' , '16:40:00' , '07:25:00' , '01:50:00' , '14:35:00' , '12:45:00' , '01:20:00' , '02:55:00' , '20:20:00' , '10:45:00' , '02:45:00' , '12:55:00' , '08:25:00' , '00:45:00' , '02:00:00' , '01:15:00' , '08:00:00' , '04:10:00' , '11:35:00' , '16:45:00' , '17:15:00' , '14:40:00' , '15:35:00' , '15:50:00' , '13:30:00' , '04:25:00' , '01:25:00' , '14:10:00' , '15:15:00' , '08:55:00' , '07:00:00' , '05:05:00' , '06:45:00' , '14:20:00' , '09:50:00' , '08:10:00' , '11:30:00' , '13:45:00' , '04:35:00' , '01:30:00' , '15:10:00' , '05:25:00' , '05:20:00' , '16:30:00' , '14:45:00' , '00:40:00' , '13:15:00' , '12:50:00' , '09:05:00' , '17:30:00' , '13:05:00' , '13:10:00' , '10:50:00' , '07:10:00' , '05:00:00' , '10:40:00' , '03:25:00' , '09:00:00' , '13:00:00' , '10:20:00' , '16:20:00' , '08:05:00' , '07:40:00' , '14:30:00' , '16:10:00' , '03:50:00' , '08:30:00' , '05:40:00' , '06:20:00' , '05:30:00' , '11:05:00' , '11:55:00' , '04:20:00' , '06:40:00' );

План выполнения тестового запроса-1 : IN

HashAggregate (cost=282.56..284.86 rows=230 width=54) (actual time=5.604..5.609 rows=17 loops=1)

Group Key: a.country

Batches: 1 Memory Usage: 40kB

-> Nested Loop (cost=0.54..276.22 rows=2534 width=54) (actual time=0.104..3.559 rows=2534 loops=1)

-> Seq Scan on routes r (cost=0.25..185.13 rows=2534 width=4) (actual time=0.049..1.650 rows=2534 loops=1)

Filter: (duration = ANY ('{09:45:00,11:50:00,02:40:00,05:50:00,15:25:00,04:30:00,11:00:00,07:15:00,12:40:00,03:40:00,05:15:00,08:35:00,10:35:00,07:30:00,09:35:00,04:15:00,11:45:00,04:05:00,01:10:00,19:50:00,07:55:00,01:35:

00,16:05:00,08:15:00,04:00:00,08:45:00,12:25:00,16:40:00,07:25:00,01:50:00,14:35:00,12:45:00,01:20:00,02:55:00,20:20:00,10:45:00,02:45:00,12:55:00,08:25:00,00:45:00,02:00:00,01:15:00,08:00:00,04:10:00,11:35:00,16:45:00,17:15:00,14:40:00,

15:35:00,15:50:00,13:30:00,04:25:00,01:25:00,14:10:00,15:15:00,08:55:00,07:00:00,05:05:00,06:45:00,14:20:00,09:50:00,08:10:00,11:30:00,13:45:00,04:35:00,01:30:00,15:10:00,05:25:00,05:20:00,16:30:00,14:45:00,00:40:00,13:15:00,12:50:00,09:

05:00,17:30:00,13:05:00,13:10:00,10:50:00,07:10:00,05:00:00,10:40:00,03:25:00,09:00:00,13:00:00,10:20:00,16:20:00,08:05:00,07:40:00,14:30:00,16:10:00,03:50:00,08:30:00,05:40:00,06:20:00,05:30:00,11:05:00,11:55:00,04:20:00,06:40:00}'::int

erval[]))

Rows Removed by Filter: 3258

-> Memoize (cost=0.29..0.39 rows=1 width=58) (actual time=0.000..0.000 rows=1 loops=2534)

Cache Key: r.arrival_airport

Cache Mode: logical

Hits: 2461 Misses: 73 Evictions: 0 Overflows: 0 Memory Usage: 11kB

-> Index Scan using airports_data_pkey on airports_data a (cost=0.28..0.38 rows=1 width=58) (actual time=0.007..0.007 rows=1 loops=73)

Index Cond: (airport_code = r.arrival_airport)

Тестовый запрос-2 : EXISTS

SELECT DISTINCT a.country

FROM airports_data a

WHERE EXISTS (

SELECT 1

FROM routes r

WHERE r.arrival_airport = a.airport_code

AND r.duration IN (

'09:45:00', '11:50:00', '02:40:00', '05:50:00', '15:25:00', '04:30:00', '11:00:00', '07:15:00',

'12:40:00', '03:40:00', '05:15:00', '08:35:00', '10:35:00', '07:30:00', '09:35:00', '04:15:00',

'11:45:00', '04:05:00', '01:10:00', '19:50:00', '07:55:00', '01:35:00', '16:05:00', '08:15:00',

'04:00:00', '08:45:00', '12:25:00', '16:40:00', '07:25:00', '01:50:00', '14:35:00', '12:45:00',

'01:20:00', '02:55:00', '20:20:00', '10:45:00', '02:45:00', '12:55:00', '08:25:00', '00:45:00',

'02:00:00', '01:15:00', '08:00:00', '04:10:00', '11:35:00', '16:45:00', '17:15:00', '14:40:00',

'15:35:00', '15:50:00', '13:30:00', '04:25:00', '01:25:00', '14:10:00', '15:15:00', '08:55:00',

'07:00:00', '05:05:00', '06:45:00', '14:20:00', '09:50:00', '08:10:00', '11:30:00', '13:45:00',

'04:35:00', '01:30:00', '15:10:00', '05:25:00', '05:20:00', '16:30:00', '14:45:00', '00:40:00',

'13:15:00', '12:50:00', '09:05:00', '17:30:00', '13:05:00', '13:10:00', '10:50:00', '07:10:00',

'05:00:00', '10:40:00', '03:25:00', '09:00:00', '13:00:00', '10:20:00', '16:20:00', '08:05:00',

'07:40:00', '14:30:00', '16:10:00', '03:50:00', '08:30:00', '05:40:00', '06:20:00', '05:30:00',

'11:05:00', '11:55:00', '04:20:00', '06:40:00'

));

План выполнения тестового запроса-2 : EXISTS

Unique (cost=299.91..300.27 rows=73 width=54) (actual time=3.071..3.114 rows=17 loops=1)

-> Sort (cost=299.91..300.09 rows=73 width=54) (actual time=3.069..3.076 rows=73 loops=1)

Sort Key: a.country

Sort Method: quicksort Memory: 25kB

-> Nested Loop (cost=191.75..297.65 rows=73 width=54) (actual time=2.457..2.942 rows=73 loops=1)

-> HashAggregate (cost=191.47..192.19 rows=73 width=4) (actual time=2.408..2.421 rows=73 loops=1)

Group Key: r.arrival_airport

Batches: 1 Memory Usage: 24kB

-> Seq Scan on routes r (cost=0.25..185.13 rows=2534 width=4) (actual time=0.048..1.834 rows=2534 loops=1)

Filter: (duration = ANY ('{09:45:00,11:50:00,02:40:00,05:50:00,15:25:00,04:30:00,11:00:00,07:15:00,12:40:00,03:40:00,05:15:00,08:35:00,10:35:00,07:30:00,09:35:00,04:15:00,11:45:00,04:05:00,01:10:00,19:50:00,07:

55:00,01:35:00,16:05:00,08:15:00,04:00:00,08:45:00,12:25:00,16:40:00,07:25:00,01:50:00,14:35:00,12:45:00,01:20:00,02:55:00,20:20:00,10:45:00,02:45:00,12:55:00,08:25:00,00:45:00,02:00:00,01:15:00,08:00:00,04:10:00,11:35:00,16:45:00,17:15:

00,14:40:00,15:35:00,15:50:00,13:30:00,04:25:00,01:25:00,14:10:00,15:15:00,08:55:00,07:00:00,05:05:00,06:45:00,14:20:00,09:50:00,08:10:00,11:30:00,13:45:00,04:35:00,01:30:00,15:10:00,05:25:00,05:20:00,16:30:00,14:45:00,00:40:00,13:15:00,

12:50:00,09:05:00,17:30:00,13:05:00,13:10:00,10:50:00,07:10:00,05:00:00,10:40:00,03:25:00,09:00:00,13:00:00,10:20:00,16:20:00,08:05:00,07:40:00,14:30:00,16:10:00,03:50:00,08:30:00,05:40:00,06:20:00,05:30:00,11:05:00,11:55:00,04:20:00,06:

40:00}'::interval[]))

Rows Removed by Filter: 3258

-> Index Scan using airports_data_pkey on airports_data a (cost=0.28..1.46 rows=1 width=58) (actual time=0.006..0.006 rows=1 loops=73)

Index Cond: (airport_code = r.arrival_airport)

Результаты сравнительного нагрузочного тестирования

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

Среднее превышение операционной скорости при использовании EXISTS составило 23.25%.

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

Среднее снижение ожиданий СУБД при использовании EXISTS составило 90.60%.

Итог

Использование паттерна EXIST повышает производительность СУБД в среднем на 20%.

Предисловие

Использование нейросетей для оптимизации баз данных кажется перспективным направлением, но реальная эффективность таких систем требует тщательной проверки. В данном исследовании проанализирована способность нейросетевой модели точно прогнозировать производительность СУБД PostgreSQL в условиях экстремальной параллельной нагрузки. Результаты демонстрируют систематические ошибки AI, связанные с неспособностью учесть динамические аспекты работы СУБД.

ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Методология эксперимента

Для тестирования прогностической способности нейросети была развернута тестовая среда PostgreSQL 17 с конфигурацией CPU=8 ядер, RAM=8GB. Создана таблица pgbench_test с 1 млн записей, выполнялся запрос с соединением по внешнему ключу. Анализировались два метода доступа: последовательное сканирование (Seq Scan) и индексное сканирование (Index Only Scan) с покрывающим индексом idx_pgbench_test_bid_abalance.

Подробности эксперимента(прогноз и анализ нейросети)

Использование нейросети для прогноза производительности СУБД PostgreSQL

Прогноз нейросети и его несоответствие реальности

Нейросеть, проанализировав планы выполнения запросов, выдала категоричный прогноз:

• Index Only Scan: оптимальная производительность (~2.5 ms) даже при 22 параллельных сессиях

• Seq Scan: катастрофическая деградация производительности (+400%) при превышении 10 сессий

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

• Среднее преимущество Seq Scan составило 9%

• В самой финальной фазе теста Index Only Scan демонстрировал лучшую производительность

• Оба метода показали сопоставимую устойчивость к нагрузке

Критический анализ ошибок нейросетевого прогноза

1. Неспособность моделировать динамическое кэширование

Нейросеть основывалась на статической стоимостной модели, игнорируя эффект прогрева БД. В реальности Seq Scan выигрывал от полного размещения данных в shared_buffers, что нивелировало его основной недостаток - физический I/O.

2. Игнорирование конкуренции за индексные структуры

Модель недооценила contention в B-деревьях при высокой параллельности. Index Only Scan, предсказанный как идеальное решение, столкнулся с блокировками страниц индекса при одновременном доступе 22 сессий.

3. Ошибочная оценка масштабируемости

Нейросеть переоценила линейность индексного доступа и недооценила эффективность параллельного Seq Scan. Распределенная нагрузка workers оказалась стабильнее концентрированной нагрузки на индекс.

Системные ограничения нейросетей в экспертизе СУБД

Проведенный эксперимент выявил фундаментальные проблемы применения AI для анализа производительности БД:

Статичность моделей - нейросети работают с моментальными снимками системы, не учитывая временные аспекты работы СУБД.

Игнорирование конкурентного доступа - модели не способны адекватно предсказать поведение системы при одновременном доступе множества процессов.

Неучет аппаратных ограничений - прогноз не учитывал реальные особенности управления памятью и планирования задач в PostgreSQL.

Рекомендации и выводы

Для эффективного использования нейросетей в экспертизе производительности PostgreSQL необходимо:

1. Обогащение данных обучения динамическими метриками (pg_stat_, pg_statio_)

2. Учет временны́х характеристик - продолжительности теста, эффекта прогрева

3. Моделирование реальной конкуренции - тестирование в условиях, приближенных к продуктивным.

4. Верификация прогнозов обязательным нагрузочным тестированием

Заключение

Нейросетевые модели демонстрируют ограниченную эффективность в прогнозировании поведения СУБД под высокой параллельной нагрузкой. Традиционные методы экспертизы с обязательным эмпирическим тестированием сохраняют критическую важность для принятия архитектурных решений в системах управления базами данных.

Результаты новых исследований указывают на необходимость создания отдельного направления по оптимизации параллельных процессов в СУБД PostgreSQL

Казань, 21.11.2025 – По результатам серии экспериментов, проведенных, был выявлен фундаментальный пробел в современных методологиях оптимизации производительности систем управления базами данных (СУБД). Установлено, что традиционные паттерны и методики оптимизации демонстрируют резкое снижение эффективности или полную неприменимость в средах с высоким уровнем параллельной обработки транзакций.

Эмпирические данные свидетельствуют о том, что при значительной конкурентной нагрузке, когда множество процессов обращаются к данным одновременно, классические подходы, такие как тонкая настройка отдельных запросов или индексация, оказываются недостаточными. Вместо ожидаемого линейного роста производительности наблюдаются нелинейные эффекты, включая интенсивную борьбу за ресурсы (contention), блокировки (locks) и деградацию общей пропускной способности системы.

На основании полученных результатов был сделан вывод о назревшей необходимости системного пересмотра принципов анализа и оптимизации СУБД. Для обеспечения устойчивой работы высоконагруженных информационных систем на базе СУБД PostgreSQL требуется выделение и глубокая проработка нового специализированного подраздела, посвященного исключительно оптимизации параллельных процессов (Parallel Processes Optimization).

Введение данной дисциплины предполагает фокусировку на таких аспектах, как:

• Анализ и минимизация конфликтов блокировок на уровне строк и таблиц.

• Оптимизация работы планировщика задач и управления памятью в условиях высокой конкуренции.

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

• Создание рекомендаций по проектированию схемы данных и логики приложений, ориентированных на параллелизм.

Этот шаг является закономерным ответом на вызовы, связанные с ростом объемов данных и требований к масштабируемости современных приложений. Новая парадигма оптимизации позволит вывести управление производительностью СУБД PostgreSQL на качественно новый уровень, обеспечивая стабильность и эффективность в высокопараллельных средах.

Контактная информация :

• Ринат Сунгатуллин

• kznalp@yandex.ru

• Postgres DBA

ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub

kznalp/PG_EXPECTO

pg-expecto pg_expecto