База 500 ГБ тормозит: оптимизация PostgreSQL для SaaS-стартапа

Рекомендуем устанавливать PostgreSQL из официального репозитория PGDG (PostgreSQL Global Development Group) для получения последних версий и обновлений безопасности:

# Добавляем официальный репозиторий PostgreSQL
sudo apt install -y curl ca-certificates gnupg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg

# Для Debian 12 (bookworm)
echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# Обновляем и устанавливаем PostgreSQL 16
sudo apt update
sudo apt install -y postgresql-16 postgresql-client-16 postgresql-contrib-16

# Проверяем статус
sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"
# PostgreSQL 16.x on x86_64-pc-linux-gnu

# Входим под пользователем postgres
sudo -u postgres psql

-- Создаём пользователя приложения
CREATE USER app_user WITH PASSWORD 'SecureP@ssw0rd!' LOGIN;

-- Создаём базу данных
CREATE DATABASE app_production
    OWNER = app_user
    ENCODING = 'UTF8'
    LC_COLLATE = 'ru_RU.UTF-8'
    LC_CTYPE = 'ru_RU.UTF-8'
    TEMPLATE = template0;

-- Даём права
GRANT ALL PRIVILEGES ON DATABASE app_production TO app_user;

-- Создаём отдельную схему
\c app_production
CREATE SCHEMA app AUTHORIZATION app_user;
ALTER USER app_user SET search_path TO app, public;

-- Устанавливаем полезные расширения
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gist;

\q

# /etc/postgresql/16/main/postgresql.conf

# === Память ===

# Общий буферный кеш — 25% от RAM
shared_buffers = 8GB

# Память для операций сортировки и хеширования на каждый запрос
work_mem = 64MB

# Память для операций обслуживания (VACUUM, CREATE INDEX)
maintenance_work_mem = 2GB

# Эффективный размер кеша (shared_buffers + кеш ОС), ~75% RAM
effective_cache_size = 24GB

# Размер WAL-буферов
wal_buffers = 64MB

# Максимальное количество подключений
max_connections = 200

# Огромные страницы — снижает overhead управления памятью
huge_pages = try

Формулы расчёта, которые мы применили:

• shared_buffers = RAM x 0.25 (не более 8 ГБ на типичных нагрузках)
• work_mem = (RAM - shared_buffers) / (max_connections x 2)
• effective_cache_size = RAM x 0.75
• maintenance_work_mem = RAM / 16 (но не более 2 ГБ)

# === WAL (Write-Ahead Log) ===

# Уровень WAL — для репликации и бэкапов
wal_level = replica

# Количество WAL-файлов для репликации
max_wal_senders = 5
max_replication_slots = 5

# Контрольные точки
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

# Максимальный размер WAL между контрольными точками
max_wal_size = 4GB
min_wal_size = 1GB

# Компрессия WAL
wal_compression = zstd

# Синхронизация записи на диск
synchronous_commit = on
fsync = on
full_page_writes = on

# === Планировщик запросов ===

# Стоимость случайного чтения (для SSD — 1.1, для HDD — 4.0)
random_page_cost = 1.1

# Стоимость последовательного чтения
seq_page_cost = 1.0

# Эффективный размер кеша ОС
effective_io_concurrency = 200  # для SSD

# === Параллельные запросы ===
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
parallel_tuple_cost = 0.01
parallel_setup_cost = 100
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB

# === Статистика ===
default_statistics_target = 200
track_activities = on
track_counts = on
track_io_timing = on
track_wal_io_timing = on

# === Логирование ===
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# Логирование медленных запросов
log_min_duration_statement = 500  # мс
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 250

# Формат для pgBadger
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
log_statement = 'ddl'

После изменений перезапускаем PostgreSQL:

sudo systemctl restart postgresql
# Для параметров, не требующих рестарта:
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# /etc/postgresql/16/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Локальные подключения через unix-сокет
local   all             postgres                                peer
local   all             all                                     scram-sha-256

# IPv4 подключения
host    all             all             127.0.0.1/32            scram-sha-256

# Приложение из локальной сети
host    app_production  app_user        10.0.0.0/24             scram-sha-256

# Репликация
host    replication     repl_user       10.0.0.2/32             scram-sha-256

# Запрещаем всё остальное
host    all             all             0.0.0.0/0               reject

