kznalp

Взято с основного технического канала Postgres DBA (Возможны исправления в исходной статье).

Экспериментальное сравнение двух ИИ-ассистентов при решении одной задачи оптимизации SQL-запроса к PostgreSQL: анализ планов выполнения, замеры времени (37 ms против 61 ms), выводы о стратегиях доступа к системным каталогам и гипотеза о причинах отставания одного из решений.

Предисловие

При работе со статистикой производительности PostgreSQL (расширение pgpro_stats_statements) иногда возникает задача исключить из выборки записи, для которых база данных уже удалена из системы, то есть datname IS NULL. На первый взгляд, это тривиальное условие, но способ его реализации может кардинально повлиять на скорость выполнения запроса.

Вопрос был задан двум популярным нейросетевым помощникам — DeepSeek и Ask Postgres — один и тот же исходный запрос с просьбой модифицировать его, чтобы строки с NULL в имени базы не попадали в результат. Оба ИИ предложили рабочие решения, однако их архитектурные подходы оказались принципиально разными.

В этой статье мы выполним оба запроса на реальной инсталляции PostgreSQL, сравним их планы выполнения (через EXPLAIN ANALYZE) и проанализируем, почему один из вариантов оказался заметно быстрее другого, а также выдвинем гипотезу о возможной причине менее эффективного решения.

Запрос нейросети

Модифицируй запрос "SELECT

dbid ,

( SELECT datname FROM pg_database WHERE oid = dbid ) AS datname ,

( SELECT rolname FROM pg_roles WHERE oid = userid ) ,

queryid ,

date_trunc( 'minute' , CURRENT_TIMESTAMP ) ,

wait_stats ,

calls ,

rows

FROM

pgpro_stats_statements( FALSE )

WHERE

dbid NOT IN

(

SELECT oid

FROM pg_database

WHERE

datname IN ('postgres' , 'template1' , 'template0' , 'pgpropwr' , 'performance_monitoring_db' )

)

AND dbid IS NOT NULL

AND userid IS NOT NULL

AND queryid IS NOT NULL" чтобы в результат не попадали строки , для которых datname IS NULL

Решение "DeepSeek"

Чтобы исключить строки, где datname равен NULL, можно добавить соответствующее условие в WHERE. Однако чтобы не выполнять подзапрос дважды (для выборки и фильтрации), лучше переписать запрос с использованием CROSS JOIN LATERAL или CTE.

Ниже приведён модифицированный запрос с CROSS JOIN LATERAL, который вычисляет datname один раз и автоматически исключает строки, для которых база данных не найдена (т.е. datname IS NULL):

sql

SELECT
s.dbid,
d.datname,
(SELECT rolname FROM pg_roles WHERE oid = s.userid) AS rolname,
s.queryid,
date_trunc('minute', CURRENT_TIMESTAMP) AS ts,
s.wait_stats,
s.calls,
s.rows
FROM
pgpro_stats_statements(FALSE) s
CROSS JOIN LATERAL (
SELECT datname
FROM pg_database
WHERE oid = s.dbid
) d
WHERE
s.dbid NOT IN (
SELECT oid
FROM pg_database
WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')
)
AND s.dbid IS NOT NULL
AND s.userid IS NOT NULL
AND s.queryid IS NOT NULL;

Пояснение:
CROSS JOIN LATERAL работает как внутреннее соединение: если подзапрос не возвращает строку (т.е. oid = dbid отсутствует в pg_database), то вся строка из pgpro_stats_statements исключается из результата. Это автоматически отсекает случаи, когда datname был бы NULL.

Решение Ask Postgres

Для того чтобы исключить строки, в которых datname оказывается NULL (что как раз соответствует сценарию с удаленными базами данных, описанному ранее), проще всего перенести логику проверки в блок WHERE.

Так как datname в вашем запросе является вычисляемым полем (подзапросом), вы не можете обратиться к нему по имени в WHERE напрямую. Вам нужно либо продублировать подзапрос в условии, либо обернуть весь запрос в CTE (Common Table Expression) или подзапрос.

Вариант 1: Использование CTE (наиболее читаемый и современный способ)

Мы сначала выбираем все данные, присваиваем имя столбцу datname, а затем фильтруем результат.

Скопироватьsql
WITH stats_data AS (
SELECT
dbid,
(SELECT datname FROM pg_database WHERE oid = dbid) AS datname,
(SELECT rolname FROM pg_roles WHERE oid = userid) AS rolname,
queryid,
date_trunc('minute', CURRENT_TIMESTAMP) AS ts,
wait_stats,
calls,
rows
FROM
pgpro_stats_statements(FALSE)
WHERE
dbid NOT IN (
SELECT oid
FROM pg_database
WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')
)
AND dbid IS NOT NULL
AND userid IS NOT NULL
AND queryid IS NOT NULL
)
SELECT *
FROM stats_data
WHERE datname IS NOT NULL;

Вариант 2: Добавление условия в основной WHERE (без изменения структуры)

Если вы не хотите использовать CTE, нужно добавить проверку существования записи в pg_database прямо в WHERE.

Скопироватьsql
SELECT
dbid,
(SELECT datname FROM pg_database WHERE oid = dbid) AS datname,
(SELECT rolname FROM pg_roles WHERE oid = userid),
queryid,
date_trunc('minute', CURRENT_TIMESTAMP),
wait_stats,
calls,
rows
FROM
pgpro_stats_statements(FALSE)
WHERE
-- Условие, чтобы исключить строки, где базы больше нет в каталоге
EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid)
AND dbid NOT IN (
SELECT oid
FROM pg_database
WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')
)
AND dbid IS NOT NULL
AND userid IS NOT NULL
AND queryid IS NOT NULL;

Что изменилось и почему это работает:

1. Логика фильтрации: В исходном запросе условие dbid NOT IN (...) отсекало только конкретные системные базы, но пропускало любые другие dbid, включая те, которых вообще нет в pg_database (для которых подзапрос возвращал NULL).

2. EXISTS или CTE:В Варианте 1 мы просто фильтруем итоговый набор данных по значению datname.
   В Варианте 2 мы добавили EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid), что гарантирует: строка попадет в результат только в том случае, если dbid существует в системном каталоге. Это эквивалентно условию datname IS NOT NULL.
   

3. Оптимизация: Вариант с EXISTS обычно работает быстрее, так как планировщик PostgreSQL может эффективно оптимизировать проверку существования записи.

Сравнение планов выполнения

Вариант, предложенный DeepSeek

EXPLAIN ANALYZE

SELECT

s.dbid,

d.datname,

(SELECT rolname FROM pg_roles WHERE oid = s.userid) AS rolname,

s.queryid,

date_trunc('minute', CURRENT_TIMESTAMP) AS ts,

s.wait_stats,

s.calls,

s.rows

FROM

pgpro_stats_statements(FALSE) s

CROSS JOIN LATERAL (

SELECT datname

FROM pg_database

WHERE oid = s.dbid

) d

WHERE

s.dbid NOT IN (

SELECT oid

FROM pg_database

WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')

)

AND s.dbid IS NOT NULL

AND s.userid IS NOT NULL

AND s.queryid IS NOT NULL ;

QUERY PLAN

-------------------------------------------------------------------------------------

Hash Join  (cost=3.41..227.49 rows=89 width=196) (actual time=27.978..36.352 rows=4258 loops=1)

Hash Cond: (s.dbid = pg_database.oid)

->  Function Scan on pgpro_stats_statements s  (cost=1.60..14.10 rows=493 width=64) (actual time=27.898..28.902 rows=4288 loops=1)

Filter: ((dbid IS NOT NULL) AND (userid IS NOT NULL) AND (queryid IS NOT NULL) AND (NOT (hashed SubPlan 2)))

Rows Removed by Filter: 588

SubPlan 2

->  Seq Scan on pg_database pg_database_1  (cost=0.00..1.58 rows=5 width=4) (actual time=0.038..0.044 rows=5 loops=1)

Filter: (datname = ANY ('{postgres,template1,template0,pgpropwr,performance_monitoring_db}'::name[]))

Rows Removed by Filter: 32

->  Hash  (cost=1.36..1.36 rows=36 width=68) (actual time=0.036..0.038 rows=37 loops=1)

Buckets: 1024  Batches: 1  Memory Usage: 12kB

->  Seq Scan on pg_database  (cost=0.00..1.36 rows=36 width=68) (actual time=0.020..0.025 rows=37 loops=1)

SubPlan 1

