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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Предисловие

В эпоху, когда нейросети становятся первым источником знаний для многих разработчиков, особенно важно проверять их утверждения на практике. Один из таких вопросов — прямая связь между типами ожиданий в PostgreSQL и отсутствием индексов. AI-помощники часто дают логичные, но упрощённые ответы, которые могут ввести в заблуждение при решении реальных задач оптимизации. В этой статье мы проверим экспериментально, насколько обоснованно распространённое мнение о том, что IO-ожидания однозначно указывают на проблемы с индексацией.

Вопрос

Является ли ожидания типа IO необходимым и достаточным условием отсутствия индекса для таблицы или материализованного представления в СУБД PostgreSQL ?

Ответ нейросети GigaChat

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

Ответ нейросети Aлиса

Ожидания типа IO не являются ни необходимым, ни достаточным условием для отсутствия индекса в таблице или материализованном представлении в PostgreSQL.

Это подтверждается следующими фактами:

* При наличии индекса также могут наблюдаться ожидания IO, но их количество и корреляция будут существенно ниже

* Основные признаки необходимости создания индекса при массовых операциях SELECT+UPDATE:
* Сильная корреляция ожиданий с типами IO/IPC
* Превышение количества ожиданий типа IPC над IO
* Использование Seq Scan или Parallel Seq Scan в плане выполнения запросов

* Отсутствие индекса приводит к последовательному сканированию таблицы (Seq Scan), что вызывает:
* Массовое чтение данных с диска (DataFileRead)
* Повышенные ожидания IO
* Низкую производительность запросов

Таким образом, хотя ожидания IO часто сопровождают работу с неиндексированными таблицами, их наличие/отсутствие само по себе не может служить единственным индикатором необходимости создания индекса. Требуется комплексный анализ планов запросов и корреляции различных типов ожиданий.

━━━━━━━━━━━━━━━━━━
На основе: dzen.ru postgrespro.ru tantorlabs.ru

Ответ нейросети Ask Postgres

В PostgreSQL ожидание типа IO (ввод-вывод) не является ни необходимым, ни достаточным условием для отсутствия индекса на таблице или материализованном представлении.

Пояснение:

1. Что такое ожидание IO?

Ожидание IO — это состояние, при котором процесс СУБД ожидает завершения операций чтения/записи на диске (например, чтение страницы данных с диска в буферный пул). Это метрика производительности, отражающая задержки, связанные с физическим доступом к данным, а не структурную особенность таблицы.

2. Связь с индексами

• Наличие или отсутствие индекса — это логическая структура, определяемая при создании таблицы или явно через CREATE INDEX.

• Ожидание IO возникает, когда PostgreSQL не может найти нужные данные в оперативной памяти (буферном пуле) и должен прочитать их с диска — независимо от наличия индекса.Без индекса: PostgreSQL может выполнять полное сканирование таблицы (Seq Scan), что приводит к большим IO-ожиданиям.
  С индексом: PostgreSQL может выполнять индексный сканирование (Index Scan), но если индекс не покрывает запрос, он всё равно может обращаться к таблице (Index + Heap Scan), что также вызывает IO.
  

3. Необходимость и достаточность

• Не является необходимым: Таблица без индекса может не вызывать IO-ожиданий, если все данные уже в буферном пуле (например, после нескольких повторных запросов).

• Не является достаточным: Наличие IO-ожиданий не означает отсутствие индекса — индексированные запросы тоже могут вызывать IO, если индекс или данные не кэшированы.

4. Вывод

Ожидание IO — это результат выполнения запроса, а не причина или признак отсутствия индекса.
Отсутствие индекса может способствовать увеличению IO-ожиданий, но не является единственной причиной, и наличие IO-ожиданий не доказывает отсутствие индекса.

Итог: Ожидание типа IO — независимый параметр производительности, не связанный логически с наличием индекса как с необходимым или достаточным условием.

См. документацию: [PostgreSQL Performance Monitoring] — раздел о pg_stat_activity и pg_stat_statements.

Ответ нейросети DeepSeek

Отличный и очень точный вопрос. Короткий ответ: нет, ожидания типа IO не являются ни необходимым, ни достаточным условием отсутствия индекса.

