Оптимизация БД: индексы, планировщик и борьба за миллисекунды

Для кого: инженеры и техлиды, у которых БД уже на пределе: CPU 80%, pg_locks красные, а EXPLAIN ANALYZE для вас — страшное заклинание. Если вы думаете, что «индексы сами себя обслужат» — эта глава разрушит иллюзии.

Провокация №1: покажите топ-5 запросов за последние сутки

• Если вы не можете за минуту отдать список запросов по total_exec_time за 24 часа — вы ведёте БД вслепую. Следующий пик может убить систему, и вы не узнаете виновника.
• Минимум: держите под рукой готовый SQL к pg_stat_statements, снимайте его ежедневно и храните историю.

1. Диагностика: что считать «узким местом»

Дисциплина: заведите дашборд («CPU, I/O, Locks, Replication, Pool»). Без телеметрии нет оптимизации.

Экстренные SQL для инцидента:

-- Что сейчас жрёт ресурсы?
SELECT pid, state, now() - query_start AS duration,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;
 
-- Кто кого блокирует?
SELECT blocked.pid  AS blocked_pid,
       blocking.pid AS blocking_pid,
       blocked.query AS blocked_query,
       blocking.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
     ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Провокация №2: смотрите в глаза EXPLAIN?

EXPLAIN (ANALYZE, BUFFERS) — ваш детектор лжи. Если план показывает Seq Scan по миллиону строк, вы обязаны остановить релиз.

2. Индексы и планы

2.1 Основы

• B-Tree: равенство/диапазоны.
• GIN/GIST: полнотекстовый/гео.
• Hash: equality, с осторожностью.
• Composite indexes: порядок важен. idx (a, b) → фильтрация по a, потом b.
• Covering index: включите INCLUDE (columns).

2.2 EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

Красные флаги:

Seq Scan on orders  (cost=0.00..18334.00 rows=1000000)
-- Индекс не используется, таблица читается полностью.
 
Nested Loop  (cost=0.00..100000.00 rows=1)
  -> Seq Scan on users (rows=1000000)
  -> Index Scan on orders
-- Нельзя позволять million-row nested loops на проде.
 
Sort  ...  Buffers: temp read=1024
-- TEMP read: значит, вы стримите данные на диск. Увеличьте work_mem или перепишите запрос.

Смотрите на Actual vs Planned Rows. Если план ожидал 10 строк, а получил 10k — статистика старая, план ошибся.

2.3 Check-list: почему индекс не используется?

1. Статистика устарела — проверьте:

   SELECT relname, last_autoanalyze, last_autovacuum
   FROM pg_stat_user_tables
   WHERE relname = 'orders';

   Если last_autoanalyze старый — запускайте ANALYZE.
2. Типы несовместимы (WHERE user_id = '42' vs INT) — индекс не подходит.
3. Функции в WHERE (lower(email)) — нужен functional index.
4. Низкая избирательность — если 90% строк = TRUE, индекс только мешает.
5. ORDER BY + LIMIT — добавьте индекс (status, created_at).

2.4 Partial / Expression indexes

CREATE INDEX idx_active_orders ON orders(user_id)
WHERE status = 'ACTIVE';
 
CREATE INDEX idx_lower_email ON users((lower(email)));

2.5 Автоматизация

• PostgreSQL: pg_stat_statements, pg_stat_user_indexes.
• MySQL: pt-index-usage, Performance Schema.
• Automated Index Advisors (Aurora, Cloud SQL). Используйте, но проверяйте.

Провокация №3: connection pool не резиновый

• 300 connection'ов из приложения → FATAL: sorry, too many clients already → 5xx на фронте. Если у вас нет пула, это случится.
• Чек:
  • pgbouncer в transaction pooling.
  • Pool size ≈ cores * 2 (максимум < 100). PostgreSQL не любит сотни соединений.
  • Настройте server_idle_timeout, query_timeout — иначе hung запросы съедят весь pool.

3. Connection pooling / распределение нагрузок

• Используйте pgbouncer/pgpool-II, для MySQL — ProxySQL.
• Разделите read/write (master -> replicas).
• Добавьте circuit breakers: если БД перегружена, возвращайте 503.
• Query timeouts: приложению (или в pgbouncer) задайте statement_timeout (например, 30s). Иначе один runaway запрос высосет pool. В postgresql.conf: idle_in_transaction_session_timeout.

-- Жёсткие таймауты
ALTER DATABASE app SET statement_timeout = '30s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '15s';

Провокация №4: вы знаете, сколько стоит один запрос?