->  Index Scan using pg_authid_oid_index on pg_authid  (cost=0.14..2.36 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=4258)

Index Cond: (oid = s.userid)

Planning Time: 0.412 ms

Execution Time: 37.153 ms

(17 rows)

Вариант, предложенные Ask Postgres

EXPLAIN ANALYZE

SELECT

dbid,

(SELECT datname FROM pg_database WHERE oid = dbid) AS datname,

(SELECT rolname FROM pg_roles WHERE oid = userid),

queryid,

date_trunc('minute', CURRENT_TIMESTAMP),

wait_stats,

calls,

rows

FROM

pgpro_stats_statements(FALSE)

WHERE

-- Условие, чтобы исключить строки, где базы больше нет в каталоге

EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid)

AND dbid NOT IN (

SELECT oid

FROM pg_database

WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')

)

AND dbid IS NOT NULL

AND userid IS NOT NULL

AND queryid IS NOT NULL;

QUERY PLAN

-------------------------------------------------------------------------------------

Hash Join  (cost=3.41..356.54 rows=89 width=212) (actual time=26.218..60.295 rows=4258 loops=1)

Hash Cond: (pgpro_stats_statements.dbid = pg_database.oid)

->  Function Scan on pgpro_stats_statements  (cost=1.60..14.10 rows=493 width=64) (actual time=26.097..27.457 rows=4288 loops=1)

Filter: ((dbid IS NOT NULL) AND (userid IS NOT NULL) AND (queryid IS NOT NULL) AND (NOT (hashed SubPlan 3)))

Rows Removed by Filter: 588

SubPlan 3

->  Seq Scan on pg_database pg_database_2  (cost=0.00..1.58 rows=5 width=4) (actual time=0.069..0.075 rows=5 loops=1)

Filter: (datname = ANY ('{postgres,template1,template0,pgpropwr,performance_monitoring_db}'::name[]))

Rows Removed by Filter: 32

->  Hash  (cost=1.36..1.36 rows=36 width=4) (actual time=0.035..0.038 rows=37 loops=1)

Buckets: 1024  Batches: 1  Memory Usage: 10kB

->  Seq Scan on pg_database  (cost=0.00..1.36 rows=36 width=4) (actual time=0.021..0.026 rows=37 loops=1)

SubPlan 1

->  Seq Scan on pg_database pg_database_1  (cost=0.00..1.45 rows=1 width=64) (actual time=0.002..0.003 rows=1 loops=4258)

Filter: (oid = pgpro_stats_statements.dbid)

Rows Removed by Filter: 36

SubPlan 2

->  Index Scan using pg_authid_oid_index on pg_authid  (cost=0.14..2.36 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=4258)

Index Cond: (oid = pgpro_stats_statements.userid)

Planning Time: 0.474 ms

Execution Time: 61.394 ms

(21 rows)

Сравнение вариантов

TEST SQL-1 : Запрос предложенный DeepSeek

TEST SQL-2 : Запрос предложенный Ask Postgres

Сравни эффективность и производительность тестовых запросов

TEST SQL-1

-------------------------------------------------------

EXPLAIN ANALYZE

SELECT

s.dbid,

d.datname,

(SELECT rolname FROM pg_roles WHERE oid = s.userid) AS rolname,

s.queryid,

date_trunc('minute', CURRENT_TIMESTAMP) AS ts,

s.wait_stats,

s.calls,

s.rows

FROM

pgpro_stats_statements(FALSE) s

CROSS JOIN LATERAL (

SELECT datname

FROM pg_database

WHERE oid = s.dbid

) d

WHERE

s.dbid NOT IN (

SELECT oid

FROM pg_database

WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')

)

AND s.dbid IS NOT NULL

AND s.userid IS NOT NULL

AND s.queryid IS NOT NULL ;

QUERY PLAN

-------------------------------------------------------------------------------------

Hash Join (cost=3.41..227.49 rows=89 width=196) (actual time=27.978..36.352 rows=4258 loops=1)

Hash Cond: (s.dbid = pg_database.oid)

-> Function Scan on pgpro_stats_statements s (cost=1.60..14.10 rows=493 width=64) (actual time=27.898..28.902 rows=4288 loops=1)

Filter: ((dbid IS NOT NULL) AND (userid IS NOT NULL) AND (queryid IS NOT NULL) AND (NOT (hashed SubPlan 2)))

Rows Removed by Filter: 588

SubPlan 2

-> Seq Scan on pg_database pg_database_1 (cost=0.00..1.58 rows=5 width=4) (actual time=0.038..0.044 rows=5 loops=1)

Filter: (datname = ANY ('{postgres,template1,template0,pgpropwr,performance_monitoring_db}'::name[]))

Rows Removed by Filter: 32

-> Hash (cost=1.36..1.36 rows=36 width=68) (actual time=0.036..0.038 rows=37 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 12kB

-> Seq Scan on pg_database (cost=0.00..1.36 rows=36 width=68) (actual time=0.020..0.025 rows=37 loops=1)

SubPlan 1

-> Index Scan using pg_authid_oid_index on pg_authid (cost=0.14..2.36 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=4258)

Index Cond: (oid = s.userid)

Planning Time: 0.412 ms

Execution Time: 37.153 ms

(17 rows)

-------------------------------------------------------

TEST SQL-2

-------------------------------------------------------

EXPLAIN ANALYZE

SELECT

dbid,

(SELECT datname FROM pg_database WHERE oid = dbid) AS datname,

(SELECT rolname FROM pg_roles WHERE oid = userid),

queryid,

date_trunc('minute', CURRENT_TIMESTAMP),

wait_stats,

calls,

rows

FROM

pgpro_stats_statements(FALSE)

WHERE

-- Условие, чтобы исключить строки, где базы больше нет в каталоге

EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid)

AND dbid NOT IN (

SELECT oid

FROM pg_database

WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')

)

AND dbid IS NOT NULL

AND userid IS NOT NULL

AND queryid IS NOT NULL;

QUERY PLAN

-------------------------------------------------------------------------------------

Hash Join (cost=3.41..356.54 rows=89 width=212) (actual time=26.218..60.295 rows=4258 loops=1)

Hash Cond: (pgpro_stats_statements.dbid = pg_database.oid)

-> Function Scan on pgpro_stats_statements (cost=1.60..14.10 rows=493 width=64) (actual time=26.097..27.457 rows=4288 loops=1)

Filter: ((dbid IS NOT NULL) AND (userid IS NOT NULL) AND (queryid IS NOT NULL) AND (NOT (hashed SubPlan 3)))

Rows Removed by Filter: 588

SubPlan 3

-> Seq Scan on pg_database pg_database_2 (cost=0.00..1.58 rows=5 width=4) (actual time=0.069..0.075 rows=5 loops=1)

Filter: (datname = ANY ('{postgres,template1,template0,pgpropwr,performance_monitoring_db}'::name[]))

Rows Removed by Filter: 32