# Применяем изменения
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Включаем scram-sha-256 (более безопасный, чем md5)
sudo -u postgres psql -c "ALTER SYSTEM SET password_encryption = 'scram-sha-256';"
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Генерируем самоподписанный сертификат
sudo -u postgres openssl req -new -x509 -days 3650 -nodes \
    -out /etc/postgresql/16/main/server.crt \
    -keyout /etc/postgresql/16/main/server.key \
    -subj '/CN=pg-server.example.com'

sudo chmod 600 /etc/postgresql/16/main/server.key
sudo chown postgres:postgres /etc/postgresql/16/main/server.*

# В postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/16/main/server.crt'
ssl_key_file = '/etc/postgresql/16/main/server.key'
ssl_min_protocol_version = 'TLSv1.3'

sudo systemctl restart postgresql

В pg_hba.conf мы заменили host на hostssl для принудительного использования SSL.

На мастере (primary):

# Создаём пользователя для репликации
sudo -u postgres psql -c "CREATE USER repl_user WITH REPLICATION PASSWORD 'ReplP@ss123!';"

# postgresql.conf на мастере
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
hot_standby = on
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

# Создаём слот репликации
sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('replica1');"

На реплике (standby):

# Останавливаем PostgreSQL на реплике
sudo systemctl stop postgresql

