Postgres DBA

Задача

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

2. Формирование набора рекомендаций по оптимизации типовых SQL-запросов.

1. Выявление SQL-запросов для оптимизации

Детали и подробности: PG_HAZEL : Анализ инцидента производительности СУБД PostgreSQL , поиск и оптимизация проблемных SQL-запросов.

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

Диаграмма Парето по типу ожидания IPC

80% событий ожиданий :

• BgWorkerShutdown: Ожидание завершения фонового рабочего процесса.

• ExecuteGather: Ожидание активности дочернего процесса при выполнении узла плана Gather.

• ParallelFinish: Ожидание завершения вычислений параллельными рабочими процессами.

Результат анализа ожиданий на уровне SQL-запросов:

Наибольшее количество ожиданий IPC по запросу 6863414396188999698

Оптимизация SQL-запроса

Измененный текст запроса

Детали и подробности: PG_HAZEL : Анализ инцидента производительности СУБД PostgreSQL , поиск и оптимизация проблемных SQL-запросов.

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

Ожидания типа IPC по SQL-запросам

Наибольшее количество ожиданий типа IPC по SQL c queryid = -5849488707035427374

Переписать запрос с использованием CTE

Детали и подробности: PG_HAZEL : Анализ инцидента производительности СУБД PostgreSQL , поиск и оптимизация проблемных SQL-запросов.

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

Ожидания типа IPC по SQL-запросам

Следующий SQL-запрос для оптимизации : -1701015661318396920

Оптимизация структуры запроса

Основная проблема - "декартово произведение" из-за нескольких LEFT JOIN.

2.Формирование набора рекомендаций по оптимизации

Детали и подробности: Оптимизация SQL-запросов PostgreSQL : LIMIT 1

Создание подходящих индексов

Создать индекс по колонкам из WHERE и ORDER BY. Позволяет найти строку за несколько шагов.

Эффект

Кардинальное ускорение (в тысячи раз), устранение полного сканирования таблицы.

Анализ плана выполнения

Использовать EXPLAIN (ANALYZE, BUFFERS) для просмотра плана запроса. Показывает, используется ли индекс.

Эффект

Точно определяет "узкое место" в запросе, позволяет оценить стоимость операций.

Использование ORDER BY с индексом

Всегда использовать ORDER BY с LIMIT для предсказуемого результата. Упорядочивание должно совпадать с индексом.

Эффект

Гарантирует корректность результата и позволяет использовать индекс для сортировки.

Устранение лишних операций

Убрать ненужные DISTINCT, сложные вычисления в WHERE, избыточные JOIN. Снижает объем работы до применения LIMIT.

Эффект

Сокращает общее время выполнения, особенно если лишняя операция требовала сортировки.

Увеличение work_mem

Увеличить параметр work_mem, если в плане есть Sort с дисковыми операциями (Disk: writes temp).

Эффект

Ускоряет операции сортировки и хеширования, выполняемые в памяти.

Детали и подробности: Оптимизация SQL-запросов PostgreSQL : IN с большим количеством значений

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

ANY(ARRAY[]) вместо IN

Замена IN (...) на = ANY(ARRAY[...]).

Оператор ANY может остановить проверку при первом совпадении.

Когда метод эффективен

Когда список значений очень большой.

JOIN с виртуальной таблицей

Преобразование списка значений в виртуальную таблицу с помощью VALUES и соединение с основной таблицей.

Когда метод эффективен

Когда список значений можно представить как набор строк.

EXISTS вместо IN для подзапросов

Проверка существования записи с помощью EXISTS.

Запрос прекращает работу, как только найдет первое совпадение.

Когда метод эффективен

Особенно эффективен для коррелированных подзапросов и сценариев с NOT IN (лучше использовать NOT EXISTS)

Материализованные представления

Предварительный расчет и сохранение результатов "тяжелого" запроса, например, с DISTINCT.

Когда метод эффективен

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

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

Вынесение часто запрашиваемых полей (например, make, vehicle_year) в отдельные справочные таблицы.

Когда метод эффективен

Когда одни и те же значения (DISTINCT ...) выбираются из таблицы миллионы раз .

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

ANY(ARRAY[]) вместо IN

Замена IN (...) на = ANY(ARRAY[...]).

Оператор ANY может остановить проверку при первом совпадении.

Когда метод эффективен

Когда список значений очень большой.

JOIN с виртуальной таблицей

Преобразование списка значений в виртуальную таблицу с помощью VALUES и соединение с основной таблицей.

Когда метод эффективен

Когда список значений можно представить как набор строк.

EXISTS вместо IN для подзапросов

Проверка существования записи с помощью EXISTS.

