Оптимизация медленных SQL-запросов PostgreSQL

Оптимизация медленных SQL-запросов PostgreSQL

Вы ждёте 20 секунд на загрузку отчёта. Пользователи уходят, база данных — узкое горло. Типичная ситуация: один запрос к PostgreSQL выполняется 5 секунд, а таких запросов сотни в минуту. Мы разбираемся, почему это происходит, и устраняем проблему: снижаем время ответа БД в 3–5 раз на нагрузке. Диагностика занимает день, оптимизация — ещё пару дней. Все изменения документируются, замеры до и после обязательны.

Медленные запросы — основная причина плохого UX. 95% проблем производительности БД решаются одним из четырёх методов: добавлением индекса, переписыванием запроса, денормализацией или кешированием. Опыт показывает, что грамотная оптимизация 10–15 самых тяжёлых запросов может высвободить до 40% ресурсов сервера. Разберём, как диагностировать и исправлять медленные запросы в PostgreSQL.

Как диагностировать медленные запросы?

pg_stat_statements — первое расширение, которое нужно включить на продакшне. Оно собирает статистику по каждому запросу: суммарное и среднее время, количество вызовов, стандартное отклонение. Коэффициент вариации (coeff_var) помогает выявить запросы с нестабильным планом.

Процесс диагностики в пять шагов:

1. Включите расширение pg_stat_statements (если отключено) и соберите статистику за несколько часов.
2. Выполните запрос топ-20 по total_exec_time.
3. Для каждого подозрительного запроса получите план через EXPLAIN (ANALYZE, BUFFERS).
4. Определите узлы плана: Seq Scan, Nested Loop, Hash Join с Batches > 1.
5. Примените соответствующую оптимизацию: добавьте индекс, перепишите запрос, настройте work_mem.

-- Включение pg_stat_statements
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- Топ-20 по суммарному времени
SELECT round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       calls,
       round((stddev_exec_time / mean_exec_time * 100)::numeric, 1) AS coeff_var_pct,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

coeff_var_pct — коэффициент вариации: высокий процент говорит о нестабильном плане (разные параметры дают кардинально разное время). Далее каждый подозрительный запрос прогоняем через EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, c.name AS category_name
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.status = 'published'
  AND p.created_at > NOW() - INTERVAL '30 days'
ORDER BY p.created_at DESC
LIMIT 50;

Ключевые узлы в плане:

• Seq Scan на большой таблице — нет индекса или планировщик решил, что индекс не выгоден.
• Nested Loop с большим количеством итераций — N+1 на уровне SQL.
• Hash Join с Batches > 1 — не хватает work_mem.
• Sort без Index Scan на ORDER BY колонке — нет подходящего индекса.

Почему индексы не используются?

Даже при наличии индекса планировщик может его проигнорировать. Основные причины:

• Функция на колонке в WHERE (например, DATE(created_at))
• Низкая селективность (индекс на булевой колонке со смещённым распределением)
• Сортировка, не совпадающая с порядком индекса

Решение: переписываем запрос, чтобы убрать обёртки функций, и создаём составные индексы под конкретные паттерны. Порядок колонок в составном индексе: сначала условия равенства, затем диапазонные и сортировка.

Какие антипаттерны встречаются чаще всего?

-- Плохо: SELECT * тянет лишние колонки; OFFSET увеличивает нагрузку; OR не использует индекс; функция на колонке; NOT IN с NULL
SELECT * FROM products WHERE category_id = 5 LIMIT 50 OFFSET 10000;
SELECT * FROM users WHERE email = $1 OR phone = $1;
SELECT * FROM orders WHERE DATE(created_at) = $1;
SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items);

-- Хорошо: только нужные поля; keyset pagination; UNION ALL; range condition; NOT EXISTS
SELECT id, title, slug FROM products WHERE (created_at, id) > ($1, $2) ORDER BY created_at DESC LIMIT 50;
SELECT * FROM users WHERE email = $1 UNION ALL SELECT * FROM users WHERE phone = $1 LIMIT 1;
SELECT * FROM orders WHERE created_at >= $1 AND created_at < $2;
SELECT p.* FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

Сравним методы пагинации:

Оптимизация JOIN: составные индексы

-- Добавляем составной индекс для типичного фильтра
CREATE INDEX idx_orders_user_status_created
    ON orders (user_id, status, created_at DESC);

-- Запрос использует index scan без Sort
SELECT id, total, status, created_at
FROM orders
WHERE user_id = $1
  AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;

Порядок колонок в индексе: сначала equality conditions (user_id = $1, status = 'completed'), затем range/sort (created_at DESC).

Настройка work_mem и LATERAL

Если в EXPLAIN ANALYZE видим external merge (Disk: ...) при Sort — увеличиваем work_mem для сессии:

SET work_mem = '64MB';
-- Выполняем тяжёлый аналитический запрос

В postgresql.conf лучше оставить work_mem низким (4-8MB по умолчанию) и поднимать для конкретных запросов через SET LOCAL work_mem.