# Удаляем старые данные
sudo rm -rf /var/lib/postgresql/16/main/*

# Создаём базовую копию с мастера
sudo -u postgres pg_basebackup -h 10.0.0.1 -U repl_user \
    -D /var/lib/postgresql/16/main \
    -Fp -Xs -P -R -S replica1

# Ключ -R автоматически создаёт standby.signal и настраивает подключение

# Запускаем реплику
sudo systemctl start postgresql

# Проверяем статус репликации на мастере
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

-- На мастере: проверяем отставание реплик
SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag
FROM pg_stat_replication;

-- На реплике: проверяем статус
SELECT
    pg_is_in_recovery() AS is_replica,
    pg_last_wal_receive_lsn() AS received_lsn,
    pg_last_wal_replay_lsn() AS replayed_lsn,
    pg_last_xact_replay_timestamp() AS last_replay_time,
    now() - pg_last_xact_replay_timestamp() AS replay_delay;

# Бэкап одной базы данных в custom-формате (сжатый, параллельный)
pg_dump -U postgres -Fc -Z 9 -j 4 \
    -f /backup/app_production_$(date +%Y%m%d_%H%M%S).dump \
    app_production

# Бэкап в plain SQL (читаемый формат)
pg_dump -U postgres --no-owner --no-privileges \
    -f /backup/app_production.sql \
    app_production

# Бэкап всех баз данных
pg_dumpall -U postgres -f /backup/all_databases_$(date +%Y%m%d).sql

# Бэкап только схемы (без данных)
pg_dump -U postgres --schema-only -f /backup/schema.sql app_production

# Бэкап конкретных таблиц
pg_dump -U postgres -t 'app.orders' -t 'app.users' \
    -Fc -f /backup/tables_$(date +%Y%m%d).dump app_production

# Восстановление из custom-формата
pg_restore -U postgres -d app_production -j 4 --clean --if-exists \
    /backup/app_production_20260331.dump

# Восстановление из SQL
psql -U postgres -d app_production -f /backup/app_production.sql

# Восстановление в новую базу
createdb -U postgres app_production_restored
pg_restore -U postgres -d app_production_restored -j 4 \
    /backup/app_production_20260331.dump

#!/bin/bash
# /usr/local/bin/pg-backup.sh

set -euo pipefail

BACKUP_DIR="/backup/postgresql"
RETENTION_DAYS=14
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/pg-backup.log"

mkdir -p "$BACKUP_DIR"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log "Начало резервного копирования"

# Получаем список баз данных
DATABASES=$(sudo -u postgres psql -At -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname != 'postgres';")

for DB in $DATABASES; do
    BACKUP_FILE="${BACKUP_DIR}/${DB}_${DATE}.dump"
    log "Бэкап базы: $DB"

    sudo -u postgres pg_dump -Fc -Z 6 -j 4 -f "$BACKUP_FILE" "$DB" 2>&1 | tee -a "$LOG_FILE"

    SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
    log "Готово: $BACKUP_FILE ($SIZE)"
done

# Удаляем старые бэкапы
log "Удаление бэкапов старше ${RETENTION_DAYS} дней"
find "$BACKUP_DIR" -name "*.dump" -mtime +${RETENTION_DAYS} -delete

# Бэкап глобальных объектов (роли, табличные пространства)
sudo -u postgres pg_dumpall --globals-only -f "${BACKUP_DIR}/globals_${DATE}.sql"

log "Резервное копирование завершено успешно"

# Опционально: отправка на удалённое хранилище
# rsync -az "$BACKUP_DIR/" backup-server:/backup/pg/

# Добавляем в cron (ежедневно в 2:00)
0 2 * * * /usr/local/bin/pg-backup.sh

# postgresql.conf — параметры автовакуума
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s

# Пороги для запуска VACUUM
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05  # 5% изменённых строк

# Пороги для запуска ANALYZE
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.02  # 2%

# Ограничение нагрузки автовакуума
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 1000

Для таблицы events с интенсивной записью мы настроили индивидуальные параметры:

-- Более агрессивный автовакуум для таблицы с высокой нагрузкой
ALTER TABLE app.events SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 0
);

-- Таблицы с наибольшим количеством dead tuples
SELECT
    schemaname || '.' || relname AS table_name,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Оценка bloat таблиц
SELECT
    schemaname || '.' || tablename AS table_name,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

# Установка
sudo apt install -y pgbouncer

# Конфигурация /etc/pgbouncer/pgbouncer.ini
[databases]
app_production = host=127.0.0.1 port=5432 dbname=app_production
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Режим пула: session, transaction или statement
pool_mode = transaction

# Размер пула
default_pool_size = 25
max_client_conn = 1000
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# Таймауты
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 120
client_login_timeout = 15

# Логирование
logfile = /var/log/pgbouncer/pgbouncer.log
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

# Админ-консоль
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

# Создаём файл аутентификации
echo '"app_user" "SCRAM-SHA-256$4096:salt$stored_key:server_key"' | sudo tee /etc/pgbouncer/userlist.txt

# Проще — генерируем хеш
sudo -u postgres psql -Atc "SELECT '\"' || usename || '\" \"' || passwd || '\"' FROM pg_shadow WHERE usename = 'app_user';" >> /etc/pgbouncer/userlist.txt

# Запускаем
sudo systemctl enable --now pgbouncer

# Приложения подключаются к порту 6432 вместо 5432
psql -h 127.0.0.1 -p 6432 -U app_user app_production

# Подключаемся к админ-консоли PgBouncer
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

-- Статистика пулов
SHOW POOLS;

-- Активные клиенты
SHOW CLIENTS;

-- Серверные соединения
SHOW SERVERS;

-- Общая статистика
SHOW STATS;

-- Статистика по базам данных
SHOW STATS_TOTALS;

-- Топ-20 самых тяжёлых запросов (требуется pg_stat_statements)
SELECT
    queryid,
    LEFT(query, 100) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric / 1000, 2) AS total_time_sec,
    ROUND(mean_exec_time::numeric, 2) AS mean_time_ms,
    rows,
    ROUND(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 2) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Текущие активные запросы
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    usename,
    datname,
    state,
    LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE state != 'idle'
    AND pid != pg_backend_pid()
ORDER BY duration DESC;

-- Блокировки
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    LEFT(blocked.query, 100) AS blocked_query,
    LEFT(blocking.query, 100) AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_locks AS bl ON bl.pid = blocked.pid
JOIN pg_locks AS kl ON kl.locktype = bl.locktype
    AND kl.database IS NOT DISTINCT FROM bl.database
    AND kl.relation IS NOT DISTINCT FROM bl.relation
    AND kl.page IS NOT DISTINCT FROM bl.page
    AND kl.tuple IS NOT DISTINCT FROM bl.tuple
    AND kl.pid != bl.pid
    AND NOT kl.granted
JOIN pg_stat_activity AS blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;

-- Размеры баз данных
SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

# Устанавливаем postgres_exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/latest/download/postgres_exporter-linux-amd64.tar.gz
tar xzf postgres_exporter-linux-amd64.tar.gz
sudo mv postgres_exporter /usr/local/bin/

# Создаём пользователя мониторинга
sudo -u postgres psql -c "
CREATE USER monitoring WITH PASSWORD 'MonitorP@ss!';
GRANT pg_monitor TO monitoring;"

# Создаём systemd unit
sudo cat > /etc/systemd/system/postgres-exporter.service <

Мы импортировали готовый дашборд Grafana ID 9628 (PostgreSQL Database) для визуализации метрик: количество подключений, кеш-хит, TPS, размер WAL, задержка репликации и bloat таблиц.