Postgres DBA

«Мы пожинаем wait_event посеянных нами индексов. pg_expecto — это наш урожайный калькулятор.»

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

Используя инструментарий на основе свободного расширения pg_expecto[1], в процессе нагрузочного тестирования[2], выявить ключевые события ожидания (wait_event), негативно влияющие на производительность базы данных, при избыточном количестве индексов на тестовых таблицах.

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

• CPU: 8 ядер

• RAM: 8GB

• ОС: RED OS MUROM (7.3.4)

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

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

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

shared_buffers = 1919MB

postgres.auto.conf

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

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

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

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

Эксперимент-1

Базовый тест с использование индексов, созданных для тестовых таблиц:

Table "public.pgbench_accounts"
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

Table "public.pgbench_branches"
Indexes:
"pgbench_branches_pkey" PRIMARY KEY, btree (bid)

Table "public.pgbench_tellers"
Indexes:
"pgbench_tellers_pkey" PRIMARY KEY, btree (tid)

Эксперимент-2

Дополнительные индексы на тестовых таблицах:

Table "public.pgbench_branches"
"pgbench_branches_idx1" btree (bbalance)
"pgbench_branches_idx2" btree (filler)

Table "public.pgbench_history"
"pgbench_history_idx1" btree (tid)
"pgbench_history_idx2" btree (bid)
"pgbench_history_idx3" btree (aid)
"pgbench_history_idx4" btree (delta)
"pgbench_history_idx5" btree (mtime)
"pgbench_history_idx6" btree (filler)

Table "public.pgbench_tellers"
"pgbench_tellers_idx1" btree (bid)
"pgbench_tellers_idx2" btree (tbalance)
"pgbench_tellers_idx3" btree (filler)

Table "public.pgbench_accounts"
"pgbench_accounts_idx1" btree (bid)
"pgbench_accounts_idx2" btree (abalance)
"pgbench_accounts_idx3" btree (filler)

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

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

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

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

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

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

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

Операционная скорость и ожидания СУБД

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

Операционная скорость СУБД

Среднее уменьшение операционной скорости составило 13.97%

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

Типы ожиданий СУБД (wait_event_type)

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

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

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

Операционная скорость в ходе Эксперимента-2 снизилась в среднем ~14%.

Характерные события ожидания в ходе Эксперимента-2, существенно изменились. Наибольший рост(более 50%) отмечен по событиям ожидания типа LWLock:

• LockManager : 100%

• BufferContent: > 60%

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

• BufferContent: Ожидание при обращении к странице данных в памяти.

• LockManager : Ожидание при чтении или изменении информации о «тяжёлых» блокировках.

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

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

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

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

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

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

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

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

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

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

Лучше неделя нагрузочного тестирования с pg_expecto, чем одна бессонная ночь на боевом сервере.

Задача

1. Выполнить нагрузочное тестирование СУБД PostgreSQL, с использованием расширения pg_expecto .

2. Подготовить данные отчетов для анализа результатов нагрузочного тестирования.

Методика проведения нагрузочного тестирования

Для создания нагрузки используется инструмент pgbench, позволяющий параллельно запускать несколько сценариев выполнения SQL-запросов. В данной методике предусмотрено три типа сценариев:

- «Select only» – выборка данных без изменений;

- «Select + Update» – чтение и обновление записей;

- «Insert only» – добавление новых записей.

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

- Select only: вес = 0.5

- Select + Update: вес = 0.35

- Insert only: вес = 0.15

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

Общая нагрузка возрастает экспоненциально, а длительность одной итерации составляет 10 минут. Максимальное значение нагрузки определяется значением соответствующего параметра в конфигурационном файле.

1. Настройка и запуск нагрузочного тестирования СУБД PostgreSQL

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

2. Подготовка отчетов по результатам нагрузочного тестирования

Скрипт для формирования исходных текстовых файлов для импорта в Excel

cd /postgres/pg_expecto/performance_reports

./load_test_report.sh