Давайте разберем это подробно, так как понимание этого различия — ключ к грамотному анализу производительности PostgreSQL.

1. Почему это не необходимое условие?

Необходимое условие означает: "Если индекса нет, то мы обязательно увидим IO ожидания".

Это неверно. Отсутствие индекса может проявляться другими типами ожиданий, или вообще не проявляться в мониторинге ожиданий в конкретный момент времени.

Пример:
Допустим, у вас есть таблица users без индекса в столбце email. Вы выполняете запрос:

SELECT * FROM users WHERE email = 'test@example.com';

Планировщик будет вынужден выполнить Sequential Scan (Seq Scan). Что при этом происходит?

• Данные уже в RAM (кеш): Если таблица небольшая и полностью находится в shared_buffers (кэш PostgreSQL) или в оперативной кэш-памяти ОС, то чтение будет происходить из памяти. В этом случае вы увидите ожидания типа CPU, а не IO (например, CPU или, более конкретно, BufferPin). Запрос будет быстрым, но он все равно загрузит CPU необходимостью просканировать всю таблицу.

• Конкуренция за блокировки: Во время полного сканирования PostgreSQL должна получить доступ к каждому блоку данных. Если в этот момент с этими блоками работают другие процессы (например, на запись), ваш запрос может ждать освобождения легковесной блокировки (LWLock), а не самого ввода-вывода.

Вывод: Отсутствие индекса может приводить к высоким CPU ожиданиям или ожиданиям Lock, если данные уже кэшированы. Таким образом, IO-ожидания не являются необходимым признаком.

2. Почему это не достаточное условие?

Достаточное условие означает: "Если мы видим IO ожидания, то это гарантированно означает, что не хватает индекса".

Это тоже неверно. IO-ожидания — это очень общий симптом, который указывает на то, что СУБД много читает с диска. Причин для этого может быть множество, и отсутствие индекса — лишь одна из них.

Другие частые причины IO-ожиданий:

1. Недостаточный размер кэша (shared_buffers): Даже если по всем запросам есть идеальные индексы, если ваш рабочий набор данных не помещается в оперативной памяти, PostgreSQL будет вынуждена постоянно "подкачивать" нужные блоки с диска, вытесняя другие. Это вызовет Read ожидания (например, datafile read).

2. Неподходящий индекс или неоптимальный запрос:
   Запрос использует индекс, но он недостаточно селективен, и PostgreSQL приходится читать много случайных страниц с диска (это часто видно как Bitmap Heap Scan после Bitmap Index Scan).
   Запрос SELECT * FROM ... с индексом, но по условию отбирается много строк. PostgreSQL решает, что дешевле прочитать всю таблицу последовательно, чем делать много случайных обращений по индексу.

3. Отсутствие индекса для части данных: У вас есть индекс, но он не покрывает все варианты использования. Например, вы ищете по WHERE status = 'pending', и индекс есть, но 99% строк имеют этот статус. Seq Scan будет эффективнее, и он вызовет IO.

4. Проблемы с вводом-выводом самой системы:
   Медленный диск (HDD вместо SSD).
   Высокая конкуренция за дисковый ресурс от других приложений.
   Неправильная настройка ОС или файловой системы.

5. Очень большие запросы: Запросы, которые сознательно читают гигантские объемы данных (аналитические отчеты, агрегации), всегда будут вызывать IO-ожидания, и это нормально. Создание индексов здесь может даже навредить, замедлив вставку данных.

Связь между отсутствием индекса и IO-ожиданиями

Несмотря на то что условие не является ни необходимым, ни достаточным, сильная корреляция между отсутствием индекса и IO-ожиданиями, безусловно, существует.

• Когда данные не влезают в оперативную память, последовательное сканирование большой таблицы без индекса — это один из самых верных способов создать мощную нагрузку на подсистему ввода-вывода и, как следствие, высокие Read ожидания.

Как правильно анализировать?

1. Используйте EXPLAIN (ANALYZE, BUFFERS): Это ваш главный инструмент. Запустите этот запрос для медленного запроса. Он покажет:
   Seq Scan — прямое указание на полное сканирование таблицы (верный признак возможного отсутствия нужного индекса).
   Количество прочитанных блокков (shared hit vs shared read):
   shared hit — данные были в кэше.
   shared read — данные пришлось читать с диска (здесь и рождаются IO-ожидания).

