DeepLearning Blog

PostgreSQL 16: Почему «серебряной пули» не существует и где искать скрытую производительность

Введение: Миф о настройках «по умолчанию»

Многие технические лидеры и разработчики ошибочно полагают, что PostgreSQL готов к промышленным нагрузкам сразу «из коробки». Это опасное заблуждение: дефолтная конфигурация ориентирована на максимальную совместимость, а не на производительность. Как говорит Евгений Аристов, старший архитектор БД и автор практических руководств по оптимизации: «Серебряной пули не существует». Любой тюнинг — это всегда компромисс между потреблением ресурсов, надежностью и скоростью.

В этом материале мы разберем глубокие нюансы внутреннего устройства PostgreSQL 16, опираясь на бенчмарки и реальные кейсы. В качестве тестового полигона мы используем инстанс с 4 ядрами, 16 ГБ ОЗУ и SSD-диском, работающий на датасете «Тайские перевозки» (Thai Bus Transportation) объемом от 6 до 600 миллионов строк. Мы увидим, что даже стандартные операции вроде COUNT могут стать узким местом, если не понимать механику работы планировщика.

Парадокс COUNT: Когда индексы не работают

Распространенный спор о том, что быстрее — count(), count(1) или count(column), — в PostgreSQL 16 имеет четкий технический ответ. Планы выполнения (EXPLAIN) показывают, что count() и count(1) абсолютно идентичны по стоимости (cost). Однако настоящая ловушка кроется в count(column).

1. Проблема NULL: В отличие от первых двух вариантов, count(column) не считает строки, где значение колонки равно NULL. Это требует от СУБД дополнительной проверки каждой записи.
2. Ловушка оптимизации: Даже если вы установите ALTER COLUMN ... SET NOT NULL, это не гарантирует переход от тяжелого Parallel Seq Scan к быстрому Parallel Index Only Scan. Планировщик часто игнорирует индекс из-за неверной оценки стоимости страниц.

Ключевым фактором здесь выступает параметр random_page_cost. По умолчанию он равен 4.0, что было актуально для эпохи HDD. Для современных SSD и NVMe это значение избыточно.

• Действие: Установите random_page_cost в значение 1.0–1.1. Это заставит планировщик понять, что чтение произвольной страницы из индекса обходится не дороже последовательного сканирования. Только после этого вы увидите в плане заветный Index Only Scan и значительное снижение «попугаев» (условных единиц стоимости запроса).

PostgreSQL может игнорировать существующий индекс, если считает, что обращение к Visibility Map (VM) для проверки видимости данных в рамках MVCC обойдется слишком дорого в текущих единицах page cost.

Скрытая цена UUID: Красиво, но медленно

UUID v4 популярен в распределенных системах, но как архитектор я обязан предупредить о его «скрытых налогах». Сравнение с классическим 8-байтовым bigserial выявляет две проблемы:

1. Overhead генерации: Генерация UUID v4 в Linux может быть в 1.5–3 раза медленнее. Причина — в системных вызовах и ожидании энтропии от генератора случайных чисел. На 10 миллионах итераций эти доли миллисекунд превращаются в десятки секунд задержки.
2. Деградация Visibility Map: Из-за полной случайности UUID v4 данные распределяются по индексу хаотично. При проверке видимости строк в рамках механизма MVCC, PostgreSQL вынужден постоянно подтягивать разные страницы Visibility Map из кэша или с диска. В отличие от последовательного bigserial, где обращения к VM локализованы в одной странице, UUID создает «эффект случайных прыжков», вымывая полезные данные из кэша.

В PostgreSQL 16 работа с UUID была оптимизирована, но до появления UUID v7 в основном ядре, случайные идентификаторы остаются источником фрагментации и лишнего I/O.

Стратегия выживания: Swappiness против OOM Killer

Настройка памяти в ОС — это вопрос выживания базы. Существует миф, что swappiness настраивается только до 100. На самом деле, в современных ядрах Linux предел — 200. Для СУБД критичны три стратегии:

1. «0»: Риск мгновенного прихода OOM Killer при всплеске нагрузки.
2. «1»: Классика — своп используется только в крайнем случае.
3. «2–5»: Оптимальный баланс для production, дающий системе время на реакцию до того, как процесс будет убит.

Чтобы OOM Killer не завершил работу основного процесса PostgreSQL, необходимо использовать точную формулу расчета лимитов памяти:

ОЗУ > 2Gb + shared_buffers + max_connections * (work_mem + temp_buffers) + maintance_parallel_workers * maintance_work_mem

Игнорирование maintance_parallel_workers (параллельных рабочих процессов обслуживания) — частая причина падения баз при выполнении регламентного REINDEX или VACUUM.

Ловушка «умных» технологий: Проблема Transparent Huge Pages

Технология Huge Pages позволяет работать с памятью блоками по 2 МБ или 1 ГБ вместо стандартных 4 КБ. Но её автоматическая реализация — Transparent Huge Pages (THP) — это враг стабильности.

Данные RedHat показывают ощутимую разницу в производительности:

• С включенным THP: 131.55s
• Со статическими Huge Pages (1GB SHP): 128.34s

Проблема в том, что динамическое выделение страниц операционной системой вносит непредсказуемые задержки (latency). В PostgreSQL 16 параметр huge_pages по умолчанию стоит в try. Это режим «молчаливого отказа»: если ОС не настроена, СУБД просто продолжит работать на стандартных страницах, теряя до 10% производительности.

• Совет практика: Всегда проверяйте реальное использование больших страниц командой grep Huge /proc/meminfo. Если HugePages_Total равен 0, ваш huge_pages = try не работает.

Почему ваши бенчмарки лгут: Опасность дефолтного pgbench

Стандартный тест pgbench по профилю TPC-B имитирует банковские транзакции и состоит из 5 запросов (1 SELECT, 3 UPDATE, 1 INSERT). Это дает 80% пишущей нагрузки.

В реальности 90% приложений — это чтение (Read-Heavy). Тестируя базу через TPC-B, вы оптимизируете её под сценарий, которого не существует в вашем проекте. Еще одна опасность — использование функции random() внутри SQL-скриптов для бенчмарков.

Внимание: Функция random() в PostgreSQL является волатильной (VOLATILE). Если использовать её напрямую в SELECT ... WHERE id = random(...), она будет вызываться заново для каждой из 6 миллионов строк таблицы, превращая простой поиск по индексу в бесконечный цикл вычислений.

Для честных тестов используйте кастомные скрипты с предварительной генерацией переменных (через \set r random(...)), которые отражают реальный профиль нагрузки вашего приложения.

Заключение: Взгляд в будущее оптимизации

Производительность PostgreSQL 16 — это не результат одной «правильной» настройки, а следствие глубокого понимания взаимодействия СУБД с железом и ОС. Мы увидели, что старые значения random_page_cost убивают индексы, а «умный» THP мешает работе с памятью.

Оптимизация — это всегда осознанный выбор. И финальный вопрос для каждого архитектора звучит так:

«Готовы ли вы пожертвовать надежностью ради 3000% прироста скорости, установив synchronous_commit = off, или в вашем бизнесе данные стоят дороже миллисекунд?»