Исходные текстовые файлы для импорта в Excel

1.Производительность и ожидания СУБД

• postgres._load_test.txt : график производительности по итерациями нагрузочного тестирования.

• postgres.1.cluster_report_meta.txt : Показатели производительности и ожиданий СУБД.

• postgres.1.cluster_report_4graph.txt : Данные для построения графиков производительности и ожиданий СУБД.

• postgres.2.wait_event.txt : Диаграмма Парето по событиям ожидания СУБД.

• postgres.3.queryid.txt : Диаграмма Парето по ожиданиям SQL запросов.

• postgres.x.sql_list.txt : Список SQL запросов.

2. История выполнений и событий ожиданий по SQL запросам тестовых сценариев

scenario.N.wait_event_type.txt

• История выполнений и событий ожидания по типу ожидания wait_event_type.

• По тестовому сценарию N (от 1 до 3)

3. Статистические значения vmstat , iostat

• linux.1.waitings_vmstat_corr.txt : Корреляция ожиданий СУБД и показателей vmstat

• linux.2.vmstat_iostat_DEVICE.txt : Статистические показатели iostat для дискового устройства DEVICE

• linux.3.vmstat_io.txt : Чек-лист IO

• linux.4.vmstat_cpu.txt : Чек-лист CPU

• linux.5.vmstat_ram.txt : Чек-лист RAM

• linux.x.iostat_DEVICE_meta.txt : Показатели iostat для дискового устройства DEVICE

• linux.x.iostat_DEVICE_4graph.txt : Данные для построения графиков показателей iostat дискового устройства DEVICE

• linux.x.vmstat_meta.txt : Показатели vmstat

• linux.x.vmstat_4graph.txt : Данные для построения графиков показателей vmstat

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

1. Производительность и ожидания СУБД

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

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

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

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

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

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

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

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

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

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

2. История выполнений и событий ожиданий по SQL запросам тестовых сценариев

PG_EXPECTO : История выполнений и событий ожиданий по SQL запросам тестовых сценариев

3. Статистические значения vmstat , iostat

Корреляция ожиданий СУБД и показателей vmstat

PG_EXPECTO : Корреляция ожиданий СУБД и показателей vmstat

Статистические показатели iostat для дискового устройства

PG_EXPECTO : Статистические показатели iostat для дискового устройства

Чек-лист IO

PG_EXPECTO : Чек-лист IO

Чек-лист CPU

PG_EXPECTO : Чек-лист CPU

Чек-лист RAM

PG_EXPECTO : Чек-лист RAM

Показатели iostat для дискового устройства

PG_EXPECTO : Показатели iostat для дискового устройства

Данные для построения графиков показателей iostat дискового устройства

PG_EXPECTO : Данные для построения графиков показателей iostat дискового устройства

Показатели vmstat

PG_EXPECTO : Показатели vmstat

Данные для построения графиков показателей vmstat

PG_EXPECTO : Данные для построения графиков показателей vmstat

PG_EXPECTO — свободное расширение для всестороннего анализа производительности PostgreSQL

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

kznalp/PG_EXPECTO

Казань, 15.10.2025 — Сегодня сообществу PostgreSQL представлено расширение pg_expecto, предназначенное для глубокого статистического анализа и тестирования производительности СУБД. Релиз первой версии знаменует собой появление мощного и свободно распространяемого инструмента для администраторов баз данных и разработчиков.

О расширении pg_expecto

pg_expecto: Статистика, которую вы ожидали. И даже больше.

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

Ключевые особенности pg_expecto 1.0:

• Свободное распространение и открытость: Исходный код расширения доступен в репозитории GitFlic, что способствует развитию открытого сотрудничества, независимой проверке и постоянному улучшению инструмента.

• Всесторонний статистический и корреляционный анализ: Расширение выполняет глубокий анализ производительности PostgreSQL и событий ожидания (wait_event_type/wait_event), помогая установить корреляцию между внутренним состоянием СУБД и общей производительностью системы.

