Postgres DBA

Оригинал на основном техническом блоге : Об одной методике резервного копирования таблиц и восстановления данных после проведения экспериментов (возможны исправления и дополнения)

Материал подготовлен нейросетью DeepSeek.

Аннотация

В работе рассматривается проблема сохранения целостности и когерентности вероятностных моделей, состояние которых непрерывно эволюционирует во времени, при проведении экспериментов и последующем восстановлении данных. Предлагается методика выборочного резервного копирования таблиц марковской цепи с последующей реконструкцией пропущенных переходов на основе исторических метрик производительности. Методика реализована в виде комплекса SQL-функций и shell-скриптов в рамках проекта pg_expecto/markov_chain и позволяет восстанавливать модель в актуальное состояние без потери накопленной за время эксперимента информации. Результаты могут быть применены в системах мониторинга, прогнозирования и автоматизированного тестирования, где требуется частая смена конфигураций модели при сохранении её преемственности.

Ключевые слова: марковская цепь, резервное копирование, восстановление данных, PostgreSQL, эксперименты, прогнозирование.

1. Введение

Современные системы мониторинга и прогнозирования производительности баз данных всё чаще обращаются к вероятностным моделям, способным учитывать динамику изменения наблюдаемых параметров. Одним из перспективных подходов является использование цепей Маркова для оценки риска возникновения инцидентов производительности на основе трендов операционной скорости и времени ожиданий. В рамках проекта pg_expecto — комплекса статистического анализа производительности СУБД PostgreSQL — реализована марковская цепь, которая каждую минуту получает актуальные метрики из таблицы cluster_stat_median и обновляет своё состояние.

Однако при проведении экспериментов, особенно связанных с модификацией структуры таблиц, изменением параметров модели или тестированием различных гипотез, возникает необходимость многократного возврата к исходному состоянию модели. Традиционные средства резервного копирования, такие как полный дамп базы данных или Point‑in‑Time Recovery, фиксируют лишь статический снимок данных, но не восстанавливают «память» модели о событиях, произошедших в промежутке между созданием резервной копии и моментом восстановления. В результате после восстановления модель может оказаться в состоянии, не соответствующем реальной динамике системы, что приводит к искажению прогнозов.

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

2. Постановка задачи

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

1. Изменение структуры или содержимого этих таблиц.

2. Модификация параметров модели (коэффициенты забывания, пороги риска и т.п.).

3. Длительные перерывы в сборе данных, в течение которых реальные переходы состояния не фиксировались.

Требуется разработать инструмент, который:

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

• при восстановлении загружает данные из резервной копии и автоматически реконструирует переходы, произошедшие в промежутке между временем создания копии и текущим моментом, на основе данных из таблиц производительности (cluster_stat_median, performance_incident);

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

3. Описание методики

Предлагаемая методика включает три основных компонента: (1) таблицу метаданных для хранения времени резервного копирования; (2) функцию вычисления состояния марковской цепи для произвольного момента времени; (3) функцию восстановления пропущенных переходов; (4) shell-скрипт, координирующий процесс резервирования и восстановления.

3.1. Таблица метаданных backup_metadata

Для фиксации времени создания каждой резервной копии вводится специальная таблица:

CREATE TABLE IF NOT EXISTS backup_metadata (
id SERIAL PRIMARY KEY,
backup_time TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);

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

3.2. Функция get_state_at_time

Ключевым элементом методики является функция, вычисляющая состояние марковской цепи для произвольного момента времени на основе исторических метрик производительности. Она полностью повторяет логику функции get_current_os_waiting_correlation_for_markov_chain, используемой для получения текущего состояния, но принимает параметр p_timestamp.

Функция:

• использует скользящее окно в 1 час для вычисления коэффициента корреляции между операционной скоростью (curr_op_speed) и временем ожиданий (curr_waitings);

• вычисляет тренды операционной скорости и времени ожиданий с помощью регрессионного анализа методом наименьших квадратов;

• возвращает идентификатор состояния (state_id), значение корреляции и знаки трендов.

Функция реализована без использования временных таблиц, с применением обобщённых табличных выражений (CTE), что позволяет объявить её как STABLE и избежать ошибок DDL при параллельных вызовах.