2. Используйте специализированные представления для анализа ожиданий:
   Включите расширение pg_stat_statements и используйте такие представления, как pg_stat_database, pg_stat_all_tables, а также системные представления для анализа событий ожидания (например, pg_stat_activity с фильтром по wait_event_type).

3. Алгоритм диагностики:
   Шаг 1: Найдите запросы с самым большим временем выполнения или наибольшим количеством вызовов (pg_stat_statements).
   Шаг 2: Проанализируйте план выполнения этих запросов через EXPLAIN (ANALYZE, BUFFERS).
   Шаг 3: Если в плане видите Seq Scan по большой таблице — это кандидат на создание индекса.
   Шаг 4: Если при этом в плане много shared read — это подтверждает, что проблема усугубляется медленным диском (IO-ожидания).

Заключение

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

Правильный подход — это не гадание по типам ожиданий, а анализ планов выполнения конкретных медленных запросов, который однозначно покажет, использует ли PostgreSQL Seq Scan из-за отсутствия подходящего индекса.

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

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

Использование PG_EXPECTO для выявления проблемных SQL запросов при анализе инцидента производительности СУБД PostgreSQL

SQL запросы, вызывающие 80% ожиданий типа IO - 43 запроса

Вопрос по ожиданиям IO

Как среди списка SQL запросов - определить запросы для которых в таблицах не хватает индексов ?

Гипотеза

Используя результаты ранее сделанных экспериментов :

PG_HAZEL : ожидания СУБД PostgreSQL при отсутствии индексов.

Использование метода доступа Seq Scan | Parallel Seq Scan совместно с использованием параллельных процессов( Workers Planned ,Workers Launched ) в плане выполнения запросов, обнаруженных в ходе корреляционного анализа.

...

ℹ️При возникновении данных условий могут возникнуть массовые ожидания IPC/BgWorkerShutdown.

Можно сделать предположение:

Подтверждающим признаком, сужающим круг поиска SQL запросов, для оптимизации которых, необходимо создать индексы в таблицах - является ожидание BgWorkerShutdown.

Проверка гипотезы

SQL запросы, вызывающие 80% ожиданий типа IPC

SQL запросы, вызывающие ожидания типа IO и IPС :

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

ИТОГ

Одновременная корреляция ожидания IPC/BgWorkerShutdown и IO/DSMFillZeroWrite может служить надежным признаком необходимости добавления индексов для таблиц, участвующих в запросах, выявленных в ходе анализа инцидента производительности СУБД, значительно сужая область оптимизации по ожиданиям IO.

Расследование инцидентов производительности в PostgreSQL часто напоминает поиск иголки в стоге сена. Десятки тысяч запросов , и определить, какой именно из них стал «слабым звеном» системы, без специальных инструментов — крайне сложная задача.

В этой статье рассмотрим, как использование PG_EXPECTO позволяет кардинально ускорить этот процесс. Мы не будем гадать на основе снимков pg_stat_statements. Вместо этого мы научимся проактивно создавать «ловушки» на проблемные паттерны производительности. Когда инцидент происходит, PG_EXPECTO позволяет быстро найти проблемные SQL-запросы , предоставляя инженеру готовый список «подозреваемых» для дальнейшей оптимизации.

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Задача

Практическое применение представленных ранее методик использования pg_expecto :

PG_EXPECTO 3.0: Когда мониторинг становится проактивным, а оптимизация — интеллектуальной.

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

Типовой шаблон расследования инцидентов PostgreSQL с помощью pg_expecto. Часть 1: Анализ на уровне СУБД

Типовой шаблон расследования инцидентов PostgreSQL с помощью pg_expecto. Часть 2: Детальный разбор инфраструктуры сервера

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

Шаг 1 - сформировать сводный отчет по метрика оценки производительности СУБД и инфраструктуры

cd /postgres/pg_expecto/sh/performance_reports/summary_report.sh '2025-11-01 10:22'

Шаг 2 - импортировать текстовые файлы в таблицы Excel

Действия аналогичны описанным ранее:

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

