Postgres DBA

Задача

Экспериментальная проверка материалов доклада "Особенности записи WAL" PGConf.СПб 2025

Евгений Александров Т-Банк Старший инженер

Исследование механизма записи WAL в PostgreSQL с акцентом влияния на дисковую систему при высокой OLTP нагрузке. В докладе рассматриваются инструменты диагностики и даются рекомендации по настройке параметров, влияющих на поведение записи WAL.

Особенности записи WAL

Конфигурация тестовой ВМ

CPU = 8

RAM = 8GB

OS: RED OS MUROM (7.3.4)

PostgreSQL :

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

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

commit_delay = 0

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

commit_delay = 1000

Сценарий тестирования и нагрузка на СУБД

Mix

1. Select only : 50% нагрузки

2. Select + Update : 30% нагрузки

3. Insert only : 15% нагрузки

Нагрузка

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

Результаты

Среднее увеличение операционной скорости в эксперименте-2 составило 13.82%

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

Результаты

Среднее уменьшение ожиданий в эксперименте-2 составило ~1%

События ожидания (диаграмма Парето:80%)

Результат

1. Доля ожиданий Lock/relation в эксперименте-2 кардинально увеличилась.

2. Доля ожиданий LWLock/CheckpointerComm в эксперименте-2 кардинально увеличилась.

Показатели производительности инфраструктуры

iostat - для файловой системы /data

iostat - для файловой системы /wal

Утилизация диска файловой системы /wal

Результаты

Среднее уменьшение утилизации дискового устройства для файловой системы /wal в эксперименте-2 составило ~10%

Чек-лист CPU

Самый главный результат эксперимента

⚠️Рекомендация DeepSeek экспериментально не подтвердилась⚠️

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

commit_delay

Для конфигурации PostgreSQL 8 CPU, 8 GB RAM с shared_buffers=2GB под нагрузкой pgbench (5→100 соединений) оптимальное значение commit_delay = 0 (по умолчанию) является наиболее эффективным. Обоснование:

⚠️ Почему НЕ рекомендуется изменять commit_delay?

1. Природа pgbench нагрузки:
   TPC-B-like транзакции выполняются за 1-5 мс. Любая задержка коммита (commit_delay > 0) увеличивает latency на 20-100% .
   При 100 соединениях группировка коммитов не даёт выигрыша: короткие транзакции редко совпадают во времени .

Рекомендации DeepSeek по тюнингу конфигурационных параметров СУБД PostgreSQL

Реализация стратегической цели

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

План

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

2. Комплексный анализ инфраструктуры.

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

4. Рекомендации нейросети DeepSeek по снижению ожиданий.

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

6. Анализ проблемного SQL запроса с использованием нейросети DeepSeek.

7. Рекомендации по оптимизации проблемного SQL запроса с использованием нейросети DeepSeek.

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

2. Комплексный анализ инфраструктуры.

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

Анализ метрик iostat для файловой системы /data

Чек-лист CPU

Чек-лист RAM

Результат

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

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

3. Свободная RAM - менее 5%.

3.Корреляционные анализ ожиданий СУБД и выявление проблемных SQL запросов для оптимизации.

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

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

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

Диаграмма Парето(80%) по событиям ожиданий

4.Рекомендации нейросети DeepSeek по снижению ожиданий

DataFileRead

ParallelHashJoin

BufferMapping

LockManager

BufferContent

5. Проблемные SQL запросов для оптимизации.

Все проблемные запросы имеют ожидания DataFileRead . Причина - проблемы инфраструктуры.

Запросы имеющие ожидания типа LWLock

Запросы -583459049601560367 , -5849488707035427374 - проанализированы , ранее .

Запрос для оптимизации -4217442339750081997

6. Анализ проблемного SQL запроса с использованием нейросети DeepSeek.

Ключевые паттерны в SQL-запросе:

1. Точечная фильтрация по позиции

"table1"."col2" IN (871798)

- Фильтрация по конкретному col1 позиции (в отличие от предыдущих запросов со списками)

- Выборка данных для одной целевой позиции

2. Архитектура мягкого удаления (Soft Delete)