3.3. Функция fill_missing_transitions

Данная функция является центральной в методике восстановления. Она принимает два параметра: p_backup_time — время создания резервной копии, и p_restore_time — текущее время (по умолчанию now()).

Алгоритм работы функции:

1. Проверяет, что p_backup_time < p_restore_time.

2. Извлекает текущее состояние из таблицы markov_chain на момент восстановления.

3. В цикле перебирает каждую минуту в диапазоне от p_backup_time до p_restore_time (шаг — 1 минута, что соответствует периодичности реального обучения).

4. Для каждой минуты вызывает get_state_at_time и получает состояние системы в данный момент.

5. При изменении состояния фиксирует переход в таблицы transition_log и markov_frequencies.

6. По завершении цикла обновляет таблицу markov_chain, устанавливая последнее вычисленное состояние.

7. При наличии инцидентов в пропущенном периоде обновляет last_incident_time в таблице markov_config.

8. Пересчитывает матрицу вероятностей (update_markov_probabilities) и поглощающую матрицу (rebuild_markov_absorbing).

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

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

3.4. Shell-скрипт markov_chain_backup.sh

Скрипт координирует процесс резервирования и восстановления. Параметры подключения к базе данных (имя базы expecto_db, пользователь expecto_user, хост localhost, порт 5432) жёстко зафиксированы в скрипте. Аутентификация осуществляется через переменную окружения PGPASSWORD или файл .pgpass.

Действие backup:

• Проверяет/создаёт таблицу backup_metadata.

• Создаёт выборочный дамп только перечисленных таблиц с помощью pg_dump -Fc -t ....

• Сохраняет время создания копии в backup_metadata.

Действие restore:

• Находит самый свежий файл резервной копии в каталоге /tmp.

• Запрашивает подтверждение пользователя.

• Выполняет восстановление таблиц из дампа с очисткой (pg_restore --clean --if-exists).

• Последовательно выполняет пост-восстановительные шаги:
  mchain_train_step() — обновление текущего состояния;
  fill_missing_transitions() — восстановление пропущенных переходов;
  refresh_critical_states() — пересчёт критических состояний за последние 14 дней;
  update_markov_probabilities() и rebuild_markov_absorbing() — перестроение матриц;
  mchain_predict_risk_current_horizon() — проверка прогноза.

Все шаги сопровождаются подробным логированием в консоль.

3.5. Состав резервируемых таблиц

Скрипт сохраняет и восстанавливает только следующие таблицы (все остальные объекты базы данных не затрагиваются):

• markov_config — конфигурация цепи;

• mchain_error_log — журнал ошибок;

• markov_frequencies — накопленные частоты переходов;

• transition_log — журнал переходов;

• markov_probabilities — матрица вероятностей;

• markov_absorbing — поглощающая матрица;

• state_descriptions — справочник состояний;

• markov_chain — текущее состояние;

• apply_forgetting_log — журнал забывания;

• prediction_log — журнал прогнозов;

• mchain_quality_metrics_history — агрегированные метрики качества;

• mchain_quality_errors — ошибки прогнозов;

• critical_states — критические состояния;

• forgetting_optimization_log — журнал экспериментов по забыванию;

• backup_metadata — вспомогательная таблица для хранения времени резервного копирования.

4. Экспериментальные результаты

Методика была апробирована в рамках проекта pg_expecto/markov_chain. Ниже приведены типичные сценарии использования.

4.1. Создание резервной копии

$ ./markov_chain_backup.sh backup
▶️ Создание выборочного бекапа таблиц цепи Маркова в /tmp/markov_chain_tables_20250625_120000.dump...
✅ Бекап создан: /tmp/markov_chain_tables_20250625_120000.dump
➜ Сохранение времени бекапа
INSERT 0 1

Время создания копии автоматически сохраняется в таблицу backup_metadata.

4.2. Восстановление с реконструкцией переходов

