Как рассчитать потребление трафика сервером PostgreSQL при интенсивной записи и как настроить лимиты в pg_hba.conf?

## Расчёт потребления трафика PostgreSQL при интенсивной записи

При интенсивной записи сервер PostgreSQL генерирует трафик нескольких типов: клиент-серверный (между приложением и СУБД), репликационный (между мастером и репликами) и служебный (WAL-архивирование).

### 1. Клиент-серверный трафик

Каждый INSERT/UPDATE/DELETE передаёт:
— SQL-запрос от клиента к серверу
— Ответ сервера (статус, количество затронутых строк)

Примерная формула:

Трафик (байт/с) = RPS × (avg_query_size + avg_response_size)

Где RPS — запросов в секунду, avg_query_size — средний размер SQL-запроса (~200–500 байт), avg_response_size — ответ (~50–100 байт для INSERT).

Пример: 1000 RPS × 600 байт = 600 КБ/с ≈ 4,8 Мбит/с.

### 2. Репликационный трафик (WAL)

WAL-трафик зависит от объёма изменяемых данных:

WAL-трафик ≈ объём изменённых данных × коэффициент_WAL

Коэффициент обычно составляет 1,5–3× от объёма «сырых» данных. Мониторинг через:
sql
SELECT pg_current_wal_lsn();
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), ‘0/0’);

### 3. Инструменты мониторинга трафика

— **pg_stat_activity** — активные соединения и запросы
— **pg_stat_bgwriter** — статистика записи буферов
— **pgBadger** — анализ логов
— **Prometheus + postgres_exporter** — метрики в реальном времени
— **iftop / nethogs** — системный мониторинг сетевого трафика

—

## Настройка pg_hba.conf

Файл `pg_hba.conf` управляет аутентификацией клиентов, но **не ограничивает трафик напрямую**. Однако он позволяет ограничить круг клиентов, тем самым косвенно снижая нагрузку.

### Структура записи:

ТИП БАЗА ПОЛЬЗОВАТЕЛЬ АДРЕС МЕТОД

### Примеры ограничений:

**Разрешить только локальные подключения:**

host all all 127.0.0.1/32 md5

**Ограничить доступ конкретной подсетью:**

host mydb appuser 192.168.1.0/24 scram-sha-256

**Запретить всем остальным:**

host all all 0.0.0.0/0 reject

### Ограничение числа соединений (косвенный лимит трафика)

В `postgresql.conf`:

max_connections = 100

Для конкретного пользователя:
sql
ALTER ROLE appuser CONNECTION LIMIT 20;

### Применение изменений без перезапуска:
sql
SELECT pg_reload_conf();

### Важные рекомендации:
— Всегда используйте `scram-sha-256` вместо устаревшего `md5`
— Применяйте принцип минимальных привилегий
— Используйте PgBouncer для пулинга соединений и снижения трафика
— Включите SSL (`hostssl`) для шифрования трафика между клиентом и сервером

Комбинируя мониторинг трафика и грамотную настройку `pg_hba.conf`, можно существенно оптимизировать нагрузку на сервер PostgreSQL.