Налаштування бази даних 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

Графік

Установка та базове налаштування під конкретне навантаження: 1–2 дні. Налаштування pgBouncer, репліка, моніторинг: 2–3 дні. Міграція існуючої бази з налаштуванням hot standby та автоматичних бэкапів: 3–5 днів залежно від обсягу даних.