• Считайте cost_per_query = DB_bill / queries. Когда новый продукт хочет «ещё один отчёт», спросите: сколько это стоит?
• Если miss_cost кэша меньше, чем повторный запрос, добавляйте кэш. Сравнивайте цифры, а не чувства.

4. Хранилищные оптимизации

• Partitioning: range/list/hash. Убирает «большие таблицы», ускоряет VACUUM.
• VACUUM discipline: Autovacuum + VACUUM (VERBOSE) для проблемных таблиц.
• WAL tuning: wal_compression, synchronous_commit (осторожно), checkpoint_timeout.
• Storage: NVMe, fio для теста, RAID10.
• Index bloat: смотрите pgstattuple, перестраивайте индексы (REINDEX CONCURRENTLY).
• Long transactions: SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'; — убийцы autovacuum.
• Autovacuum tuning: для тяжёлых таблиц увеличьте autovacuum_vacuum_cost_limit, уменьшите autovacuum_naptime, ставьте autovacuum_vacuum_scale_factor = 0.02 вместо дефолтных 0.2.

Провокация №5: БД = SPOF?

• Если master падает, сколько времени бизнес offline? Ответ > 1 мин — плохо.

5. Репликация и отказоустойчивость

• Sync vs Async: sync = данные не теряются, но latency растёт. Async = eventual consistency.
• Failover: patroni, repmgr, managed services (Aurora, Cloud SQL failover).
• Backups: Logical + physical. PITR (point-in-time recovery).

6. Экономика оптимизации БД: индексы стоят $0, но экономят тысячи

Главный инсайт: Индексы — это бесплатная производительность. Один индекс за 10 минут может сэкономить $10k/год на масштабировании БД.

6.1 Стоимость запроса: считаем cost per query

Формула:

Cost per query = (DB_monthly_cost / total_queries_per_month)
 
или более точно:
 
Cost per query = (DB_cost + network_cost + storage_cost) / queries_per_month

Пример (реальный e-commerce):

БД: RDS PostgreSQL db.r5.2xlarge

• Instance: $730/месяц
• Storage (1TB gp3): $100/месяц
• Backups (500GB): $50/месяц
• Data transfer: $20/месяц
• Total: $900/месяц

Нагрузка:

• Queries: 100M/месяц (SELECT/INSERT/UPDATE)
• Cost per query: $900 / 100M = $0.000009 (0.0009 цента)

Кажется дешево? Но:

• Медленный запрос (3 секунды) блокирует connection pool
• 10 таких запросов = вся БД встала
• Нужен апгрейд до db.r5.4xlarge ($1,460/мес)

После добавления индекса:

• Query time: 3s → 50ms (-98%)
• БД остается на db.r5.2xlarge
• Экономия: $1,460 - $730 = $730/месяц = $8,760/год

ROI индекса:

• Стоимость создания: $0 (только время инженера: 10 минут)
• Экономия: $8,760/год
• ROI: бесконечность ♾️

6.2 RDS vs Aurora vs Self-hosted: TCO сравнение

Сценарий: PostgreSQL, 16 vCPU, 128GB RAM, 1TB storage, 50M queries/месяц

Вариант A: Amazon RDS PostgreSQL

Конфигурация:

• Instance: db.r5.4xlarge (16 vCPU, 128GB)
• Storage: 1TB gp3 (3,000 IOPS)
• Multi-AZ: да (для HA)

Стоимость:

• Instance: $1,460/мес
• Multi-AZ: ×2 = $2,920/мес
• Storage: 1TB × $0.10/GB = $100/мес
• IOPS (provisioned): 3,000 × $0.10 = $300/мес (если нужно больше)
• Backups: 1TB × $0.095 = $95/мес
• Total: $3,415/месяц

Плюсы:

• ✅ Managed (патчи, backups, failover)
• ✅ Multi-AZ automatic failover
• ✅ Read replicas легко

Минусы:

• 🔴 Дорого
• 🔴 Vendor lock-in

Вариант B: Amazon Aurora PostgreSQL

Конфигурация:

• Instance: db.r5.4xlarge (16 vCPU, 128GB)
• Storage: оплата за использование (не нужно provisioning)

Стоимость:

• Instance: $1,460/мес
• Storage: 1TB × $0.10/GB = $100/мес
• I/O requests: 50M queries × 2 I/O avg × $0.20/1M = $20/мес
• Backups (автоматически): включены в storage
• Total: $1,580/месяц

Плюсы:

• ✅ Дешевле RDS Multi-AZ (-54%)
• ✅ Автоматическое масштабирование storage
• ✅ Быстрее failover (30 секунд vs 2-5 минут)
• ✅ До 15 read replicas

Минусы:

• 🔴 I/O costs могут вырасти при неоптимизированных запросах
• 🔴 Нет полной совместимости с PostgreSQL extensions

