Оптимизация PostgreSQL: как мы ускорили запросы в 50 раз

С чего мы обычно начинаем, когда речь заходит об оптимизации PostgreSQL? Конечно, первым делом включаем pg_stat_statements и смотрим, что он нам покажет. Это расширение — наш главный помощник, оно бережно собирает статистику по каждому запросу, который выполняется: сколько раз его вызывали, сколько суммарно и в среднем времени он «съел», и как сильно нагрузил I/O. Поверьте мне, без этих точных цифр любая оптимизация — это просто гадание на кофейной гуще, и мы такого не любим.

-- Включаем pg_stat_statements (если ещё не включено)
-- postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Топ-20 самых тяжёлых запросов по суммарному времени
SELECT
    round(total_exec_time::numeric, 2) AS total_time_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round((100 * total_exec_time /
        sum(total_exec_time) OVER ())::numeric, 2) AS percent_total,
    left(query, 120) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Результат нас поразил. Три запроса потребляли 72% всего времени базы данных:

А сам запрос выглядел примерно так:

SELECT p.id, p.title, p.price, p.rating, c.name AS category
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.is_active = true
  AND p.category_id IN (142, 143, 144, 145)
  AND p.price BETWEEN 1000 AND 5000
  AND p.rating >= 4.0
ORDER BY p.rating DESC, p.created_at DESC
LIMIT 40 OFFSET 800;

Смотрим план выполнения:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.id, p.title, p.price, p.rating, c.name AS category
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.is_active = true
  AND p.category_id IN (142, 143, 144, 145)
  AND p.price BETWEEN 1000 AND 5000
  AND p.rating >= 4.0
ORDER BY p.rating DESC, p.created_at DESC
LIMIT 40 OFFSET 800;

-- Результат (сокращённо):
-- Limit  (cost=89234.12..89234.22 rows=40 width=128)
--        (actual time=378.442..378.451 rows=40 loops=1)
--   -> Sort  (cost=89232.12..89456.33 rows=89684 width=128)
--           (actual time=375.221..378.102 rows=840 loops=1)
--     Sort Key: p.rating DESC, p.created_at DESC
--     Sort Method: top-N heapsort  Memory: 312kB
--       -> Seq Scan on products p  (cost=0.00..72145.00 rows=89684 width=128)
--               (actual time=0.023..298.445 rows=87234 loops=1)
--             Filter: (is_active AND category_id = ANY(...) AND ...)
--             Rows Removed by Filter: 7912766
--             Buffers: shared hit=12045 read=43210

Проблема очевидна: Seq Scan по 8 миллионам строк с фильтрацией 7.9 миллионов из них. Индексов подходящих нет.

Таблица stock_movements распухла до 450 миллионов строк — это каждое поступление и списание товара за четыре года. И баланс по ней считался на лету, прямо в момент запроса:

SELECT
    product_id,
    warehouse_id,
    SUM(CASE WHEN direction = 'in' THEN quantity ELSE -quantity END) AS balance
FROM stock_movements
WHERE product_id = ANY($1)  -- массив из 50 товаров
GROUP BY product_id, warehouse_id;

Запрос сканировал миллионы строк для подсчёта баланса.

Лента товаров крутилась на классической пагинации через OFFSET:

SELECT id, title, price, image_url
FROM products
WHERE is_active = true
ORDER BY created_at DESC
LIMIT 40 OFFSET 40000;  -- страница 1000

PostgreSQL вынужден пропустить 40 000 строк, прежде чем вернуть 40. Чем глубже страница — тем медленнее.

Картина у «ДатаМаркета» была такая: работало 24 экземпляра приложения, и каждый из них держал до 20 активных подключений к PostgreSQL. В итоге мы насчитывали 480 коннектов, хотя реально активными из них были в среднем всего 30-50. Главная проблема заключалась в том, что каждое подключение в PostgreSQL — это отдельный процесс, и он, хотите верьте, хотите нет, съедает 5-10 MB RAM просто по факту своего существования.

# /etc/pgbouncer/pgbouncer.ini
[databases]
datamarket = host=127.0.0.1 port=5432 dbname=datamarket

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Transaction pooling — подключение возвращается в пул
# после завершения транзакции, а не сессии
pool_mode = transaction

# Максимум реальных подключений к PostgreSQL
default_pool_size = 40
max_client_conn = 600
max_db_connections = 60

# Таймауты
server_idle_timeout = 300
client_idle_timeout = 600
query_timeout = 30

# Резерв для административных запросов
reserve_pool_size = 5
reserve_pool_timeout = 3

Результат: количество реальных подключений к PostgreSQL сократилось с 480 до 40-60, потребление RAM базой снизилось на 2.5 GB, а среднее время установления подключения для приложения упало с 45 мс до 0.3 мс (подключение уже готово в пуле).

Таблица orders к тому моменту весила 180 миллионов строк за четыре года. А аналитика за текущий месяц всё равно перелопачивала её целиком. Лечили декларативным партицированием по дате:

-- Создаём партицированную таблицу
CREATE TABLE orders_partitioned (
    id          bigserial,
    customer_id bigint NOT NULL,
    total       numeric(12,2) NOT NULL,
    status      varchar(20) NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Создаём партиции помесячно
CREATE TABLE orders_y2025m01 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_y2025m02 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- ... и так далее

-- Автоматическое создание партиций через pg_partman
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table := 'public.orders_partitioned',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3  -- создавать партиции на 3 месяца вперёд
);

Миграция данных из старой таблицы в новую заняла 6 часов (вне пиковой нагрузки). Зато теперь запрос за текущий месяц сканирует только одну партицию с 3 миллионами строк вместо всех 180 миллионов.

Тут стоит вспомнить, как устроен MVCC. При обновлении строки PostgreSQL не меняет её на месте — создаёт новую версию, а старую помечает «мёртвой». Подчищает этот мусор VACUUM. Загвоздка в том, что дефолтные настройки оказались слишком вялыми для нагрузки «ДатаМаркета»:

# postgresql.conf — было:
# autovacuum_vacuum_cost_delay = 20ms  (слишком медленно)
# autovacuum_vacuum_cost_limit = 200

# Стало:
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 800
autovacuum_max_workers = 4

# Для таблицы orders — агрессивный автовакуум
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_threshold = 500,
    autovacuum_analyze_scale_factor = 0.005
);

После тюнинга bloat таблицы orders снизился с 35% до 3%, а размер на диске уменьшился с 89 GB до 58 GB.

И что самое приятное, мы добились всего этого без апгрейда версии — PostgreSQL так и остался 15.4. Нам не понадобилось ни новое железо, ни, что очень важно, мы не допустили ни секунды даунтайма. Индексы мы строили умным способом, через CONCURRENTLY, партицирование выкатывали аккуратной миграцией через промежуточную таблицу, а PgBouncer подключали вообще параллельно уже работающим живым соединениям, так что никого не пришлось даже на минуту выключать!

• Включите pg_stat_statements и анализируйте топ-20 запросов еженедельно.
• Всегда используйте EXPLAIN (ANALYZE, BUFFERS) — без ANALYZE план может врать.
• Partial indexes экономят место и ускоряют запросы, если запрашивается подмножество данных.
• Covering indexes (INCLUDE) позволяют избежать обращения к таблице.
• Замените OFFSET на keyset pagination на всех публичных страницах.
• PgBouncer в режиме transaction pooling — обязателен при более чем 50 подключениях.
• Партицируйте таблицы, которые растут более чем на 10 миллионов строк в год.
• Настройте autovacuum под вашу нагрузку — дефолты почти никогда не подходят.