Серия «СУБД PostgreSQL»

Лучше неделя нагрузочного тестирования с 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), особенно при высокой параллельной нагрузке, что объясняет возникновение ожиданий этого типа.

Задача

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

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

3. Провести семантический анализ SQL запросов.

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

Анализируя текст об оптимизации ожиданий DataFileRead в PostgreSQL, можно выделить следующее семантическое ядро:

Центральная концепция

"Сокращение дисковых операций через эффективное использование памяти и оптимизацию доступа к данным"

Ключевые смысловые кластеры

1. Управление памятью и кэшированием

-- Ключевые параметры

shared_buffers -- кэширование данных

work_mem -- операции в памяти

Семантика: Предотвращение физического чтения с диска через эффективное кэширование

2. Оптимизация доступа к данным

• Индексное сканирование vs Последовательное сканирование

• Партиционирование больших таблиц

• Переиндексация для борьбы с раздуванием

Семантика: Минимизация объема читаемых данных

3. Проактивное обслуживание

autovacuum -- автоматическая очистка

VACUUM -- ручная очистка

ANALYZE -- обновление статистики

REINDEX -- перестройка индексов

Семантика: Предотвращение деградации производительности

4. Архитектурное масштабирование

• Вертикальное масштабирование: больше RAM, быстрые диски

• Горизонтальное масштабирование: горячие реплики для read-нагрузки

Семантика: Распределение нагрузки и увеличение ресурсов

Семантические связи и иерархия

Причинно-следственные связи

Недостаток памяти → Дисковые операции → DataFileRead ожидания

Отсутствие индексов → Sequential Scans → Увеличение I/O

Нет обслуживания → Bloat → Неэффективные чтения

Таксономия решений

Проактивные (предотвращение):

├── Настройка памяти

├── Создание индексов

└── Партиционирование

Реактивные (исправление):

├── Переиндексация

├── Агрессивный autovacuum

└── Оптимизация запросов

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

• Эффективность кэширования (hit ratio)

• Объем последовательных сканирований

• Уровень раздувания индексов и таблиц

• Частота операций autovacuum

Семантические паттерны оптимизации

Паттерн "Память вместо диска"

-- До: операции на диске

-- После: операции в памяти

work_mem = 4MB → work_mem = 64MB

Паттерн "Точечный доступ вместо полного сканирования"

-- До: SELECT * FROM large_table WHERE condition

-- После: CREATE INDEX idx_condition ON large_table(condition)

Паттерн "Разделяй и властвуй"

-- Партиционирование больших таблиц по датам/диапазонам

CREATE TABLE data_2024 PARTITION OF main_table FOR VALUES FROM...

Выводы о семантическом ядре

Текст построен вокруг трех фундаментальных принципов:

1. Профилактика - настройка памяти и индексов до возникновения проблем

2. Эффективность - минимизация избыточных операций ввода-вывода

3. Регулярность - систематическое обслуживание для поддержания производительности

Главная семантическая ось: Память → Кэш → Диск с акцентом на смещении операций влево по этой оси для максимальной производительности.

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

1. Общие причины проблем производительности

Конкуренция за ресурсы

• BufferContent: "конкуренция процессов за доступ к одним и тем же данным"

• BufferMapping: "конкуренция за доступ к буферному кэшу"

• LockManager: "общая проблема с большим количеством конкурентных блокировок"

• ParallelHashJoin: "процессы координируют свою работу или ожидают друг друга"

Проблемы с памятью

• BufferContent: "неэффективная работа с памятью", "данные не находятся в памяти"

• BufferMapping: "частота замены буферов и операций ввода-вывода"

• ParallelHashJoin: "нехватка оперативной памяти для построения хэш-таблиц"

2. Общие подходы к оптимизации

Настройка памяти

-- Общие параметры памяти :

BufferContent: shared_buffers

BufferMapping: shared_buffers

ParallelHashJoin: work_mem, hash_mem_multiplier

Борьба с раздуванием (Bloat)

• BufferContent: "Боритесь с раздуванием", "VACUUM FULL", "pg_repack", "REINDEX"

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

• ParallelHashJoin: "раздутые (bloated) индексы", "VACUUM и ANALYZE"

Оптимизация индексов

• BufferContent: "Удалите неиспользуемые и дублирующиеся индексы", "Используйте частичные индексы"

• BufferMapping: "оптимизировать индексы"

• ParallelHashJoin: "Проверьте наличие адекватных индексов"