$ ./markov_chain_backup.sh restore
▶️ Восстановление таблиц цепи Маркова из /tmp/markov_chain_tables_20250625_120000.dump...
ВНИМАНИЕ: все таблицы цепи Маркова в базе expecto_db будут заменены данными из бекапа!
Продолжить? [y/N]: y
✅ Восстановление таблиц завершено.
▶️ Выполнение пост-восстановительных операций...
➜ Обновление текущего состояния (mchain_train_step)
Step completed
Время бекапа: 2026-06-25 12:00:00+03
➜ Восстановление переходов за пропущенный период
Восстановлено 47 переходов за период 2026-06-25 12:00:00+03 – 2026-06-25 16:30:00+03. Инциденты: обновлён last_incident_time
➜ Обновление критических состояний (refresh_critical_states)
...
➜ Пересчёт вероятностей
...
➜ Пересчёт поглощающей матрицы
...
➜ Проверка прогноза (должен вернуть число от 0 до 1)
0.045
✅ Все пост-восстановительные операции успешно выполнены. Цепь Маркова готова к работе.

В приведённом примере за период в 4,5 часа было восстановлено 47 переходов, что позволило модели «догнать» актуальное состояние системы.

5. Обсуждение

5.1. Достоинства методики

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

2. Самодостаточность — для реконструкции пропущенных переходов не требуются дополнительные журналы или архивы WAL; используется уже существующая историческая информация из таблиц производительности.

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

4. Прозрачность — все этапы логируются, пользователь получает детальный отчёт о выполненной работе.

5.2. Ограничения

1. Точность восстановления зависит от качества и полноты данных в cluster_stat_median. Если в пропущенном периоде отсутствуют записи за какие-то минуты, состояние вычисляется по доступным данным, что может привести к неточностям.

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

3. Производительность — для периодов более нескольких дней цикл по минутам может выполняться медленно. В таких случаях рекомендуется оптимизировать функцию fill_missing_transitions (например, использовать пакетную вставку) или сократить период восстановления.

5.3. Применимость для других проектов

Хотя методика разработана для конкретной модели марковской цепи в контексте мониторинга PostgreSQL, её архитектурные принципы могут быть перенесены на широкий класс систем, где:

• состояние модели непрерывно эволюционирует во времени;

• существуют исторические данные, позволяющие реконструировать это состояние для произвольного момента в прошлом;

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

Для адаптации методики к другому проекту необходимо:

1. Определить набор таблиц, подлежащих резервированию.

2. Реализовать функцию вычисления состояния по произвольной временной метке, аналогичную get_state_at_time.

3. Адаптировать функцию восполнения переходов с учётом специфики предметной области и требуемой дискретности времени.

6. Заключение

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

Методика реализована в виде открытого инструментария, доступного в репозитории github.com/pg-expecto/markov_chain, и сопровождается документацией markov_chain_backup.md. Предложенный подход может быть полезен не только для систем мониторинга производительности, но и для любых других приложений, использующих вероятностные модели с дискретным временем и требующих частой смены конфигураций в экспериментальных целях.

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

1. pg_expecto/markov_chain — репозиторий с реализацией марковской цепи для прогнозирования инцидентов производительности PostgreSQL

2. markov_chain_backup.md — документация по резервному копированию и восстановлению цепи Маркова

Оригинал на основном техническом блоге : Марковская цепь для DBA: эволюция, фильтрация и путь в промышленную эксплуатацию (возможны исправления и дополнения)

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

Предисловие

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

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

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

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

Версия 10.1.6 — статичный фундамент

Первая реализация опиралась на предположение о стационарности и использовала фиксированные аварийные критерии. Модель работала с 189 состояниями, которые представляли собой комбинации трёх параметров — корреляции, тренда ОС и тренда ожиданий. Аварийным считалось только одно строгое условие: отрицательная корреляция при падающем тренде ОС и растущем тренде ожиданий.

Прогноз строился через поглощающую матрицу, пересчитываемую при каждом обновлении вероятностей. Горизонты были фиксированными — 1, 15, 30 и 60 минут. Достоверность оценивалась по пятибалльной шкале на основе объёма данных и стабильности вероятностей. Это было рабочее решение, но оно не учитывало, что реальные инциденты могут проявляться иначе, а параметры системы со временем меняются.

Версия 11.3 — динамика и эволюция