-> Hash (cost=1.36..1.36 rows=36 width=4) (actual time=0.035..0.038 rows=37 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 10kB

-> Seq Scan on pg_database (cost=0.00..1.36 rows=36 width=4) (actual time=0.021..0.026 rows=37 loops=1)

SubPlan 1

-> Seq Scan on pg_database pg_database_1 (cost=0.00..1.45 rows=1 width=64) (actual time=0.002..0.003 rows=1 loops=4258)

Filter: (oid = pgpro_stats_statements.dbid)

Rows Removed by Filter: 36

SubPlan 2

-> Index Scan using pg_authid_oid_index on pg_authid (cost=0.14..2.36 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=4258)

Index Cond: (oid = pgpro_stats_statements.userid)

Planning Time: 0.474 ms

Execution Time: 61.394 ms

(21 rows)

-------------------------------------------------------

Анализ DeepSeek

Сравнение эффективности и производительности запросов

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

1. Ключевое различие в способе получения имени базы данных (datname)

• TEST SQL-1
  Использует CROSS JOIN LATERAL (SELECT datname FROM pg_database WHERE oid = s.dbid).
  Оптимизатор преобразовал это в Hash Join между функцией pgpro_stats_statements и таблицей pg_database по s.dbid = pg_database.oid. В результате имя базы выбирается однократным последовательным сканированием pg_database и хэш-таблицей, без дополнительных подзапросов на каждую строку.

• TEST SQL-2
  Помещает подзапрос прямо в список SELECT:
  (SELECT datname FROM pg_database WHERE oid = dbid).
  Это коррелированный скалярный подзапрос, который выполняется для каждой выходной строки (4258 раз). Каждый вызов делает Seq Scan по pg_database (cost=0.00..1.45, loops=4258).
  Несмотря на малое количество строк в pg_database (37), накопленные издержки существенно замедляют запрос.

2. Планы выполнения и время

TEST SQL-1

• Основной метод чтения: Hash Join по s.dbid = pg_database.oid

• Получение datname: через тот же Hash Join (ноль отдельных вызовов)

• Получение rolname: 4258 индексных сканирований pg_authid_oid_index

• Проверка NOT IN: один SubPlan 2 – одно последовательное сканирование pg_database

• Общее время выполнения: 37.153 ms

• Время планирования: 0.412 ms

TEST SQL-2

• Основной метод чтения: Hash Join (для EXISTS) + два скалярных подзапроса

• Получение datname: 4258 последовательных сканирований pg_database (в SubPlan 1)

• Получение rolname: 4258 индексных сканирований pg_authid_oid_index

• Проверка NOT IN: один SubPlan 3 – одно последовательное сканирование pg_database

• Общее время выполнения: 61.394 ms (≈ на 65% медленнее)

• Время планирования: 0.474 ms

3. Избыточность условий

TEST SQL-2 содержит явную проверку EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid). Оптимизатор реализовал её через тот же Hash Join, который одновременно служит и для соединения. Однако в TEST SQL-1 аналогичная проверка неявно выполняется самим CROSS JOIN LATERAL, так как при отсутствии базы подзапрос не вернёт строку, и запись будет исключена. Таким образом, EXISTS в SQL-2 избыточен и не даёт преимущества, а основное замедление вызвано именно скалярным подзапросом для datname.

Вывод

TEST SQL-1 значительно эффективнее благодаря использованию латерального соединения вместо скалярного подзапроса в SELECT. Это позволяет оптимизатору получить имя базы данных один раз через Hash Join, избегая многократных последовательных сканирований pg_database.
Рекомендация: для похожих отчётов всегда выносить коррелированные подзапросы во FROM с помощью JOIN LATERAL или обычных соединений – это радикально снижает накладные расходы при большом числе строк.

Анализ Ask Postgres

Анализ планов выполнения показывает, что TEST SQL-1 значительно эффективнее, чем TEST SQL-2. Несмотря на то, что оба запроса решают одну и ту же задачу, они используют разные стратегии извлечения данных из системных каталогов.

Сравнение ключевых показателей

• Время выполнения (Execution Time):TEST SQL-1: 37.153 ms
  TEST SQL-2: 61.394 ms
  Результат: SQL-1 быстрее примерно на 40%.
  

• Метод получения имени базы данных (datname):TEST SQL-1: Использует CROSS JOIN LATERAL, который оптимизатор преобразовал в Hash Join.
  TEST SQL-2: Использует скалярный подзапрос в блоке SELECT, что привело к Seq Scan.
  

• Интенсивность доступа к pg_database:TEST SQL-1: Сканирует таблицу один раз для всего набора данных.
  TEST SQL-2: Обращается к таблице 4258 раз (по одному разу для каждой строки результата).
  

Детальный разбор причин разницы в производительности

1. Механизм получения имени базы данных (datname)

• В SQL-1 (Эффективно): Благодаря CROSS JOIN LATERAL база данных один раз сканирует таблицу pg_database, строит хэш-таблицу в памяти и за один проход сопоставляет её со всеми строками из pgpro_stats_statements.

• В SQL-2 (Неэффективно): Скалярный подзапрос в SELECT выполняется как SubPlan 1. Это означает, что для каждой из 4258 строк результата сервер заново запускает последовательное сканирование таблицы pg_database. Это создает огромную лишнюю нагрузку на CPU и память.

2. Работа с фильтрацией (EXISTS vs JOIN)

• В SQL-2 условие EXISTS в блоке WHERE действительно отфильтровало строки (через Hash Join), но это не помогло ускорить получение имени базы.

• Проблема SQL-2: База данных дважды делает одну и ту же работу. Первый раз в WHERE (через EXISTS), чтобы проверить наличие базы, и второй раз в SELECT (через подзапрос), чтобы извлечь её имя. В SQL-1 эта работа объединена в одну операцию JOIN.

3. Доступ к ролям (rolname)

• В обоих запросах доступ к pg_authid реализован через Index Scan по первичному ключу (pg_authid_oid_index). Это максимально эффективный способ, поэтому в этой части производительность запросов идентична.

Итоговый вывод

TEST SQL-1 является оптимальным выбором, так как он использует множественную операцию (Join) вместо итеративной (Scalar Subquery).

Рекомендация: Для фильтрации NULL значений (удаленных баз) используйте подход из SQL-1. Поскольку CROSS JOIN LATERAL (в данной конфигурации) работает как INNER JOIN, он автоматически отсекает все строки, для которых dbid не найден в pg_database. Дополнительные проверки IS NOT NULL в таком случае становятся избыточными, но не вредными.

Итог: сравнение нейросетей DeepSeek и Ask Postgres

DeepSeek (TEST SQL-1):

• Время выполнения: 37.153 ms

• Способ получения datname: CROSS JOIN LATERAL → преобразован в Hash Join

• Количество обращений к pg_database: 1 последовательное сканирование

• Наличие избыточных условий: нет (фильтрация через LATERAL неявная)

• Читаемость кода: чуть сложнее (требует знания LATERAL)

• Рекомендация к использованию: да (оптимальный по производительности)

Ask Postgres (TEST SQL-2):

• Время выполнения: 61.394 ms (примерно на 65% дольше, чем DeepSeek)

• Способ получения datname: скалярный подзапрос в SELECT → выполняется 4258 раз

• Количество обращений к pg_database: 4258 последовательных сканирований (SubPlan 1)

• Наличие избыточных условий: есть (EXISTS + подзапрос в SELECT — двойная работа)

• Читаемость кода: проще и интуитивнее

• Рекомендация к использованию: нет (только для очень малых выборок)

Общий вывод: DeepSeek предложил значительно более эффективное решение. Основное преимущество — использование латерального соединения, которое позволило оптимизатору PostgreSQL применить Hash Join вместо многократных коррелированных подзапросов.

Гипотеза о причине неэффективного решения, предложенного нейросетью Ask Postgres

Почему Ask Postgres выдал менее оптимальный вариант, тогда как DeepSeek сразу выбрал CROSS JOIN LATERAL?

Возможные причины:

1. Асимметрия обучающей выборки
   Ask Postgres мог быть обучен преимущественно на простых, «классических» запросах, где скалярные подзапросы в SELECT встречаются часто и на малых объёмах данных не вызывают проблем. DeepSeek же, вероятно, получил больше примеров с продвинутой оптимизацией и использованием LATERAL.

2. Отсутствие явного указания на объём данных
   Исходный запрос не содержал информации о том, что функция pgpro_stats_statements(FALSE) возвращает сотни или тысячи строк. Ask Postgres, вероятно, не сделал допущение о большом количестве записей и поэтому не стал искать метод, избегающий перебора.

3. Предпочтение краткости и прямолинейности
   Решение Ask Postgres (EXISTS в WHERE + подзапрос в SELECT) короче по символам и не требует знания конструкции LATERAL. Нейросеть могла выбрать путь наименьшего сопротивления, отдав приоритет простоте кода, а не производительности.

4. Недостаточная глубина анализа плана выполнения
   В отличие от человека, ИИ не выполняет мысленный EXPLAIN и не оценивает затраты на многократные Seq Scan. Если в обучающих данных не было достаточного числа примеров с разбором планов для подобных ситуаций, нейросеть склонна генерировать «среднестатистический» работающий запрос без учёта кардинальности.

5. Архитектурная особенность Ask Postgres
   Возможно, этот помощник сильнее заточен на синтаксическую точность и соответствие стандартам SQL, а не на специфические трюки оптимизации для PostgreSQL (где LATERAL и CROSS JOIN LATERAL позволяют эффективно обходить проблемы коррелированных подзапросов).

Послесловие

Проведённый эксперимент наглядно демонстрирует, что даже небольшие различия в написании SQL-запроса могут приводить к серьёзной разнице в производительности — в нашем случае почти 40% преимущества у решения DeepSeek. Однако не менее интересен сам факт того, что нейросети, обученные на огромных массивах текстов, могут генерировать неоптимальные планы там, где, казалось бы, хватает стандартной эвристики («не используй коррелированные подзапросы в SELECT для тысяч строк»). Это не означает, что Ask Postgres плох, но подчёркивает важность для инженера не слепо доверять ИИ, а всегда проверять реальные планы выполнения. В конечном счёте, лучший результат достигается в диалоге: человек ставит задачу, нейросеть предлагает вариант, а опытный DBA уточняет и направляет.