-- LATERAL: для row-dependent subqueries
SELECT u.id, u.email, recent.total
FROM users u
CROSS JOIN LATERAL (
    SELECT SUM(total) AS total
    FROM orders o
    WHERE o.user_id = u.id
      AND o.created_at > NOW() - INTERVAL '30 days'
) AS recent;

LATERAL часто даёт лучший план, чем JOIN на агрегированный CTE.

Объём работ по оптимизации

• Аудит 10–15 самых тяжёлых запросов через pg_stat_statements и EXPLAIN ANALYZE
• Переписывание запросов с устранением антипаттернов
• Добавление и настройка индексов (включая составные и частичные)
• Настройка параметров PostgreSQL (shared_buffers, work_mem, effective_cache_size)
• Предоставление отчёта с замерами до/после и рекомендациями для команды
• Дополнительно: обучение разработчиков работе с планами запросов

В одном из проектов мы сократили время выполнения запросов с 4 секунд до 200 мс — это снизило нагрузку на сервер и позволило избежать upgrade инфраструктуры. Оптимизация 15 медленных запросов может высвободить значительную часть ресурсов сервера.

Сроки и стоимость

Диагностика и оптимизация 10–15 медленных запросов — 2–3 дня. Глубокий аудит схемы и запросов для высоконагруженного приложения — 3–5 дней. Стоимость рассчитывается индивидуально после оценки объёма.

Для старта проекта свяжитесь с нами в Telegram или по почте — мы проведём бесплатный анализ первых двух запросов. Закажите диагностику — и получите отчёт с замерами до/после. Мы гарантируем измеримое снижение времени запросов не менее чем на 30% — результат фиксируем до и после оптимизации.

Услуги бэкенд-разработки: Laravel, Node.js, Go, Django, PostgreSQL

На production-сервере в 3:14 ночи очередь Laravel Jobs перестала обрабатываться. 40 000 необработанных задач в Redis. Причина: worker упал из-за memory leak в одном из Jobs (утечка через статическую переменную в Eloquent observer), supervisor не перезапустил его из-за misconfigured stopwaitsecs. Это не гипотетический сценарий — это вторник. Мы разбирали такой инцидент на проекте с нагрузкой 500 RPS: диагностика заняла 4 часа, фикс — 20 минут. Чтобы вы не теряли деньги на простоях, предлагаем услуги бэкенд-разработки с акцентом на production-grade надёжность. Оценим ваш проект за 2 дня.

Backend — это то, что работает когда никто не смотрит. Или не работает. Гарантируем, что у вас будет первый вариант.

Что мы делаем с первого дня правильно

Service Layer поверх Fat Controllers. Controller получает HTTP-запрос, валидирует его через Form Request, передаёт данные в Service, возвращает ответ. Бизнес-логика в Service, не в Controller. Это звучит банально, но большинство legacy-проектов — это контроллеры по 500 строк с SQL-запросами внутри.

Repository Pattern используем осторожно. Если вы просто оборачиваете Model::where(...) в метод репозитория — это бойлерплейт без пользы. Repository оправдан когда: нужно абстрагироваться от источника данных (БД + кеш + внешний API) или когда логика запросов достаточно сложна для изоляции.

Jobs, Events, Listeners. Всё, что можно сделать асинхронно — делаем асинхронно. Отправка email, генерация PDF, синхронизация с внешним API, пересчёт агрегатов — в Queue. Laravel Horizon для мониторинга очередей в Redis: видно throughput, failed jobs, время обработки по очередям.

Как Octane справляется с высокой нагрузкой

Laravel Octane с RoadRunner или Swoole держит приложение в памяти между запросами — убирает overhead bootstrap (загрузка конфигов, автозагрузка классов) на каждый HTTP-запрос. Прирост: 3–8x на синтетических бенчмарках, 2–4x на реальных приложениях. Важно: нельзя хранить состояние между запросами в статических переменных — это приводит именно к таким инцидентам, как в начале. Применяем это в проектах с >1000 RPS.

Что делать с N+1 запросами

N+1 — самая распространённая причина медленных страниц в Laravel-приложениях. Стандартная история: страница работала нормально на dev с 10 записями, на production с 10 000 — 8-секундная загрузка.

Laravel Debugbar в dev-окружении показывает количество запросов на страницу. Более 20 запросов на одну страницу — сигнал для audit.

Model::preventLazyLoading(! app()->isProduction());

Telescope для профилирования в staging: логирует все запросы, jobs, mail, notifications с детализацией по времени. Цифры: после внедрения eager loading время загрузки страницы падает с 8 с до 0.3 с — в 27 раз.

PostgreSQL: индексы, которые реально нужны

PostgreSQL 14+ — основная БД на всех проектах. Используем связку PgBouncer + PostgreSQL. Опыт 10+ лет, более 50 backend-проектов, 5 лет на рынке.

Как PostgreSQL помогает избежать медленных запросов

Composite indexes для частых WHERE + ORDER BY. Если у вас WHERE user_id = ? AND status = ? ORDER BY created_at DESC — нужен (user_id, status, created_at DESC). Индекс по (user_id) отдельно плохо помогает с сортировкой.

Partial indexes. Если 95% запросов идут по WHERE status = 'active':

CREATE INDEX idx_orders_active ON orders (created_at DESC)
WHERE status = 'active';

