DevOpsMar 28, 2026

Deep EVM #27: Производительность PostgreSQL — индексы, VACUUM и оптимизация запросов

Engineering Team

PostgreSQL под нагрузкой: что ломается первым

PostgreSQL — это надёжная, зрелая СУБД, способная обрабатывать тысячи транзакций в секунду. Но без правильной настройки производительность деградирует по мере роста данных. В блокчейн-инфраструктуре, где таблицы растут на миллионы строк в день, правильная настройка — это разница между системой, отвечающей за 5мс, и системой, отвечающей за 5 секунд.

Типы индексов и когда их использовать

B-tree (по умолчанию)

Универсальный индекс для операторов =, <, >, BETWEEN, IN, LIKE 'prefix%':

CREATE INDEX idx_tx_hash ON transactions (tx_hash);
CREATE INDEX idx_tx_block_time ON transactions (block_number, created_at DESC);

GIN (Generalized Inverted Index)

Для массивов, JSONB и полнотекстового поиска:

-- JSONB
CREATE INDEX idx_events_data ON events USING GIN (data jsonb_path_ops);

-- Полнотекстовый поиск
CREATE INDEX idx_articles_fts ON articles
USING GIN (to_tsvector('russian', title || ' ' || content_md));

-- Массивы
CREATE INDEX idx_tags ON articles USING GIN (tags);

GiST (Generalized Search Tree)

Для геоданных, диапазонов и полнотекстового поиска:

-- IP-диапазоны
CREATE INDEX idx_ip_ranges ON access_log USING GIST (ip_range);

-- Диапазоны блоков
CREATE INDEX idx_block_ranges ON sync_jobs
USING GIST (int8range(start_block, end_block));

BRIN (Block Range Index)

Для колоночно-коррелированных данных (монотонно растущие значения):

-- Для таблицы, где строки вставляются хронологически
CREATE INDEX idx_tx_created_brin ON transactions
USING BRIN (created_at) WITH (pages_per_range = 32);

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

Partial и Expression индексы

-- Индексировать только актуальные записи
CREATE INDEX idx_pending_tx ON transactions (created_at)
WHERE status = 'pending';

-- Индекс на выражении
CREATE INDEX idx_tx_lower_hash ON transactions (LOWER(tx_hash));

VACUUM: жизненный цикл строк в PostgreSQL

PostgreSQL использует MVCC (Multi-Version Concurrency Control). Каждый UPDATE и DELETE создаёт новую версию строки, а старая помечается как мёртвая. VACUUM удаляет мёртвые строки и освобождает место.

Настройка autovacuum

ALTER TABLE transactions SET (
    autovacuum_vacuum_threshold = 10000,
    autovacuum_vacuum_scale_factor = 0.01,  -- 1% вместо 20% по умолчанию
    autovacuum_analyze_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 2  -- мс, агрессивнее
);

Для таблиц с высокой частотой обновлений (> 1000 UPDATE/s) стандартные настройки autovacuum слишком консервативны.

Мониторинг мёртвых строк

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Если dead_pct превышает 10-20%, autovacuum не справляется.

Оптимизация запросов

EXPLAIN ANALYZE — ваш главный инструмент

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT a.*, c.name as category_name
FROM articles a
JOIN categories c ON a.category_id = c.id
WHERE a.published = true AND a.locale = 'ru'
ORDER BY a.published_at DESC
LIMIT 20;

На что обращать внимание:

Seq Scan на большой таблице — нужен индекс
Nested Loop с большим outer — рассмотрите Hash Join
Sort с external merge Disk — не хватает work_mem
Buffers: shared hit vs shared read — кэш-хит рейт

Типичные оптимизации

-- ПЛОХО: SELECT *
SELECT * FROM articles WHERE category_id = $1;

-- ХОРОШО: только нужные колонки
SELECT id, title, slug, excerpt, published_at
FROM articles WHERE category_id = $1;

-- ПЛОХО: OFFSET для пагинации
SELECT * FROM articles ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- ХОРОШО: keyset пагинация
SELECT * FROM articles
WHERE created_at < $1
ORDER BY created_at DESC
LIMIT 20;

OFFSET сканирует и отбрасывает строки. Keyset pagination использует индекс.

Настройка PostgreSQL для производительности

Ключевые параметры:

# Память
shared_buffers = 4GB            # 25% от RAM
effective_cache_size = 12GB     # 75% от RAM
work_mem = 64MB                 # Для сортировок и хешей
maintenance_work_mem = 1GB      # Для VACUUM, CREATE INDEX

# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Планировщик
random_page_cost = 1.1          # Для SSD (по умолчанию 4.0)
effective_io_concurrency = 200  # Для SSD

# Параллелизм
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

Мониторинг в реальном времени

pg_stat_statements

SELECT
    LEFT(query, 80) AS query,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS mean_ms,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Активные запросы

SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    state,
    LEFT(query, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Блокировки

SELECT
    blocked.pid AS blocked_pid,
    blocking.pid AS blocking_pid,
    blocked.query AS blocked_query
FROM pg_locks blocked
JOIN pg_stat_activity blocked_act ON blocked.pid = blocked_act.pid
JOIN pg_locks blocking ON blocked.locktype = blocking.locktype
JOIN pg_stat_activity blocking_act ON blocking.pid = blocking_act.pid
WHERE NOT blocked.granted;

Connection pooling с PgBouncer

PostgreSQL создаёт отдельный процесс для каждого соединения (~5MB RAM). Для 500 соединений — 2.5GB только на соединения. PgBouncer решает эту проблему:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction    # Переиспользовать после транзакции
max_client_conn = 1000     # Макс. клиентских соединений
default_pool_size = 50     # Размер пула к PostgreSQL

В режиме transaction 1000 клиентов мультиплексируются через 50 реальных соединений.

Заключение

Производительность PostgreSQL — это не одна настройка, а комбинация правильных индексов, настроенного VACUUM, оптимизированных запросов и мониторинга. Начните с pg_stat_statements для поиска проблемных запросов, используйте EXPLAIN ANALYZE для диагностики, и помните: 90% проблем производительности решаются добавлением правильного индекса или устранением N+1 запросов.

Теги

DevOps Performance PostgreSQL