• Мониторинг операционной системы: pg_expecto выходит за рамки самой СУБД и включает в себя инструменты для сбора и анализа метрик операционной системы с помощью утилит vmstat и iostat. Это позволяет напрямую увязать нагрузку на диск, память и процессор с поведением базы данных.

• Встроенное нагрузочное тестирование: Пользователи могут проводить нагрузочные тесты непосредственно с помощью pg_expecto, оценивая, как база данных ведет себя под давлением, и определяя пределы ее производительности.

• Подготовка данных для построения отчетов для Excel: Развитые возможности построения отчетов позволяют легко экспортировать результаты анализа в форматы, совместимые с Microsoft Excel, что упрощает дальнейшую обработку, визуализацию и представление данных руководству.

• Обширная база знаний: В распоряжение пользователей передан большой объем результатов экспериментов и исследований по проекту pg_hazel, служащего ценным источником знаний и практических примеров для проведения эффективного анализа.

Использование расширения pg_expecto

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

Ближайшие планы развития

• Версия 2 - формирование файлов метрик производительности и ожиданий СУБД. История инцидентов производительности СУБД.

• Версия 3 - формирование промптов для нейросети по результатам анализа производительности и ожиданий СУБД.

Контакты :

• Сунгатуллин Ринат Раисович

• kznalp@yandex.ru

• GitFlic

Для варианта 3 , всё есть на основном дзен-канале : Postgres DBA

P.S. Дело в том, что в настоящее время продукт основан на расширении pgpro_stats, не используемом для ванильного PostgreSQL. Но ничто не мешает использовать pg_stat_statements + pg_wait_sampling, если имеет смысл тратить на это время.

Начало работ по теме

PG_HAZEL + DeepSeek : Семантический анализ текста и NLP при анализе инцидента производительности СУБД PostgreSQL.

Задача

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

2. Используя семантический анализ выявить аномалии SQL запросов.

Период штатной производительности СУБД(1)

Инцидент снижения производительности СУБД(2)

Диаграмма Парето по wait_event

Семантический анализ SQL запросов по типу ожидания IPC между периодами штатной производительности (1) и снижением производительности (2)

Общие паттерны

Паттерн 1: SELECT с LEFT OUTER JOIN и сложной фильтрацией

SELECT main_table.*, joined_tables.*

FROM main_table

LEFT OUTER JOIN table1 ON ...

LEFT OUTER JOIN table2 ON ... AND (conditions)

WHERE (main_table.deleteDateTime IS NULL AND main_table.field IN (...))

Пример: Запросы к successionPlan с джойнами riskLeaving и successor

Паттерн 2: CTE с оконными функциями и рекурсивной структурой

WITH cte1 AS (SELECT ..., row_number() OVER (PARTITION BY ...) FROM ...),

cte2 AS (SELECT ... FROM cte1 JOIN multiple_tables ON complex_conditions)

SELECT array_agg(...) FROM cte2 WHERE ... OR level = (SELECT max(level) FROM cte2)

Пример: Запрос с CTE orgUnitIds и responsible

Паттерн 3: Комплексный SELECT с цепочкой JOIN

SELECT main_table.*, multiple_joined_tables.*

FROM main_table

INNER JOIN table1 ON ... AND (condition)

INNER JOIN table2 ON ...

LEFT OUTER JOIN table3 ON ...

LEFT OUTER JOIN table4 ON ...

LEFT OUTER JOIN table5 ON ...

WHERE main_table.uuid = $1

Пример: Запрос к plans с джойнами plans_meta, positions, plans_statuses и др.

Паттерн 4: Простой SELECT из VIEW

SELECT * FROM view_name WHERE view_name.foreign_key IN (...)

Пример: Запрос к employeeManagerView

Паттерн 5: SELECT с фильтрацией и сортировкой

SELECT fields FROM table

WHERE field1 = $1 AND field2 != $2

ORDER BY date_field DESC

LIMIT $3

Пример: Запрос к таблице rating