WHERE "table1"."col8" IS NULL

AND "table3"."col8" IS NULL

- Многоуровневая проверка на мягкое удаление

- Применяется к основной и связанным таблицам

3. Составные условия JOIN с бизнес-фильтрацией

LEFT OUTER JOIN ... ON "table1"."col1" = "table3"."table1col1"

AND ("table3"."col8" IS NULL

AND "table3"."col4" NOT IN (65, 66))

- Бизнес-логика в условиях JOIN, а не WHERE

- Исключение специфических workflow статусов (65, 66)

- Комбинация условий удаления и статусов

4. Система архивации записей

"table1"."col9" IS NULL

- Отдельный механизм архивации помимо мягкого удаления

- Дополнительный уровень управления жизненным циклом данных

5. LEFT JOIN для опциональных отношений

- `table2` - опциональная связь (риск ухода может отсутствовать)

- `table3` - опциональная связь с фильтрацией по статусам

6. Иерархическая модель данных

table1 (основа)

├── table2 (опциональный риск ухода)

└── table3 (преемники с фильтрацией)

- Древовидная структура с центральной сущностью table1

7. Паттерн именования алиасов

"table2"."col1" AS "table2.col1"

"table3"."col1" AS "table3.col1"

- Точечная нотация для представления отношений

- Четкое указание источника данных в результатах

8. Статусная модель workflow

- `col4` - управление рабочим процессом

- `col5` - статус кандидата

- `col6` - уровень готовности преемника

9. Экранирование идентификаторов

- Все имена в двойных кавычках для сохранения регистра

- Согласованное именование по всему запросу

Ключевые паттерны запроса

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

7. Рекомендации по оптимизации проблемного SQL запроса с использованием нейросети DeepSeek.

1. Создание специализированных индексов

Для таблицы `table1`:

-- Составной индекс для условий WHERE

CREATE INDEX CONCURRENTLY col1x_succession_plan_main

ON "table1" ("col2", "col8", "col9")

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

-- Индекс для связи с table2

CREATE INDEX CONCURRENTLY col1x_succession_plan_risk_leaving

ON "table1" ("table2col1")

WHERE "col8" IS NULL;

Для таблицы `col7`:

-- Оптимизированный индекс для JOIN с фильтрацией

CREATE INDEX CONCURRENTLY col1x_col7_filtered

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

WHERE "col8" IS NULL AND "col4" NOT IN (65, 66);

-- Альтернативный вариант для лучшей селективности

CREATE INDEX CONCURRENTLY col1x_col7_main

ON "col7" ("table1col1")

INCLUDE ("col4", "col5", "col6")

WHERE "col8" IS NULL;

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

Рассмотрите рефакторинг условия:

-- Текущий вариант может быть оптимизирован

WHERE "table1"."col8" IS NULL

AND "table1"."col2" IN (871798)

AND "table1"."col9" IS NULL

-- Убедитесь, что порядок условий соответствует индексам

3. Оптимизация JOIN операций

Для больших объемов данных рассмотрите:

-- Разделение запроса на два с использованием CTE

WITH filtered_plans AS (

SELECT "col1", "col2", "table2col1"

FROM "table1"

WHERE "col8" IS NULL

AND "col2" IN (871798)

AND "col9" IS NULL

)

SELECT

fp."col1", fp."col2",

rl."col1" AS "table2.col1",

rl."col3" AS "table2.col3",

s."col1" AS "table3.col1",

s."col4" AS "table3.col4",

s."col5" AS "table3.col5",

s."col6" AS "table3.col6"

FROM filtered_plans fp

LEFT JOIN "table2" rl ON fp."table2col1" = rl."col1"

LEFT JOIN "col7" s ON fp."col1" = s."table1col1"

AND s."col8" IS NULL

AND s."col4" NOT IN (65, 66);

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

-- Увеличьте work_mem для лучшей сортировки и хэширования

SET work_mem = '256MB';

-- Убедитесь, что effective_cache_size правильно настроен

SHOW effective_cache_size;

5. Дополнительные рекомендации

