Ускорение запросов PostgreSQL в 50 раз для интернет-магазина

Интернет-магазин электроники «МаркетПро» обратился к нам в марте 2026 года с критической проблемой производительности. Их каталог содержал 2.4 миллиона товаров, ежедневно обрабатывалось 180 000 запросов к базе данных PostgreSQL 15, и страницы каталога начали отдаваться за 4-8 секунд вместо допустимых 500 мс.

Симптомы нарастали постепенно: за последние полгода время ответа увеличилось втрое. Команда «МаркетПро» пыталась решить проблему увеличением ресурсов сервера — удвоили RAM до 64 ГБ и перешли на NVMe SSD, но эффект был минимальным.

Мы провели экспресс-диагностику и выявили корневую причину: запросы работали без индексов или использовали неподходящие типы индексов. База данных размером 180 ГБ содержала всего 12 индексов, из которых 4 были неиспользуемыми дубликатами.

Первым шагом мы провели полный аудит индексов. Для этого мы использовали системные представления PostgreSQL и расширение pg_stat_statements:

-- Поиск неиспользуемых индексов (за последние 30 дней)
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan AS scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
  AND idx_scan = 0
  AND pg_relation_size(i.indexrelid) > 1024 * 1024
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- Результат: 4 индекса общим размером 2.3 ГБ, ни разу не использованных

Затем мы нашли самые тяжёлые запросы без индексной поддержки:

-- Топ-10 самых медленных запросов
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Результаты выявили пять критических запросов со средним временем от 800 мс до 6200 мс. Все они выполняли последовательное сканирование (Seq Scan) таблиц с миллионами строк.

B-tree — основной тип индекса в PostgreSQL и самый универсальный. Он оптимален для операций сравнения: =, <, >, BETWEEN, IS NULL. Именно с B-tree мы начали оптимизацию.

Главный проблемный запрос — поиск товаров по категории с сортировкой по цене:

-- Проблемный запрос: 4200 мс
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, price, rating
FROM products
WHERE category_id = 142
  AND is_active = true
ORDER BY price ASC
LIMIT 50;

-- РЕЗУЛЬТАТ ДО ОПТИМИЗАЦИИ:
-- Seq Scan on products (cost=0.00..185432.00 rows=2400000)
--   Filter: ((category_id = 142) AND is_active)
--   Rows Removed by Filter: 2387650
--   Buffers: shared hit=12300 read=95432
-- Planning Time: 0.3 ms
-- Execution Time: 4187.4 ms

Мы создали составной B-tree индекс, учитывающий и фильтрацию, и сортировку:

-- Составной индекс: фильтрация + сортировка
CREATE INDEX CONCURRENTLY idx_products_category_price
    ON products (category_id, price)
    WHERE is_active = true;

-- Проверка после создания:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, price, rating
FROM products
WHERE category_id = 142
  AND is_active = true
ORDER BY price ASC
LIMIT 50;

-- РЕЗУЛЬТАТ ПОСЛЕ ОПТИМИЗАЦИИ:
-- Index Scan using idx_products_category_price on products
--   Index Cond: (category_id = 142)
--   Buffers: shared hit=8
-- Planning Time: 0.2 ms
-- Execution Time: 0.08 ms

Результат: ускорение с 4200 мс до 0.08 мс — в 52 000 раз. Обратите внимание: мы использовали частичный индекс (WHERE is_active = true), что уменьшило его размер на 15% — в индекс попали только активные товары.

Второй по тяжести запрос — поиск товаров по тексту. «МаркетПро» использовали LIKE-запросы, которые не могут использовать обычные B-tree индексы:

-- Проблемный запрос поиска: 6200 мс
SELECT id, name, price
FROM products
WHERE name ILIKE '%ноутбук%'
   OR description ILIKE '%ноутбук%';

-- Seq Scan: полный перебор 2.4 млн строк каждый раз

Мы внедрили полноценный полнотекстовый поиск PostgreSQL с GIN-индексом. GIN (Generalized Inverted Index) — специализированный тип индекса для составных значений, идеальный для полнотекстового поиска, массивов и JSONB:

-- 1. Добавляем колонку для поискового вектора
ALTER TABLE products ADD COLUMN search_vector tsvector;

-- 2. Заполняем вектор
UPDATE products SET search_vector =
    setweight(to_tsvector('russian', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('russian', coalesce(description, '')), 'B');

-- 3. Создаём GIN-индекс
CREATE INDEX CONCURRENTLY idx_products_search
    ON products USING gin(search_vector);

-- 4. Триггер для автоматического обновления
CREATE FUNCTION products_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('russian', coalesce(NEW.name, '')), 'A') ||
        setweight(to_tsvector('russian', coalesce(NEW.description, '')), 'B');
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_search
    BEFORE INSERT OR UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION products_search_trigger();

-- 5. Новый запрос поиска: 12 мс
SELECT id, name, price,
       ts_rank(search_vector, query) AS rank
FROM products, to_tsquery('russian', 'ноутбук') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 50;