Практический вывод для инженеров:
При работе с ИИ-ассистентами всегда полезно давать дополнительный контекст о размере данных и требовать не просто работающего, а производительного решения. А ещё лучше — знать приёмы вроде LATERAL самому и проверять планы через EXPLAIN ANALYZE.

Взято с основного технического канала Postgres DBA (Возможны исправления в исходной статье).

Материал подготовлен с помощью нейросети DeepSeek. Не для публикации на Хабре.

От интегральной корреляции к событийно-ориентированному пространству состояний: методология сбора и кластеризации raw-событий ожидания PostgreSQL, построение марковской модели переходов между агрегированными wait-состояниями, адаптивное забывание и комбинированный прогноз риска деградации производительности на основе цепочек блокировок.

1. Анализ существующей реализации и выявление ограничений

Текущее состояние модели :

• Пространство состояний: 189 дискретных состояний, определяемых комбинацией:

• correlation (скоррелированность операционной скорости и времени ожидания, шаг 0.1 от –1.0 до +1.0)

• os_trend (тренд операционной скорости: –1, 0, +1)

• wait_trend (тренд времени ожидания: –1, 0, +1)

• Источник данных: таблица cluster_stat_median (агрегированные метрики производительности кластера)

• Обучение: однозначный переход каждую минуту, логирование в transition_log, обновление частот

• Прогноз риска: поглощающая матрица для аварийных состояний (отрицательная корреляция + снижение os_trend + рост wait_trend)

Ограничения текущей модели для анализа цепочек ожиданий:

• Не использует напрямую события ожидания PostgreSQL (wait_event_type / wait_event из pg_stat_activity)

• Работает с обобщённой корреляцией, что даёт интегральный риск, но не позволяет диагностировать конкретные цепочки блокировок (например, LWLock:BufferContent → IO:DataFileRead)

• Частота дискретизации (1 минута) может быть недостаточной для захвата быстрых переходов между событиями ожидания (субминутные паттерны)

2. Расширение источников данных: сбор цепочек ожиданий

2.1. Внедрение сбора raw-событий ожидания

Использовать расширение pg_wait_sampling (доступно с PostgreSQL 9.6+) для периодического снимка событий ожидания всех процессов

Создать таблицу wait_event_snapshots со следующими колонками:

ts (TIMESTAMPTZ NOT NULL) – время снимка

pid (INT NOT NULL) – идентификатор процесса

wait_event_type (TEXT) – тип события ожидания

wait_event (TEXT) – конкретное событие

state (TEXT) – состояние процесса

query_id (BIGINT) – идентификатор запроса

Настроить фоновый сбор (например, каждые 5–10 секунд) через background worker

2.2. Формирование цепочек ожиданий по процессам

Для каждого процесса (pid) за период активной сессии построить временную последовательность событий ожидания:

• Сгладить шум: убрать быстрое переключение между несущественными состояниями (фильтр скользящего большинства или минимальная длительность)

Сохранять цепочки в таблицу wait_event_chains:

chain_id (BIGSERIAL) – первичный ключ

pid (INT) – идентификатор процесса

start_ts (TIMESTAMPTZ) – время начала цепочки

end_ts (TIMESTAMPTZ) – время окончания цепочки

events (TEXT[]) – массив wait_event в порядке следования

3. Определение пространства состояний на основе событий ожидания

3.1. Агрегация событий в значимые состояния

• Слишком много raw-событий (более 200). Необходимо кластеризовать их в разумное число состояний (10–30) на основе:

• Группировки по wait_event_type (Lock, LWLock, IO, Client, Activity, Extension…)

• Дополнительной детализации для самых частых типов (например, отдельные состояния для LWLock:BufferContent, LWLock:WALWrite)

• Экспертных правил из документации PostgreSQL

Создать справочник wait_state_descriptions с колонками:

state_id (SMALLINT PRIMARY KEY) – идентификатор состояния

state_name (TEXT NOT NULL) – например, 'LWLock_BufferContent', 'IO_DataFileRead'

wait_event_type (TEXT) – тип события

wait_event (TEXT) – событие

is_absorbing (BOOLEAN DEFAULT FALSE) – флаг аварийного/поглощающего состояния

3.2. Функция приведения snapshot’а к состоянию

Реализовать get_wait_state_for_process(pid, ts) RETURNS SMALLINT, которая для данного процесса в момент времени возвращает идентификатор состояния на основе текущего wait_event (или NULL, если процесс активен)

Для агрегации по кластеру: основное состояние системы в момент времени – это наиболее часто встречающееся wait_event_type среди всех активных процессов (или состояние с максимальным временем ожидания)

4. Модификация модели цепи Маркова для анализа цепочек ожиданий

4.1. Новая таблица переходов для wait-событий

Аналог transition_log, но с более высокой частотой (каждые 5–10 секунд):

id (BIGSERIAL PRIMARY KEY)

ts (TIMESTAMPTZ NOT NULL)

from_state (SMALLINT NOT NULL)

to_state (SMALLINT NOT NULL)

process_pid (INT NULL) – опционально для индивидуальных цепочек

Индексы по (ts, from_state) и (from_state, to_state)

4.2. Обучение цепи (адаптация mchain_train_step)

Создать отдельную функцию wchain_train_step(), вызываемую с частотой сбора (например, каждые 10 секунд)

Логика:

• Получить текущее состояние системы на основе агрегированных wait events

• Если предыдущее состояние существует – записать переход в wait_transition_log

• Обновить wait_frequencies (аналог markov_frequencies для wait-состояний)

• Периодически (например, раз в 10 шагов) пересчитывать вероятности и применять забывание

4.3. Оценка марковского свойства для wait-цепочек

Добавить диагностическую функцию check_markov_property_wait(), которая для реальных цепочек вычисляет:

• Среднюю длину корреляции (на основе partial autocorrelation)

• Сравнение вероятностей переходов первого и второго порядка (тест отношения правдоподобия)

• Результат сохранять в markov_config как wait_markov_verified

5. Прогнозирование риска на основе цепочек ожиданий

5.1. Определение аварийных состояний в wait-пространстве

Аварийными считать состояния, соответствующие:

• Длительным блокировкам (Lock:transactionid, Lock:tuple)

• Деградации ввода-вывода (IO:DataFileRead с высоким временем)

• Сочетаниям, предшествующим deadlock’ам (по историческим данным)

• В таблице wait_state_descriptions установить флаг is_absorbing = TRUE

5.2. Функции прогноза риска (адаптация mchain_predict_risk_k)

Создать wchain_predict_risk_k(k INT) с использованием поглощающей матрицы wait_absorbing

Шаг прогноза – интервал дискретизации (например, 10 секунд). Для удобства добавить обёртки:

• wchain_predict_risk_1min

• wchain_predict_risk_5min

• (вычисляют количество шагов)

Учитывать возможность неизвестного состояния (например, если текущее состояние не встречалось в обучении) – возвращать априорный риск

5.3. Комбинированный риск

Итоговый риск инцидента производительности можно рассчитывать как взвешенную сумму:

• Риск на основе корреляции (старая модель) – для общих трендов

• Риск на основе wait-цепочек – для специфических блокировок

• Веса настраиваются через markov_config (например, wait_model_weight = 0.7)

6. Расширение таблиц конфигурации и метаданных

6.1. Новая конфигурация для wait-модели

Добавить в markov_config (или создать wait_markov_config) следующие поля:

• wait_sampling_interval_sec (INT DEFAULT 10) – интервал сбора wait-событий

• wait_min_transitions_for_forgetting (INT DEFAULT 5000) – порог числа переходов для включения забывания

• wait_absorbing_states (TEXT[]) – список названий аварийных состояний

6.2. Таблицы для wait-частот и вероятностей

• wait_frequencies (from_state, to_state, frequency)

• wait_probabilities

• wait_absorbing

• Все строятся по аналогии с существующими, но с ключом по wait_state_id

6.3. Журнал забывания для wait-модели

• Можно использовать единую таблицу apply_forgetting_log с дополнительной колонкой model_type ('correlation' / 'wait_chain')

7. Адаптация механизма забывания и достаточности данных

7.1. Раздельная проверка достаточности

