Как мы разгоняли PostgreSQL для каталога шин

В Tirebase каталог шин --- около 250 тысяч позиций. Фильтрация по бренду, сезону, размеру, цене, наличию. Типичный запрос: «Michelin, зимние, R17, до 15000 рублей» --- менеджер на точке ждёт несколько секунд, и это раздражает. Я потратил пару недель на оптимизацию и довёл среднее время ответа до приемлемого уровня. Вот приёмы, которые сработали.

1. Научиться читать EXPLAIN ANALYZE

Прежде чем что-то оптимизировать, нужно понять, что тормозит. EXPLAIN ANALYZE --- рентген для запросов.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM products
WHERE brand = 'Michelin' AND season = 'winter' AND rim_size = 17
  AND price <= 15000 AND in_stock = true
ORDER BY price
LIMIT 20;

Вот что показал первый запуск (вывод сокращён):

Seq Scan on products  (cost=0.00..18742.00 rows=247832 width=312)
                      (actual time=0.031..4832.109 rows=247832 loops=1)
  Filter: ((in_stock = true) AND (brand = 'Michelin'::text) AND ...)
  Rows Removed by Filter: 0
  Buffers: shared read=12847
Planning Time: 0.182 ms
Execution Time: 4847.293 ms

Полный Seq Scan по всей таблице. Ни одного индекса. shared read говорит, что данные читаются с диска, а не из кеша. Отсюда и многосекундное ожидание.

Ключевые метрики в EXPLAIN: Seq Scan vs Index Scan, actual time, rows (ожидаемые vs реальные), Buffers: shared hit/read. Если shared read большой --- данные не в кеше, идут с диска.

2. Правильные B-tree индексы

Первый рефлекс --- CREATE INDEX ON products(brand). Но для запроса с несколькими условиями составной индекс эффективнее:

CREATE INDEX idx_products_filter ON products(brand, season, rim_size, in_stock)
WHERE in_stock = true;

Порядок колонок важен: сначала колонки с высокой селективностью (brand --- сотни значений), потом с низкой (in_stock --- два значения).

После создания индекса EXPLAIN показал:

Index Scan using idx_products_filter on products
    (cost=0.42..892.15 rows=412 width=312)
    (actual time=0.089..18.347 rows=387 loops=1)
  Index Cond: ((brand = 'Michelin'::text) AND (season = 'winter'::text) AND (rim_size = 17))
  Buffers: shared hit=394
Planning Time: 0.241 ms
Execution Time: 18.592 ms

Вместо Seq Scan --- Index Scan, вместо чтения с диска --- shared hit (данные из кеша). Время упало в сотни раз для этого конкретного запроса.

3. Частичные (partial) индексы

Подавляющее большинство запросов --- товары in_stock = true. Зачем индексировать то, что никто не ищет?

-- Индексирует только товары в наличии
CREATE INDEX idx_products_active ON products(brand, season, rim_size, price)
WHERE in_stock = true AND deleted_at IS NULL;

Частичный индекс меньше по размеру --- быстрее обновляется, быстрее сканируется, больше помещается в RAM. Для нашего каталога индекс уменьшился примерно на треть.

4. GIN-индекс для полнотекстового поиска

Пользователи ищут «мишлен зимние шипованные 205/55». Раньше это был LIKE '%мишлен%' --- убийца производительности.

-- Добавляем tsvector-колонку
ALTER TABLE products ADD COLUMN search_vector tsvector;
UPDATE products SET search_vector =
  to_tsvector('russian', coalesce(brand,'') || ' ' || coalesce(model,'') || ' ' || coalesce(description,''));

CREATE INDEX idx_products_search ON products USING GIN(search_vector);

EXPLAIN для полнотекстового поиска:

Bitmap Heap Scan on products  (cost=44.00..3412.72 rows=1200 width=312)
                              (actual time=1.293..12.847 rows=1147 loops=1)
  Recheck Cond: (search_vector @@ '''мишлен'' & ''зимн'' & ''шипован'''::tsquery)
  Heap Blocks: exact=987
  ->  Bitmap Index Scan on idx_products_search  (cost=0.00..43.70 rows=1200 width=0)
        (actual time=0.892..0.893 rows=1147 loops=1)
Buffers: shared hit=1031
Planning Time: 0.384 ms
Execution Time: 13.294 ms

Запрос:

SELECT * FROM products
WHERE search_vector @@ plainto_tsquery('russian', 'мишлен зимние шипованные')
  AND in_stock = true
ORDER BY ts_rank(search_vector, plainto_tsquery('russian', 'мишлен зимние шипованные')) DESC
LIMIT 20;

Вместо секунд с LIKE --- миллисекунды с GIN. Честно говоря, у полнотекстового поиска PostgreSQL есть ограничения с русской морфологией, и для более сложных случаев, возможно, стоит смотреть в сторону отдельного поискового движка. Но для нашего каталога пока хватает.

5. Materialized Views для агрегаций

Страница каталога показывает счётчики: «Michelin (1247), Continental (893), …». Считать COUNT(*) с GROUP BY на ~250К строк при каждом запросе --- не бесплатно.