Методология заложеная в версия 11 принципиально отличается. Вместо жёсткого условия разработчики ввели таблицу critical_states, которая автоматически пополняется функцией refresh_critical_states на основе эмпирического риска из таблицы инцидентов. Теперь аварийные состояния определяются не раз и навсегда, а извлекаются из истории наблюдений.

Одновременно изменился сам механизм прогноза: поглощающая матрица уступила место итеративному расчёту с обнулением вероятностей критических состояний на каждом шаге. Горизонт стал единым и задаётся в конфигурации (по умолчанию 30 минут). Появились новые отчёты, в том числе mchain_quality_report, а достоверность стала штрафоваться за нестабильность.

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

Текущий рубеж: версия 12.1 — фильтрация и аккуратность

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

Главное нововведение — фильтрация при расчёте максимального изменения вероятностей переходов (max_prob_change).

Из анализа исключаются:

• переходы в критические состояния (те, что перечислены в critical_states);

• состояния, для которых за анализируемый период зафиксировано менее 200 переходов.

Это позволяет отсечь шум: редкие события и аварийные пики перестают влиять на оценку стабильности, делая её более репрезентативной. Кроме того, скорректированы параметры забывания по умолчанию — интервал уменьшен с 180 до 60 минут, а базовый коэффициент альфа — с 0.1 до 0.07. Такая настройка обеспечивает более плавную адаптацию к новым данным, снижая резкие скачки.

Фильтрация внедрена во все ключевые функции: mchain_check_sufficiency, mchain_forecast_reliability, mchain_reliability_report, evaluate_forgetting_params и report_stability_trend. Теперь каждый отчёт о надёжности учитывает только статистически значимые и некритические переходы.

Как менялись приоритеты: сравнение трёх версий

Если обобщить различия, можно выделить несколько осей развития.

По определению аварийности:

• Версия 10: жёсткое логическое условие, зашитое в код.

• Версии 11 и 12: динамический список critical_states, обновляемый по данным инцидентов.

По механизму прогноза:

• Версия 10: поглощающая матрица, перестраиваемая при каждом обновлении.

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

По горизонту прогноза:

• Версия 10: четыре фиксированных горизонта (1, 15, 30, 60 минут).

• Версии 11 и 12: единый горизонт, задаваемый в конфигурации (по умолчанию 30 минут).

По расчёту стабильности:

• Версии 10 и 11: учитывались все переходы без исключений.

• Версия 12: исключены переходы в критические состояния и состояния с числом переходов менее 200.

По параметрам забывания по умолчанию:

• Версии 10 и 11: interval_minute=180, base_alpha=0.1.

• Версия 12: interval_minute=60, base_alpha=0.07.

Взгляд вперёд

Версия 12.1 — не революция, а эволюционное уточнение, которое делает модель более устойчивой к выбросам и редким событиям.

Благодаря фильтрации оценка стабильности стала объективнее, а прогнозы — достовернее в реальных условиях эксплуатации.

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

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

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

• Адаптация к нестационарности. Производительность реальных систем со временем меняется — растут объёмы данных, эволюционируют запросы, обновляется аппаратное обеспечение. Цепь Маркова первого порядка, предполагающая стационарность переходных вероятностей, может давать сбои в таких условиях. Необходимы исследования того, как часто нужно переобучать модель, какие механизмы обнаружения дрейфа распределения следует внедрить и как автоматически перестраивать пространство состояний при изменении характера нагрузки.

• Проблема разреженности данных. В версии 12.1 уже введён порог в 200 переходов для включения состояния в расчёт стабильности. Однако на продуктивных системах многие состояния могут оставаться редкими, особенно в начальный период наблюдения. Это ставит вопрос о разработке методов сглаживания (например, байесовской априорной регуляризации) или агрегации схожих состояний, чтобы повысить статистическую значимость оценок без потери чувствительности к аномалиям.

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

• Калибровка гиперпараметров. В версии 12.1 скорректированы параметры забывания: interval_minute уменьшен с 180 до 60, а base_alpha — с 0.1 до 0.07. Однако оптимальные значения этих параметров могут существенно зависеть от конкретной рабочей нагрузки: для высокодинамичных систем требуется более быстрое забывание, для стабильных — напротив, более долгая память. Необходимы систематические исследования по автоматическому подбору гиперпараметров, возможно, с использованием методов оптимизации, адаптивных к текущим условиям.

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