Раздел : Импортирование данных отчетов в Excel

Шаг 3 - Выявление аномалий инфраструктуры

1.Корреляция "Ожидания СУБД - vmstat"

Результат отчета:

• SQL запросы создают нагрузку на инфраструктуру

2. Статистика vmstat+iostat по файловой подсистеме /data

Результат отчета:

• Имеются проблемы производительности на запись для дискового устройства используемого для файловой системы /data

3. Статистика vmstat+iostat по файловой подсистеме /wal

Результат отчета:

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

4. Чек-лист IO

Результат отчета:

• Аномалий - не обнаружено.

5. Чек-лист CPU

Результат отчета:

• Аномалий - не обнаружено.

6. Чек-лист RAM

Результат отчета:

• Аномалий - не обнаружено.

7. Результат анализа инфраструктуры

Аномалии инфраструктуры, оказывающая влияние на производительность СУБД:

1. Превышение времени отклика на запись для дискового устройства используемого для файловой системы /data

Шаг 4 - корреляционный анализ производительности СУБД

Операционная скорость и ожидания СУБД в период, предшествующий инциденту

Корреляционный анализ ожиданий СУБД

Тип ожидания, имеющий наибольший коэффициент корреляции с ожиданиями СУБД - IPC

• IPC: Серверный процесс ожидает взаимодействия с другим процессом. В wait_event обозначается конкретное место ожидания;

Диаграмма Парето по событиям ожидания СУБД - для типа ожидания IPC

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

Гипотеза(спойлер)

Можно сразу предположить , что причина - отсутствие индексов.

Диаграмма Парето по ожиданиям SQL запросов для типа ожидания IPC

PG_EXPECTO : Диаграмма Парето по ожиданиям SQL запросов

Шаг 5 - формирование списка проблемных SQL запросов для последующей оптимизации

Список queryid SQL-запросов для оптимизации:

• -1701015661318396920

• 3449463017331132112

• -7715565454820708773

• 1374759154717555017

• -678327810318891437

• 5459520954633506046

• -3969322877824419761

• 3985919093425059746

Шаг 6 - получение рекомендации нейросети по снижению ожиданий типа IPC

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

Файл сформированный отчетом : net.1.wait_event.prompt.txt

Выдели общие части из текста и найти смысловые совпадения. Сформируй краткий итог по необходимым мероприятиям в виде сводной таблицы.

Входной файл для нейросети сформированный отчетом : net.1.wait_event.IPC.txt

Результат работы нейросети DeepSeek

Шаг 7 - анализ проблемных SQL запросов нейросетью

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

Файл сформированный отчетом : net.2.sql.prompt.txt

Выдели ключевые паттерны SQL запросов , с уточнением - сколько раз встречается паттерн. Сформируй итоговую таблицу - какие паттерны используются для каждого queryid. Выдели ключевые особенности SQL запроса, использующего наибольшее количество паттернов.

Входной файл для нейросети сформированный отчетом : net.2.sql.IPC.txt

Результат работы нейросети DeepSeek

Шаг 8 - Примерный план получения рекомендаций нейросети по оптимизации проблемного запроса queryid = 1374759154717555017

Проблемный запрос для оптимизации

Текст запроса

SELECT

"Table-1"."col1",

"Table-1"."col2",

"Table-2"."col1" AS "Table-2.col1",

"Table-2"."col3" AS "Table-2.col4",

"Table-2"."col6" AS "Table-2.col5",

"Table-3"."col1" AS "Table-3.col1",

"Table-3"."col6" AS "Table-3.col5",

"Table-3"."col7" AS "Table-3.col7",

"Table-3"."col8" AS "Table-3.col8"

FROM "public"."Table-1" AS "Table-1"

INNER JOIN "public"."Table-4" AS "Table-2" ON "Table-1"."col1" = "Table-2"."col6"

INNER JOIN "public"."Table-1_Table-3" AS "Table-3" ON "Table-1"."col1" = "Table-3"."col6" AND "Table-3"."col7" = 'ipr_training_id' AND "Table-3"."col8" = 'XXX'

Таблицы участвующие в запросе

Table "public.Table-1"

Column | Col2 | Collation | Nullable | Default

--------+---------------+----------+---------+---------------------