Паттерн 6: SELECT с INNER JOIN по атрибутам

SELECT main_table.*, joined_tables.*

FROM main_table

INNER JOIN table1 ON ...

INNER JOIN table2 ON ... AND table2.key = $1 AND table2.value = $2

Пример: Запрос к tasks с джойнами plans_tasks и tasks_attributes

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

Паттерн 7: UPDATE с множественными полями

UPDATE table

SET field1=$1, field2=$2, ..., fieldN=$N

WHERE primary_key1 = $M AND primary_key2 = $K

Пример: UPDATE запрос к auditLog

Паттерн 8: SELECT COUNT

SELECT count(*) AS count FROM table WHERE foreign_key = $1

Пример: Подсчёт записей в plans_meetings

Паттерн 9: SELECT с IN для одного параметра

SELECT fields FROM table WHERE foreign_key IN ($1)

Пример: Запрос к excludeSendEmailToNotificationSettings

Итог по ожиданию типа IPC:

Паттерн 1 2

SELECT с LEFT OUTER JOIN ✅ ✅

CTE с оконными функциями ✅ ✅

Комплексный SELECT с JOIN ✅ ✅

SELECT из VIEW ✅ ✅

SELECT с сортировкой и LIMIT ✅ ✅

SELECT с INNER JOIN по атрибутам ✅ ✅

UPDATE с множественными полями❌ ✅

SELECT COUNT ❌ ✅

SELECT с IN для одного параметра ❌ ✅

Период штатной производительности содержит 6 уникальных паттернов, период деградации производительности содержит все 9 паттернов.

Семантический анализ SQL запросов по типу ожидания LWLock между периодами штатной производительности (1) и снижением производительности (2)

Общие паттерны

Паттерн 1: Установка временной зоны

SET TimeZone='<+00>-00'

Частота: Повторяется многократно в обоих файлах

Паттерн 2: SELECT successionPlan с LEFT OUTER JOIN

SELECT "SuccessionPlan".*, "riskLeaving".*, "successors".*

FROM "successionPlan"

LEFT OUTER JOIN "riskLeaving" ON ...

LEFT OUTER JOIN "successor" ON ... AND (deleteDateTime IS NULL)

WHERE ("SuccessionPlan"."deleteDateTime" IS NULL

AND "SuccessionPlan"."positionId" IN (...)

AND "SuccessionPlan"."archiveDate" IS NULL)

Особенности: Одинаковая структура с разными значениями positionId

Паттерн 5: INSERT с множественными значениями

INSERT INTO "table" (field1, field2, ...)

VALUES ($1, $2, ...)

Примеры: INSERT INTO "request", INSERT INTO "outbox_v1"

Паттерн 6: SELECT из outbox_v1 с пагинацией

SELECT "id", "stream", "subject", "payload", "options"

FROM "outbox_v1"

ORDER BY "createdAt" ASC

LIMIT $1 OFFSET $2

Встречается только в период штатной производительности(1)

Паттерн 3: SELECT improvementPlan с агрегацией

SELECT ip.*, array_agg(g.id) as goals, array[]::json[] as ...

FROM "improvementPlan" ip

LEFT JOIN "improvementPlanGoal" g ON ...

WHERE ip.id > ...

GROUP BY ip.id

LIMIT 1000

Паттерн 4: UPDATE с RETURNING

UPDATE "table" SET ... WHERE ... RETURNING fields

Пример: UPDATE "offerApprovalStatusHistory"

Паттерн 7: Системный мониторинговый запрос

SELECT current_database(), s1.relname AS table, seq_scan, idx_scan, ...

FROM pg_stat_user_tables s1

JOIN pg_class c ON s1.relid = c.oid

WHERE NOT EXISTS (SELECT ... FROM pg_locks WHERE ...)

Встречается только в период деградации производительности (2)

Паттерн 8: UPDATE auditLog с множественными полями

UPDATE "auditLog"

SET field1=$1, field2=$2, ..., fieldN=$N

WHERE primary_key_conditions