Вариант C: Self-hosted (на EC2)

Конфигурация:

• Primary: r5.4xlarge (16 vCPU, 128GB)
• Standby (для HA): r5.4xlarge
• Storage: 2× 1TB EBS gp3

Стоимость:

• Instances: 2 × $730/мес = $1,460/мес
• Storage: 2 × 1TB × $0.08/GB = $160/мес
• Backups (S3): 1TB × $0.023/GB = $23/мес
• Monitoring (CloudWatch): $20/мес
• Total infrastructure: $1,663/месяц

DevOps costs (скрытые):

• Setup time: 80 часов ($4,000 one-time)
• Maintenance: 10 часов/месяц ($1,000/мес)
• Total with DevOps: $2,663/месяц

Плюсы:

• ✅ Full control
• ✅ Customization

Минусы:

• 🔴 Операционная сложность
• 🔴 Нужен DBA
• 🔴 Failover требует настройки (Patroni, repmgr)

TCO сравнение (3 года)

Вывод:

• Aurora выигрывает для большинства случаев: -54% vs RDS, -43% vs self-hosted
• Self-hosted выгоден только при масштабе 20+ БД или специфичных требованиях

6.3 Индексы как бесплатная производительность

Реальный кейс: SaaS платформа, PostgreSQL db.r5.xlarge ($365/мес)

Проблема:

• Топ-5 запросов = 80% CPU
• P99 latency = 2,800ms
• Нужен апгрейд до db.r5.2xlarge ($730/мес)

Решение: Добавить индексы вместо апгрейда

Анализ:

-- Запрос #1 (25% CPU, 1,200ms avg)
SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 10;
-- Индекс: ОТСУТСТВУЕТ
 
-- Решение:
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders(user_id, created_at DESC);
 
-- Результат:
-- Query time: 1,200ms → 15ms (-99%)
-- CPU usage: -25%

После добавления 3 индексов:

• CPU: 85% → 35%
• P99 latency: 2,800ms → 180ms
• БД: остается на db.r5.xlarge
• Экономия: $730 - $365 = $365/месяц = $4,380/год

Стоимость индексов:

• Создание: 30 минут инженера ($50)
• Поддержка: автоматическая
• Disk overhead: +5% storage (~$5/мес)
• Total cost: $55 one-time + $5/мес

ROI:

First year savings: $4,380 - $55 - ($5 × 12) = $4,265
ROI: ($4,265 / $55) × 100% = 7,754%

6.4 Connection pooling: экономия через эффективность

Проблема: Connection exhaustion

Без pooling:

• PostgreSQL: max_connections = 100
• 5 API серверов × 20 connections каждый = 100 connections
• При добавлении 6го сервера: FATAL: too many clients
• Решение: апгрейд БД до большего instance (больше RAM = больше connections)
• Стоимость: db.r5.xlarge ($365) → db.r5.2xlarge ($730)

С pooling (pgbouncer):

• pgbouncer: transaction pooling mode
• API серверы: 20 connections к pgbouncer
• pgbouncer → PostgreSQL: 20 connections (пул shared)
• Может обслужить 10+ API серверов без апгрейда БД

Экономия:

• Избегаем апгрейд БД: $730 - $365 = $365/месяц
• Стоимость pgbouncer: $0 (на существующих серверах)
• ROI: бесконечность

6.5 Стоимость медленных запросов: hidden costs

Медленный запрос = не только latency, но и деньги.

Пример расчета:

Если connection pool = 100:

• Быстрые запросы: можем обслужить 7.2M запросов/час
• Медленные запросы: только 120k запросов/час

Стоимость:

• Без оптимизации: нужно 60 connections → апгрейд БД ($730/мес)
• С оптимизацией (запрос 50ms): 1 connection → текущая БД ($365/мес)
• Экономия: $365/месяц

6.6 Managed services overhead: сколько стоит удобство?

Сравнение для db.r5.2xlarge (8 vCPU, 64GB, 1TB storage):

Managed services premium: $3,115 - $2,663 = $452/месяц (17%)

За что платим:

• ✅ Автоматические патчи
• ✅ Автоматический failover
• ✅ Простое масштабирование
• ✅ Zero DevOps overhead

Вывод: Managed services стоят +17%, но экономят 20% DevOps времени. Для команд <50 человек — выгодно.

6.7 Реальный кейс: оптимизация вместо масштабирования сэкономила $50k/год

Компания: Fintech стартап, PostgreSQL

Было:

• БД: db.r5.4xlarge Multi-AZ ($2,920/месяц)
• CPU: 75% avg, 95% peak
• Slow queries: топ-10 = 70% CPU
• Plan: апгрейд до db.r5.8xlarge ($5,840/мес)