Реализовать wchain_check_sufficiency(), проверяющую:

• Общее число переходов в wait_transition_log ≥ порога

• Стабильность вероятностей для wait-состояний (аналог mchain_forecast_reliability)

• Автоматическое включение забывания для wait-модели через wchain_enable_forgetting_when_sufficient()

7.2. Адаптивный alpha для wait-модели

Использовать те же принципы:

• alpha = base_alpha * exp(-days_since_incident / half_life)

• Базовый alpha может быть другим (например, 0.05 для более быстрой адаптации к изменяющимся паттернам блокировок)

• Параметры: wait_base_alpha, wait_min_alpha, wait_incident_half_life_days

8. Интеграция с существующими функциями очистки и логирования

Расширить mchain_clean_transition_log (или создать wchain_clean_transition_log) для удаления старых записей из wait_transition_log

Адаптировать mchain_clean_apply_forgetting_log для фильтрации по модели

Использовать общую таблицу mchain_error_log для ошибок в wait-функциях

9. Мониторинг и отладка для wait-цепочек

9.1. Функции текущего состояния

• wchain_get_current_state() – возвращает wait_state_id текущего агрегированного состояния системы

• wchain_get_process_chain(pid, interval) – показывает цепочку ожиданий для конкретного процесса за заданный интервал

9.2. Отчёт достоверности

Дополнить mchain_reliability_report() секцией по wait-модели:

• Рейтинг достоверности для wait-цепочек (0–5)

• Рекомендации по настройке частоты сбора

10. Поэтапный план внедрения

Подготовка (1 неделя)

• Создание таблиц для сбора snapshot’ов

• Написание скрипта сбора

Разработка состояний (1 неделя)

• Анализ wait-событий на реальной нагрузке

• Кластеризация событий

• Создание справочника wait_state_descriptions и функции get_wait_state_id()

Реализация базовой цепи (2 недели)

1. Создание таблиц wait_frequencies, wait_transition_log

• Функция wchain_train_step (без забывания)

• Тестовое обучение

Прогнозирование (1 неделя)

• Реализация wchain_predict_risk_k и поглощающей матрицы

• Проверка на исторических данных о инцидентах

Забывание и достаточность (1 неделя)

• Адаптация wchain_apply_forgetting

• wchain_check_sufficiency

• Интеграция с markov_config

Интеграция с существующей моделью (1 неделя)

• Функция комбинированного риска

• Настройка весов

• Автоматический выбор модели

Тестирование и документирование (2 недели)

• Нагрузочное тестирование

• Сравнение точности прогнозов старой и новой модели

• Написание документации

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

• ➡️Возможность предсказывать инциденты производительности, связанные с конкретными цепочками блокировок (например, «через 10 минут высокая вероятность deadlock из-за накопления LWLock:BufferContent»)

• ➡️Повышение точности прогноза за счёт использования более детерминированных сигналов (wait events) вместо косвенной корреляции

• ➡️Диагностические отчёты: «ваша система 80% времени проводит в состоянии IO:DataFileRead, переход в LWLock:WALWrite с вероятностью 0.3 ведёт к деградации за 15 минут»

• ➡️Единая архитектура, позволяющая в будущем добавлять другие источники состояний (например, статистику индексов, размер очереди блокировок)

12. Рекомендации по дальнейшему развитию

• Использовать скрытые марковские модели (HMM) для учёта ненаблюдаемых факторов (например, внутренних очередей ОС)

• Внедрить неоднородные цепи Маркова с учётом времени суток и дня недели (циклическая нагрузка)

• Автоматическое определение аварийных состояний на основе исторических инцидентов (обучение с учителем)

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

Взято с основного технического канала Postgres DBA (Возможны исправления в исходной статье).

Экспериментальная верификация диагностики преднамеренно созданных проблем производительности инфраструктуры и СУБД PostgreSQL на основе нагрузочного тестирования с пуассоновским распределением сессий и имитацией инцидента VACUUM FREEZE в среде PG_EXPECTO 10.1.3

Предисловие

Современные методики нагрузочного тестирования СУБД требуют не только генерации синтетической нагрузки, приближенной к реальным паттернам работы приложений, но и способности контролируемо воспроизводить аномальные режимы эксплуатации, такие как внезапное возрастание конкуренции за ресурсы или выполнение фоновых обслуживающих операций. В рамках настоящего исследования представлен комплекс PG_EXPECTO версии 10.1.3, расширяющий возможности нагрузочного тестирования PostgreSQL за счёт имитации пуассоновского потока сессий (период теста – бесконечный, среднее количество сессий – 40–50 в час) и встроенного сценария инцидента – принудительного выполнения VACUUM FREEZE на эталонной таблице pgbench_accounts. Ключевой особенностью эксперимента стало умышленное занижение критических параметров конфигурации СУБД (shared_buffers = 200 МБ, work_mem = 16 МБ, эффективный размер кэша – 1 ГБ) до заведомо недостаточного уровня. Целью работы являлась экспериментальная проверка способности PG_EXPECTO корректно идентифицировать заранее известные проблемы инфраструктуры (дисковая подсистема, оперативная память, планировщик ввода-вывода) и установить первопричину инцидента производительности, возникшего в ходе теста.

Дополнительные возможности по настройке нагрузочного тестирования версии PG_EXPECTO 10.1.3 с помощью файла конфигурации param.conf

Нагрузочное тестирование с имитацией распределения Пуассона

# Параметры Пуассоновского распределения

period_hours = 2

average_load = 40

Результат : Период теста = 2 часа (+1 час на разогрев метрик), среднее количество сессий pgbench в час = 40.

Бесконечный тест с имитацией распределения Пуассона

# БЕСКОНЕЧНЫЙ ТЕСТ.
# ДЛЯ ОСТАНОВКИ
# /postgres/pg_expecto/sh/load_test/load_test_stop.sh
period_hours = -1
average_load = 40

Результат : Тест не будет остановлен , средняя количество сессий в каждой итерации теста = 40

Имитация инцидента (дополнительная нагрузка vacuum/freeze)

#vacuum_incident = 1

Результат : В случайную минуту, в течении часа запускается дополнительная нагрузка на СУБД с помощью выполнения vacuum freeze на таблице pgbench_accounts

# Выполняем VACUUM через psql. Все настройки – только для этой сессии.

${PSQL} -d "${PGDATABASE}" -U "${PGUSER}" -v ON_ERROR_STOP=1 <<-SQL

SET vacuum_cost_delay = ${VACUUM_COST_DELAY};

SET vacuum_cost_limit = ${VACUUM_COST_LIMIT};

VACUUM FREEZE ${TABLE_NAME};

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

Тестовые настройки СУБД

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

postgres=# show shared_buffers;

shared_buffers

----------------

200MB

(1 row)

postgres=# show work_mem ;

work_mem

----------

16MB

(1 row)

Конфигурация нагрузочного тестирования : param.conf

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ

# Тестовая БД

testdb = default

# Тип синтетической нагрузки

load_mode = olap

# Параметры Пуассоновского распределения

period_hours = -1

average_load = 50

# Имитация инцидента - vacuum

vacuum_incident = 1

# Веса сценариев по умолчанию

scenario1 = 0.7

scenario2 = 0.2

scenario3 = 0.1

# Размер тестовой БД

#~10GB

scale = 685

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

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

Рис.1 График изменения операционной скорости в процессе инцидента.

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

Рис.2 График изменения ожиданий СУБД в процессе инцидента.

1. Сводный отчет по метрикам СУБД и ОС

Autovacuum работает очень интенсивно (более 170 запусков в час), но удаляет мизерное количество страниц (80–122).

...

Длительность autovacuum в инциденте почти удвоилась (117,7 сек против 61,1 сек) при том же количестве операций – вероятно, из-за возросшей конкуренции за IO или блокировок.

...

За час создаётся ~480 временных файлов общим объёмом ~21 ГБ. Это прямое следствие использования диска для сортировок/хэшей, не помещающихся в work_mem (16 МБ).

...

Диск данных (vdd) – критическая перегрузка: util 100%, задержки чтения/записи >15 мс, очередь >50.

...

RAM (7,5 ГБ) с shared_buffers=200 МБ и effective_cache_size=1 ГБ – возможно, недостаточно для рабочего набора.

Итог : Ключевые проблемы определены корректно.

2. Аналитический отчет по инциденту производительности СУБД PostgreSQL

Итоговый аналитический отчёт по инциденту производительности PostgreSQL

Общая информация

Периоды наблюдения:

• Тестовый отрезок: 2026-06-05 12:30 – 13:30

• Инцидент: 2026-06-05 13:30 – 14:30

Конфигурация:

• PostgreSQL 17.5 (Postgres Pro Enterprise), 8 vCPU, RAM 7.5 ГБ

• shared_buffers = 200 МБ, effective_cache_size = 1 ГБ, work_mem = 16 МБ

• random_page_cost = 1.1 (SSD-ориентированное значение)

• checkpoint_timeout = 3600 с, max_wal_size = 4 ГБ, min_wal_size = 2 ГБ

• autovacuum включён (workers=4, scale_factor=0.2, analyse_scale_factor=0.005)

• vm.dirty_background_ratio = 10%, vm.dirty_ratio = 30%, vm.swappiness = 1

Краткое описание меток

• Подтверждено — значение получено из предоставленных метрик или прямого математического следствия.

• Вероятно — вывод основан на косвенных признаках, корреляциях или общеизвестных практиках, но не подтверждён прямыми данными.

• Предположение — гипотеза, для проверки которой необходимы дополнительные данные либо данные отсутствуют.

• Неизвестно — термин или метрика не фигурируют в отчёте, значение неизвестно.

Ключевые проблемы СУБД и инфраструктуры

1. Критическая перегрузка дискового устройства данных (vdd)

• Тезис: Дисковое устройство данных работает на пределе пропускной способности: утилизация 100%, задержки чтения/записи >15 мс, глубина очереди 54–72.

• Способ подтверждения: iostat показатели за оба периода: %util = 99,97–99,98%, r_await = 11–20 мс, w_await = 15–16 мс, aqu_sz = 54–72.

• Способ опровержения: Если бы %util был ниже 50%, а r_await и w_await <5 мс.

• Метка: Подтверждено

2. Доминирование IO-ожиданий и их влияние на производительность

• Тезис: Ожидания ввода-вывода (IO) остаются критическим фактором в обоих периодах, причём в инциденте их связь с общими ожиданиями стала исключительно сильной (R²=0,92).

• Способ подтверждения: В тесте для IO: корреляция 0,7972, R²=0,64; в инциденте: корреляция 0,959, R²=0,92, ВКО 0,84.

• Способ опровержения: Если бы в инциденте R² для IO был ниже 0,6 или ВКО ниже 0,2.

• Метка: Подтверждено

3. Два проблемных запроса генерируют почти все ожидания

• Тезис: Два конкретных запроса (queryid -76972891903573700 и 7783752063509965868) являются основными источниками IO- и LWLock-ожиданий: на них приходится >97% всех IO-ожиданий.

• Способ подтверждения: Диаграммы Парето, где на эти два queryid приходится 65–68% и 32–35% IO-ожиданий соответственно.

• Способ опровержения: Если бы распределение ожиданий было равномерным между многими запросами.

• Метка: Подтверждено

4. Массовое создание временных файлов (temp_files)

• Тезис: За час создаётся ~480 временных файлов общим объёмом ~21 ГБ – прямое следствие того, что операции сортировки/хэширования не помещаются в work_mem (16 МБ).

• Способ подтверждения: temp_files = 479–481, temp_bytes ≈ 21 ГБ/час.

• Способ опровержения: Если бы temp_files отсутствовали или объём был менее 1 ГБ/час.

• Метка: Подтверждено

5. Аномально долгие контрольные точки (checkpoint)

• Тезис: Время записи контрольной точки (2415–3167 секунд) и синхронизации (540–742 секунды) огромно; контрольные точки запускаются из-за заполнения max_wal_size (4 ГБ), а не по тайм-ауту.

• Способ подтверждения: 3–4 checkpoint за час при checkpoint_timeout=3600с (ожидалось 1); длительность записи >> тайм-аута.

• Способ опровержения: Если бы время записи было менее 600 секунд и checkpoint запускались только по тайм-ауту.

• Метка: Подтверждено

6. Низкая эффективность autovacuum

• Тезис: Autovacuum запускается более 170 раз в час, но удаляет лишь 80–122 страницы из сотен тысяч оставшихся – параметр scale_factor=0.2 слишком консервативен для больших таблиц.

• Способ подтверждения: Оставлено страниц 430 340–450 193, удалено 80–122 (<0,03%).

• Способ опровержения: Если бы autovacuum удалял значительную долю мёртвых кортежей.

• Метка: Вероятно

7. Высокая конкуренция за CPU

• Тезис: Очередь процессов на CPU (procs r) стабильно превышает число ядер (8) в 3–4 раза, доля us+sy = 100% времени – хроническая нехватка CPU.

• Способ подтверждения: vmstat: procs r = 30–34 (при 8 ядрах), us+sy >80% – 100% периода.

• Способ опровержения: Если бы procs r был ниже числа ядер или us+sy <80%.

• Метка: Подтверждено

8. Переключения контекста (cs) и прерывания (in) сильно коррелируют

• Тезис: Высокая корреляция между context switches и interrupts (r=0,946 в тесте, 0,758 в инциденте) указывает на то, что переключения контекста вызваны прерываниями от дискового IO.

• Способ подтверждения: Коэффициенты корреляции и R² из раздела 2.1.

• Способ опровержения: Если бы cs коррелировали в основном с us или sy.

• Метка: Подтверждено

9. Недостаток свободной RAM и риск OOM

• Тезис: Свободная RAM постоянно менее 5% (100% периода) – это повышает риск отказа в выделении памяти (OOM) и может вызывать рециркуляцию страниц.

• Способ подтверждения: free RAM = 128–133 МБ при общей RAM 7,5 ГБ (<5%).

• Способ опровержения: Если бы свободной RAM было >10% постоянно.

• Метка: Подтверждено

10. Появление ошибок lock_not_available в инциденте

• Тезис: Три ошибки lock_not_available (55P03) в инциденте указывают на попытки захвата блокировки, не удавшиеся из-за тайм-аута (deadlock_timeout=1000 мс) – косвенный признак конкуренции за ресурсы.

• Способ подтверждения: Лог ошибок за период инцидента.

• Способ опровержения: Если бы таких ошибок не было.

• Метка: Подтверждено

Рекомендации по оптимизации СУБД и инфраструктуры

Рекомендации для СУБД

1. Оптимизировать два доминирующих запроса

• Тезис: Необходимо получить планы выполнения queryid -76972891903573700 и 7783752063509965868, устранить массовые чтения и записи временных файлов, добавить индексы или переписать запросы.

• Способ подтверждения: После оптимизации должно снизиться значение DataFileRead и BuffileWrite в диаграммах Парето.

• Способ опровержения: Если после изменений IO-ожидания не уменьшатся.

• Метка: Вероятно

2. Увеличить work_mem

• Тезис: Увеличить work_mem с 16 МБ до 128–256 МБ (с учётом max_connections=100) для снижения использования temp_files.

• Способ подтверждения: Снижение temp_bytes и количества временных файлов.

• Способ опровержения: Если temp_files не уменьшатся.

• Метка: Вероятно

3. Настроить контрольные точки

• Тезис: Увеличить max_wal_size до 16–32 ГБ и уменьшить checkpoint_timeout до 900–1800 с, чтобы контрольные точки были более частыми, но менее тяжёлыми.

• Способ подтверждения: Снижение времени записи и синхронизации checkpoint, уменьшение max WAL usage.

• Способ опровержения: Если время записи останется более 1000 секунд.

• Метка: Вероятно

4. Настроить autovacuum

• Тезис: Уменьшить autovacuum_vacuum_scale_factor для больших таблиц (например, до 0,05) и увеличить autovacuum_max_workers (до 8).

• Способ подтверждения: Увеличение доли удалённых страниц при том же количестве запусков.

• Способ опровержения: Если autovacuum продолжит удалять менее 1% оставшихся страниц.

• Метка: Вероятно

5. Увеличить shared_buffers и effective_cache_size

• Тезис: Увеличить shared_buffers с 200 МБ до 1–2 ГБ (25% RAM), а effective_cache_size – до 4–5 ГБ для улучшения кэширования.

• Способ подтверждения: Рост hit ratio и снижение DataFileRead.

• Способ опровержения: Если hit ratio не изменится или снизится.

• Метка: Вероятно

Рекомендации для инфраструктуры

1. Улучшить дисковую подсистему данных

• Тезис: Перенести табличное пространство данных на более быстрый диск (NVMe) или выделить отдельный LUN с лучшей IOPS/латентностью; увеличить effective_io_concurrency до 100–200.