• Интеграция с существующими системами мониторинга. Промышленное внедрение требует бесшовной интеграции с распространёнными стеками наблюдения (Prometheus, Zabbix, Grafana и др.). Это означает не только техническую совместимость по протоколам и форматам данных, но и согласование моделей данных: метрики, используемые цепью Маркова, должны соответствовать тем, что уже собираются в продуктивной среде, без необходимости доработки агентов сбора.

• Интерпретируемость для инженеров. Цепи Маркова дают вероятностный прогноз, но администраторам баз данных нужны не только цифры, но и понятные объяснения: почему модель ожидает инцидент, какие именно переходы между состояниями привели к такому выводу, на какие метрики следует обратить внимание в первую очередь. Требуются дополнительные исследования в области объяснимого искусственного интеллекта (XAI) применительно к марковским моделям.

• Сравнительные исследования с альтернативными подходами. На сегодняшний день не проведено систематического сравнения марковского подхода с другими методами прогнозирования — рекуррентными нейронными сетями, градиентным бустингом на временных рядах, скрытыми марковскими моделями или байесовскими структурными временными рядами. Без такого бенчмарка на репрезентативных датасетах сложно утверждать, что цепь Маркова является оптимальным выбором, а не просто удобной и интерпретируемой альтернативой.

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

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

Проект открыт для сотрудничества, и каждый из этих вызовов может стать отдельной темой для совместной разработки.

Послесловие

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

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

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

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

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

Предисловие

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

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

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

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

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

dbid ,

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

( SELECT rolname FROM pg_roles WHERE oid = userid ) ,

queryid ,

date_trunc( 'minute' , CURRENT_TIMESTAMP ) ,

wait_stats ,

calls ,

rows

FROM

pgpro_stats_statements( FALSE )

WHERE

dbid NOT IN

(

SELECT oid

FROM pg_database

WHERE

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

)

AND dbid IS NOT NULL

AND userid IS NOT NULL

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

Решение "DeepSeek"

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

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

sql

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

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

Решение Ask Postgres

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

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

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

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

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

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

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

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

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

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

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

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

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

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

EXPLAIN ANALYZE

SELECT

s.dbid,

d.datname,

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

s.queryid,

date_trunc('minute', CURRENT_TIMESTAMP) AS ts,

s.wait_stats,

s.calls,

s.rows

FROM

pgpro_stats_statements(FALSE) s

CROSS JOIN LATERAL (

SELECT datname

FROM pg_database

WHERE oid = s.dbid

) d

WHERE

s.dbid NOT IN (

SELECT oid

FROM pg_database

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

)

AND s.dbid IS NOT NULL

AND s.userid IS NOT NULL

AND s.queryid IS NOT NULL ;

QUERY PLAN

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

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

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

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

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

Rows Removed by Filter: 588

SubPlan 2

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

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

Rows Removed by Filter: 32

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

Buckets: 1024  Batches: 1  Memory Usage: 12kB

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

SubPlan 1

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

Index Cond: (oid = s.userid)

Planning Time: 0.412 ms

Execution Time: 37.153 ms

(17 rows)

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

EXPLAIN ANALYZE

SELECT

dbid,

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

(SELECT rolname FROM pg_roles WHERE oid = userid),

queryid,

date_trunc('minute', CURRENT_TIMESTAMP),

wait_stats,

calls,

rows

FROM

pgpro_stats_statements(FALSE)

WHERE

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

EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid)

AND dbid NOT IN (

SELECT oid

FROM pg_database

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

)

AND dbid IS NOT NULL

AND userid IS NOT NULL

AND queryid IS NOT NULL;

QUERY PLAN

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

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

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

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

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

Rows Removed by Filter: 588

SubPlan 3

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

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

Rows Removed by Filter: 32

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

Buckets: 1024  Batches: 1  Memory Usage: 10kB

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

SubPlan 1

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

