Оптимизация PostgreSQL для высоконагруженных систем

За время работы техническим директором в финтех-компании мне пришлось решать множество задач, связанных с производительностью. Одной из ключевых стала оптимизация PostgreSQL для обслуживания более 40 000 активных пользователей.

Проблема

Когда я пришёл в проект, система уже работала, но с ростом пользовательской базы начались проблемы:

• Медленные отчёты (некоторые формировались по 30+ секунд)
• Блокировки при массовых операциях
• Высокая нагрузка на CPU сервера БД
• Периодические таймауты при пиковых нагрузках

Анализ и диагностика

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

-- Включаем сбор статистики по запросам
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Находим самые тяжёлые запросы
SELECT
    calls,
    mean_exec_time::numeric(10,2) as avg_ms,
    total_exec_time::numeric(10,2) as total_ms,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Это позволило выявить "узкие места" — запросы, которые съедали больше всего ресурсов.

Решение 1: Правильные индексы

Многие таблицы имели только первичные ключи. Добавил составные индексы для частых выборок:

-- Индекс для поиска активных заявок пользователя
CREATE INDEX CONCURRENTLY idx_loans_user_status
ON loans(user_id, status)
WHERE status IN ('active', 'pending');

-- Частичный индекс для просроченных платежей
CREATE INDEX CONCURRENTLY idx_payments_overdue
ON payments(due_date)
WHERE paid_at IS NULL AND due_date < CURRENT_DATE;

Ключевое слово CONCURRENTLY позволяет создавать индексы без блокировки таблицы.

Решение 2: Партицирование таблиц

Таблица с историей транзакций разрослась до 50+ миллионов записей. Внедрил партицирование по дате:

-- Создаём партицированную таблицу
CREATE TABLE transactions_new (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Создаём партиции по месяцам
CREATE TABLE transactions_2024_01
    PARTITION OF transactions_new
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

Это дало прирост скорости выборок по датам в 10-15 раз.

Решение 3: Материализованные представления

Для тяжёлых аналитических отчётов создал материализованные представления:

CREATE MATERIALIZED VIEW mv_daily_stats AS
SELECT
    DATE(created_at) as date,
    COUNT(*) as total_loans,
    SUM(amount) as total_amount,
    AVG(amount) as avg_amount
FROM loans
GROUP BY DATE(created_at);

-- Обновление по расписанию через pg_cron
SELECT cron.schedule('refresh-stats', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_stats');

Решение 4: Пул соединений

Настроил PgBouncer для управления соединениями:

[databases]
production = host=127.0.0.1 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50

Это снизило нагрузку на PostgreSQL и устранило проблему с исчерпанием соединений.

Результаты

После всех оптимизаций:

• Среднее время ответа API снизилось с 800ms до 120ms
• Отчёты формируются за 1-2 секунды вместо 30
• Сервер БД стабильно держит пиковые нагрузки
• Количество таймаутов снизилось до нуля

Выводы

Оптимизация PostgreSQL — это не разовая задача, а постоянный процесс. Важно:

1. Мониторить — без метрик вы не знаете, что оптимизировать
2. Анализировать EXPLAIN — понимать, как выполняются запросы
3. Тестировать на реальных данных — синтетические тесты могут врать
4. Документировать изменения — чтобы команда понимала, почему так сделано

PostgreSQL — мощная СУБД, которая при правильной настройке легко справляется с высокими нагрузками.