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

Deferred Join — 644% эффективности

Предыдущая работа по теме пагинации PostgreSQL

Предисловие

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

Задача

Сравнить методы пагинации получения случайной страницы размером 100 строк для SQL-запроса, позволяющего получить информацию для анализа о проданных билетах из таблицы >15M строк.

Прогноз нейросети

Методология исследования

Тестовая среда и инструменты:

• СУБД: PostgreSQL 17
• Инструмент нагрузочного тестирования: pg_expecto
• Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
• Условия тестирования: параллельная нагрузка, запрос к большой таблице.

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

Исследованные методы пагинации

OFFSET+LIMIT

DEFFERED JOIN

KEYSET

Метод ROW_NUMBER - исключён из тестов, по результатам предыдущих экспериментов.

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

График изменения операционной скорости в ходе нагрузочного тестирования

Результат

Среднее превышение производительности , при использовании метода "Deffered Join" :

• По сравнению с методом "Offset + Limit" : 263.08%
• По сравнению с методом "Keyset" : 644.13%

Характерные особенности тестовых запросов и планов выполнения

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

1. Прямой OFFSET-LIMIT

Основные характеристики:

• Использует простой OFFSET с LIMIT для получения случайных 100 строк
• Выполняет полное сканирование индекса по первичному ключу
• Время выполнения: 8451 мс - самый медленный вариант

Проблемы производительности:

• OFFSET требует просмотра всех пропускаемых строк
• Каждая строка читается из таблицы (Index Scan), а не только из индекса
• Большие накладные расходы на чтение и отбрасывание миллионов строк

2. Deferred Join

Основные характеристики:

• Использует двухэтапный подход: сначала выбирает ключи, затем данные
• Время выполнения: 1728 мс - самый быстрый вариант (в 4.9 раза быстрее plan-1)

Преимущества:

• Вложенный запрос использует Index Only Scan - читает только индекс, без обращения к таблице
• Внешний запрос делает точечные выборки по индексу (Index Scan)
• Меньше чтений с диска благодаря работе с индексом на первом этапе

3. Keyset-пагинация

Основные характеристики:

• Использует курсорный подход с условием WHERE
• Время выполнения: 8206 мс - почти такой же медленный как plan-1

Проблемы производительности:

• Хотя используется CTE и индексное сканирование, возникает проблема с Join Filter
• Rows Removed by Join Filter: 6,284,350 - фильтр отбрасывает миллионы строк
• Nested Loop выполняется 6.2 млн раз (один раз для каждой строки)

Принципиальные различия:

Ключевые выводы:

1. Deferred Join показал лучшую производительность благодаря:
2. Разделению запроса на два этапа
3. Использованию Index Only Scan для первоначальной фильтрации
4. Минимизации обращений к табличной части данных
5. Основная проблема OFFSET - необходимость материализации и пропуска всех предыдущих строк
6. Keyset-пагинация в данном варианте реализации неэффективна из-за:
7. Неоптимального использования условия WHERE
8. Огромного количества отбрасываемых строк через Join Filter

Рекомендация:

Для выборки случайных строк из большой таблицы подход Deferred Join является наиболее оптимальным, так как минимизирует I/O операции и эффективно использует индексы

Характерные особенности производительности СУБД

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

Общая картина производительности:

1. Deferred Join (cluster-2) - Лучшая производительность
2. Начальная скорость: 580-588
3. Пиковая скорость: 920-922
4. Средняя стабильная скорость: 918-922
5. OFFSET-LIMIT (cluster-1) - Средняя производительность
6. Начальная скорость: 160-164
7. Пиковая скорость: 254-258
8. Средняя стабильная скорость: 254-256
9. Keyset (cluster-3) - Наихудшая производительность
10. Начальная скорость: 78-82
11. Пиковая скорость: 120-124
12. Средняя стабильная скорость: 122-124

Ключевые особенности по метрикам:

1. WAITINGS (общее количество ожиданий):

• Keyset: Высокие ожидания в начале (28), затем снижаются до 2-3
• Deferred Join: Умеренные ожидания в начале (3-5), затем снижаются до 1-3
• OFFSET-LIMIT: Минимальные ожидания (0-4), но с более низкой производительностью

2. IO (ожидания ввода-вывода):

• Keyset и Deferred Join: Имеют IO ожидания только в начальной фазе (9 и 3 соответственно), затем падают до 0
• Это указывает на кэширование данных - после начальной загрузки в память IO ожидания исчезают

3. IPC (межпроцессное взаимодействие):

• Только Keyset показывает IPC ожидания (19 в начале), что объясняется сложной структурой запроса с CTE и соединениями
• После прогрева IPC ожидания исчезают

4. LWLOCK (легковесные блокировки):

• Все подходы показывают LWLOCK ожидания после прогрева
• Keyset: 2-3 ожидания
• Deferred Join: 1-3 ожидания
• OFFSET-LIMIT: 2-4 ожидания

Фазы выполнения:

Фаза прогрева (первые ~30 измерений):

1. Deferred Join: Быстрый рост от 580 до 600
2. OFFSET-LIMIT: Рост от 160 до 170
3. Keyset: Медленный рост от 78 до 82

Фаза стабилизации (измерения 40-110):