Filter: (oid = pgpro_stats_statements.dbid)

Rows Removed by Filter: 36

SubPlan 2

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

Index Cond: (oid = pgpro_stats_statements.userid)

Planning Time: 0.474 ms

Execution Time: 61.394 ms

(21 rows)

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

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

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

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

TEST SQL-1

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

EXPLAIN ANALYZE

SELECT

s.dbid,

d.datname,

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

s.queryid,

date_trunc('minute', CURRENT_TIMESTAMP) AS ts,

s.wait_stats,

s.calls,

s.rows

FROM

pgpro_stats_statements(FALSE) s

CROSS JOIN LATERAL (

SELECT datname

FROM pg_database

WHERE oid = s.dbid

) d

WHERE

s.dbid NOT IN (

SELECT oid

FROM pg_database

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

)

AND s.dbid IS NOT NULL

AND s.userid IS NOT NULL

AND s.queryid IS NOT NULL ;

QUERY PLAN

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

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

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

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

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

Rows Removed by Filter: 588

SubPlan 2

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

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

Rows Removed by Filter: 32

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

Buckets: 1024 Batches: 1 Memory Usage: 12kB

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

SubPlan 1

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

Index Cond: (oid = s.userid)

Planning Time: 0.412 ms

Execution Time: 37.153 ms

(17 rows)

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

TEST SQL-2

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

EXPLAIN ANALYZE

SELECT

dbid,

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

(SELECT rolname FROM pg_roles WHERE oid = userid),

queryid,

date_trunc('minute', CURRENT_TIMESTAMP),

wait_stats,

calls,

rows

FROM

pgpro_stats_statements(FALSE)

WHERE

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

EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid)

AND dbid NOT IN (

SELECT oid

FROM pg_database

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

)

AND dbid IS NOT NULL

AND userid IS NOT NULL

AND queryid IS NOT NULL;

QUERY PLAN

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

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

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

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

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

Rows Removed by Filter: 588

SubPlan 3

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

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

Rows Removed by Filter: 32

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

Buckets: 1024 Batches: 1 Memory Usage: 10kB

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

SubPlan 1

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

Filter: (oid = pgpro_stats_statements.dbid)

Rows Removed by Filter: 36

SubPlan 2

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

Index Cond: (oid = pgpro_stats_statements.userid)

Planning Time: 0.474 ms

Execution Time: 61.394 ms

(21 rows)

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

Анализ DeepSeek

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

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

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

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

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

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

TEST SQL-1

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

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

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

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

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

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

TEST SQL-2

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

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

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

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

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

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

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

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

Вывод

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

Анализ Ask Postgres

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

DeepSeek (TEST SQL-1):

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

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

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

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

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

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

Ask Postgres (TEST SQL-2):

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Послесловие

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

id (BIGSERIAL PRIMARY KEY)

ts (TIMESTAMPTZ NOT NULL)

from_state (SMALLINT NOT NULL)

to_state (SMALLINT NOT NULL)

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

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

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

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

Логика:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• wchain_predict_risk_1min

• wchain_predict_risk_5min

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• wait_frequencies (from_state, to_state, frequency)

• wait_probabilities

• wait_absorbing

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Адаптация wchain_apply_forgetting

• wchain_check_sufficiency

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Предисловие

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

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

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

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

period_hours = 2

average_load = 40

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

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

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

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

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

#vacuum_incident = 1

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

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

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

SET vacuum_cost_delay = ${VACUUM_COST_DELAY};

SET vacuum_cost_limit = ${VACUUM_COST_LIMIT};

VACUUM FREEZE ${TABLE_NAME};

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

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

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

postgres=# show shared_buffers;

shared_buffers

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

200MB

(1 row)

postgres=# show work_mem ;

work_mem

----------

16MB

(1 row)

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

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

# Тестовая БД

testdb = default

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

load_mode = olap

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

period_hours = -1

average_load = 50

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

vacuum_incident = 1

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

scenario1 = 0.7

scenario2 = 0.2

scenario3 = 0.1

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

#~10GB

scale = 685

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

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

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

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

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

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

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

...

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

...

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

...

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

...

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Послесловие

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

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

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

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