Индекс маленький, быстрый, покрывает основную нагрузку.

GIN-индексы для JSONB и массивов. @> оператор без GIN-индекса — seq scan. С индексом — быстро даже на миллионах записей.

GIN для full-text search. to_tsvector + GIN вместо LIKE '%query%'. LIKE без индекса — всегда seq scan. С pg_trgm extension и gin_trgm_ops — поддержка LIKE с индексом, полезно для CRM-поиска по частичному совпадению.

Connection pooling: почему важнее чем кажется

Rails, Laravel, Django открывают новое соединение с PostgreSQL на каждый PHP/Python процесс. На 100 воркерах — 100 соединений. PostgreSQL начинает деградировать от 200–300 активных соединений — overhead на управление соединениями становится значительным.

PgBouncer — connection pooler перед PostgreSQL. Режим transaction pooling: соединение с PostgreSQL занято только на время транзакции, между запросами возвращается в пул. 1000 приложений-воркеров → 20–50 реальных соединений к PostgreSQL. Это снижает latency на 40% и уменьшает затраты на хостинг на 30%.

Node.js с Fastify: когда это лучше Laravel

Node.js оправдан для:

• Realtime: WebSocket-серверы, Server-Sent Events, чат, live-обновления
• Streaming: большие файлы, видео, данные потоком
• High I/O concurrency: много параллельных запросов к внешним API без тяжёлой бизнес-логики
• Serverless: Lambda/Cloud Functions — Node.js стартует быстрее PHP

Fastify вместо Express: в 2–3 раза быстрее на benchmarks, встроенная JSON Schema валидация, лучшая TypeScript поддержка, plugin-архитектура.

Типичная архитектура realtime: Laravel — основная бизнес-логика и REST API. Node.js + Socket.io или ws — WebSocket сервер. Laravel публикует события в Redis Pub/Sub, Node.js подписывается и транслирует клиентам. Это разделение позволяет масштабировать WebSocket-сервер независимо от основного приложения.

Go: микросервисы и высокая нагрузка

Go используем для:

• Высоконагруженных микросервисов (> 10 000 RPS)
• Фоновых воркеров с жёсткими требованиями к latency
• Инструментов DevOps и CLI
• gRPC-сервисов в микросервисной архитектуре

Goroutines — дешевле OS-потоков в тысячи раз. 10 000 конкурентных соединений на Go — норма на одном сервере.

Но Go — не волшебная таблетка. Разработка медленнее чем на Laravel: больше бойлерплейта, нет ORM уровня Eloquent, обработка ошибок через if err != nil везде. Оправдан только когда производительность — реальное требование, не предположение.

Django и Python backend

Django с DRF (Django REST Framework) — для задач где нужен Python: ML-пайплайны, обработка данных, интеграции с AI-инструментами.

Celery для фоновых задач — аналог Laravel Queue, но сложнее в конфигурации. Celery Beat для cron-задач.

Django ORM vs raw SQL: ORM удобен для CRUD. Для аналитических запросов с несколькими JOIN, оконными функциями и CTE — connection.execute() с raw SQL читаемее и предсказуемее.

Redis: не только кеш

Redis в наших проектах выполняет несколько ролей:

Redis Cluster для горизонтального масштабирования. Sentinel для автоматического failover на standalone установках.

Деплой и инфраструктура

Docker + docker-compose — стандарт для локальной разработки и production. Каждый сервис в контейнере: PHP-FPM/Octane, Nginx, PostgreSQL, Redis, Queue Worker, Scheduler.

CI/CD через GitHub Actions:

1. Прогон тестов (PHPUnit / Pest, Vitest, Playwright)
2. Сборка Docker-образа
3. Push в Container Registry
4. Deploy: docker pull → docker-compose up -d на сервере, или Kubernetes rolling update

Zero-downtime deploy для Laravel: php artisan down --secret=TOKEN не нужен при правильной настройке. Стратегия: новый контейнер стартует рядом со старым, Nginx переключает трафик после health check, старый контейнер останавливается.

Мониторинг: Sentry для exception tracking с alerting в Slack/Telegram. Grafana + Prometheus (или Grafana Cloud) для метрик: CPU, memory, request rate, queue depth, database connection count. Алерт на: error rate > 1%, p99 latency > 2s, queue depth > 1000 jobs.

Что входит в работу под ключ

• Архитектурное проектирование (документация API, схема БД, диаграмма сервисов)
• Реализация по согласованному ТЗ с code review
• Настройка CI/CD, мониторинга, алертинга
• Нагрузочное тестирование (k6, wrk) с отчётом
• Передача исходников, доступов, инструкция по деплою
• Обучение команды заказчика (2-3 сессии)
• Гарантийная поддержка 1 месяц после сдачи

Ориентиры по срокам

Стоимость рассчитывается индивидуально после анализа требований к нагрузке, интеграциям и бизнес-логике. Типичный бюджет backend-проекта — от 500 000 до 2 000 000 рублей в зависимости от сложности. Свяжитесь с нами для бесплатного аудита вашего текущего backend — получите план оптимизации за 2 дня. Закажите консультацию.