col1 | col1 | | not null | col1_generate_v1mc()

col2 | enum_task_col2 | | not null |

Indexes:

"Table-1_pcol7" PRIMARY COL7, btree (col1)

Referenced by:

TABLE "Table-4" CONSTRAINT "Table-4_col6_fcol7" FOREIGN COL7 (col6) REFERENCES Table-1(col1)

TABLE "Table-1_Table-3" CONSTRAINT "Table-1_Table-3_col6_fcol7" FOREIGN COL7 (col6) REFERENCES Table-1(col1)

TABLE "Table-1_meta" CONSTRAINT "Table-1_meta_col6_fcol7" FOREIGN COL7 (col6) REFERENCES Table-1(col1)

TABLE "templates_Table-1" CONSTRAINT "templates_Table-1_col6_fcol7" FOREIGN COL7 (col6) REFERENCES Table-1(col1)

Table "public.Table-4"

Column | Col2 | Collation | Nullable | Default

-----------+-----+----------+---------+---------------------

col1 | col1 | | not null | col1_generate_v1mc()

col3 | col1 | | not null |

col6 | col1 | | not null |

Indexes:

"Table-4_pcol7" PRIMARY COL7, btree (col1)

"Table-4_col3_col6_col7" UNIQUE CONSTRAINT, btree (col3, col6)

Foreign-col7 constraints:

"Table-4_col3_fcol7" FOREIGN COL7 (col3) REFERENCES plans(col1)

"Table-4_col6_fcol7" FOREIGN COL7 (col6) REFERENCES Table-1(col1)

Referenced by:

TABLE "Table-1_statuses" CONSTRAINT "Table-1_statuses_plan_col6_fcol7" FOREIGN COL7 (plan_col6) REFERENCES Table-4(col1)

Table "public.Table-1_Table-3"

Column | Col2 | Collation | Nullable | Default

-----------+------------------------+----------+---------+---------------------

col1 | col1 | | not null | col1_generate_v1mc()

col6 | col1 | | not null |

col7 | enum_task_attribute_col7 | | not null |

col8 | text | | not null |

Indexes:

"Table-1_Table-3_pcol7" PRIMARY COL7, btree (col1)

"col6_col7" UNIQUE CONSTRAINT, btree (col6, col7)

Foreign-col7 constraints:

"Table-1_Table-3_col6_fcol7" FOREIGN COL7 (col6) REFERENCES Table-1(col1)

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

EXPLAIN ( ANALYZE , VERBOSE , COSTS , BUFFERS , TIMING , SUMMARY )

SELECT "Table-1"."col1", "Table-1"."col2", "Table-2"."col1" AS "Table-2.col1", "Table-2"."col3" AS "Table-2.col4", "Table-2"."col6" AS "Table-2.col5", "Table-3"."col1" AS "Table-3.col1", "Table-3"."col6" AS "Table-3.col5", "Table-3"."col7" AS "Table-3.col7", "Table-3"."col8" AS "Table-3.col8"

FROM "public"."Table-1" AS "Table-1"

INNER JOIN "public"."Table-4" AS "Table-2" ON "Table-1"."col1" = "Table-2"."col6"

INNER JOIN "public"."Table-1_Table-3" AS "Table-3" ON "Table-1"."col1" = "Table-3"."col6" AND "Table-3"."col7" = 'ipr_training_id' AND "Table-3"."col8" = 'XXX';

QUERY PLAN

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

Gather (cost=51291.67..61070.22 rows=11 width=138) (actual time=212.782..228.904 rows=0 loops=1)

Output: "Table-1".col1, "Table-1".col2, "Table-2".col1, "Table-2".col3, "Table-2".col6, Table-3.col1, Table-3.col6, Table-3.col7, Table-3.col8

Workers Planned: 2

Workers Launched: 2

Buffers: shared hit=38808

-> Nested Loop (cost=50291.67..60069.12 rows=5 width=138) (actual time=198.025..198.030 rows=0 loops=3)

Output: "Table-1".col1, "Table-1".col2, "Table-2".col1, "Table-2".col3, "Table-2".col6, Table-3.col1, Table-3.col6, Table-3.col7, Table-3.col8

Inner Unique: true

