PostgreSQL на VPS: от 10 до 1000 запросов в секунду без смены железа
Полгода назад мы оптимизировали PostgreSQL для одного проекта — небольшого маркетплейса с ~50 000 товаров. API отвечал в среднем за 400-800 мс, база данных съедала всё время. VPS: 4 ядра, 8 ГБ RAM, SSD. После двух дней работы — 40-80 мс, тот же сервер.
Это руководство — конкретные шаги которые мы применили, с объяснением почему каждый из них работает.
Шаг 1: postgresql.conf — параметры которые реально важны
Дефолтная конфигурация PostgreSQL рассчитана на работу на машине с 256 МБ RAM. Это не шутка — они сохранили консервативные дефолты для совместимости. На вашем VPS с 8 ГБ дефолты — это в ~10 раз меньше возможного.
shared_buffers
Буфер памяти для кэширования страниц данных (8 KB каждая). PostgreSQL читает с диска в этот буфер, следующие запросы к тем же данным идут из RAM.
# Правило: 25% от RAM, но не более 8 ГБ
# Для 8 ГБ RAM:
shared_buffers = 2GB
Дефолт: 128 МБ. Это разница между "читаем с SSD каждый запрос" и "кэшируем горячие данные в RAM".
work_mem
Память для операций сортировки и хэш-таблиц в запросах. Если запрос требует больше — PostgreSQL сбрасывает на диск (temp files, log_temp_files поможет обнаружить).
# Осторожно: умножается на количество одновременных операций сортировки
# При 100 соединениях и запросе с 3 sorts: 100 * 3 * work_mem
# Правило для 8 ГБ: (RAM - shared_buffers) / max_connections / 2
# (8192 - 2048) МБ / 100 соединений / 2 = ~30 МБ
work_mem = 32MB
Дефолт: 4 МБ. Именно из-за него ORDER BY на больших наборах данных уходил на диск и тормозил.
effective_cache_size
Не выделяет память. Это подсказка планировщику о том, сколько кэша доступно (shared_buffers + OS page cache). Влияет на выбор между index scan и seq scan.
# Правило: 75% от RAM
effective_cache_size = 6GB
checkpoint_completion_target и wal_buffers
# WAL buffer — буфер для журнала транзакций
wal_buffers = 64MB # дефолт: -1 (автоматически ~3% shared_buffers), явно задаём
# Растянуть checkpoint на 90% интервала — меньше пиковая нагрузка на I/O
checkpoint_completion_target = 0.9 # дефолт: 0.5
# Интервал между checkpoint'ами
checkpoint_timeout = 15min # дефолт: 5min — слишком часто для SSD
Полный блок настроек для VPS 4CPU/8GB
# /etc/postgresql/16/main/postgresql.conf
# Память
shared_buffers = 2GB
work_mem = 32MB
maintenance_work_mem = 512MB
effective_cache_size = 6GB
# WAL и checkpoint
wal_buffers = 64MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
max_wal_size = 2GB
min_wal_size = 512MB
# Параллельные запросы
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2
# Соединения
max_connections = 100 # Будем использовать PgBouncer
# Планировщик
random_page_cost = 1.1 # SSD (дефолт 4.0 для HDD)
effective_io_concurrency = 200 # SSD
# Логирование медленных запросов
log_min_duration_statement = 100 # логируем запросы > 100 мс
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
# pg_stat_statements (включить в shared_preload_libraries)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
После изменений: sudo systemctl reload postgresql. Некоторые параметры требуют полного рестарта (max_connections, shared_buffers) — проверьте pg_settings.context.
Шаг 2: Индексы — четыре типа для четырёх разных задач
Partial Index: индексируем только нужные строки
-- Таблица заказов с 2 миллионами записей
-- 95% заказов имеют status = 'completed', нас интересуют только активные
-- ❌ Обычный индекс: индексирует ВСЕ 2 миллиона строк
CREATE INDEX idx_orders_status ON orders(status);
-- ✅ Partial index: индексирует только ~100k активных заказов
CREATE INDEX idx_orders_active ON orders(created_at)
WHERE status IN ('pending', 'processing');
-- Размер: 8 МБ вместо 45 МБ
-- Запрос находит активные заказы мгновенно:
SELECT * FROM orders
WHERE status = 'processing' AND created_at > NOW() - INTERVAL '7 days';
Composite Index: порядок полей критически важен
-- Запрос: фильтр по user_id + сортировка по created_at
SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20;
-- ❌ Не поможет: индекс создан в обратном порядке
CREATE INDEX idx_orders_wrong ON orders(created_at, user_id);
-- Postgres будет читать все записи пользователя и сортировать
-- ✅ Правильно: высококардинальное поле (user_id) идёт первым
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Index scan: сразу переходит к нужному user_id, данные уже отсортированы
Правило: поля с фильтром = идут первыми, поля с ORDER BY или BETWEEN — последними.
Covering Index (INCLUDE): index-only scan без heap
-- Запрос в листинге: нужны только id, title, price, status
SELECT id, title, price, status FROM products WHERE category_id = $1 AND active = true;
-- ❌ Обычный индекс: находит строки, идёт в heap за остальными полями
CREATE INDEX idx_products_cat ON products(category_id) WHERE active = true;
-- ✅ Covering index: все нужные данные в индексе, heap не нужен
CREATE INDEX idx_products_cat_covering ON products(category_id)
INCLUDE (title, price, status)
WHERE active = true;
-- EXPLAIN ANALYZE покажет: "Index Only Scan" вместо "Index Scan"
-- На таблице 500k строк: 23 мс → 1.2 мс
INCLUDE поля не используются для фильтрации, но хранятся в leaf-страницах индекса — запрос может не ходить в основную таблицу вообще.
Expression Index: индекс по функции
-- Поиск по email без учёта регистра
-- ❌ Full seq scan, индекс по email не используется
SELECT * FROM users WHERE LOWER(email) = LOWER($1);
-- ✅ Expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Теперь этот запрос использует индекс:
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Полнотекстовый поиск по заголовку
CREATE INDEX idx_products_title_fts ON products USING GIN(to_tsvector('russian', title));
SELECT * FROM products WHERE to_tsvector('russian', title) @@ to_tsquery('russian', 'ноутбук');
Шаг 3: VACUUM и autovacuum — почему таблица растёт при DELETE
PostgreSQL использует MVCC (Multiversion Concurrency Control): UPDATE и DELETE не удаляют строки физически, а помечают старые версии как "мёртвые" (dead tuples). Это нужно для транзакционности, но создаёт "bloat" — таблица растёт даже при DELETE.
-- Смотрим bloat по таблицам
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
Если dead_ratio > 20% — autovacuum не справляется. Нужно агрессивнее настроить:
# postgresql.conf — агрессивный autovacuum для высоконагруженных таблиц
autovacuum_vacuum_scale_factor = 0.05 # вакуум при 5% dead tuples (дефолт 20%)
autovacuum_analyze_scale_factor = 0.02 # analyze при 2% изменений (дефолт 10%)
autovacuum_vacuum_cost_delay = 2ms # меньше пауза = быстрее вакуум (дефолт 2ms в PG14+)
autovacuum_max_workers = 4 # больше воркеров (дефолт 3)
Для конкретных таблиц с высоким write throughput — переопределяем параметры на уровне таблицы:
-- Таблица событий с миллионами INSERT/DELETE в день
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 1000,
autovacuum_analyze_scale_factor = 0.005
);
-- VACUUM FULL пересобирает таблицу на диске (блокирует таблицу!)
-- Используйте pg_repack для zero-downtime
-- apt install postgresql-16-repack
-- pg_repack -t events mydb
Шаг 4: PgBouncer — почему 1000 прямых соединений убивают базу
PostgreSQL создаёт отдельный процесс на каждое соединение (~5-10 МБ RAM каждый). При 1000 соединениях — 5-10 ГБ только на процессы, не считая буферов. Плюс накладные расходы планировщика ОС на переключение между 1000 процессами.
Реальный кейс: Node.js приложение с пулом 20 соединений × 10 реплик = 200 соединений. Нормально. Добавили ещё 5 микросервисов × 20 соединений = +100. Добавили cron jobs, фоновые воркеры... и вот уже 500+ соединений и PostgreSQL начинает "проседать" не из-за нагрузки запросов, а из-за overhead самих соединений.
# Установка PgBouncer
sudo apt install pgbouncer
# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp_prod
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 5432 ; PgBouncer слушает на 5432, Postgres переведём на 5433
; Transaction pooling: одно соединение с БД обслуживает множество клиентов
; Важно: SET, PREPARE, LISTEN не работают в transaction mode
pool_mode = transaction
max_client_conn = 1000 ; максимум клиентских соединений
default_pool_size = 25 ; реальных соединений к PostgreSQL
; При пиковой нагрузке — временно расширяем пул
reserve_pool_size = 5
reserve_pool_timeout = 3
; Таймауты
server_idle_timeout = 600
client_idle_timeout = 0
server_connect_timeout = 15
query_timeout = 0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Логирование
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
# /etc/pgbouncer/userlist.txt
# Пароль: md5 + hash(password + username)
# Генерация: echo -n "passwordusername" | md5sum
"myapp" "md5ХЭШПАРОЛЯ"
# Меняем порт PostgreSQL на 5433
# /etc/postgresql/16/main/postgresql.conf
port = 5433
sudo systemctl restart postgresql
sudo systemctl start pgbouncer
# Приложение подключается к PgBouncer на порту 5432
# PgBouncer держит 25 реальных соединений к Postgres:5433
Результат: 1000 клиентских соединений → 25 реальных. PostgreSQL перестал тратить ресурсы на управление соединениями.
Шаг 5: pg_stat_statements — находим медленные запросы
-- Включено через shared_preload_libraries = 'pg_stat_statements'
-- Создаём расширение (один раз):
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Топ-10 самых медленных запросов по суммарному времени
SELECT
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
calls,
round(rows::numeric / calls, 1) AS rows_per_call,
left(query, 120) AS query_snippet
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 10;
-- Запросы с самым большим разбросом времени (нестабильные)
SELECT
round(stddev_exec_time::numeric, 2) AS stddev_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
calls,
left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY stddev_exec_time DESC
LIMIT 10;
-- Сбросить статистику после оптимизации для чистых измерений
SELECT pg_stat_statements_reset();
Используйте EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) для детального анализа конкретного запроса. BUFFERS показывает сколько данных пришло из shared_buffers (cache hit) и сколько с диска (cache miss) — ключевая метрика эффективности кэша.
Итоговые результаты
На том маркетплейсе о котором мы говорили в начале:
| Метрика | До | После |
|---|---|---|
| Среднее время API запроса | 400-800 мс | 40-80 мс |
| Cache hit ratio (pg_stat_bgwriter) | 67% | 94% |
| Самый медленный запрос листинга | 230 мс | 8 мс |
| Dead tuples в таблице products | 40% | < 5% |
| Количество реальных соединений к БД | 180 | 25 |
Всё это — без изменения тарифа VPS. Правильная конфигурация PostgreSQL и индексы дают больше чем переход с 8 ГБ на 16 ГБ RAM с дефолтными настройками.
Если у вас есть проект на PostgreSQL который работает медленно и вы хотите разобраться в чём дело — команда Aunimeda проведёт аудит производительности базы данных. Работаем с проектами по всему Кыргызстану. Написать в WhatsApp или оставить заявку.