Проблемы:

• ❌ Нет индексов на 40% таблиц
• ❌ VACUUM не настроен (bloat 30%)
• ❌ Connection pool отсутствует
• ❌ N+1 queries в ORM

Что сделали (2 недели работы, $8k зарплат):

1. Добавили 15 индексов (фокус на топ-10 запросов)

   • Query time топ-5: -85% avg
   • CPU: 75% → 45%

2. Настроили VACUUM и autovacuum

   • Table bloat: 30% → 5%
   • Disk I/O: -20%

3. Внедрили pgbouncer

   • Connections: 95/100 → 30/100
   • Headroom для роста

4. Исправили N+1 queries (10 мест в коде)

   • Queries per request: 50 → 5
   • DB load: -30%

Результат:

• CPU: 75% → 30%
• Не нужен апгрейд → downgrade до db.r5.2xlarge
• Стоимость: $2,920 → $1,460/месяц
• Экономия: $1,460/месяц = $17,520/год

ROI:

• Работа: 2 недели ($8k)
• Экономия год 1: $17,520 - $8,000 = $9,520
• Окупаемость: 6 месяцев
• ROI год 1: 119%

6.8 Cost optimization checklist для БД

• Профилируйте перед масштабированием: pg_stat_statements → топ-10 запросов
• Индексы: Проверьте missing indexes (могут сэкономить тысячи)
• VACUUM: Настройте autovacuum, проверьте bloat
• Connection pooling: pgbouncer может избавить от апгрейда
• N+1 queries: Одна из главных причин перегрузки БД
• Managed vs Self-hosted: При <10 БД managed выгоднее
• RDS vs Aurora: Aurora часто дешевле на 50%
• Reserved Instances: Экономия 40-60% для stable workloads
• Right-sizing: Проверьте CPU/Memory utilization (может быть over-provisioned)
• Storage optimization: gp3 дешевле и быстрее gp2

6.9 Формула: масштабировать или оптимизировать?

def should_scale_or_optimize(db_cpu_avg, db_cost_current, optimization_effort_hours):
    """
    Решает: масштабировать БД или оптимизировать запросы
    """
    # Стоимость апгрейда (обычно удвоение instance)
    db_cost_scaled = db_cost_current * 2
 
    # Стоимость оптимизации
    hourly_rate = 100  # $/час инженера
    optimization_cost = optimization_effort_hours * hourly_rate
 
    # Потенциальная экономия от оптимизации
    # Если CPU > 70%, вероятность что оптимизация снизит его на 30-50%
    if db_cpu_avg > 70:
        potential_savings = db_cost_current  # Избегаем апгрейда
    else:
        potential_savings = 0  # Оптимизация не избавит от масштабирования
 
    # Break-even period (months)
    monthly_savings = db_cost_scaled - db_cost_current
    break_even = optimization_cost / monthly_savings if monthly_savings > 0 else float('inf')
 
    # Decision
    if break_even < 6:  # Окупится за полгода
        return "OPTIMIZE", f"Окупится за {break_even:.1f} месяцев, экономия ${monthly_savings}/мес"
    else:
        return "SCALE", "Оптимизация не окупится, масштабируйте"
 
# Пример
should_scale_or_optimize(
    db_cpu_avg=75,
    db_cost_current=730,  # db.r5.2xlarge
    optimization_effort_hours=80  # 2 недели
)
# Output: ("OPTIMIZE", "Окупится за 1.1 месяцев, экономия $730/мес")

7. Практическая работа

1. Найдите и убейте:
   • SELECT pid, query, now()-query_start FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start LIMIT 5;
   • Завершите самый старый runaway запрос (pg_terminate_backend(pid)), задокументируйте причину.
2. EXPLAIN autopsy:
   • Возьмите самый медленный запрос из логов, сделайте EXPLAIN (ANALYZE, BUFFERS).
   • Опишите «проблема → решение» (индекс, переписанный запрос).
3. Индексный аудит:
   • SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;
   • Удалите 1–2 индекса, которые не использовались месяцами.
4. Pool stress test:
   • Ограничьте pool до 50 соединений, запустите 1000 соединений.
   • Проверьте, что приложение возвращает 503, а не повисает.
5. Failover rehearsal:
   • На staging отключите master (или используйте patroni failover).
   • Замерьте, сколько секунд уходит на переключение.

Что дальше

Далее — очереди и event-driven архитектура. После того как БД перестала быть bottleneck, нужно научиться разгружать её через асинхронность и очереди. Будет так же жёстко: dead-letter, backpressure, гарантии доставки.