• Способ подтверждения: Снижение %util, r_await, w_await и aqu_sz по данным iostat.

• Способ опровержения: Если задержки и утилизация останутся на прежнем уровне.

• Метка: Подтверждено

2. Настроить параметры dirty pages ядра

• Тезис: Уменьшить vm.dirty_ratio до 10–15% и vm.dirty_background_ratio до 5%, чтобы снизить накопление грязных страниц и синхронные записи.

• Способ подтверждения: Снижение корреляции dirty pages с wa и bo, уменьшение длительности checkpoint.

• Способ опровержения: Если dirty pages продолжат достигать 40%+ RAM.

• Метка: Вероятно

3. Увеличить объём RAM

• Тезис: Увеличить RAM до 16–32 ГБ, чтобы рабочий набор данных помещался в кэш страниц и shared_buffers, и всегда был запас свободной памяти.

• Способ подтверждения: Снижение свободной RAM <5% более не наблюдается, уменьшение IO-ожиданий.

• Способ опровержения: Если после увеличения RAM IO-ожидания не снизятся.

• Метка: Вероятно

4. Масштабировать CPU при необходимости

• Тезис: После устранения IO-узких мест, если загрузка CPU останется высокой, увеличить число vCPU или использовать реплики чтения.

• Способ подтверждения: После оптимизации запросов и IO показатель procs r станет близким к числу ядер.

• Способ опровержения: Если procs r снизится сам собой после других оптимизаций.

• Метка: Предположение

Необходимая дополнительная информация для продолжения анализа и оптимизации производительности СУБД и инфраструктуры

1. Планы выполнения (query plans) для двух проблемных queryid, включая реальное использование памяти, сортировок и хэш-таблиц.

2. Размеры объектов БД (таблиц, индексов) и количество мёртвых кортежей для оценки эффективности autovacuum.

3. Логи PostgreSQL за период инцидента для выявления предупреждений (checkpoint occurring too frequently, temporary file size exceeds temp_file_limit и т.п.).

4. Текущие значения параметров автовакуума для конкретных таблиц (per-table settings).

5. Статистика по блокировкам (pg_locks, pg_blocking_pids) для анализа lock_not_available.

6. Данные о сетевой задержке и пропускной способности (если есть удалённые подключения).

7. Тип и характеристики дискового массива (HDD/SSD, RAID-уровень, общая нагрузка на гипервизоре) для проверки несоответствия random_page_cost=1.1 реальному оборудованию.

8. Тренды долгосрочной статистики (а не только за 2 часа) для выявления сезонности или постепенной деградации.

Общий технический итог

В ходе эксперимента с бесконечным пуассоновским потоком сессий и имитацией инцидента VACUUM FREEZE комплекс PG_EXPECTO 10.1.3 позволил корректно и с высокой степенью детализации установить все преднамеренно заложенные дефекты инфраструктуры и конфигурации PostgreSQL.

Аналитический отчёт, сгенерированный инструментом, зафиксировал критическую перегрузку дискового устройства данных (утилизация 99,97–99,98 %, задержки чтения/записи >15 мс, глубина очереди 54–72), что подтверждено метриками iostat; доминирование ожиданий ввода-вывода с коэффициентом детерминации R² = 0,92 в период инцидента; массовое создание временных файлов (около 480 файлов объёмом ~21 ГБ/час) вследствие недостаточного work_mem; аномальную длительность контрольных точек (2415–3167 секунд записи); низкую эффективность автовакуума (более 170 запусков в час при удалении менее 0,03 % мёртвых страниц); хроническую нехватку оперативной памяти (свободно <5 % от 7,5 ГБ) и процессорного времени (очередь на CPU в 3–4 раза превышает число ядер).

Все перечисленные проблемы были выявлены инструментом именно в том составе и с теми количественными характеристиками, которые были заложены в экспериментальную конфигурацию, что подтверждает валидность диагностических алгоритмов PG_EXPECTO.

Послесловие

Представленный эксперимент демонстрирует, что PG_EXPECTO 10.1.3 выступает не только как генератор нагрузки, но и как полноценная платформа для воспроизведения и последующего анализа инцидентов производительности PostgreSQL в контролируемых условиях.

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

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

Дальнейшее развитие комплекса предполагает расширение библиотеки сценариев инцидентов (имитация сетевых задержек, внезапного отказа реплики, всплеска блокировок) и интеграцию с системами мониторинга для автоматизированной оценки эффективности рекомендаций.

Взято с основного технического канала Postgres DBA (Возможны исправления в исходной статье).

Корреляционный анализ аномалий автовакуума, перераспределения типов ожиданий (BufferIO vs. Extension) и дисковой утилизации в высоконагруженной СУБД PostgreSQL (192 CPU, 1 ТБ RAM).

Предисловие

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

В настоящей работе представлены результаты применения комплекса pg_expecto к инциденту, зафиксированному на конфигурации с 192 виртуальными CPU и 1 ТБ оперативной памяти.

В фокусе исследования — количественная оценка сдвигов корреляционных связей между wait_event_type (IPC, Extension, IO, Lock), показателями iostat (util, aqu_sz), статистикой автовакуума и ошибками блокировок.

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

Инцидент производительности СУБД

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

Рис.1 График изменения операционной скорости в процессе инцидента.

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

Рис.2 График изменения ожиданий СУБД в процессе инцидента.

Аналитический отчет по инциденту производительности СУБД PostgreSQL

Общая информация

• Период теста: 2026-05-18 09:10 – 10:10 (1 час)

• Период инцидента: 2026-05-18 10:10 – 11:10 (1 час)

• Версия PostgreSQL: 15.13

• Аппаратная конфигурация: 192 CPU (Intel Xeon Platinum 8280L, 4 сокета по 48 ядер), RAM 1007.58 GB, KVM виртуализация

• Дисковые устройства: vdg (WAL), vdh, vdi, vdj, vdk (data, LVM /data), vdc (/backup), vde (/log)

• Ключевые параметры СУБД: shared_buffers = 251807 MB, effective_cache_size = 747230 MB, work_mem = 1 GB, autovacuum_naptime = 1s, checkpoint_timeout = 15 min, max_wal_size = 8 GB, random_page_cost = 1.1, max_parallel_workers_per_gather = 0

Краткое описание меток

• Подтверждено — значение получено из предоставленных метрик или прямого математического следствия.

• Вероятно — вывод основан на косвенных признаках, корреляциях или общеизвестных практиках, но не подтверждён прямыми данными.

• Предположение — гипотеза, для проверки которой необходимы дополнительные данные либо данные отсутствуют.

• Неизвестно — термин или метрика не фигурируют в отчёте, значение неизвестно.

Ключевые проблемы СУБД и инфраструктуры

1. Аномальная активность автовакуума

• Тезис: Количество операций autovacuum выросло с 693 до 18 642 за час (+2590%) при одновременном падении числа удалённых страниц с 19 930 до 2 068 (–89.6%). Это свидетельствует о чрезмерно частых, но малоэффективных запусках vacuum.

• Способ подтверждения: Прямое сравнение метрик из раздела 3.2 отчёта (операций autovacuum, удалено страниц).

• Способ опровержения: Если бы рост числа операций сопровождался пропорциональным ростом удалённых страниц.

• Метка: Подтверждено

2. Смещение ожиданий в сторону IPC (BufferIO) и Extension

• Тезис: В инциденте 90.98% всех ожиданий приходится на BufferIO (тип IPC), 9.02% – на Extension. Ожидания IO и Lock статистически незначимы (p > 0.05). Корреляция SPEED с WAITINGS в инциденте отрицательная (r = –0.9184, R²=0.84).

• Способ подтверждения: Диаграммы Парето по wait_event_type и корреляционный анализ из разделов 1 и 1.1 отчёта.

• Способ опровержения: Если бы в инциденте сохранилась значимость корреляций IO или Lock (p < 0.05, ВКО ≥ 0.01).

• Метка: Подтверждено

3. Высокая и стабильная загрузка дисков data без прямой корреляции со скоростью

• Тезис: Диски vdh, vdi, vdj, vdk работают с утилизацией 91–92% и глубиной очереди >1 в 100% времени. Однако корреляция операционной скорости с IOPS и MBps слабая (R² < 0.4), поэтому дисковая подсистема не является прямым ограничением производительности.

• Способ подтверждения: iostat метрики (util, aqu_sz) и корреляции SPEED–IOPS / SPEED–MBps из раздела 2.1.4.

