Настройка PostgreSQL

Мало просто поставить PostgreSQL, ну для localhost, конечно достаточно. Но что бы базы работали как положено и как можно быстрее я сделал заметку по его настройке.

1. Настройка параметров под процессор и оперативную память

Под количество ядер CPU

• 
  

  max_worker_processes: Устанавливает общее число фоновых процессов. Рекомендуется значение, равное количеству ядер CPU (например, max_worker_processes = 8 для 8-ядерного процессора).

  
  


• 
  

  max_parallel_workers_per_gather: Определяет максимальное число параллельных процессов для одного запроса. Оптимально — 4–8 (зависит от нагрузки).

  
  


• 
  

  max_parallel_workers: Общее число параллельных процессов. Не должно превышать max_worker_processes.

  
  

Пример для 16-ядер CPU:

max_worker_processes = 16

max_parallel_workers_per_gather = 4

max_parallel_workers = 16

Под объем оперативной памяти

• 
  

  shared_buffers: Выделяет память под кэш данных. Для серверов с 16+ ГБ ОЗУ — 25% памяти, но не более 8–12 ГБ.

  
  


• 
  

  work_mem: Память для сортировки и хеширования. Рассчитывается как:
  
  work_mem = (Total RAM × 0.25) / max_connections
  
  Например, для 32 ГБ ОЗУ и 100 соединений: work_mem = 80MB.

  
  


• 
  

  maintenance_work_mem: Память для обслуживания (VACUUM, CREATE INDEX). Рекомендуется 1–2 ГБ.

  
  


• 
  

  effective_cache_size: Оценка доступной памяти для кэша ОС. Обычно 50–75% от ОЗУ.

  
  

Пример для сервера с 64 ГБ ОЗУ:

shared_buffers = 16GB

work_mem = 256MB

maintenance_work_mem = 2GB

effective_cache_size = 48GB

2. Временные таблицы: хранение в RAM-диске

Временные таблицы и сортировки по умолчанию используют диск, что замедляет операции. Решение — перенаправить их в RAM.

Создание RAM-диска (Linux)

mkdir /mnt/postgresql_tmpfs

mount -t tmpfs -o size=4G tmpfs /mnt/postgresql_tmpfs

Добавьте запись в /etc/fstab для автоматического монтирования:

tmpfs /mnt/postgresql_tmpfs tmpfs defaults,size=4G 0 0

Настройка PostgreSQL

Измените postgresql.conf:

temp_tablespaces = 'ram_disk'

Создайте tablespace:

CREATE TABLESPACE ram_disk LOCATION '/mnt/postgresql_tmpfs';

Теперь временные объекты будут использовать RAM-диск.

3. Создание пользователя и базы данных

Шаг 1: Создание пользователя

CREATE USER app_user WITH PASSWORD 'secure_password';

Шаг 2: Создание базы

CREATE DATABASE app_db     WITH OWNER = app_user     ENCODING = 'UTF8'     CONNECTION LIMIT = 100;

Шаг 3: Выдача прав

GRANT CONNECT ON DATABASE app_db TO app_user;GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

4. План обслуживания базы данных

Autovacuum

Автоочистка критична для производительности. Настройки в postgresql.conf:

autovacuum = on
autovacuum_vacuum_scale_factor = 0.1  -- Запуск при 10% изменений
autovacuum_analyze_scale_factor = 0.05
autovacuum_max_workers = 4            -- Для высоконагруженных систем

Ручное обслуживание

• 
  

  Анализ статистики:

  
  

  
  ANALYZE;

  
  
  
  


• 
  

  Очистка мусора:

  
  

  
  VACUUM FULL VERBOSE;

  
  
  
  

Резервное копирование

Используйте pg_dump для дампов:

pg_dump -U postgres -F c -f backup.dump app_db

Или настройте непрерывное архивирование через pg_basebackup и WAL-файлы.

Планировщик задач (cron)

Пример задания для ежедневного бэкапа:

0 2 * * * pg_dump -U postgres app_db > /backups/app_db_$(date +\%F).sql

Заключение

Правильная настройка PostgreSQL — баланс между ресурсами железа и нагрузкой. Все изменения в конфигурации тестируйте на staging-среде. Используйте мониторинговые инструменты (pg_stat_activity, pgBadger) для анализа производительности. Не забывайте о регулярных бэкапах и актуальной документации!

Оптимизируйте с умом, и ваша PostgreSQL будет работать как часы.