Как выстроить процесс доработак для улучшения базы данных?

Как выстроить процесс доработок для улучшения базы данных?

1. Оценка текущего состояния БД

Аудит БД:

-- Размер таблиц
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Неиспользуемые индексы
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan  -- 0 = не используется
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Размер индексов
SELECT 
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

2. Выявление проблем

Медленные запросы:

-- Включаем логирование долгих запросов
SET log_min_duration_statement = 1000;  -- логируем запросы > 1s

-- Анализируем план выполнения
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

Проблемы:

• Seq Scan вместо Index Scan
• Отсутствуют индексы на часто фильтруемых столбцах
• Плохие JOIN'ы
• Отсутствует нормализация (дублирование данных)

Статистика БД:

-- Количество записей в таблицах
SELECT 
    schemaname,
    tablename,
    n_live_tup as row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- Мёртвые строки (требуют VACUUM)
SELECT 
    schemaname,
    tablename,
    n_dead_tup,
    ROUND(100 * n_dead_tup / (n_live_tup + n_dead_tup), 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY dead_ratio DESC;

3. Приоритизация доработок

Матрица Приоритет vs Сложность:

Критерии приоритета:

• Влияние на performance (сейчас упирается?) → высокий
• Влияние на бизнес (теряем деньги?) → высокий
• Частота проблемы (часто или редко?) → влияет

4. Процесс внедрения улучшений

Шаг 1: Планирование

Проблема: Запрос "SELECT * FROM orders" выполняется 5s
Идея: Добавить индекс на (user_id, created_at)
Ожидаемый результат: 5s → 100ms
Сложность: LOW (просто добавить индекс)
Риск: LOW (индекс не сломает приложение)

Шаг 2: Разработка и тестирование

-- В тестовой БД
CREATE INDEX CONCURRENTLY idx_orders_user_created 
ON orders(user_id, created_at DESC);

-- Проверяем план выполнения
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;

-- Проверяем статистику
ANALYZE orders;

Шаг 3: Миграция БД

-- Создаём миграцию (если используем Goose)
-- File: migrations/0099_add_orders_index.sql

-- +goose Up
CREATE INDEX CONCURRENTLY idx_orders_user_created 
ON orders(user_id, created_at DESC);

-- +goose Down
DROP INDEX CONCURRENTLY idx_orders_user_created;

Шаг 4: Раскатка

# На проде во время low-traffic
goose -dir migrations postgres "..." up

# Или без блокировок
CREATE INDEX CONCURRENTLY  -- не блокирует SELECT/INSERT

5. Типичные улучшения

А) Добавление индексов

-- На часто фильтруемые столбцы
CREATE INDEX idx_users_email ON users(email);

-- Комбинированные (для WHERE + ORDER BY)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial (на подмножество)
CREATE INDEX idx_active_users ON users(id) WHERE is_active = TRUE;

Б) Денормализация (когда нужна скорость)

-- Было: 3 JOIN'а для получения order_total
-- Решение: добавить столбец total в orders

ALTER TABLE orders ADD COLUMN total DECIMAL(10,2);

-- Обновляем значения
UPDATE orders o
SET total = (SELECT SUM(amount) FROM order_items WHERE order_id = o.id);

-- Теперь SELECT total FROM orders работает мгновенно

В) Партиционирование больших таблиц

-- Таблица events с миллиардом строк
-- Партиционируем по дате

CREATE TABLE events_2026_q1 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

CREATE TABLE events_2026_q2 PARTITION OF events
  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

-- Запросы по диапазону дат работают быстрее

Г) Архивирование старых данных

-- Перемещаем старые логи в отдельную таблицу
CREATE TABLE logs_archive AS
SELECT * FROM logs WHERE created_at < '2024-01-01';

DELETE FROM logs WHERE created_at < '2024-01-01';
VACUUM ANALYZE logs;

Д) Оптимизация схемы

-- Уменьшаем тип данных (экономим место + быстрее)
ALTER TABLE users 
ALTER COLUMN age TYPE SMALLINT;  -- INT (4 bytes) → SMALLINT (2 bytes)

-- Убираем неиспользуемые столбцы
ALTER TABLE users DROP COLUMN unused_field CASCADE;

6. Мониторинг эффекта

# До vs После
before_time = 5000  # мс
after_time = 100   # мс
improvement = ((before_time - after_time) / before_time) * 100
print(f"Улучшение: {improvement}%")  # 98%

# Проверяем нет ли регрессии
query_latencies = [95, 98, 102, 99, 101]  # мс
avg = sum(query_latencies) / len(query_latencies)
print(f"Средняя задержка: {avg}ms < целевой 100ms ✓")

7. Документирование

## Улучшение #47: Индекс на orders(user_id)

**Проблема:** Запрос SELECT * FROM orders WHERE user_id=123 выполняется 5s

**Решение:** Добавить индекс

**Дата внедрения:** 2026-03-20

**Результат:** 5000ms → 95ms (52x ускорение)

**Миграция:** 0099_add_orders_index.sql

**Статус:** DONE ✓

8. Цикл улучшений

Оценка → Выявление → Приоритизация → Планирование → Разработка → Тестирование → Раскатка → Мониторинг → Документирование → (Повтор)

Заключение

Процесс доработок БД:

1. Аудит текущего состояния
2. Выявление узких мест
3. Приоритизация по impact vs effort
4. Планирование с миграциями
5. Раскатка с мониторингом
6. Документирование результатов