PostgreSQL: Best Practices для production

Введение

PostgreSQL — одна из самых мощных и надёжных СУБД с открытым исходным кодом. Для достижения максимальной производительности в production требуется правильная настройка.

1. Настройка памяти

-- postgresql.conf

-- Общий буферный кэш (25% от RAM)
shared_buffers = 4GB

-- Память для сортировки
work_mem = 256MB

-- Память для обслуживания
maintenance_work_mem = 1GB

-- Эффективный размер кэша (75% от RAM)
effective_cache_size = 12GB

-- WAL буферы
wal_buffers = 64MB

2. Оптимизация индексов

-- Составной индекс
CREATE INDEX idx_orders_user_status 
ON orders (user_id, status) 
WHERE status != 'cancelled';

-- Частичный индекс
CREATE INDEX idx_users_active 
ON users (email) 
WHERE is_active = true;

-- Полнотекстовый поиск
CREATE INDEX idx_products_search 
ON products 
USING GIN (to_tsvector('russian', name || ' ' || description));

-- JSONB индекс
CREATE INDEX idx_settings_data 
ON user_settings 
USING GIN (data jsonb_path_ops);

-- Проверка использования
SELECT indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

3. Анализ запросов

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT 
  o.id, o.total, u.name,
  array_agg(p.name) as products
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at > NOW() - INTERVAL '30 days'
  AND o.status = 'completed'
GROUP BY o.id, u.name
ORDER BY o.total DESC
LIMIT 100;

4. Партиционирование

-- Партиционированная таблица
CREATE TABLE events (
  id BIGSERIAL,
  event_type VARCHAR(50),
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Партиции по месяцам
CREATE TABLE events_2024_01 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

5. Мониторинг

-- Включение статистики
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Топ медленных запросов
SELECT 
  round(total_exec_time::numeric, 2) as total_time_ms,
  calls,
  round(mean_exec_time::numeric, 2) as avg_time_ms,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Размер таблиц
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

6. Обслуживание

-- Настройка autovacuum
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05

-- Ручной VACUUM
VACUUM (VERBOSE, ANALYZE) orders;

-- Перестроение индексов
REINDEX INDEX CONCURRENTLY idx_orders_user_status;

Чек-лист для production

1. ✅ Настроена память (shared_buffers, work_mem)
2. ✅ Созданы необходимые индексы
3. ✅ Настроен autovacuum
4. ✅ Включён мониторинг (pg_stat_statements)
5. ✅ Настроена репликация
6. ✅ Настроены бэкапы
7. ✅ Настроен connection pooling (PgBouncer)

Регулярно анализируйте медленные запросы и обновляйте статистику. PostgreSQL становится лучше, когда вы понимаете, как он работает.

8. Connection Pooling с PgBouncer

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5

9. Бэкапы и восстановление

# Полный бэкап
pg_dump -Fc -f backup.dump mydb

# Бэкап только схемы
pg_dump -s -f schema.sql mydb

# Бэкап конкретных таблиц
pg_dump -t users -t orders -Fc -f partial.dump mydb

# Восстановление
pg_restore -d mydb backup.dump

# Автоматический бэкап (cron)
0 2 * * * pg_dump -Fc mydb > /backups/mydb_$(date +\%Y\%m\%d).dump

10. Полезные расширения

-- UUID генерация
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

-- Полнотекстовый поиск на русском
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_name_trgm ON products USING GIN (name gin_trgm_ops);

-- Криптография
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT crypt('password', gen_salt('bf'));

-- Статистика запросов
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Управление соединениями

Пул соединений критически важен для производительности. PgBouncer управляет пулом и снижает нагрузку на сервер.

Транзакции и блокировки

Транзакции обеспечивают ACID-гарантии. Блокировки предотвращают конфликты при параллельном доступе. Используйте короткие транзакции для минимизации блокировок.

Мониторинг производительности через pg_stat_statements показывает медленные запросы и помогает оптимизировать базу данных.