Паттерн 9: SELECT improvementPlanGoalToCourse с цепочкой JOIN

SELECT "ImprovementPlanGoalToCourse".*, joined_tables.*

FROM "improvementPlanGoalToCourse"

LEFT OUTER JOIN "courseSession" ON ...

LEFT OUTER JOIN "participation_results" ON ...

INNER JOIN "improvementPlanGoal" AS "goal" ON ...

LEFT OUTER JOIN "improvementPlan" AS "goal->improvementPlan" ON ...

WHERE "deleteDateTime" IS NULL AND id > ...

ORDER BY "id" ASC

LIMIT 200

Паттерн 10: SELECT task с пагинацией

SELECT "Task".*, "module"."title", "process"."title"

FROM "task" AS "Task"

LEFT OUTER JOIN "module" ON ...

LEFT OUTER JOIN "processes" ON ...

WHERE "Task"."targetEmpCodeId" = ...

LIMIT 500 OFFSET ...

Паттерн 11: SELECT user с подзапросом

SELECT u.*, eC.*, eCTP.*

FROM "user" AS u

JOIN "empCodeToUser" AS eCTU ON ...

JOIN "empCode" AS eC ON ...

JOIN "empCodeToPosition" eCTP ON eCTP.id = (

SELECT id FROM "empCodeToPosition" AS subECTP

WHERE ...

ORDER BY subECTP.id DESC LIMIT 1

)

WHERE u."isEnabled" AND NOW() < u."endDateTime" AND ...

Паттерн 12: SELECT plans_tasks с цепочкой INNER JOIN

SELECT "PlansTasksModel".*, multiple_joined_tables.*

FROM "plans_tasks" AS "PlansTasksModel"

INNER JOIN "tasks" AS "task" ON ...

INNER JOIN "tasks_meta" AS "task->meta" ON ...

INNER JOIN "tasks_attributes" AS "task->filter" ON ...

LEFT OUTER JOIN "tasks_attributes" AS "task->attributes" ON ...

LEFT OUTER JOIN "tasks_statuses" AS "statuses" ON ...

WHERE "PlansTasksModel"."plan_uuid" = ...

Паттерн 13: UPDATE с массовыми параметрами

UPDATE "user" SET ... WHERE id IN ($1, $2, ..., $1002)

Особенности: Очень большое количество параметров (1002)

Паттерн 14: INSERT с RETURNING

INSERT INTO "table" (...) VALUES (...) RETURNING fields

Пример: INSERT INTO "auditLog" ... RETURNING ...

Итог по ожиданию типа LWLock:

Паттерн 1 2

SET TimeZone ✅ ✅

SELECT successionPlan ✅ ✅

SELECT improvementPlan с агрегацией ✅ ❌

UPDATE с RETURNING ✅ ❌

INSERT с множественными значениями ✅ ✅

SELECT outbox_v1 с пагинацией ✅ ✅

Системный мониторинг ✅ ❌

UPDATE auditLog ❌ ✅

SELECT improvementPlanGoalToCourse ❌ ✅

SELECT task с пагинацией ❌ ✅

SELECT user с подзапросом ❌ ✅

SELECT plans_tasks ❌ ✅

UPDATE с массовыми параметрами ❌ ✅

INSERT с RETURNING ❌ ✅

Период штатной производительности содержит 7 уникальных паттернов, период деградации производительности содержит 11 паттернов.

Стратегическая цель

Стратегическая цель - интеграция PG_HAZEL и DeepSeek.

Начало работ

Использование семантического анализа (NLP) для анализа рекомендаций по оптимизации ожиданий СУБД и текстов SQL запросов.

Задача

Протестировать возможности и результаты использования cемантического анализа текста и технологии обработки естественного языка (NLP) при анализе инцидента производительности СУБД .

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

Корреляция ожиданий

Исходные данные для семантического анализа

80% ожидания СУБД (диаграмма Парето)

80% SQL-запросы, вызывающие ожидания (диаграмма Парето)

