PostgreSQL на малом VDS: базовый tuning, autovacuum и быстрые индексы

Небольшой VDS — это чаще всего 1–4 ГБ ОЗУ, 1–2 vCPU и ограниченный по IOPS диск. PostgreSQL при дефолтной конфигурации на таких ресурсах работает, но не всегда предсказуемо: скачки задержек из‑за чекпоинтов, распухшие таблицы от неотработавшего autovacuum, индексы, которые не помогают, а тормозят. В этой статье — концентрат практических рекомендаций по tuning PostgreSQL на малом VDS: память, WAL и чекпоинты, autovacuum, а также правила быстрых и полезных индексов.

Зачем тюнить PostgreSQL на малом VDS

В ограниченной среде любая ошибка бьёт сильнее: лишние 100–200 МБ на один запрос через work_mem легко приведут к свопу, редкие чекпоинты — к гигантским всплескам записи, а отложенный autovacuum быстро наращивает «мёртвые» строки и делает индексы бесполезно толстыми. Цель настройки — сделать поведение базы стабильным: сгладить ввод/вывод, держать под контролем память и не допускать блоата.

Собираем вводные перед настройкой

Прежде чем крутить параметры, зафиксируйте базовую картину: объём данных, тип нагрузки, характер запросов, количество одновременных соединений и пиковую активность. Если выбираете конфигурацию «под рост», посмотрите гайд по ресурсам как выбрать план VDS.

• Объём базы, размер самых тяжёлых таблиц и индексов.
• Профиль нагрузки: больше чтений или записей? Есть ли массовые вставки/обновления?
• Параллелизм: сколько одновременных соединений и «тяжёлых» запросов.
• Ограничения VDS: ОЗУ, vCPU, диск (IOPS/пропускная способность).

Минимальные запросы для оценки (выполняем под суперпользователем на тестовой или в часы низкой нагрузки):

-- Топ-10 таблиц и индексов по размеру
SELECT relkind, schemaname, relname, pg_size_pretty(total_bytes) AS size
FROM (
  SELECT c.relkind, n.nspname AS schemaname, c.relname,
         pg_total_relation_size(c.oid) AS total_bytes
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind IN ('r','i')
) t
ORDER BY total_bytes DESC LIMIT 10;

-- Число «мёртвых» строк (требует VACUUM)
SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

Память и подключения

В PostgreSQL память расходуется по-разному: часть — глобально (буферы), часть — на соединение и даже на каждый узел плана запроса. На малом VDS разумно ограничить максимальные пики.

shared_buffers

Рекомендованный старт: 20–25% от ОЗУ. Для 1 ГБ — 256 МБ, для 2 ГБ — 512 МБ, для 4 ГБ — 1 ГБ. Слишком большой shared_buffers на маленьких инстансах не даст прироста, а вот конкуренция за память с файловым кэшем ОС может навредить.

effective_cache_size

Это не аллокация, а подсказка планировщику, сколько данных влезает в кэш (ОС + shared_buffers). Ставьте 50–75% ОЗУ (на 2 ГБ — 1–1.5 ГБ).

work_mem

work_mem расходуется на каждый оператор сортировки/хеширования внутри запроса, а не на соединение. Начинайте консервативно: 4–16 МБ. При 50 соединениях и 16 МБ «на узел» можно неожиданно съесть гигабайты. Для тяжёлых отчётов задавайте сессионно: SET work_mem = '64MB'.

maintenance_work_mem

Используется VACUUM/CREATE INDEX/ALTER TABLE. На малом VDS хватит 64–256 МБ. Для CREATE INDEX CONCURRENTLY можно временно поднять.

max_connections

Чем выше, тем больше потенциальная память «на соединение». На небольшом сервере часто достаточно 50–100. Лучше ограничить и использовать пул подключений на уровне приложения или посредника.

Пример базовых настроек памяти

# postgresql.conf (фрагмент)
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 8MB
maintenance_work_mem = 128MB
temp_buffers = 16MB
max_connections = 60
huge_pages = try

Важно: каждое окружение уникально. Значения выше — стартовая точка. Дальше измеряем и корректируем по фактам.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

WAL и чекпоинты: сглаживаем запись

На малом VDS основная цель — избежать «зубцов» I/O во время чекпоинтов и не переполнить диск WAL-журналами.

• checkpoint_timeout: 10–15 минут — типичное безопасное значение.
• checkpoint_completion_target: 0.9 — растягиваем чекпоинт, чтобы запись была плавной.
• max_wal_size/min_wal_size: подбираем под диск. Для малого VDS часто 512 МБ–2 ГБ/128–512 МБ.
• wal_compression: on на системах с обновлениями/вставками уменьшит запись, ценой CPU.
• full_page_writes: оставляйте on ради целостности.
• synchronous_commit: по умолчанию on. Если допустима малая потеря последних миллисекунд данных — local уменьшит задержки записи.

