Alex_Wilson
New member
На одном из проектов у нас была таблица событий примерно на 10 млн строк, и отчёты по клиентам, периодам и статусам открывались по 30–40 секунд. Дашборд иногда отваливался по таймауту, аналитики жаловались, а я сначала грешил на нехватку железа. Но после серии замеров стало ясно: проблема не в сервере, а в том, как Postgres читал данные. Я решил действовать не наугад, а через статистику запросов и планы выполнения.
Сначала я включил pg_stat_statements и выбрал шесть самых тяжёлых отчётов. Дальше смотрел EXPLAIN ANALYZE BUFFERS и искал Seq Scan, лишние сортировки и nested loops там, где они не нужны. Первый индекс я добавил только после того, как понял, какой именно фильтр и порядок сортировки использует запрос. Это важный момент: я не стал лепить индексы на все колонки подряд, а менял по одному и сразу замерял эффект.
Первым стал составной индекс по client_id и created_at DESC. Отчёты по конкретному клиенту за период ускорились с 12 секунд до 1,1 секунды. Второй индекс сделал частичным: status и created_at DESC с условием deleted_at IS NULL. Он получился компактнее и отлично подошёл для активных записей. Третий индекс по organization_id, report_date и status закрыл сводные отчёты по организациям и дням.
Четвёртым я добавил BRIN по created_at для архивных отчётов, где данные почти монотонны по времени. Он занял минимум места, но заметно ускорил range scan. Пятый индекс был GIN по jsonb-полю attributes с jsonb_path_ops: фильтры по тегам и кастомным полям до этого шли очень медленно. Шестым стал expression index по lower(email) для отчёта по контактам и поиска дублей. В сумме время выборки упало примерно в 40 раз: с 40 секунд до 1 секунды, а p95 с 28 секунд до 0,8 секунды.
Больше всего выигрыша дали составные индексы под конкретные WHERE и ORDER BY, а также частичные и BRIN для экономии места. Но были и грабли. Отдельный индекс по created_at Postgres почти не использовал из-за низкой селективности. Ещё один индекс ускорил чтение, но заметно замедлил вставку, и его пришлось удалить. Индексы не бесплатны: они занимают место, требуют обслуживания и влияют на запись. Поэтому я всегда проверяю нагрузку на insert и update.
Вот мой короткий чек-лист для похожих задач. Начинайте с pg_stat_statements и EXPLAIN ANALYZE BUFFERS. Индексируйте под запрос, а не под колонку. Проверяйте размер индексов и bloat. Для больших таблиц смотрите в сторону BRIN и частичных индексов. После изменений обновляйте статистику и перепроверяйте план. И не забывайте про VACUUM и autovacuum, иначе даже хорошие индексы могут работать плохо. Схема простая: гипотеза, замер, изменение, повторный замер.
В итоге отчёты перестали быть болью, пользователи снова открывают дашборды без страха, а сервер перестал уходить в пике. Но это не серебряная пуля: если запрос написан плохо, никакие индексы не спасут. Иногда лучше переписать SQL, добавить материализованное представление или партиционирование. У нас 10 млн строк — не предел, и я уже готовлюсь к 50 млн. Главное — не бояться экспериментировать и всё измерять.
А какие индексы в Postgres давали вам самый большой прирост на больших таблицах? Делитесь кейсами: составные, частичные, BRIN, GIN или что-то более экзотическое? Очень интересно сравнить опыт и найти новые рабочие приёмы.
Сначала я включил pg_stat_statements и выбрал шесть самых тяжёлых отчётов. Дальше смотрел EXPLAIN ANALYZE BUFFERS и искал Seq Scan, лишние сортировки и nested loops там, где они не нужны. Первый индекс я добавил только после того, как понял, какой именно фильтр и порядок сортировки использует запрос. Это важный момент: я не стал лепить индексы на все колонки подряд, а менял по одному и сразу замерял эффект.
Первым стал составной индекс по client_id и created_at DESC. Отчёты по конкретному клиенту за период ускорились с 12 секунд до 1,1 секунды. Второй индекс сделал частичным: status и created_at DESC с условием deleted_at IS NULL. Он получился компактнее и отлично подошёл для активных записей. Третий индекс по organization_id, report_date и status закрыл сводные отчёты по организациям и дням.
Четвёртым я добавил BRIN по created_at для архивных отчётов, где данные почти монотонны по времени. Он занял минимум места, но заметно ускорил range scan. Пятый индекс был GIN по jsonb-полю attributes с jsonb_path_ops: фильтры по тегам и кастомным полям до этого шли очень медленно. Шестым стал expression index по lower(email) для отчёта по контактам и поиска дублей. В сумме время выборки упало примерно в 40 раз: с 40 секунд до 1 секунды, а p95 с 28 секунд до 0,8 секунды.
Больше всего выигрыша дали составные индексы под конкретные WHERE и ORDER BY, а также частичные и BRIN для экономии места. Но были и грабли. Отдельный индекс по created_at Postgres почти не использовал из-за низкой селективности. Ещё один индекс ускорил чтение, но заметно замедлил вставку, и его пришлось удалить. Индексы не бесплатны: они занимают место, требуют обслуживания и влияют на запись. Поэтому я всегда проверяю нагрузку на insert и update.
Вот мой короткий чек-лист для похожих задач. Начинайте с pg_stat_statements и EXPLAIN ANALYZE BUFFERS. Индексируйте под запрос, а не под колонку. Проверяйте размер индексов и bloat. Для больших таблиц смотрите в сторону BRIN и частичных индексов. После изменений обновляйте статистику и перепроверяйте план. И не забывайте про VACUUM и autovacuum, иначе даже хорошие индексы могут работать плохо. Схема простая: гипотеза, замер, изменение, повторный замер.
В итоге отчёты перестали быть болью, пользователи снова открывают дашборды без страха, а сервер перестал уходить в пике. Но это не серебряная пуля: если запрос написан плохо, никакие индексы не спасут. Иногда лучше переписать SQL, добавить материализованное представление или партиционирование. У нас 10 млн строк — не предел, и я уже готовлюсь к 50 млн. Главное — не бояться экспериментировать и всё измерять.
А какие индексы в Postgres давали вам самый большой прирост на больших таблицах? Делитесь кейсами: составные, частичные, BRIN, GIN или что-то более экзотическое? Очень интересно сравнить опыт и найти новые рабочие приёмы.