3. Общие методы диагностики

Мониторинг метрик

• BufferContent: "Следите за метрикой BufferCacheHitRatio"

• BufferMapping: "Мониторить blks_hit и blks_read"

• ParallelHashJoin: "EXPLAIN ANALYZE", "EXPLAIN (ANALYZE, BUFFERS)"

Анализ рабочей нагрузки

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

4. Общие архитектурные рекомендации

Масштабирование ресурсов

• BufferContent: "Масштабируйте вычислительные ресурсы", "увеличение размера экземпляра БД"

• ParallelHashJoin: "Контролируйте уровень параллелизма", "max_parallel_workers"

Оптимизация приложения

• BufferContent: "пересмотреть логику приложения" для "горячих" данных

• LockManager: "пересмотр логики блокировок в приложении"

• ParallelHashJoin: "пересмотрите необходимость параллелизма для конкретных запросов"

5. Общие философские принципы

Комплексный подход

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

Баланс ресурсов

• ParallelHashJoin: "Помните о балансе: Увеличивая лимиты памяти для одного запроса, вы можете уменьшить доступную память для других"

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

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

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

2. Проактивное обслуживание - необходимость регулярного VACUUM, ANALYZE и мониторинга

3. Архитектурный подход - оптимизация требует изменений на всех уровнях: от параметров БД до логики приложения

4. Диагностика прежде оптимизации - везде подчеркивается важность анализа перед внесением изменений

3. Cемантический анализ SQL запросов.

1. Структурные паттерны

Паттерн мягкого удаления (Soft Delete)

-- Во всех запросах присутствует:

"deleteDateTime" IS NULL

• Запрос 1,2: "SuccessionPlan"."deleteDateTime" IS NULL, "successors"."deleteDateTime" IS NULL

• Запрос 3: "ImprovementPlanGoalToCourse"."deleteDateTime" IS NULL

Паттерн JOIN с дополнительными условиями

-- LEFT JOIN с фильтрацией в условии соединения

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

2. Паттерны проекции данных

Селекция конкретных полей с алиасами

-- Паттерн: table."column" AS "alias.table.column"

"riskLeaving"."id" AS "riskLeaving.id"

"successors"."workflowStatusId" AS "successors.workflowStatusId"

Вложенные связи через JOIN

-- Цепочка связей между таблицами

"goal->improvementPlan"."empCodeId" AS "goal.improvementPlan.empCodeId"

3. Паттерны фильтрации

Фильтрация по идентификаторам

-- IN с списками значений

"SuccessionPlan"."positionId" IN (871798)

"SuccessionPlan"."positionId" IN (80214, 81665, 80217, ...)

Исключающая фильтрация

-- NOT IN для исключения статусов

"successors"."workflowStatusId" NOT IN (65, 66)

4. Паттерны бизнес-логики

Управление состоянием через workflowStatusId

• Во всех запросах присутствует работа со статусами (workflowStatusId, candidateStatusId)

Архивация и временные метки

-- Проверка на архивные записи

"SuccessionPlan"."archiveDate" IS NULL

Пагинация и упорядочивание

-- Запрос 3: ORDER BY ... LIMIT

ORDER BY "ImprovementPlanGoalToCourse"."id" ASC LIMIT 200

5. Смысловые совпадения по доменной модели

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

• Преемственность: successionPlan, successor, readinessId

• Развитие сотрудников: ImprovementPlanGoalToCourse, courseSession

• Оценка компетенций: participationResult, score, passed

Workflow и статусы

-- Общая система статусов

workflowStatusId, candidateStatusId, readinessId

applicantListType, participation_result

Временные жизненные циклы

• recordingDateTime, sessionBeginDate, sessionEndDate, update_date_time

6. Архитектурные паттерны

Нормализация данных

• Множество связанных таблиц с внешними ключами

• Разделение сущностей на основные и справочные

Гибкая система расширений

• Использование типа applicantListType

• Поддержка миграций через migration_code

Выводы и рекомендации

1. Единая архитектура: Все запросы следуют одним принципам проектирования

2. Консистентность: Единый подход к мягкому удалению и архивации

3. Масштабируемость: Использование пагинации для больших наборов данных

4. Бизнес-логика: Система ориентирована на управление HR-процессами

5. Оптимизационные возможности:
   Индексы по deleteDateTime, workflowStatusId
   Составные индексы для частых JOIN условий
   Оптимизация запросов с IN для больших списков

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