Семантический анализ ожиданий типа IO (DataFileRead)

🎯 Общие категории оптимизации:

1. Настройка памяти и кэширования

• Увеличение shared_buffers для эффективного кэширования данных

• Настройка work_mem для операций в памяти вместо диска

2. Оптимизация запросов и схемы данных

• Создание индексов для замены последовательных сканирований на индексные

• Борьба с "вздутием" индексов через переиндексацию

• Использование партиционирования больших таблиц

• Анализ и переписывание "тяжелых" запросов

3. Обслуживание базы данных

• Настройка агрессивного автовакуума

• Регулярное выполнение VACUUM и ANALYZE

• Оптимизация параметров autovacuum_vacuum_scale_factor и autovacuum_analyze_scale_factor

4. Масштабирование инфраструктуры

• Увеличение аппаратных ресурсов (RAM, быстрые диски)

• Использование реплик для распределения нагрузки

🔄 Смысловые совпадения:

Важность профилактики операций ввода-вывода через:

• Кэширование в памяти вместо чтения с диска

• Эффективное использование индексов

• Регулярное обслуживание для предотвращения "вздутия"

• Правильное планирование ресурсов

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

Семантический анализ ожиданий типа IPC (BgWorkerShutdown, ParallelFinish, ExecuteGather, BufferIO)

🎯 Общие категории оптимизации:

1. Управление параллельными операциями

• Настройка параметров max_parallel_workers_per_gather, max_worker_processes, max_parallel_workers

• Балансировка количества параллельных процессов для избежания конкуренции за ресурсы

• Отключение параллелизма для быстрых запросов через SET max_parallel_workers_per_gather = 0

2. Оптимизация запросов и планов выполнения

• Анализ планов запросов с помощью EXPLAIN (ANALYZE, BUFFERS)

• Обновление статистики через ANALYZE для улучшения решений оптимизатора

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

3. Настройка памяти и ресурсов

• Увеличение shared_buffers для кэширования данных в памяти

• Настройка work_mem для операций сортировки и хеширования в памяти

• Оптимизация maintenance_work_mem для операций обслуживания

4. Управление системными ресурсами

• Мониторинг загрузки CPU и дисковой подсистемы

• Обеспечение достаточности ресурсов для обработки параллельных рабочих нагрузок

• Настройка контрольных точек через max_wal_size и checkpoint_timeout

5. Обслуживание базы данных

• Борьба с "вздутием" таблиц и индексов

• Создание и оптимизация индексов для условий WHERE и JOIN

• Управление подключениями через пулы соединений

🔄 Смысловые совпадения:

Ключевые пересекающиеся темы:

• Параллельные операции - центральная проблема в трех из четырех типов ожиданий

• Эффективность использования памяти - критически важна для всех типов оптимизации

• Балансировка нагрузки - необходимость согласования параллелизма с доступными ресурсами

• Качество индексов - влияет как на параллельные операции, так и на эффективность буферного ввода-вывода

Общий подход: Все рекомендации направлены на снижение накладных расходов и оптимизацию распределения ресурсов между конкурирующими процессами в PostgreSQL.

Семантический анализ ожиданий типа LWLock (ParallelHashJoin, LockManager, BufferMapping, BufferContent)

🎯 Общие категории оптимизации:

1. Настройка памяти и ресурсов

• Увеличение shared_buffers - для улучшения кэширования данных

• Оптимизация work_mem - предотвращение операций на диске

• Настройка hash_mem_multiplier - для хэш-операций

• Мониторинг эффективности кэша через BufferCacheHitRatio

2. Управление параллелизмом

• Настройка max_parallel_workers_per_gather - ограничение параллельных процессов

• Контроль max_parallel_workers - общее количество параллельных воркеров

• Отключение параллелизма для быстрых запросов через SET max_parallel_workers_per_gather = 0

3. Оптимизация индексов и схемы БД

• Борьба с раздутостью (Bloat) таблиц и индексов