• Способ опровержения: Если бы R² для SPEED и IOPS был >0.6.

• Метка: Подтверждено

4. Рост ошибок блокировок и появление взаимоблокировки

• Тезис: В инциденте число ошибок lock_not_available выросло с 34 до 58 (+70%), зафиксирован один deadlock_detected (было 0). Ожидания Lock и LWLock при этом статистически незначимы.

• Способ подтверждения: Сравнение статистики ошибок из раздела 3.1.

• Способ опровержения: Если бы количество ошибок не изменилось или снизилось.

• Метка: Подтверждено

5. Изменение корреляционной структуры между ожиданиями и системными метриками

• Тезис: В тесте ожидания IPC и Extension сильно коррелировали с переключениями контекста (cs), прерываниями (in) и системным временем (sy). В инциденте эти корреляции исчезли, а ожидания Extension стали коррелировать с пользовательским временем (us, R²=0.68).

• Способ подтверждения: Сравнение R² из раздела 1.3 отчёта (тест vs инцидент).

• Способ опровержения: Если бы в инциденте сохранились высокие значения R² для cs, in, sy.

• Метка: Подтверждено

6. Отсутствие временных файлов (temp_files = 0)

• Тезис: Временные файлы не создавались ни в тесте, ни в инциденте. Это означает, что выделенного work_mem (1 GB) достаточно для всех сортировок и хеш-таблиц.

• Способ подтверждения: Значение 0 в статистике temp_files (раздел 3.4).

• Способ опровержения: Если бы temp_files > 0.

• Метка: Подтверждено

7. Недостаток данных для оценки корреляции vmstat/wa с iostat/util в инциденте

• Тезис: В тесте корреляция wa и util для data-устройств была высокой (R² 0.61–0.66). В инциденте эта корреляция не рассчитана, поэтому связь неизвестна.

• Способ подтверждения: Отсутствие раздела «1. КОРРЕЛЯЦИЯ VMSTAT и IOSTAT» для инцидента в source.txt.

• Способ опровержения: Если бы в инциденте были приведены значения корреляции.

• Метка: Неизвестно

Рекомендации по оптимизации СУБД и инфраструктуры

Рекомендация 1. Изменить параметры autovacuum

• Тезис: Увеличить autovacuum_naptime с 1с до 5–10 с, пересмотреть autovacuum_vacuum_scale_factor (с 0.001 до 0.01–0.02) и настроить autovacuum_vacuum_cost_delay/cost_limit для снижения влияния vacuum на основную нагрузку.

• Способ подтверждения: После изменений число операций autovacuum за час должно снизиться до сотен, длительность – уменьшиться.

• Способ опровержения: Если нагрузка не изменится – возможно, высокая скорость обновления строк требует иного подхода (например, partitioning).

• Метка: Подтверждено (на основе аномальной активности и общеизвестной практики)

Рекомендация 2. Исследовать и оптимизировать запросы, лидирующие по ожиданиям

• Тезис: Проанализировать планы запросов для queryid из топов Парето (например, -4280293605113329019, -1757223094415174739) с помощью auto_explain. Искать недостающие индексы, неэффективные сканирования, избыточные параллельные операции.

• Способ подтверждения: После оптимизации ожидания по IPC должны снизиться.

• Способ опровержения: Если планы запросов оптимальны – проблема в другом (например, в расширениях).

• Метка: Предположение (требуется анализ планов запросов)

Рекомендация 3. Проверить расширения (Extension)

• Тезис: Определить, какие расширения активны (postgres_fdw, dblink, кастомные) и какой код они выполняют. Рассмотреть перенос вызовов в фоновые процессы или оптимизацию логики.

• Способ подтверждения: Снижение ожиданий Extension после отключения/оптимизации.

• Способ опровержения: Если ожидания не связаны с расширениями, а ошибочно классифицированы.

• Метка: Предположение

Рекомендация 4. Рассмотреть увеличение max_parallel_workers_per_gather

• Тезис: Текущее значение 0 отключает параллельные запросы. Для аналитических операций это может быть неоптимально, но включение параллелизма может усилить конкуренцию за буферы. Требуется анализ планов запросов.

• Способ подтверждения: Рост операционной скорости для тяжёлых запросов при осторожном увеличении параметра.

• Способ опровержения: Ухудшение ожиданий IPC из-за увеличения параллельных сканирований.

• Метка: Предположение

Рекомендация 5. Снизить утилизацию дисков data

• Тезис: Перенести наиболее активные таблицы/индексы на отдельные табличные пространства (другие диски). Уменьшить effective_io_concurrency (с 300 до 100–200) для снижения глубины очереди.

• Способ подтверждения: Снижение %util и aqu_sz на data-дисках.

• Способ опровержения: Если утилизация останется высокой – объём IO слишком велик для текущей дисковой подсистемы.

• Метка: Вероятно

Рекомендация 6. Настроить мониторинг автовакуума и блокировок

• Тезис: Добавить алерты на число операций autovacuum >1000 в час и на длительные блокировки в pg_stat_activity.

• Способ подтверждения: Быстрое обнаружение аномалий в будущем.

• Способ опровержения: Если аномалии не повторятся – мониторинг не сработает.

• Метка: Вероятно

Необходимая дополнительная информация для продолжения анализа и оптимизации производительности

1. Планы запросов (auto_explain) для queryid, лидирующих по ожиданиям IPC и Extension (особенно -4280293605113329019, -1757223094415174739, -5248322003985466995).

2. Список активных расширений и их конфигурация (pg_extension, параметры для postgres_fdw, если используется).

3. Данные о самых обновляемых/удаляемых таблицах (pg_stat_user_tables: n_tup_upd, n_tup_del, n_tup_hot_upd) для оценки необходимости автовакуума.

4. Статистика контрольных точек с разбивкой по времени (checkpoint_write_time, checkpoint_sync_time) и распределение записываемых буферов – для оценки влияния на IO.

5. Топ запросов по времени выполнения и по числу блокировок (pg_stat_statements, если включён).

6. Логи автовакуума (с параметром log_autovacuum_min_duration) – для понимания, какие таблицы вакуумируются чаще всего.

7. Измерения пропускной способности дисковой подсистемы (максимальные IOPS и MBps на устройство) – для оценки запаса.

8. Данные о использовании буферного кэша PostgreSQL (pg_buffercache) – для оценки эффективности shared_buffers.

Общий технический итог

Проведённый анализ подтверждает, что непосредственным триггером инцидента производительности явилась аномальная активация автовакуума: за час число операций выросло на 2590% при снижении объёма удаляемых страниц на 89,6%, что свидетельствует о чрезмерно частых, но малопродуктивных запусках. Доминирующим типом ожиданий стал BufferIO (IPC-группа, 90,98%) при статистически незначимых вкладах IO и Lock; отрицательная корреляция операционной скорости с ожиданиями достигла r = –0,9184 (R²=0,84). Дисковая подсистема data-массива эксплуатировалась с утилизацией 91–92% и глубиной очереди >1 в 100% времени, однако прямая связь скорости с IOPS/MBps оказалась слабой (R²<0,4), что исключает диск как первичное узкое место.

Выявлено изменение корреляционной структуры: ожидания IPC и Extension перестали коррелировать с переключениями контекста и прерываниями, но Extension стали значимо связаны с пользовательским временем (R²=0,68). Отсутствие временных файлов (temp_files=0) указывает на достаточность work_mem (1 ГБ), тогда как рост ошибок lock_not_available (+70%) и появление deadlock_detected, при одновременной незначимости ожиданий Lock, требуют пересмотра логики блокировок на уровне прикладных запросов.

Послесловие

Представленный анализ выявил ряд зон неопределённости, требующих дополнительных инструментальных измерений. Для верификации гипотез о роли расширений (Extension) необходима детализация активных модулей (postgres_fdw, кастомные расширения) и их планов выполнения. Также настоятельно рекомендуется получение планов запросов для queryid, лидирующих по ожиданиям IPC и Extension (например, -4280293605113329019), с помощью auto_explain, а также включение расширенного логирования автовакуума (log_autovacuum_min_duration) и сбора pg_stat_statements.

Лишь после этого возможно окончательное заключение о необходимости увеличения max_parallel_workers_per_gather или переноса горячих таблиц на отдельные табличные пространства.

Предложенный в работе методологический каркас pg_expecto, однако, уже сейчас позволяет уверенно дифференцировать инфраструктурные и внутрисистемные аномалии производительности PostgreSQL.