-Статистика по индексам: Убедитесь, что `default_statistics_target` достаточно высок для точной оценки селективности

- Параллельные запросы: Проверьте настройки `max_parallel_workers_per_gather` для больших таблиц

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

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

1. Читаемость и однозначность запроса (Самый главный положительный эффект)

Это не прямое последствие для СУБД, а скорее для разработчика, но оно косвенно влияет на качество кода и меньшее количество ошибок.

• WHERE — это финальное фильтрующее условие. Оно четко указывает, какие строки должны попасть в окончательный результат.

• ON — это условие связи таблиц. Оно определяет, как строки двух таблиц соотносятся друг с другом.

Разделяя логику связи (ON) и логику фильтрации (WHERE), вы делаете запрос более понятным и легким для поддержки.

Пример:
Предположим, нам нужны все заказы и информация о клиентах, но только для клиентов из Москвы.

-- Менее читаемо (условие связи и фильтрации перемешаны)

SELECT *

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id AND c.city = 'Москва';

-- Более читаемо и логично

SELECT *

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id

WHERE c.city = 'Москва'; -- Но ВНИМАНИЕ! Этот запрос не эквивалентен предыдущему!

Здесь ключевой момент: Эти два запроса не эквивалентны. Второй запрос превратит LEFT JOIN в INNER JOIN, потому что условие на таблицу customers в WHERE отфильтрует все строки, где c.city IS NULL (т.е. все "не совпавшие" строки от LEFT JOIN).

2. Потенциальная помощь планировщику в сложных запросах

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

• Что делает планировщик? Он пытается "протолкнуть" условия (push down predicates) как можно ближе к сканированию данных.

• Разница: Условие в ON должно быть применено во время операции соединения, а условие в WHERE — после него. Иногда явное разделение позволяет планировщику применить фильтр на более раннем этапе, если это выгодно.

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

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

Это обратная сторона медали. Перенос условия из ON в WHERE для LEFT|RIGHT|FULL JOIN кардинально меняет результат запроса.

• Условие в ON: Фильтрует строки из правой таблицы до того, как будет выполнен JOIN. Строки из левой таблицы остаются, даже если условие не выполнилось.

• Условие в WHERE: Фильтрует результат после того, как JOIN был выполнен. Для LEFT JOIN это означает, что все строки, где правое поле — NULL (из-за несовпадения), будут отброшены, превращая join по сути во INNER.

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

-- Хочу всех клиентов и их заказы, но только те заказы, которые > 1000

SELECT *

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id AND o.amount > 1000;

-- Вернет ВСЕХ клиентов. Для тех, у кого нет больших заказов, поля заказа будут NULL.

-- Хочу только тех клиентов, у КОТОРЫХ ЕСТЬ заказы > 1000

SELECT *

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

WHERE o.amount > 1000;

-- Это, по факту, INNER JOIN по условию на заказ. Не вернет клиентов без подходящих заказов.

Выводы

1. Для INNER JOIN смело переносите условия из ON в WHERE. Разницы в производительности не будет, но запрос станет чище и понятнее. Планировщик обработает их одинаково.

2. Для OUTER JOIN (LEFT, RIGHT, FULL) понимайте разницу. Перенос условия из ON в WHERE полностью меняет логику запроса. Это не оптимизация, а изменение результата.
   ON: "Как соединять таблицы?"
   WHERE: "Что показывать в финальном результате?"

3. Положительные последствия переноса в WHERE — это в первую очередь:
   Улучшенная читаемость и сопровождаемость кода.
   Более явное и четкое выражение вашего намерения.
   В редких сложных случаях — потенциально более простой план запроса для СУБД.

Таким образом, главный положительный эффект — смысловой и архитектурный, а не прямой прирост производительности в современных версиях PostgreSQL.

Задача

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

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

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

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

События ожидания, составляющие 80% ожиданий СУБД по типам ожиданий (диаграмма Парето)

Рекомендации DeepSeek по снижению событий ожидания(сформированы заранее , формируются локально для заданных wait_event)

Формирование локальной базы знаний по рекомендациям нейросети

DeepSeek : рекомендации по оптимизации ожиданий СУБД PostgreSQL.