# WAL и чекпоинты
wal_level = replica
max_wal_size = 1GB
min_wal_size = 256MB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = on
full_page_writes = on
synchronous_commit = on

Если вы видите пилообразные задержки записи и рост buffers_checkpoint в статистике, аккуратно увеличьте max_wal_size и проверьте, сгладились ли пики I/O.

Autovacuum без сюрпризов

autovacuum удаляет мёртвые версии строк, обновляет статистику и предотвращает stop-the-world «freeze» из-за достижения возрастных лимитов транзакций. Отключать его нельзя; правильная настройка — обязательна.

Ключевые параметры

• autovacuum: должно быть on.
• autovacuum_naptime: 10–30 секунд, чтобы воркеры чаще проверяли таблицы.
• autovacuum_max_workers: на малом VDS обычно 2–3. Больше — риск I/O-конкуренции.
• autovacuum_vacuum_cost_limit и autovacuum_vacuum_cost_delay: задают «тормоза» для плавной работы. Начните с 1000 и 5–10 мс.
• autovacuum_work_mem: 64–256 МБ, если есть место в памяти.
• autovacuum_vacuum_scale_factor/autovacuum_analyze_scale_factor: глобально можно снизить с дефолтных 0.2 до 0.05–0.1 для активных таблиц.

# Autovacuum (база)
autovacuum = on
autovacuum_naptime = 15s
autovacuum_max_workers = 3
autovacuum_vacuum_cost_limit = 1000
autovacuum_vacuum_cost_delay = 10ms
autovacuum_work_mem = 128MB
# Глобально чуть агрессивнее
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000

Перетюнинг по таблицам

Не все таблицы одинаковы. Для горячих (часто обновляемых) — снизьте порог, чтобы пылесос приходил раньше; для холодных — оставьте дефолт.

-- Пример: активная таблица заказов
ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 50,
  autovacuum_analyze_scale_factor = 0.05
);

-- Для таблицы аудита, растущей по времени, можно увеличить пороги
ALTER TABLE audit.log SET (
  autovacuum_vacuum_scale_factor = 0.2,
  autovacuum_analyze_scale_factor = 0.2
);

Основной симптом проблем с autovacuum — рост n_dead_tup, увеличение размеров индексов и спад эффективности планов. Лечится снижением порогов и/или увеличением воркеров и лимитов стоимости.

Мониторинг autovacuum и блоата

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

-- Кто сейчас пылесосит
SELECT pid, relid::regclass AS table, phase, heap_blks_total, heap_blks_scanned
FROM pg_stat_progress_vacuum;

-- Таблицы с большим числом мёртвых строк
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum, last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Индексы, которые почти не используются
SELECT schemaname, relname AS table, indexrelname AS index,
       idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Если индекс крупный и при этом почти не сканируется, проверьте, не дублирует ли он другой (например, есть и (a), и левый префикс (a,b)), либо не мешает ли планировщику завышенная статистика.

Индексы, которые реально ускоряют

Индекс — это не «ускоритель по умолчанию», а структура данных с ценой сопровождения. На малом VDS плата за лишние индексы выше, поэтому особенно важно создавать их осознанно.

Основы B-Tree

• Идеален для равенств и диапазонов.
• Составные индексы упорядочиваются слева направо: (a,b) покрывает фильтры по a и a,b, но не по одному b.
• Если ORDER BY a,b совпадает с индексом (a,b), возможна сортировка из индекса без дополнительного work_mem.

Covering-индексы и INCLUDE

Чтобы уменьшить чтение таблицы и получить index-only scan, добавляйте неключевые столбцы через INCLUDE. Это увеличит индекс, но снизит обращения к таблице на чтении.

-- Частые фильтр и сортировка по (user_id, created_at), а в выдаче нужен status
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON public.orders (user_id, created_at DESC) INCLUDE (status);

Для реального index-only scan держите таблицу «чистой»: регулярный VACUUM обновляет visibility map, иначе придётся идти в таблицу для проверки видимости строк.

Частичные индексы

Если запросы почти всегда фильтруют активные записи, индексируйте именно их:

CREATE INDEX CONCURRENTLY idx_orders_active
ON public.orders (user_id)
WHERE status = 'active';

Такой индекс меньше и дешевле в сопровождении, чем универсальный по всему набору.

BRIN для больших «приточных» таблиц

Когда данные добавляются по возрастанию времени или идентификатора, а запросы читают свежие диапазоны, BRIN даёт существенную экономию диска и памяти, оставаясь достаточно быстрым.

CREATE INDEX CONCURRENTLY idx_log_brin ON audit.log
USING BRIN (event_time);

Настраивайте pages_per_range под плотность данных. Для очень больших таблиц это критично.

GIN/GiST для JSONB и поиска

Для JSONB и полнотекстового поиска используйте соответствующие типы индексов: GIN отлично подходит для jsonb_path_ops, GiST — для геоданных. Помните, что GIN может требовать заметного maintenance_work_mem при создании.