Запрос прекращает работу, как только найдет первое совпадение.

Когда метод эффективен

Особенно эффективен для коррелированных подзапросов и сценариев с NOT IN (лучше использовать NOT EXISTS)

Материализованные представления

Предварительный расчет и сохранение результатов "тяжелого" запроса, например, с DISTINCT.

Когда метод эффективен

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

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

Вынесение часто запрашиваемых полей (например, make, vehicle_year) в отдельные справочные таблицы.

Когда метод эффективен

Когда одни и те же значения (DISTINCT ...) выбираются из таблицы миллионы раз .

💡 Рекомендации по применению методов

Помимо замены оператора, важно рассмотреть более фундаментальные оптимизации.

• Правильные индексы: Необходимо убедится, что на столбцах, которые участвуют в условиях IN, ANY или JOIN, созданы индексы (чаще всего BTREE). Без индекса даже переписанный запрос будет выполняться медленно .

• Анализ плана запроса.

• Нормализация данных: Если часто делается запросы вида SELECT DISTINCT column_name к очень большой таблице, это может указывать на недостатки в структуре базы данных. Вынесение возможных значений атрибута (например, марок автомобилей) в отдельную справочную таблицу — кардинальное, но очень эффективное решение .

⚠️ Чего следует избегать

• NOT IN с подзапросами: Для сценариев исключения (NOT IN) PostgreSQL может строить неоптимальные планы с подзапросами (SubPlan). Вместо NOT IN практически всегда лучше использовать NOT EXISTS или LEFT JOIN ... WHERE ... IS NULL, которые приводят к более эффективному плану с Hash Anti Join .

• DISTINCT без необходимости: Если нужно получить только уникальные значения, а не все строки, использование DISTINCT может создать дополнительную нагрузку. Иногда эту задачу можно решить на уровне приложения или с помощью других методов SQL .

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

Ключевые моменты:

1. правильные индексы

2. анализ плана выполнения

3. избегание лишних операций.

Создание подходящих индексов

Создать индекс по колонкам из WHERE и ORDER BY. Позволяет найти строку за несколько шагов.

Эффект

Кардинальное ускорение (в тысячи раз), устранение полного сканирования таблицы.

Анализ плана выполнения

Использовать EXPLAIN (ANALYZE, BUFFERS) для просмотра плана запроса. Показывает, используется ли индекс.

Эффект

Точно определяет "узкое место" в запросе, позволяет оценить стоимость операций.

Использование ORDER BY с индексом

Всегда использовать ORDER BY с LIMIT для предсказуемого результата. Упорядочивание должно совпадать с индексом.

Эффект

Гарантирует корректность результата и позволяет использовать индекс для сортировки.

Устранение лишних операций

Убрать ненужные DISTINCT, сложные вычисления в WHERE, избыточные JOIN. Снижает объем работы до применения LIMIT.

Эффект

Сокращает общее время выполнения, особенно если лишняя операция требовала сортировки.

Увеличение work_mem

Увеличить параметр work_mem, если в плане есть Sort с дисковыми операциями (Disk: writes temp).

Эффект

Ускоряет операции сортировки и хеширования, выполняемые в памяти.

💡 Рекомендации по созданию индексов

Для LIMIT 1 особенно важны индексы, которые позволяют сразу найти единственную запись.

Типичные сценарии:

• Простой поиск: Для запроса SELECT * FROM users WHERE email = 'test@example.com' LIMIT 1; индекс по колонке email: CREATE INDEX CONCURRENTLY idx_users_email ON users(email);.

• Поиск с сортировкой: Для запроса SELECT * FROM logs WHERE user_id = 123 ORDER BY created_at DESC LIMIT 1; (последняя запись пользователя) оптимален составной индекс: CREATE INDEX CONCURRENTLY idx_logs_user_id_created_at ON logs(user_id, created_at DESC);. Так PostgreSQL быстро найдет все записи пользователя в обратном порядке и возьмет первую.

🔍 Анализ план запроса

После создания индекса используйте EXPLAIN (ANALYZE, BUFFERS) для проверки , необходимо убедиться, что в плане появился узел Index Scan или Index Only Scan, а не Seq Scan (последовательное сканирование таблицы). Обратить внимание на стоимость операции и количество прочитанных данных.

🚫 Частые ошибки

• LIMIT без ORDER BY: Без явного указания порядка PostgreSQL возвращает строки в удобном для него порядке, который может быть непредсказуемым. Всегда нужно использовать ORDER BY с LIMIT для гарантированного результата.