SQL запросы(queryid), составляющие 80% по количеству ожиданий СУБД по типам ожиданий (диаграмма Парето)

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

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

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

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

Рекомендации DeepSeek по оптимизации SQL запроса

1. Создание индексов

-- Для таблицы table1 (условия WHERE и JOIN)

CREATE INDEX CONCURRENTLY idx_table1_filter ON table1 (col2, col7, col8)

WHERE col7 IS NULL AND col8 IS NULL;

-- Для таблицы table2 (первичный ключ)

CREATE INDEX CONCURRENTLY idx_table2_col1 ON table2 (col1);

-- Для таблицы table4 (JOIN и условия)

CREATE INDEX CONCURRENTLY idx_table4_join ON table4 (table1col1, col7, col4)

WHERE col7 IS NULL AND col4 NOT IN (65, 66);

2. Упрощение условий

Перенесите условия из JOIN в WHERE, где это возможно, но для LEFT JOIN это нужно делать аккуратно:

SELECT ...

FROM table1

LEFT JOIN table2 ON table1.table2col1 = table2.col1

LEFT JOIN table4 AS table3 ON table1.col1 = table3.table1col1

WHERE

table1.col7 IS NULL

AND table1.col2 IN (...)

AND table1.col8 IS NULL

AND table3.col7 IS NULL -- Условие перенесено из JOIN

AND table3.col4 NOT IN (65,66)

3. Анализ и переписывание запроса

• Проверьте, не выполняется ли неявное преобразование типов в условиях JOIN

• Используйте EXISTS вместо IN, если список значений большой

• Разбейте запрос на CTE, если это упрощает логику

4. Дополнительные оптимизации

-- Обновите статистику после создания индексов

ANALYZE table1;

ANALYZE table2;

ANALYZE table4;

-- Проверьте настройки производительности

SET work_mem = '256MB'; -- Для операций сортировки и хэширования

5. Альтернативный вариант с подзапросом

WITH filtered_table1 AS (

SELECT col1, col2, table2col1

FROM table1

WHERE

col7 IS NULL

AND col2 IN (80214, 81665, ..., 564497)

AND col8 IS NULL

)

SELECT

t1.col1,

t1.col2,

t2.col1 AS "table2.col1",

t2.col3 AS "table2.col3",

t3.col1 AS "table3.col1",

...

FROM filtered_table1 t1

LEFT JOIN table2 t2 ON t1.table2col1 = t2.col1

LEFT JOIN table4 t3 ON

t1.col1 = t3.table1col1

AND t3.col7 IS NULL

AND t3.col4 NOT IN (65,66);

Для проверки эффективности:

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

2. Следите за операциями Seq Scan -> они должны замениться на Index Scan

3. Проверьте оценку количества строк (rows) на каждом этапе

Особое внимание уделите селективности условия table1.col2 IN (...) - если выбирается больше 5-10% данных, полносканирование может быть эффективнее индекса. В этом случае рассмотрите индекс только на (col7, col8).

Также убедитесь, что в таблице table4 нет избыточных данных - условие col4 NOT IN (65,66) может быть вынесено в предварительную фильтрацию, если эти значения встречаются часто.

Начало работ и детали запроса

Оптимизация SQL-запросов PostgreSQL : LIKE (пример)

Оптимизация запросов с оператором LIKE в PostgreSQL — это важная задача, особенно при работе с большими объемами данных. Существует несколько эффективных подходов, которые кардинально меняют производительность в зависимости от конкретного шаблона поиска.

1. LIKE 'строка%' (Поиск по префиксу)

Рекомендуемый метод

B-tree индекс

Ключевые действия

Создать стандартный индекс: CREATE INDEX idx_name ON table (column);

2. ILIKE 'строка%' (Case-insensitive префикс)

Рекомендуемый метод

Функциональный B-tree индекс

Ключевые действия

Создать индекс на LOWER(column) и использовать LOWER(column) LIKE 'строка%'

3.LIKE '%строка' (Поиск по суффиксу)

Рекомендуемый метод

Индекс на обратной строке