CREATE MATERIALIZED VIEW brand_counts AS
SELECT brand, season, COUNT(*) as cnt
FROM products
WHERE in_stock = true AND deleted_at IS NULL
GROUP BY brand, season;

CREATE UNIQUE INDEX ON brand_counts(brand, season);

-- Обновление раз в 5 минут через cron
REFRESH MATERIALIZED VIEW CONCURRENTLY brand_counts;

CONCURRENTLY --- обновляет view без блокировки чтения. Пользователи не заметят.

После этого запрос агрегации --- простое чтение из materialized view, пара миллисекунд.

6. Connection Pooling через PgBouncer

Python-приложение на FastAPI, 4 воркера Uvicorn, каждый держит пул из 20 соединений. Итого 80 соединений к PostgreSQL. PostgreSQL тратит ~10 МБ RAM на каждое соединение и начинает тормозить.

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 500
default_pool_size = 25
reserve_pool_size = 5

pool_mode = transaction --- соединение возвращается в пул после каждой транзакции. 500 клиентов используют 25 реальных соединений к PostgreSQL.

На нашей нагрузке PgBouncer заметно помог с p99 latency под нагрузкой.

7. Переписывание запросов

Оригинальный запрос для «похожих товаров»:

-- Было: подзапрос на каждую строку
SELECT *, (SELECT AVG(price) FROM products p2 WHERE p2.brand = p.brand) as avg_brand_price
FROM products p WHERE p.id IN (1,2,3,4,5);

EXPLAIN показывал SubPlan на каждую строку:

->  SubPlan 1
      ->  Aggregate  (cost=4182.00..4182.01 rows=1 width=32)
            (actual time=287.412..287.413 rows=1 loops=5)

5 строк, и на каждую --- полный проход по таблице для подсчёта AVG. Переписал через JOIN:

SELECT p.*, ba.avg_price as avg_brand_price
FROM products p
JOIN (
  SELECT brand, AVG(price) as avg_price
  FROM products GROUP BY brand
) ba ON ba.brand = p.brand
WHERE p.id IN (1,2,3,4,5);

Один проход вместо пяти. Время запроса упало на порядок.

8. VACUUM и autovacuum tuning

Каталог обновляется ежедневно --- импорт новых позиций, удаление устаревших. Dead tuples накапливаются, таблица распухает, индексы деградируют.

-- Проверяем мёртвые строки
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) as dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

У таблицы products бывало до 30-40% мёртвых строк. Настроил autovacuum агрессивнее для этой таблицы:

ALTER TABLE products SET (
  autovacuum_vacuum_scale_factor = 0.05,    -- было 0.2
  autovacuum_analyze_scale_factor = 0.02,   -- было 0.1
  autovacuum_vacuum_cost_delay = 10         -- было 20мс
);

После этого размер таблицы стал стабильнее, и производительность перестала деградировать к концу дня.

9. Партиционирование таблицы заказов

Таблица заказов растёт со временем. Большинство запросов --- за последние пару месяцев. Партиционирование по дате:

CREATE TABLE orders (
  id bigserial,
  created_at timestamptz NOT NULL,
  tenant_id int NOT NULL,
  total numeric
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025_q3 PARTITION OF orders
  FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');
CREATE TABLE orders_2025_q4 PARTITION OF orders
  FOR VALUES FROM ('2025-10-01') TO ('2026-01-01');

PostgreSQL автоматически сканирует только нужную партицию. Запрос за октябрь не трогает данные за прошлые кварталы. Для наших объёмов выигрыш не драматический, но с ростом данных это заложит фундамент.

10. Batch-операции и prepared statements

Импорт каталога через отдельные INSERT --- медленно. Через COPY --- на порядки быстрее:

import io
import csv

def bulk_import(products: list[dict], conn):
    buffer = io.StringIO()
    writer = csv.writer(buffer, delimiter='\t')
    for p in products:
        writer.writerow([p['sku'], p['brand'], p['price'], p['in_stock']])
    buffer.seek(0)

    with conn.cursor() as cur:
        cur.copy_expert(
            "COPY products(sku, brand, price, in_stock) FROM STDIN WITH (FORMAT csv, DELIMITER E'\\t')",
            buffer
        )
    conn.commit()

Для частых однотипных запросов --- prepared statements в asyncpg:

# asyncpg — prepared statements из коробки
stmt = await conn.prepare('SELECT * FROM products WHERE brand = $1 AND season = $2 LIMIT $3')
products = await stmt.fetch('Michelin', 'winter', 20)

Итог

Не все приёмы дали одинаковый эффект. Самые значительные для нашего случая: правильные составные и частичные индексы, GIN для полнотекстового поиска и PgBouncer. Партиционирование и materialized views --- скорее задел на будущее.

Главный каталожный запрос перестал быть проблемой. Не нужен Redis, не нужен Elasticsearch --- достаточно правильно приготовленного PostgreSQL. Начинайте с EXPLAIN ANALYZE, а не с «давайте добавим кеш». Хотя я допускаю, что при дальнейшем росте каталога поисковый движок может понадобиться.