Поделиться
Поделиться

PostgreSQL умеет многое из коробки, но «работает» и «работает быстро» — разные вещи. Разработчики нередко обнаруживают проблемы с производительностью уже под нагрузкой, когда база выросла до десятков миллионов строк, а запросы, которые раньше отвечали за 10 мс, теперь блокируют интерфейс на 3 секунды.

В этой статье разберём полный цикл оптимизации: от чтения плана запроса до горизонтального масштабирования.

EXPLAIN ANALYZE: читаем план запроса

Первый шаг при любой проблеме с производительностью — посмотреть, что делает планировщик.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.created_at, u.email, SUM(oi.price * oi.qty) AS total
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
  AND o.created_at >= '2026-01-01'
GROUP BY o.id, o.created_at, u.email
ORDER BY o.created_at DESC
LIMIT 50;

Что искать в плане

| Узел | Красный флаг | Что делать | |---|---|---| | Seq Scan на большой таблице | rows > 10k | Добавить индекс | | Hash Join с большим rows | Batches > 1 | Увеличить work_mem | | Sort без индекса | cost высокий | Индекс по ORDER BY колонке | | Nested Loop + Seq Scan | loops * rows велик | Индекс на join-колонке | | Rows estimation off > 10x | actual rows >> rows= | Обновить статистику: ANALYZE |

-- Полезный шаблон: находим самые медленные запросы за последние 24 часа
SELECT query,
       calls,
       round(total_exec_time::numeric / calls, 2) AS avg_ms,
       round(total_exec_time::numeric, 2) AS total_ms,
       rows / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 10
ORDER BY avg_ms DESC
LIMIT 20;

Расширение pg_stat_statements нужно включить в postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Типы индексов и когда их применять

B-tree — индекс по умолчанию

Подходит для операций =, <, >, BETWEEN, LIKE 'prefix%'.

-- Составной индекс: порядок колонок важен
-- Правило: equality-колонки первыми, range — последней
CREATE INDEX CONCURRENTLY idx_orders_status_created
    ON orders (status, created_at DESC)
    WHERE status IN ('completed', 'refunded'); -- partial index

CONCURRENTLY — создаёт индекс без блокировки таблицы. В production используйте всегда.

GIN — для массивов, JSONB и полнотекстового поиска

-- Поиск по тегам (массив)
CREATE INDEX idx_products_tags ON products USING GIN (tags);
SELECT * FROM products WHERE tags @> ARRAY['sale', 'electronics'];

-- Поиск по JSONB
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);
SELECT * FROM events WHERE payload @? '$.user.country == "RU"';

-- Полнотекстовый поиск
CREATE INDEX idx_articles_fts ON articles
    USING GIN (to_tsvector('russian', title || ' ' || body));
SELECT * FROM articles
WHERE to_tsvector('russian', title || ' ' || body) @@ plainto_tsquery('russian', 'оптимизация база');

GiST — для геопространственных данных и диапазонов

-- PostGIS: поиск точек в радиусе
CREATE INDEX idx_stores_location ON stores USING GIST (location);
SELECT name FROM stores
WHERE ST_DWithin(location, ST_MakePoint(37.6, 55.75)::geography, 5000);

-- Диапазоны дат (tsrange, daterange)
CREATE INDEX idx_bookings_period ON bookings USING GIST (period);
SELECT * FROM bookings WHERE period && '[2026-08-01, 2026-08-31]'::daterange;

BRIN — для временны́х рядов и монотонных данных

BRIN-индекс занимает в 1000 раз меньше места, чем B-tree, и идеален для таблиц с естественной физической сортировкой (логи, события).

CREATE INDEX idx_logs_created_brin ON logs USING BRIN (created_at)
    WITH (pages_per_range = 128);

Партиционирование таблиц

Когда таблица превышает 50–100 млн строк, даже хорошие индексы перестают помогать. Партиционирование позволяет Postgres читать только нужные секции.

-- Декларативное партиционирование по диапазону дат
CREATE TABLE events (
    id          bigserial,
    user_id     bigint NOT NULL,
    type        text NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Создаём партиции (удобнее автоматизировать через pg_partman)
CREATE TABLE events_2026_08 PARTITION OF events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- Индекс создаётся на каждой партиции отдельно
CREATE INDEX ON events_2026_08 (user_id, created_at DESC);
CREATE INDEX ON events_2026_09 (user_id, created_at DESC);

Важно: партиционирование замедляет INSERT на 5–15% и усложняет схему. Применяйте только когда это реально нужно.

PgBouncer: пул соединений

PostgreSQL создаёт отдельный процесс на каждое соединение (~5 МБ RAM). При 500+ одновременных подключениях от Node.js/Python-сервисов база начинает задыхаться. PgBouncer решает это, мультиплексируя соединения.

# pgbouncer.ini
[databases]
myapp = host=postgres-primary port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction          # оптимально для большинства приложений
max_client_conn = 1000           # максимум соединений от приложений
default_pool_size = 20           # реальных соединений к Postgres
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# Аутентификация
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

listen_addr = 0.0.0.0
listen_port = 5432

Caveat: в режиме transaction нельзя использовать PREPARE/EXECUTE, SET и LISTEN. Если нужны временные таблицы — переключайтесь на session режим или используйте отдельный пул.

Реплики чтения

Для read-heavy нагрузки (дашборды, отчёты, аналитика) выносим SELECT на реплику.

# Django пример: router для реплик
class PrimaryReplicaRouter:
    REPLICA_DB = "replica"
    
    def db_for_read(self, model, **hints):
        return self.REPLICA_DB
    
    def db_for_write(self, model, **hints):
        return "default"
    
    def allow_relation(self, obj1, obj2, **hints):
        return True
    
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        return db == "default"
# docker-compose для тестирования репликации локально
services:
  postgres-primary:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: secret
      POSTGRES_REPLICATION_USER: replicator
      POSTGRES_REPLICATION_PASSWORD: repl_secret
    command: >
      postgres
      -c wal_level=replica
      -c max_wal_senders=3
      -c max_replication_slots=3

  postgres-replica:
    image: postgres:16
    environment:
      PGUSER: replicator
      PGPASSWORD: repl_secret
    command: >
      bash -c "
        pg_basebackup -h postgres-primary -D /var/lib/postgresql/data -U replicator -P -Xs -R &&
        postgres
      "
    depends_on:
      - postgres-primary

Настройка autovacuum

Autovacuum предотвращает bloat таблиц и поддерживает актуальность статистики. Дефолтные параметры слишком консервативны для высоконагруженных таблиц.

-- Более агрессивный autovacuum для активных таблиц
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- вакуум при 1% dead tuples (вместо 20%)
    autovacuum_analyze_scale_factor = 0.005, -- analyze при 0.5%
    autovacuum_vacuum_cost_delay = 2         -- меньше пауз между страницами
);

-- Мониторинг: таблицы с наибольшим bloat
SELECT relname,
       n_dead_tup,
       n_live_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

Checklist: быстрая диагностика

Когда что-то стало медленно работать:

  1. pg_stat_statements — топ медленных запросов
  2. EXPLAIN (ANALYZE, BUFFERS) — смотрим план проблемного запроса
  3. Есть ли Seq Scan на большой таблице? → нужен индекс
  4. Корректна ли оценка rows планировщиком? → запустить ANALYZE table
  5. Высокая нагрузка на диск? → проверить autovacuum, bloat
  6. Много соединений? → PgBouncer
  7. Рост таблицы без партиций? → партиционирование

Подробнее про инфраструктурные паттерны в production читайте в Redis: паттерны кеширования, а про Docker-окружение для баз данных — в Docker Compose для разработчика.