• Удаление неиспользуемых индексов - снижение нагрузки при операциях записи

• Создание частичных индексов где это применимо

• Обновление статистики через ANALYZE

4. Оптимизация запросов и рабочей нагрузки

• Анализ планов запросов с помощью EXPLAIN (ANALYZE, BUFFERS)

• Сокращение времени транзакций - уменьшение времени удержания блокировок

• Снижение конкуренции за "горячие" данные

• Пересмотр логики приложения для уменьшения конфликтов

🔄 Смысловые совпадения:

Ключевые пересекающиеся темы:

• Эффективность использования памяти - центральная проблема для всех типов ожиданий

• Балансировка параллельных операций - предотвращение чрезмерной конкуренции

• Качество индексов - влияет на все аспекты производительности

• Своевременное обслуживание - VACUUM, ANALYZE, REINDEX

Общие технические подходы:

• Профилактика операций на диске - через адекватную настройку памяти

• Снижение конкуренции за ресурсы - через оптимизацию параллелизма

• Устранение избыточности - удаление неиспользуемых индексов и объектов

Универсальные рекомендации:

1. Начинать с настройки памяти - как наиболее эффективный способ

2. Анализировать конкретные запросы - через EXPLAIN и мониторинг

3. Балансировать системные ресурсы - учитывать общую нагрузку

4. Регулярно проводить обслуживание - для поддержания здоровья БД

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

Семантический анализ SQL запросов, вызывающих события ожидания типа IO(DataFileRead)

🎯 Общие категории запросов:

1. Операции с аудитом и логированием

• Множественные INSERT INTO "auditLog" с похожей структурой

• UPDATE "public"."auditLog" - обновление записей аудита

2. Работа с планами развития (Improvement Plan)

• Запросы к таблицам: improvementPlan, improvementPlanGoal, improvementPlanGoalToCourse

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

• Фильтрация по periodYear, empCodeId, empCodeToPositionId

3. Управление организационной структурой

• Рекурсивные запросы с WITH RECURSIVE для иерархии подразделений

• Работа с таблицами: orgUnit, structureLink, empCodeToPosition

• Связи между позициями и организационными единицами

4. Работа с файлами и уведомлениями

• SELECT FROM "FileInfo" с JOIN к fileCategory

• INSERT INTO "notification" - создание уведомлений

• INSERT INTO "task" - управление задачами

5. Управление кадровым резервом (Succession Planning)

• Запросы к SuccessionPlan с связями к riskLeaving и successors

• Фильтрация по positionId и статусам workflow

🔄 Смысловые совпадения и паттерны:

Структурные паттерны:

• Шаблон мягкого удаления: почти все запросы используют "deleteDateTime" IS NULL или "isDeleted" = false

• Версионность данных: частые обращения к versionId, versionBeginDateTime

• Сложные JOIN: большинство запросов объединяют 3+ таблицы

Бизнес-контекст:

• Управление сотрудниками: запросы связаны с empCode, position, person

• Учебные процессы: курсы, сессии, планы развития

• Рекрутинг: заявки, кандидаты, предложения о работе

Технические особенности:

• Пагинация: использование LIMIT и ORDER BY

• Агрегации: COUNT, GROUP BY для статистики

• CTE (Common Table Expressions): сложные аналитические запросы

Типы операций:

• Чтение: 80% запросов - SELECT с различной сложностью

• Запись: INSERT в лог, уведомления, задачи

• Обновление: редкие UPDATE операций

Все запросы отражают работу HR-системы с акцентом на управление развитием сотрудников, организационной структурой и процессами найма.

Семантический анализ SQL запросов, вызывающих события ожидания типа IPC (BgWorkerShutdown, ParallelFinish, ExecuteGather, BufferIO)

🎯 Общие категории запросов:

1. Сложные JOIN-запросы с множественными связями

• Запросы объединяют 3+ таблицы через INNER JOIN и LEFT OUTER JOIN

• Пример: запрос к plans с соединениями к plans_meta, positions, plans_statuses, plans_meetings, plans_attributes