REINDEX/CLUSTER и когда их применять

Если индекс разросся из‑за блоата, используйте REINDEX CONCURRENTLY, чтобы не блокировать запись. CLUSTER (физическая переупорядоченность таблицы по индексу) даёт выигрыш чтения по диапазону, но требует окна обслуживания и свободного места.

Быстрое и безопасное создание индексов

В продакшене на малом VDS используйте CREATE INDEX CONCURRENTLY: он не блокирует запись, но работает дольше и расходует больше WAL. План действий:

• Временно увеличить maintenance_work_mem и включить wal_compression.
• Запускать в период минимальной нагрузки.
• Следить за свободным местом на разделе с WAL.

SET maintenance_work_mem = '256MB';
CREATE INDEX CONCURRENTLY idx_users_email ON public.users (email);

После успешного создания не забудьте ANALYZE, чтобы планировщик сразу учитывал новый индекс.

Запросы и дисциплина подключений

Даже идеальные параметры не спасут от плохих запросов. Несколько практик, которые особенно важны на малом VDS:

• Не используйте SELECT * в тяжёлых запросах — это ломает index-only scan и раздувает передачу данных.
• Ограничивайте параллелизм дорогих операций (отчёты, миграции) — лучше последовательно, чем одновременно всё.
• Анализируйте планы на стейдже: EXPLAIN (ANALYZE, BUFFERS) покажет, где жжётся память и диск.
• Поддерживайте актуальную статистику: ANALYZE после массовых изменений.

Регулярное обслуживание

На малом VDS предсказуемость важнее пикового throughput. Введите регламент:

• Ежедневно: проверка отставаний autovacuum, размеров WAL, наличия долгоживущих транзакций.
• Еженедельно: VACUUM (VERBOSE, ANALYZE) больших активных таблиц в окно низкой нагрузки, если автопроцессы не успевают.
• Ежемесячно: ревизия неиспользуемых индексов, проверка блоата и при необходимости REINDEX CONCURRENTLY.
• Периодически: контроль параметров — не выросли ли данные так, что пора увеличить max_wal_size или shared_buffers.

Если автоматизируете задачи обслуживания, пригодится расписание через cron или systemd timers — см. практики в статье cron и systemd timers.

Частые ошибки и как их избежать

• Слишком большой work_mem. Ведёт к свопу. Держите консервативно и повышайте точечно на сессию.
• Игнорирование autovacuum. Отключение ради «ускорения» почти всегда оборачивается деградацией и простоями на emergency VACUUM.
• Избыточные индексы. Дубликаты и перекрывающиеся составные индексы раздувают запись и диск.
• Редкие чекпоинты с маленьким max_wal_size. Пики I/O и задержки. Сглаживайте checkpoint_completion_target и держите разумный запас WAL.
• Высокий max_connections без пулера. Излишняя фрагментация памяти и накладные расходы планировщика.

Мини‑план внедрения на выходные

1. Снимите метрики: размеры таблиц/индексов, топ по n_dead_tup, текущий WAL.
2. Примените базовые память/WAL параметры и перезапустите сервис в окно.
3. Настройте autovacuum глобально и для 1–3 самых горячих таблиц — индивидуальные пороги.
4. Постройте 1–2 критичных индекса через CREATE INDEX CONCURRENTLY с увеличенным maintenance_work_mem.
5. Сделайте ANALYZE, проверьте планы и задержки, скорректируйте work_mem при необходимости.
6. Запланируйте еженедельный контроль и возможный REINDEX CONCURRENTLY при блоате.

FAQ коротко

Нужно ли повышать shared_buffers выше 25%? Иногда да, если рабочий набор данных небольшой и у вас есть свободная память. Но прирост не всегда заметен: файловый кэш ОС и так помогает.

Можно ли отключить synchronous_commit? Если вы готовы принять потерю последних миллисекунд транзакций при сбое, режим local уменьшит задержки записи. В противном случае оставьте on.

Когда BRIN лучше B-Tree? При больших таблицах, растущих по времени/ID, с диапазонными запросами. BRIN в разы меньше и достаточно быстрый для таких сценариев.

Почему index-only scan не срабатывает? Видимость строк не подтверждена в карте видимости — нужен регулярный VACUUM. Либо запрос выбирает слишком много столбцов вне INCLUDE.

Чем опасен высокий max_connections? Память разлетается на бэкэнды, а планировщик тратит ресурсы на управление множеством соединений. Лучше держать лимит ниже и контролировать пик нагрузки.

Резюмируя: настройка PostgreSQL на малом VDS — это не «разогнать всё на максимум», а выровнять систему. Небольшие, но осмысленные изменения в shared_buffers, work_mem, WAL и autovacuum дают предсказуемую задержку, уменьшают блоат и стабилизируют индексацию. Если нужен надёжный тариф под PostgreSQL, поднимайте инстанс на нашем VDS.