Buffers: shared hit=38808

Worker 0: actual time=191.589..191.593 rows=0 loops=1

Buffers: shared hit=12830

Worker 1: actual time=191.283..191.288 rows=0 loops=1

Buffers: shared hit=10816

-> Parallel Hash Join (cost=50291.24..60066.84 rows=5 width=118) (actual time=198.023..198.027 rows=0 loops=3)

Output: "Table-2".col1, "Table-2".col3, "Table-2".col6, Table-3.col1, Table-3.col6, Table-3.col7, Table-3.col8

Inner Unique: true

Hash Cond: ("Table-2".col6 = Table-3.col6)

Buffers: shared hit=38808

Worker 0: actual time=191.587..191.590 rows=0 loops=1

Buffers: shared hit=12830

Worker 1: actual time=191.281..191.285 rows=0 loops=1

Buffers: shared hit=10816

-> Parallel Seq Scan on public.Table-4 "Table-2" (cost=0.00..9045.05 rows=278305 width=48) (never executed)

Output: "Table-2".col1, "Table-2".col3, "Table-2".col6

-> Parallel Hash (cost=50291.20..50291.20 rows=3 width=70) (actual time=197.528..197.529 rows=0 loops=3)

Output: Table-3.col1, Table-3.col6, Table-3.col7, Table-3.col8

Buckets: 1024 Batches: 1 Memory Usage: 0kB

Buffers: shared hit=38728

Worker 0: actual time=191.259..191.260 rows=0 loops=1

Buffers: shared hit=12790

Worker 1: actual time=190.969..190.970 rows=0 loops=1

Buffers: shared hit=10776

-> Parallel Seq Scan on public.Table-1_Table-3 Table-3 (cost=0.00..50291.20 rows=3 width=70) (actual time=194.885..194.885 rows=0 loops=3)

Output: Table-3.col1, Table-3.col6, Table-3.col7, Table-3.col8

Filter: ((Table-3.col7 = 'ipr_training_id'::enum_task_attribute_col7) AND (Table-3.col8 = 'XXX'::text))

Rows Removed by Filter: 1027564

Buffers: shared hit=38728

Worker 0: actual time=187.238..187.239 rows=0 loops=1

Buffers: shared hit=12790

Worker 1: actual time=187.176..187.176 rows=0 loops=1

Buffers: shared hit=10776

-> Index Scan using Table-1_pcol7 on public.Table-1 "Table-1" (cost=0.42..0.46 rows=1 width=20) (never executed)

Output: "Table-1".col1, "Table-1".col2

Index Cond: ("Table-1".col1 = "Table-2".col6)

Query Identifier: 1374759154717555017

Planning:

Buffers: shared hit=217

Planning Time: 2.928 ms

Execution Time: 228.955 ms

(49 rows)

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

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

Результат работы нейросети DeepSeek

Новый инструмент для СУБД PostgreSQL с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub :

• GitFlic

• GitHub

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

kznalp/PG_EXPECTO

PG Expecto представляет версию 3.0 с проактивным мониторингом и интеграцией с ИИ

В дополнении к стандартным возможностям расширения pg_expecto

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

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

Ключевые инновации версии 3.0:

1. Проактивный мониторинг и автоматизация реагирования

Вместо того чтобы пассивно ждать появления проблем, PG Expecto 3.0 теперь активно предотвращает их.

Система научилась формировать репрезентативные профили производительности на основе стандартизированных файлов метрик. Это позволяет создать «цифровой двойник» вашей СУБД в ее оптимальном состоянии.

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

• Автоматизация инцидентов: Данное событие служит триггером для автоматического создания инцидентов в ваших системах мониторинга (например, в Zabbix, Grafana Labs). Система самостоятельно классифицирует критичность и инициирует стандартные или приоритетные процедуры реагирования, значительно сокращая время на обнаружение проблемы (MTTD).

2. Интеграция с нейросетями для семантического анализа и оптимизации запросов

Самая передовая функция PG Expecto 3.0 — это встроенный «мозг», который помогает не только найти проблему, но и понять пути ее решения. Мы реализовали автоматическую интеграцию с современными языковыми моделями (такими как GPT, Claude и аналогичными).