2. Рекурсивные запросы и CTE (Common Table Expressions)

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

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

• Пример: запрос с orgUnitIds и responsible CTE

3. Работа с планами и задачами

• Запросы к таблицам plans, tasks, plans_tasks

• Сложные структуры с атрибутами и метаданными

4. Управление кадровым резервом (Succession Planning)

• Дублирующиеся запросы к SuccessionPlan с разными наборами positionId

• Связи с riskLeaving и successors

• Фильтрация по статусам workflow (65, 66)

🔄 Смысловые совпадения и паттерны:

Структурные паттерны:

• Шаблон мягкого удаления: "deleteDateTime" IS NULL, "deleted_at" IS NULL, "archiveDate" IS NULL

• Фильтрация по статусам: workflowStatusId NOT IN (65, 66) - исключение определенных статусов

• Массовые операции: positionId IN (85986, 81928, ...) - работа с наборами ID

Бизнес-контекст:

• HR-процессы: управление преемственностью, планами развития, задачами

• Организационная структура: иерархия подразделений, менеджеры, роли

• Система оценок: запросы к рейтингам сотрудников

Технические особенности:

• Сложные SELECT: много столбцов с алиасами, вложенные структуры

• Агрегации: COUNT(*), array_agg() для группировки данных

• Сортировка и лимиты: ORDER BY ... DESC LIMIT для получения последних записей

Производительность IPC:

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

• Параллельные операции: рекурсивные запросы и агрегации могут использовать несколько рабочих процессов

• Блокировки ресурсов: операции с общими структурами данных

Все запросы характеризуются высокой сложностью и связаны с обработкой организационной иерархии и бизнес-процессов, что объясняет возникновение ожиданий типа IPC (Inter-Process Communication).

Семантический анализ SQL запросов, вызывающих события ожидания типа LWLock (ParallelHashJoin, LockManager, BufferMapping, BufferContent)

🎯 Общие категории запросов:

1. Массовые операции UPDATE

• Крайне большой UPDATE с 1000+ параметрами к таблице user

• UPDATE к таблице auditLog с множественными условиями WHERE

2. Сложные SELECT с множественными JOIN

• Запросы к SuccessionPlan с LEFT OUTER JOIN к riskLeaving и successors

• Сложные запросы к планам задач с 5+ JOIN операциями

• Запросы к ImprovementPlanGoalToCourse с пагинацией (LIMIT 200)

3. Операции вставки (INSERT)

• Многократные INSERT в auditLog и outbox_v1

• Операции с возвратом данных через RETURNING

4. Системные операции

• Повторяющиеся SET TimeZone='<+00>-00'

• Запросы к системным представлениям и outbox-очереди

🔄 Смысловые совпадения и паттерны:

Паттерны блокировок LWLock:

• Массовые операции: UPDATE с огромным количеством параметров создает длительные блокировки

• Конкуренция за ресурсы: одновременные операции к одним таблицам (auditLog, SuccessionPlan)

• Сложные транзакции: запросы с множественными JOIN могут удерживать блокировки долгое время

Бизнес-контекст:

• HR-процессы: управление преемственностью, планы развития, аудит действий

• Фоновые процессы: работа с outbox-очередью для асинхронной обработки

• Управление пользователями: массовые обновления статусов пользователей

Технические особенности:

• Условия фильтрации: "deleteDateTime" IS NULL, "archiveDate" IS NULL, workflowStatusId NOT IN (65, 66)

• Пагинация: LIMIT 200, LIMIT 500 OFFSET 5083075500 (очень большое смещение)

• Сортировка: ORDER BY "ImprovementPlanGoalToCourse"."id" ASC

Проблемные операции для LWLock:

• Длительные UPDATE: массовое обновление 1000+ записей в одной транзакции

• Частые INSERT: интенсивная запись в таблицы аудита и outbox

• Сложные SELECT: запросы с 5+ JOIN операциями, которые блокируют метаданные

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