Ключевые действия

Создать индекс: CREATE INDEX idx_name ON table (reverse(column));

и использовать reverse(column) LIKE reverse('%строка')

4. LIKE '%строка%' или ILIKE '%СтрОкА%' (Поиск по подстроке)

Рекомендуемый метод

GIN/GIST индекс с расширением pg_trgm

Ключевые действия

Включить расширение: CREATE EXTENSION pg_trgm;.

Создать индекс: CREATE INDEX idx_name ON table USING GIN (column gin_trgm_ops);

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

1. Использование расширения pg_trgm для сложных шаблонов

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

• Создание индекса: После активации расширения (CREATE EXTENSION IF NOT EXISTS pg_trgm;) можно создать GIN-индекс (обычно он предпочтительнее для такого типа поиска) или GiST-индекс.

CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

Важное ограничение: Триграммы работают для шаблонов длиной от 3 символов. Поиск одно- или двухсимвольных подстрок не будет эффективно использовать этот индекс.

2. Тонкая настройка B-tree индексов для простых случаев

Если поиск осуществляется только по началу строки, стандартный B-tree индекс будет самым эффективным решением, так как он создает упорядоченную структуру, идеально подходящую для поиска по диапазону.

• Для ILIKE (регистронезависимый поиск) обычный B-tree индекс не подойдет. Вместо него нужно создать функциональный индекс на результат функции LOWER() или UPPER() и использовать эту же функцию в запросе.

CREATE INDEX idx_users_name_lower ON users (LOWER(name));

-- Использовать в запросе:

SELECT * FROM users WHERE LOWER(name) LIKE LOWER('ivan%');

Локаль базы данных: Если база данных использует не C локаль, для поддержки LIKE может потребоваться создать индекс с классом операторов text_pattern_ops.

Начало работ и детали запроса

Оптимизация SQL-запросов PostgreSQL : большое количество LEFT OUTER JOIN (пример)

Оптимизация запросов с большим количеством LEFT OUTER JOIN в PostgreSQL — это комплексная задача, которая требует подхода как к написанию запроса, так и к настройке самой СУБД. Ключевые направления — это помощь планировщику запросов, правильное индексирование и рассмотрение альтернативных архитектурных решений.

1.Управление порядком JOIN

Ключевая идея

Уменьшить количество вариантов плана для анализа планировщиком

Пример действий

Использовать явный синтаксис JOIN и настроить параметр join_collapse_limit.

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

Ключевая идея

Снизить нагрузку на операции сравнения и хеширования.

Пример действий

Использовать более компактные типы данных (например, INT вместо TEXT для ID).

3.Эффективное индексирование

Ключевая идея

Обеспечить быстрое нахождение строк для соединения

Пример действий

Создать индексы на колонках, участвующих в условиях ON для каждого соединения.

4.Архитектурные изменения

Ключевая идея

Полностью избежать затрат на соединение во время выполнения запроса

Пример действий

Рассмотреть денормализацию таблиц или использование материализованных представлений.

💡 Практические шаги по оптимизации

1.Помочь планировщику запросов

Планировщик PostgreSQL при большом количестве JOINов сталкивается с экспоненциальным ростом числа возможных планов выполнения. Чтобы сократить время планирования, можно использовать явный синтаксис JOIN (например, a LEFT JOIN b ON ... LEFT JOIN c ON ...), который задает более предсказуемый порядок. Затем можно установить параметр join_collapse_limit = 1, чтобы планировщик следовал этому порядку. Это особенно актуально, если само построение плана запроса занимает значительное время (секунды).

2. Анализ и упрощение данные

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

3.Правильные индексы

Это основа основ. Для каждого условия ON в ваших LEFT JOIN должен существовать индекс. Как минимум, индексируются колонки из правой таблицы. Например, для ... LEFT JOIN table_b ON table_a.id = table_b.a_id ... полезно иметь индекс на table_b.a_id.

4.Альтернативные подходы

Если запрос выполняется редко, но требует много ресурсов, эффективным решением может быть материализованное представление (Materialized View), которое хранит результат запроса на диске и периодически обновляется.

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

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