Результат: ускорение с 6200 мс до 12 мс — в 516 раз. При этом поиск стал релевантнее за счёт весов (weight A для названия, weight B для описания) и ранжирования результатов.

Таблица заказов (orders) содержала 28 миллионов записей за 5 лет работы магазина. Запросы по дате — отчёты, аналитика, выгрузки — работали медленно, а B-tree индекс на колонке created_at занимал 4.2 ГБ.

Мы применили BRIN-индекс (Block Range Index) — тип индекса, который хранит минимальное и максимальное значение для диапазона блоков. BRIN идеален для данных с высокой корреляцией — когда физический порядок строк совпадает с логическим порядком значений:

-- Проверяем корреляцию: значение близкое к 1.0 — идеально для BRIN
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'created_at';
-- created_at | 0.9987  ← почти идеальная корреляция

-- Удаляем тяжёлый B-tree и создаём компактный BRIN
DROP INDEX idx_orders_created_at;

CREATE INDEX CONCURRENTLY idx_orders_created_at_brin
    ON orders USING brin(created_at)
    WITH (pages_per_range = 32);

-- Сравнение размеров:
-- B-tree: 4.2 ГБ
-- BRIN:   2.8 МБ  ← в 1500 раз меньше!

BRIN-индекс занял всего 2.8 МБ вместо 4.2 ГБ, при этом запросы по диапазону дат выполнялись лишь на 10-15% медленнее, чем с B-tree. Для отчётных запросов, выполняемых раз в день, это абсолютно приемлемый компромисс, а освобождённые 4.2 ГБ RAM пошли на кэширование горячих данных.

Ряд запросов в приложении «МаркетПро» использовал функции в условиях WHERE. Обычные индексы бесполезны для таких запросов — PostgreSQL не может применить индекс по колонке email, если в запросе используется lower(email).

Мы создали функциональные индексы (expression indexes) для наиболее частых паттернов:

-- Поиск по email без учёта регистра
CREATE INDEX CONCURRENTLY idx_users_email_lower
    ON users (lower(email));

-- Теперь этот запрос использует индекс:
SELECT * FROM users WHERE lower(email) = 'client@example.com';

-- Поиск по дате (извлечение месяца из timestamp)
CREATE INDEX CONCURRENTLY idx_orders_month
    ON orders (date_trunc('month', created_at));

-- Эффективная помесячная агрегация:
SELECT date_trunc('month', created_at) AS month, count(*)
FROM orders
GROUP BY 1
ORDER BY 1;

Для таблицы products мы применили частичные индексы — они индексируют только подмножество строк, что экономит место и ускоряет обновления:

-- Индекс только для товаров со скидкой (5% от общего числа)
CREATE INDEX CONCURRENTLY idx_products_discounted
    ON products (discount_percent DESC, price)
    WHERE discount_percent > 0;

-- Размер: 12 МБ вместо 240 МБ (полный индекс)
-- Запрос страницы скидок: 0.3 мс вместо 1800 мс

-- Индекс для товаров, ожидающих модерации
CREATE INDEX CONCURRENTLY idx_products_pending
    ON products (created_at DESC)
    WHERE status = 'pending_review';

-- Размер: 96 КБ (обычно ~200 товаров на модерации)

Все индексы мы создавали с опцией CONCURRENTLY, которая позволяет не блокировать таблицу во время построения индекса. Это критично для продакшн-базы с круглосуточной нагрузкой:

-- Стандартное создание: блокирует INSERT/UPDATE/DELETE
CREATE INDEX idx_name ON table (column);  -- НЕ делайте так в продакшене!

-- Конкурентное создание: без блокировки, но медленнее
CREATE INDEX CONCURRENTLY idx_name ON table (column);

-- Проверка на невалидные индексы (результат прерванного CONCURRENTLY)
SELECT indexrelid::regclass AS index_name,
       indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;

-- Если нашли невалидный индекс — пересоздать:
REINDEX INDEX CONCURRENTLY idx_name;

Мы также настроили регулярное обслуживание индексов, добавив задания в cron:

# Еженедельный REINDEX раздутых индексов (воскресенье, 3:00)
0 3 * * 0 psql -d marketpro -c "
  SELECT 'REINDEX INDEX CONCURRENTLY ' || indexname || ';'
  FROM pg_indexes
  WHERE pg_relation_size(indexname::regclass) >
        pg_relation_size(tablename::regclass) * 0.3
" -t | psql -d marketpro

# Ежедневный VACUUM ANALYZE для актуализации статистики
0 2 * * * vacuumdb --analyze --jobs=4 marketpro

Проект оптимизации занял 2 недели. Мы создали 18 новых индексов, удалили 6 бесполезных и перестроили 3 существующих. Суммарный результат:

Экономический эффект: снижение нагрузки позволило отказаться от планировавшегося апгрейда сервера за 420 000 рублей, а рост конверсии за первый месяц принёс дополнительные 1.2 млн рублей выручки. Подробнее о наших услугах по оптимизации баз данных — на itfresh.ru.