• Слишком сложные условия в WHERE: Вычисления наподобие WHERE extract(month from created_at) = 12 часто не позволяют использовать индекс. Переписать условие так, чтобы колонка была "чистой": WHERE created_at >= '2025-12-01' AND created_at < '2026-01-01'.

• OFFSET с большими значениями: LIMIT 1 OFFSET 1000000 заставляет СУБД перебрать и отбросить все миллион строк. Для постраничной навигации использовать условие WHERE id > last_seen_id ORDER BY id LIMIT n.

P.S.

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

Аналогичная задача

PG_HAZEL : Анализ инцидента производительности СУБД PostgreSQL , поиск и оптимизация проблемных SQL-запросов.

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

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

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

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

Ожидания типа IPC

80% ожиданий:

• BgWorkerShutdown

• ParallelFinish

• ExecuteGather

Ожидания типа IPC по SQL-запросам

Следующий SQL-запрос для оптимизации : -1701015661318396920

Текст запроса содержит параметр, поэтому для анализа возможных вариантов оптимизации придется воспользоваться планом выполнения запроса, полученному из отчетов pgpro_pwr:

• pgpro_pwr_queryid: e864c744b5bda408

• plan_id: b8b72054c691886a

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

Оптимизация SQL-запроса

1. Оптимизация структуры запроса

Основная проблема - "декартово произведение" из-за нескольких LEFT JOIN.

2. Добавление недостающих индексов

3. Оптимизация текущего запроса

4. Настройка PostgreSQL

Возможный план оптимизации

1. Добавление индекса для plans_table4 - это самое слабое место в текущем плане

2. Разделить запрос на несколько - это даст наибольший прирост производительности

3. Использование агрегацию через JSON, если нужны все данные в одной строке

4. Настройка параметров СУБД

Аналогичная задача

PG_HAZEL : Анализ инцидента производительности СУБД PostgreSQL , поиск и оптимизация проблемных SQL-запросов.

Задача

Проанализировать причины инцидента производительности СУБД . Выявить и оптимизировать SQL-запросы влияющие на снижение производительности СУБД.

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

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

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

Ожидания типа IO

80% ожиданий DataFileRead

Ожидания типа IO по SQL-запросам

Ожидания типа IPC

80% ожиданий:

• BgWorkerShutdown

• ParallelFinish

• ExecuteGather

Ожидания типа IPC по SQL-запросам

Наибольшее количество ожиданий типа IPC по SQL c queryid = -5849488707035427374

Ожидания типа LWLock

80% ожиданий:

• BufferMapping

• ParallelHashJoin

• LockManager

Ожидания типа LWLock по SQL-запросам

Наибольшее количество ожиданий типа IPC по SQL c queryid = -5849488707035427374

SQL-запрос для оптимизации: -5849488707035427374

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

Оптимизация SQL-запроса

1. Добавить индексы

-- Для фильтрации table1

CREATE INDEX CONCURRENTLY col1x_succession_plan_filter

ON "table1" ("col2")

WHERE "col8" IS NULL AND "col9" IS NULL;

-- Для соединения с col7

CREATE INDEX CONCURRENTLY col1x

ON "col7" ("table1col1", "col8", "col4");

-- Для связи с table2

CREATE INDEX CONCURRENTLY col1x

ON "table1" ("table2col1");

2. Переписать запрос с использованием CTE

3. Оптимизация работы с IN

CREATE TEMP TABLE temp_tables (col2 BIGINT);

INSERT INTO temp_tables VALUES (Y1), (Y2), ...; -- все значения

-- Затем в основном запросе:

WITH filtered_tables AS (

SELECT ...

FROM "table1" sp

JOIN temp_tables pt ON sp."col2" = pt.col2

WHERE ...

)

Результат

Применение комплекса PG_HAZEL "Орешник" позволило резко сократить время на анализ причин снижения производительности СУБД и быстро определить ключевые возможности для оптимизации проблемных SQL-запросов.

Задача

Проанализировать причины инцидента производительности СУБД . Выявить и оптимизировать SQL-запросы влияющие на снижение производительности СУБД.

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

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

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

Диаграмма Парето по типу ожидания IPC

80% событий ожиданий :

• BgWorkerShutdown: Ожидание завершения фонового рабочего процесса.

• ExecuteGather: Ожидание активности дочернего процесса при выполнении узла плана Gather.

• ParallelFinish: Ожидание завершения вычислений параллельными рабочими процессами.

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

80% событий ожидания IPС по SQL-запросам:

• 6863414396188999698

• -4533756551948631336

• 106835746851505948

• -5395258115281111645

• -4460774138492313959

• -4665732847868082957

• -7678244758353921905

Результат анализа ожиданий на уровне SQL-запросов:

Наибольшее количество ожиданий IPC по запросу 6863414396188999698

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

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

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

• Sort

• Gather

• Workers Planned: 2

• Parallel Seq Scan

Оптимизация SQL-запроса

Измененный текст запроса

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

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

Результат

Применение комплекса PG_HAZEL "Орешник" позволило резко сократить время на анализ причин снижения производительности СУБД и быстро определить ключевые возможности для оптимизации проблемных SQL-запросов.

Задача

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

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

Анализ показателей инфраструктуры (iostat)

Анализ метрики w-await

С какими событиями ожидания (wait_event) может коррелировать рост значений w_await (iostat) для СУБД PostgreSQL ?

Рост показателя w_await в iostat (среднее время ожидания завершения операций записи на диск) в контексте PostgreSQL обычно коррелирует с событиями ожидания типа IO (Input/Output), которые указывают на то, что процессы СУБД активно ждут завершения операций записи на диск.

DataFileWrite

Ожидание записи данных в файл основной таблицы или индекса.

Высокая нагрузка UPDATE/INSERT, недостаточно быстрая работа подсистемы хранения.

WALWrite / WALWrite

Ожидание записи в журнал предзаписи (Write-Ahead Log, WAL).

Интенсивные изменения данных (любые DML-операции), синхронная репликация, настройки synchronous_commit

BufFileWrite

Ожидание записи во временный файл на диске.

Объемные операции, не помещающиеся в work_mem (сортировки, хэш-соединения), большие запросы с CREATE TEMPORARY TABLE.

SLRUWrite

Ожидание записи данных в структуры SLRU (например, журналы транзакций clog).

Очень высокая интенсивность commit/rollback транзакций.

DSMFillZeroWrite

Ожидание инициализации (заполнения нулями) нового сегмента динамической разделяемой памяти.

Активное использование параллельных запросов.

События ожиданий типа IO

80% ожиданий типа IO вызваны событиями ожиданий DataFileRead и DSMFillZeroWrite

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

• DSMFillZeroWrite Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

Статистика ожиданий типа IO на уровне SQL запросов для операций записи на диск

Итог

Рост времени ожидания записи(w_await/iostat) на дисковых устройствах используемых СУБД оказывает существенное влияние на рост ожиданий типа IO и снижение производительности СУБД.

Последствия и возможные причины роста значений w_await выше 11ms:

🔴 Критические проблемы инфраструктуры

1. Перегрузка подсистемы хранения

• Нехватка IOPS: Диски не справляются с количеством операций записи в секунду

• Исчерпание пропускной способности: Превышение лимитов по MB/s на уровне хранилища

• Высокая задержка (latency) контроллера: Проблемы с HBA-адаптерами или контроллерами RAID

2. Проблемы с конфигурацией хранилища

• Неправильный RAID уровень: RAID-5/6 для write-intensive workload (типично для PostgreSQL)

• Отсутствие BBU/конденсаторов: Нет кэширования записи на контроллере

• Неоптимальный размер stripe: Слишком маленький или большой stripe size

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

• "Шумные соседи": Другие ВМ/контейнеры на том же гипервизоре активно пишут

• Совместное использование дисков: WAL, данные и TEMP на одних дисках

• Фоновые процессы: Бэкапы, vacuum, аналитика на той же системе

4. Проблемы сетевого хранилища (SAN/NAS)

• Сетевая задержка: Проблемы с сетью 10GbE/FC

• Перегруженные порты коммутатора

• Проблемы на стороне массива хранения

📊 Интерпретация значений w_await

Значение w_await : 1-5 ms

Серьезность: ✅ Норма

Возможные причины: SSD, хорошо настроенное хранилище

Значение w_await : 5-10 ms

Серьезность: ⚠️ Внимание

Возможные причины: Начальная стадия перегрузки

Значение w_await : 10-20 ms

Серьезность: 🔶 Проблема

Возможные причины: Явные проблемы с IOPS/пропускной способностью

Значение w_await : 20-50 ms

Серьезность: 🔴 Критично

Возможные причины: Серьезная перегрузка хранилища

Значение w_await : 50+ ms

Серьезность: 🚨 Авария

Возможные причины: Практически отказ хранилища

📈 Когда бить тревогу?

• Постоянные значения > 20 ms - требуется срочное вмешательство

• Пики > 50 ms во время checkpoint - необходимо оптимизировать контрольные точки

• Рост w_await вместе с количеством active sessions - масштабирование необходимо

Значение 11 ms - это уже явный сигнал, что подсистема хранения работает на пределе и требует оптимизации, особенно для высоконагруженной PostgreSQL.