Настройка базы данных PostgreSQL для веб-приложения

Настройка базы данных PostgreSQL для веб-приложения

PostgreSQL — стандарт для большинства веб-приложений, которым нужна надёжная реляционная база с поддержкой JSON, полнотекстового поиска, расширений и сложных запросов. Правильная установка и настройка с самого начала экономит много времени при росте нагрузки.

Установка и базовая конфигурация

На Ubuntu 24.04 / Debian 12:

apt install -y postgresql-16 postgresql-client-16
systemctl enable postgresql
systemctl start postgresql

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

CREATE USER myapp WITH PASSWORD 'strong_password_here';
CREATE DATABASE myapp_production OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE myapp_production TO myapp;

-- Подключиться к БД и выдать права на схему
\c myapp_production
GRANT ALL ON SCHEMA public TO myapp;

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

Настройки по умолчанию рассчитаны на сервер с 256 МБ RAM. Для production минимум:

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

# Память (для сервера с 8 ГБ RAM)
shared_buffers = 2GB              # 25% RAM
effective_cache_size = 6GB        # 75% RAM
work_mem = 64MB                   # для сортировки, hash join
maintenance_work_mem = 512MB      # для VACUUM, CREATE INDEX

# Checkpoint
checkpoint_completion_target = 0.9
wal_buffers = 64MB
max_wal_size = 2GB
min_wal_size = 512MB

# Параллелизм
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Подключения
max_connections = 200             # под pgBouncer — можно меньше
shared_preload_libraries = 'pg_stat_statements'

# Логирование медленных запросов
log_min_duration_statement = 1000  # 1 секунда
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Connection pooling через pgBouncer

Прямые подключения к PostgreSQL дороги: каждое — это отдельный процесс. pgBouncer мультиплексирует их:

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp_production = host=127.0.0.1 port=5432 dbname=myapp_production

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction          # лучший режим для большинства приложений
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
server_idle_timeout = 600
log_connections = 0
log_disconnections = 0

Приложение подключается к pgBouncer на порту 6432, а не к PostgreSQL на 5432.

Индексы

-- Простые индексы на часто используемых полях
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at DESC);

-- Частичный индекс — только активные записи
CREATE INDEX CONCURRENTLY idx_sessions_active ON sessions(user_id, expires_at)
WHERE revoked_at IS NULL;

-- Составной индекс для типичного WHERE + ORDER BY
CREATE INDEX CONCURRENTLY idx_products_category_price
ON products(category_id, price) WHERE deleted_at IS NULL;

-- GIN-индекс для JSONB
CREATE INDEX CONCURRENTLY idx_orders_metadata ON orders USING gin(metadata);

-- Полнотекстовый поиск
CREATE INDEX CONCURRENTLY idx_articles_search
ON articles USING gin(to_tsvector('russian', title || ' ' || body));

Партиционирование для больших таблиц

-- Таблица событий с партиционированием по месяцам
CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    user_id     int NOT NULL,
    event_type  text NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Автоматическое создание партиций через pg_partman
CREATE EXTENSION IF NOT EXISTS pg_partman;
SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');

Мониторинг медленных запросов

-- Включить pg_stat_statements (в shared_preload_libraries)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Топ-10 медленных запросов
SELECT
    round((total_exec_time / 1000)::numeric, 2) AS total_sec,
    round((mean_exec_time)::numeric, 2) AS mean_ms,
    calls,
    round(rows::numeric / calls, 1) AS rows_per_call,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 10;

-- Неиспользуемые индексы
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Репликация

# На primary — postgresql.conf
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

# Создать пользователя репликации
CREATE USER replicator REPLICATION LOGIN PASSWORD 'repl_password';

# На replica — pg_basebackup
pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/16/main -P -R -X stream

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

# pg_dump для логических бэкапов
pg_dump -Fc -Z 9 myapp_production > backup_$(date +%Y%m%d_%H%M%S).dump

# WAL-G для непрерывного архивирования в S3
export WALG_S3_PREFIX=s3://my-bucket/postgres-wal
export AWS_REGION=eu-central-1
wal-g backup-push /var/lib/postgresql/16/main

Сроки

Установка и базовая настройка PostgreSQL под конкретную нагрузку: 1–2 дня. Настройка pgBouncer, репликации и мониторинга: 2–3 дня. Миграция существующей базы с настройкой hot standby и автоматических бэкапов: 3–5 дней в зависимости от объёма данных.