Настройте scale_factor
Параметр autovacuum_vacuum_scale_factor определяет, на сколько процентов роста таблицы будет спущен триггер автозадачи. Для активно обновляемых таблиц значение лучше уменьшить.
Тюнинг PostgreSQL: 7 настроек для ускорения запросов
Инфраструктура, которая растёт вместе с вами
Введение: почему медленные запросы — это деньги
Каждый миллисекунда задержки при загрузке страницы напрямую влияет на конверсию. Согласно исследованиям Google, если скорость загрузки падает с 1 до 3 секунд, конверсия может упасть на целых 7%. Для e-commerce это означает потерю тысяч рублей ежедневно.
PostgreSQL — мощная СУБД, но её «потолок» зависит не только от железа, но и от корректной настройки конфигурации. В этом гайде мы разберем 7 ключевых параметров, которые помогут выжать максимум из вашей базы данных.
Настройка shared_buffers и work_mem
Эти два параметра отвечают за использование оперативной памяти. Ошибка в их настройке — самая частая причина медленной работы PostgreSQL.
1. shared_buffers
Это главный параметр кэширования. Данные, которые вы читаете, сначала попадают сюда. Опытные администраторы рекомендуют устанавливать этот параметр в 25% от общего объема оперативной памяти. Например, на сервере с 16 ГБ RAM значение должно быть 4 ГБ.
Важно: не делайте shared_buffers слишком большим, иначе PostgreSQL начнет конкурировать с операционной системой за память, что может привести к фризам.
2. work_mem
Этот параметр определяет объем памяти, который PostgreSQL может использовать для сортировки и хеширования каждого отдельного запроса. Если вы выполняете сложный запрос с сортировкой на 100 ГБ данных, а work_mem равен 1 МБ, PostgreSQL будет вынужден использовать диск, что вызовет резкое падение производительности.
Индексы: когда и какие создавать
Индексы в PostgreSQL работают как оглавление в книге. Они ускоряют поиск строк, но замедляют запись (INSERT, UPDATE, DELETE), так как требуют обновления структуры.
Не переиндексируйте всё
Самый распространенный миф — что индексы нужно ставить на каждое поле. На практике это приводит к «информационному перегрузу». Индексируйте только те поля, по которым происходит JOIN или WHERE.
Выбор типа индекса
- B-Tree — стандарт для сортировки и равенства (по умолчанию).
- GIN — отлично подходит для массивов и JSON-полей.
- GiST — полезен для геоданных и полнотекстового поиска.
VACUUM и autovacuum: правильная настройка
Когда вы обновляете или удаляете строки в таблице, места в физическом файле не освобождаются. Это называется базой (bloat). Постепенно таблица растет в размерах, даже если данных в ней меньше.
Autovacuum — это процесс, который автоматически очищает старые версии строк. Если autovacuum отключен или настроен слишком редко, таблица может «заболеть» и перестать отвечать на запросы.
Проверьте stats_target
Для больших таблиц можно ограничить статистику (stats_target), чтобы ускорить сбор статистики, но это может снизить качество плана запроса.
Мониторинг медленных запросов через pg_stat_statements
Вы не можете улучшить то, что не можете измерить. Для глубокого анализа производительности в PostgreSQL используется расширение pg_stat_statements.
Как включить и использовать
В файле postgresql.conf нужно раскомментировать строку shared_preload_libraries = 'pg_stat_statements' и перезапустить сервер. Также добавьте pg_stat_statements.track = all.
После этого вы сможете выполнять запросы:
SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
Это покажет вам самые долгие запросы, которые требуют оптимизации.
Итоги
Чеклист для оптимизации
Перед деплоем нового проекта или обновлением старого обязательно проверьте эти настройки.
Проверьте shared_buffers
Установите значение в 25% от доступной RAM, но не более 8GB на 32-битных системах.
Настройте work_mem
Рассчитайте потребление на основе ожидаемого объема данных и количества одновременных пользователей.
Удалите лишние индексы
Отключите неиспользуемые индексы, чтобы ускорить операции записи.
Активируйте autovacuum
Убедитесь, что процессы autovacuum работают и не блокируют таблицы.
Настройте pg_stat_statements
Активируйте расширение и следите за топом медленных запросов.
Используйте индексы B-Tree
Для большинства случаев B-Tree является оптимальным выбором по скорости и ресурсам.
eMatch
Попробуйте управляемый PostgreSQL в eMatch
Не хотите возиться с конфигурациями и обновлениями? Мы предоставляем полностью управляемые кластеры PostgreSQL с автоматическим мониторингом, бэкапами и высокой доступностью. Зарегистрируйтесь за 2 минуты.