🗄️ Базы данных 📅 04.04.2026 ⏱️ 10 мин чтения ✍️ АйТи Фреш 👁 —

Оптимизация 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% всего времени базы данных:

ЗапросВызовов/часСреднее время% от общего
Поиск товаров с фильтрами45 000380 мс38%
Подсчёт остатков на складах12 000920 мс22%
Формирование ленты товаров85 00085 мс12%

Проблема 1: поиск товаров с фильтрами

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

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 миллионов из них. Индексов подходящих нет.

Решение: составной partial index

-- Partial index только для активных товаров
-- (неактивных 15%, они никогда не запрашиваются)
CREATE INDEX CONCURRENTLY idx_products_active_category_price_rating
ON products (category_id, price, rating DESC)
WHERE is_active = true;

-- Covering index для избежания обращений к таблице (Index Only Scan)
CREATE INDEX CONCURRENTLY idx_products_catalog_covering
ON products (category_id, rating DESC, created_at DESC)
INCLUDE (title, price)
WHERE is_active = true;

После создания индекса тот же запрос:

-- Limit  (cost=2.34..18.56 rows=40 width=128)
--        (actual time=0.089..0.134 rows=40 loops=1)
--   -> Index Only Scan using idx_products_catalog_covering on products p
--           (cost=0.56..1823.44 rows=89684 width=128)
--           (actual time=0.044..0.128 rows=840 loops=1)
--     Index Cond: (category_id = ANY(...) AND rating >= 4.0)
--     Filter: (price >= 1000 AND price <= 5000)
--     Heap Fetches: 0
--     Buffers: shared hit=12

Время упало с 378 мс до 0.13 мс — ускорение почти в 3000 раз. Index Only Scan не обращается к таблице вообще (Heap Fetches: 0), потому что все нужные колонки есть в индексе через INCLUDE.

Проблема 2: подсчёт остатков на складах

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

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;

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

Решение: материализованное представление + триггерная таблица балансов

-- Создаём таблицу текущих балансов
CREATE TABLE stock_balances (
    product_id   bigint NOT NULL,
    warehouse_id integer NOT NULL,
    balance      integer NOT NULL DEFAULT 0,
    updated_at   timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (product_id, warehouse_id)
);

-- Заполняем текущими данными
INSERT INTO stock_balances (product_id, warehouse_id, balance)
SELECT
    product_id,
    warehouse_id,
    SUM(CASE WHEN direction = 'in' THEN quantity ELSE -quantity END)
FROM stock_movements
GROUP BY product_id, warehouse_id;

-- Триггер для автоматического обновления баланса
CREATE OR REPLACE FUNCTION update_stock_balance()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO stock_balances (product_id, warehouse_id, balance, updated_at)
    VALUES (
        NEW.product_id,
        NEW.warehouse_id,
        CASE WHEN NEW.direction = 'in' THEN NEW.quantity ELSE -NEW.quantity END,
        now()
    )
    ON CONFLICT (product_id, warehouse_id) DO UPDATE SET
        balance = stock_balances.balance +
            CASE WHEN NEW.direction = 'in' THEN NEW.quantity ELSE -NEW.quantity END,
        updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_stock_movement_balance
AFTER INSERT ON stock_movements
FOR EACH ROW EXECUTE FUNCTION update_stock_balance();

Теперь запрос баланса:

SELECT product_id, warehouse_id, balance
FROM stock_balances
WHERE product_id = ANY($1);

-- Index Scan using stock_balances_pkey
-- actual time=0.018..0.042 rows=50 loops=1

С 920 мс до 0.04 мс. Вместо сканирования сотен миллионов строк — простой поиск по первичному ключу.

Проблема 3: лента товаров и OFFSET

Лента товаров использовала классический 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. Чем глубже страница — тем медленнее.

Решение: keyset pagination

-- Вместо OFFSET передаём последний увиденный элемент
SELECT id, title, price, image_url
FROM products
WHERE is_active = true
  AND (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 40;

-- Индекс для эффективной работы:
CREATE INDEX CONCURRENTLY idx_products_keyset
ON products (created_at DESC, id DESC)
WHERE is_active = true;

Keyset pagination работает за постоянное время O(1) независимо от номера страницы, потому что PostgreSQL начинает чтение индекса ровно с нужной позиции.

Connection Pooling с PgBouncer

У «ДатаМаркета» 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 миллионов строк за 4 года. Аналитические запросы за текущий месяц затрагивали всю таблицу. Мы внедрили декларативное партицирование по дате:

-- Создаём партицированную таблицу
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 миллионов.

Тюнинг VACUUM

PostgreSQL использует MVCC — при обновлении строки создаётся новая версия, а старая помечается как «мёртвая». 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.

Итоговые результаты

МетрикаДо оптимизацииПослеУлучшение
Поиск товаров (P95)520 мс8 мс65x
Остатки на складах (P95)1100 мс2 мс550x
Лента товаров (P95)240 мс5 мс48x
Отчёт по заказам за месяц12 мин14 сек51x
CPU утилизация БД85-92%15-25%3.5x
Активные подключения к БД48040-608x
Размер данных на диске340 GB210 GB1.6x

Всё это было сделано без изменения версии 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 под вашу нагрузку — дефолты почти никогда не подходят.

Нужна помощь с проектом?

Специалисты АйТи Фреш помогут с архитектурой, DevOps, безопасностью и разработкой — 15+ лет опыта

📞 Связаться с нами
#connection#offset#pgbouncer#pooling#seq scan#vacuum#базы данных#вашего
Комментарии 0

Оставить комментарий

загрузка...