1. Deferred Join: Стабильно высокие значения 900-922
2. OFFSET-LIMIT: Стабильно средние значения 254-258
3. Keyset: Стабильно низкие значения 122-124

Принципиальные выводы:

1. Deferred Join быстрее в 3.6 раза, чем OFFSET-LIMIT, и в 7.4 раза, чем Keyset
2. Наличие IO ожиданий только в начале у Deferred Join и Keyset подтверждает эффективное использование кэша после прогрева
3. Высокие IPC ожидания у Keyset объясняют его худшую производительность - дополнительные накладные расходы на управление курсорами и CTE
4. Несмотря на минимальные waitings, OFFSET-LIMIT показывает худшую производительность, чем Deferred Join - проблема в алгоритмической сложности OFFSET, а не в системных ожиданиях
5. Deferred Join демонстрирует лучшую масштабируемость - его производительность продолжает расти дольше и достигает более высоких значений

Рекомендации:

• Для production-систем с высокой нагрузкой Deferred Join - оптимальный выбор
• OFFSET-LIMIT следует избегать для больших смещений
• Keyset-пагинация в данной реализации неэффективна и требует пересмотра
• Все подходы выигрывают от кэширования, что важно учитывать при проектировании

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

❓Проанализируй показатели метрик vmstat в ходе нагрузочного тестирования для разных тестовых запросов . Выдели ключевые особенности по метрикам.

Все три теста показывают схожую динамику:

• Первая фаза (измерения 1-50): умеренная нагрузка, CPU idle ~36%
• Переходная фаза (измерения 48-53): резкий рост нагрузки
• Вторая фаза (измерения 54-110): полная загрузка CPU (99% user time)

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

1. procs_r (процессы в состоянии выполнения):

• OFFSET-LIMIT: 6 → 15 процессов (рост в 2.5 раза)
• Deferred Join: 6 → 15 процессов (рост в 2.5 раза)
• Keyset: 5 → 15 процессов (рост в 3 раза)

Вывод: Keyset создает более равномерную нагрузку на планировщик процессов.

2. Память (memory_swpd, memory_free, memory_cache):

Начальное состояние памяти:

• OFFSET-LIMIT: 277МБ свободно, 6816МБ кэш
• Deferred Join: 393МБ свободно, 6686МБ кэш (лучший старт)
• Keyset: 168МБ свободно, 6855МБ кэш (худший старт)

Динамика использования памяти:

• Deferred Join показывает наиболее агрессивное использование кэша (рост до 6794МБ)
• OFFSET-LIMIT освобождает память в конце теста (рост free с 167 до 290МБ)
• Keyset имеет стабильно низкий free memory (168-191МБ)

3. Ввод-вывод (io_bo - блоки out):

• OFFSET-LIMIT: 54 → 60 (рост 11%)
• Deferred Join: 53 → 52 (снижение 2%) - лучший показатель
• Keyset: 48 → 58 (рост 21%) - наибольший рост

Вывод: Deferred Join создает минимальную нагрузку на диск.

4. Системные события (system_in - прерывания, system_cs - переключения контекста):

Прерывания:

• OFFSET-LIMIT: 5721 → 8245 (рост 44%)
• Deferred Join: 5672 → 8252 (рост 46%)
• Keyset: 5704 → 8261 (рост 45%)

Переключения контекста:

• OFFSET-LIMIT: 652 → 1008 (рост 55%)
• Deferred Join: 632 → 1002 (рост 59%)
• Keyset: 642 → 998 (рост 55%)

Вывод: Все подходы создают сравнимую системную нагрузку.

5. Использование CPU (cpu_us, cpu_sy, cpu_id):

Критическое различие - переход к полной загрузке:

• OFFSET-LIMIT: Переход на 51-м измерении (8209 прерываний)
• Deferred Join: Переход на 50-м измерении (8196 прерываний)
• Keyset: Переход на 53-м измерении (8189 прерываний)

Вывод: Deferred Join быстрее достигает максимальной производительности.

Стабильность в фазе полной нагрузки:

• Все три подхода стабильно держат 99% user CPU time
• 1% system CPU time - нормальная работа ядра
• 0% idle - система полностью загружена

Принципиальные выводы:

1. Deferred Join демонстрирует лучшую эффективность использования ресурсов:
2. Наименьшая нагрузка на диск (io_bo)
3. Наиболее агрессивное кэширование
4. Быстрый выход на максимальную производительность
5. Keyset имеет проблемы с памятью:
6. Стабильно низкий free memory
7. Наибольший рост нагрузки на диск
8. OFFSET-LIMIT показывает промежуточные результаты:
9. Умеренная нагрузка на все ресурсы
10. Способность освобождать память под конец теста
11. Все подходы создают сопоставимую нагрузку на CPU и системные ресурсы после выхода на стабильный режим.

Рекомендации для продуктивной нагрузки:

1. Для систем с ограниченной памятью: Осторожно с Keyset-подходом
2. Для систем с медленным диском: Deferred Join - оптимальный выбор
3. Для систем с высокой параллельной нагрузкой: Все подходы создают сравнимую нагрузку на CPU
4. Для смешанной нагрузки: Deferred Join показывает лучший баланс ресурсов

Главный вывод:

Несмотря на схожую итоговую нагрузку на CPU, Deferred Join демонстрирует лучшую эффективность на уровне подсистем ввода-вывода и использования памяти.