Пару лет назад мне в руки попал проект на PostgreSQL, который тормозил так, что пользователи успевали сходить за кофе, пока открывается список заказов. Переписывать код было нельзя: сроки, легаси и три команды на смежных сервисах, каждая из которых уверена, что узкое место у соседей. Я начал с самого простого — включил логирование медленных запросов и прогнал самые болезненные через EXPLAIN ANALYZE. Уже на этом этапе стало ясно, что дело не в языке и не в архитектуре, а в нескольких незаметных деталях, которые я и хочу разобрать. Ниже — семь приемов, которые в моем случае дали ощутимый прирост, и ни один из них не потребовал правок в прикладном коде.
Первый прием — правильные индексы. Звучит банально, но почти в каждом проекте я нахожу либо отсутствующий индекс под самый частый фильтр, либо индекс, который оптимизатор принципиально не использует. Помогает составной индекс с порядком колонок, повторяющим реальный запрос, частичный индекс с условием, отсекающим ненужные строки, и покрывающий индекс с INCLUDE, чтобы движок вообще не ходил в таблицу. И да, лишние индексы — это не бесплатно: каждый из них замедляет вставку и обновление и занимает место. Я обычно начинаю с того, что смотрю статистику использования индексов за неделю и безжалостно удаляю мертвый груз.
Второй и третий приемы — это статистика и обслуживание. Планировщик PostgreSQL не угадывает, он опирается на статистику, и если вы только что залили миллион строк, а ANALYZE не запускали, он будет строить планы по устаревшей картине мира. После массовых загрузок я всегда запускаю ANALYZE явно, а для горячих таблиц поднимаю частоту обновления статистики. С autovacuum та же история: дефолтные пороги на большой таблице означают, что мертвые строки копятся быстрее, чем чистятся, размер раздувается, и запросы начинают читать в разы больше страниц, чем нужно. Настройка autovacuum под конкретные таблицы и контроль раздутия дали мне, пожалуй, самый неожиданный прирост из всего списка.
Четвертый и пятый приемы — соединения и память. Каждое подключение в PostgreSQL — это отдельный процесс, и когда приложение открывает новое соединение на каждый запрос, сервер тратит время на форк и инициализацию вместо полезной работы. Пул соединений в транзакционном режиме решил это почти мгновенно, без единой строчки в коде сервиса. Дальше — память: я поднял shared_buffers примерно до четверти доступной памяти, аккуратно увеличил work_mem для тяжелых сортировок и агрегаций, выставил реалистичный effective_cache_size и снизил random_page_cost, потому что у нас уже давно SSD, а настройки остались с эпохи шпиндельных дисков. Тут важно не перестараться с work_mem: он выделяется на операцию, и на сотне соединений легко уйти в своп.
Шестой и седьмой приемы — разгрузить основную базу и не бояться денормализации. Читающие запросы я увел на реплику, а тяжелые регулярные отчеты, которые раньше били по живому, перенес в материализованные представления с обновлением по расписанию. Если данные можно кэшировать на минуту и никто не заметит разницы — почему бы и нет. Для очень больших таблиц с историей партиционирование по времени оказалось той самой соломинкой: запросы стали читать одну партицию вместо всей истории, а удаление старых данных превратилось из многочасовой операции в мгновенное отсоединение партиции. Это уже ближе к архитектуре, чем к настройке, но код приложения остался прежним.
Все эти приемы объединяет одно: сначала измеряем, потом трогаем. Я потратил немало времени на бесполезную оптимизацию того, что и так работало нормально, пока не приучил себя смотреть планы запросов и статистику, а не полагаться на интуицию. И еще: любое изменение настроек я проверяю под нагрузкой, близкой к продакшену, иначе легко получить красивые цифры на пустой базе и сюрприз в понедельник утром.
А теперь вопрос к вам, коллеги: какой прием из этого списка дал вам самый заметный прирост, а может, у вас в запасе есть свой восьмой трюк, который я упустил? Делитесь опытом в комментариях — вместе мы точно сделаем еще не одну базу быстрее, чем она была!
Первый прием — правильные индексы. Звучит банально, но почти в каждом проекте я нахожу либо отсутствующий индекс под самый частый фильтр, либо индекс, который оптимизатор принципиально не использует. Помогает составной индекс с порядком колонок, повторяющим реальный запрос, частичный индекс с условием, отсекающим ненужные строки, и покрывающий индекс с INCLUDE, чтобы движок вообще не ходил в таблицу. И да, лишние индексы — это не бесплатно: каждый из них замедляет вставку и обновление и занимает место. Я обычно начинаю с того, что смотрю статистику использования индексов за неделю и безжалостно удаляю мертвый груз.
Второй и третий приемы — это статистика и обслуживание. Планировщик PostgreSQL не угадывает, он опирается на статистику, и если вы только что залили миллион строк, а ANALYZE не запускали, он будет строить планы по устаревшей картине мира. После массовых загрузок я всегда запускаю ANALYZE явно, а для горячих таблиц поднимаю частоту обновления статистики. С autovacuum та же история: дефолтные пороги на большой таблице означают, что мертвые строки копятся быстрее, чем чистятся, размер раздувается, и запросы начинают читать в разы больше страниц, чем нужно. Настройка autovacuum под конкретные таблицы и контроль раздутия дали мне, пожалуй, самый неожиданный прирост из всего списка.
Четвертый и пятый приемы — соединения и память. Каждое подключение в PostgreSQL — это отдельный процесс, и когда приложение открывает новое соединение на каждый запрос, сервер тратит время на форк и инициализацию вместо полезной работы. Пул соединений в транзакционном режиме решил это почти мгновенно, без единой строчки в коде сервиса. Дальше — память: я поднял shared_buffers примерно до четверти доступной памяти, аккуратно увеличил work_mem для тяжелых сортировок и агрегаций, выставил реалистичный effective_cache_size и снизил random_page_cost, потому что у нас уже давно SSD, а настройки остались с эпохи шпиндельных дисков. Тут важно не перестараться с work_mem: он выделяется на операцию, и на сотне соединений легко уйти в своп.
Шестой и седьмой приемы — разгрузить основную базу и не бояться денормализации. Читающие запросы я увел на реплику, а тяжелые регулярные отчеты, которые раньше били по живому, перенес в материализованные представления с обновлением по расписанию. Если данные можно кэшировать на минуту и никто не заметит разницы — почему бы и нет. Для очень больших таблиц с историей партиционирование по времени оказалось той самой соломинкой: запросы стали читать одну партицию вместо всей истории, а удаление старых данных превратилось из многочасовой операции в мгновенное отсоединение партиции. Это уже ближе к архитектуре, чем к настройке, но код приложения остался прежним.
Все эти приемы объединяет одно: сначала измеряем, потом трогаем. Я потратил немало времени на бесполезную оптимизацию того, что и так работало нормально, пока не приучил себя смотреть планы запросов и статистику, а не полагаться на интуицию. И еще: любое изменение настроек я проверяю под нагрузкой, близкой к продакшену, иначе легко получить красивые цифры на пустой базе и сюрприз в понедельник утром.
А теперь вопрос к вам, коллеги: какой прием из этого списка дал вам самый заметный прирост, а может, у вас в запасе есть свой восьмой трюк, который я упустил? Делитесь опытом в комментариях — вместе мы точно сделаем еще не одну базу быстрее, чем она была!