• Автоматическое конструирование контекста: Система автоматически генерирует детализированные и структурированные промпты (вводные инструкции) для нейросети. В них входит вся необходимая информация: от проблемных SQL-запросов, выявленных методами корреляционной аналитики, до контекста выполнения и метрик СУБД.

• Семантический анализ рекомендаций: Нейросеть получает идеально подготовленные данные и выдает готовые, понятные рекомендации на естественном языке.

Это позволяет:
Минимизировать задержки обработки: Получать конкретные советы по настройке параметров PostgreSQL, индексов и архитектуры.
Улучшать структуру сложных SQL-запросов: Нейросеть проводит семантический разбор запросов, предлагает варианты рефакторинга, оптимизации JOIN'ов и конструкций WHERE, объясняя логику своих предложений.

Резюме для администраторов и разработчиков:

С выходом PG Expecto 3.0 работа с производительностью PostgreSQL становится проще, быстрее и интеллектуальнее. Вы получаете не просто инструмент с графиками, а проактивного помощника, который сам находит аномалии, создает тикеты и, с помощью интеграции с ИИ, дает вам готовые, обоснованные решения для их устранения.

Это следующий шаг к полностью автономной и самооптимизирующейся системе управления базами данных.

P.S. Пример анализа инцидента

PG_HAZEL + vmstat/iostat + DeepSeek: анализ инцидента производительности СУБД PostgreSQL.

«PostgreSQL — это гость в доме вашего сервера. И если в доме нет электричества (CPU), течет водопровод (память) или разъехался фундамент (диски), гостю будет некомфортно, как бы он ни был совершенен.»

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

https://gitflic.ru/project/kznalp/pg_expecto

Задача

Используя отчеты, сформированные с помощью расширения pg_expecto провести анализ производительности инфраструктуры сервера СУБД и взаимного влияния СУБД на инфраструктуру, возникновении инцидента производительности СУБД.

Начало

Типовой шаблон расследования инцидентов PostgreSQL с помощью pg_expecto. Часть 1: Анализ на уровне СУБД

Формирование отчетов по метрикам производительности СУБД и инфраструктуры

Входной параметр отчета summary_report.sh:

• Дата и время возникновения инцидента

cd /postgres/pg_expecto/performance_reports

./summary_report.sh '2025-10-25 11:09'

Результаты сводного отчета в виде текстовых файлов сохраняются в папке /tmp/pg_expecto_reports

Период формирования отчетов: 1 час .

Результирующие файлы отчетов аналогичны отчетам по нагрузочному тестированию

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

1.КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat

Фрагмент отчета:

Результаты анализа отчета:

1. Проблемы с подсистемой IO

2. SQL запросы , возможно нуждаются в оптимизации.

2.КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT для файловой системы /data

Фрагмент отчета

Результаты анализа отчета:

1. Серьезные проблемы с дисковым устройством, используемом для файловой системы /data, оказывающие влияние на производительность СУБД.

3.КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT для файловой системы /wal

Фрагмент отчета

Результаты анализа отчета:

1. Проблемы с дисковым устройством, используемом для файловой системы /wal, оказывающие влияние на производительность СУБД.

4.Чек-лист подсистемы IO

Фрагмент отчета

Результаты анализа отчета:

1. Серьезные проблемы с подсистемой IO для сервера СУБД, оказывающие влияние на производительность СУБД.

5.Чек-лист CPU

Фрагмент отчета

Результаты анализа отчета:

1. Проблем со стороны CPU, оказывающих влияние на производительность СУБД - нет.

6.Чек-лист RAM

Фрагмент отчета

Результаты анализа отчета:

1. Проблем со стороны RAM, оказывающих влияние на производительность СУБД - нет.

2. Свопинг - отсутствует.

Итог: типовой шаблон анализа производительности инфраструктуры сервера СУБД при расследования инцидентов.

1. По результатам отчета "КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat" - исключить или подтвердить влияние на производительность СУБД недостаточной производительности подсистемы IO.

2. По результатам отчета "КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat" - исключить или подтвердить влияние на инфраструктуры сервера СУБД нагрузки создаваемой выполнением SQL запросов.

3. По результатам отчетов "КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT" , "Чек-лист подсистемы IO" - исключить или подтвердить:

• Наличие проблем подсистемы IO

• Производительность подсистемы IO

4. По результатам отчета "Чек-лист CPU" - исключить или подтвердить гипотезу о недостатке вычислительных ресурсов сервера СУБД.

5. По результатам отчета "Чек-лист RAM" - исключить или подтвердить гипотезу о недостатке RAM и наличии свопинга.

«Правильно заданный вопрос — это половина ответа. Данная статья — это структурированный список вопросов, которые вы должны задать данным из pg_expecto, чтобы докопаться до истинной причины инцидента.»

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

https://gitflic.ru/project/kznalp/pg_expecto

Задача

Используя отчеты, сформированные с помощью расширения pg_expecto провести анализ метрик производительности СУБД возникновении инцидента производительности СУБД.

Формирование отчетов по метрикам производительности СУБД и инфраструктуры

Входной параметр отчета summary_report.sh:

• Дата и время возникновения инцидента

cd /postgres/pg_expecto/performance_reports

./summary_report.sh '2025-10-25 11:09'

Результаты сводного отчета в виде текстовых файлов сохраняются в папке /tmp/pg_expecto_reports

Период формирования отчетов: 1 час .

Результирующие файлы отчетов аналогичны отчетам по нагрузочному тестированию

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

1. Определение типа ожидания СУБД имеющего наибольшую корреляцию со снижением производительности СУБД

Формирование таблицы в Excel

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

Отчет:

Результат анализа отчета - определение типа ожидания с наибольшей корреляцией и абсолютным значением.

Тип ожидания IO - имеет наибольшую корреляцию с ожиданиями СУБД и оказывает наибольшее влияние на снижение производительности СУБД в целом.

2. Определение проблемных SQL запросов.

Список SQL запросов, вызывающих наибольшее количество событий ожиданий по типу ожидания, оказывающего наибольшее влияние на снижение производительности СУБД.

Формирование таблицы в Excel

PG_EXPECTO : Диаграмма Парето по ожиданиям SQL запросов

Результат анализа отчета - определение SQL запросов и событий ожиданий по типу ожидания оказывающего наибольшее влияние на снижение производительности СУБД.

Проблемные SQL запросы:

• -3805078444547199896

• -4883642671474097249

События ожидания по проблемным запросам:

• DataFileRead: Ожидание чтения из файла данных отношения.

• DataFileWrite: Ожидание записи в файл данных отношения.

• DataFileExtend: Ожидание расширения файла данных отношения.

Итог: типовой шаблон анализа производительности и ожиданий СУБД при расследования инцидентов.

1. Определение типа ожидания СУБД имеющего наибольшую корреляцию со снижением производительности СУБД

2. Определение проблемных SQL запросов.

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

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

https://gitflic.ru/project/kznalp/pg_expecto

Задача

Расчет и формирование файлов метрик оценки производительности СУБД с использованием расширения pg_expecto, для применения в системах мониторинга при необходимости реализация проактивного мониторинга производительности СУБД .

Предыдущие работы по теме:

Условие начала инцидента снижения скорости СУБД:

Если угол наклона линии регрессии операционной скорости < 0 ,

И

угол наклона линии регрессии ожиданий > 0

ТО

Создать оповещение мониторинга "Инцидент деградации производительности".

В качестве уровня важности оповещения , можно использовать абсолютное значение коэффициента корреляции :

• < 0.7 : низкий уровень

• >= 0.7 : высокий уровень.

"Индикатор снижения скорости" как сигнал для начала корреляционного анализа ожиданий СУБД.

Метрики оценки производительности СУБД PostgreSQL, используемые в оперативно-тактическом комплексе "PG_HAZEL".

Практическая реализация мониторинга производительности СУБД с использованием расширения pg_expecto

Порядок формирования метрик оценки производительности СУБД

1. Рассчитанные значения операционной скорости и ожидания СУБД сохраняются в текстовых файлах для использования системой мониторинга Zabbix

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

3. Ненулевое значение индикатора деградации производительности СУБД является стартовым событием для начала процесса решения инцидента производительности СУБД.

Пример использования метрик оценки производительности СУБД и индикатора деградации производительности СУБД в системе мониторинга Zabbix.