Тюнінг PostgreSQL: shared_buffers, work_mem, effective_cache_size

Дефолтна конфігурація PostgreSQL розрахована на скромне залізо і неефективна на сучасних серверах. shared_buffers = 128MB, work_mem = 4MB — ці параметри залишають 95% пам'яті невикористаною. Наприклад, сервер з 32 ГБ RAM та дефолтними налаштуваннями використовує лише 128 МБ для кешу — база простоює, а запити гальмують. Правильний тюнінг дає приріст продуктивності щонайменше 30% і знижує навантаження на дискову підсистему. Ми виконуємо налаштування під ваш профіль: OLTP, аналітика або змішане навантаження. Досвід команди — 50+ успішних проєктів за 10+ років, гарантія результату. Компанія працює 5 років на ринку. Оцінимо ваш проект безкоштовно — пишіть.

Основні параметри пам'яті

Як налаштувати shared_buffers для OLTP?

Загальний кеш сторінок бази для всіх процесів. Для виділеного сервера — 25% RAM. На сервері з 32 ГБ це 8 ГБ. Більше 25% може призвести до конфлікту з кешем ОС. Перевірити, чи вистачає shared_buffers, можна по hit rate: якщо cache_hit_ratio < 99% — або shared_buffers малий, або робочий набір не вміщається в пам'ять. Використовуйте pg_buffercache, щоб побачити, які таблиці та індекси займають буфер. Збільшуйте shared_buffers до 25% RAM, але не більше 8 ГБ на Linux через архітектурні обмеження.

Що робити при низькому cache hit ratio

Якщо cache_hit_ratio нижче 99%, це сигнал до тюнінгу. Перевірте shared_buffers — можливо, потрібно збільшити. Також може допомогти додавання індексів. Для аналітичних запитів розгляньте збільшення work_mem. Використовуйте запит з code-блоку для перевірки hit rate.

Чому маленьке work_mem часто краще великого

work_mem — пам'ять для кожної сортування / hash join в одному запиті. Якщо запит має 3 sort node, він може зайняти 3 × work_mem. При 100 паралельних з'єднаннях з важкими запитами споживання може бути 100 × 3 × work_mem. Завелике значення викличе swap. Типова помилка — встановити 64 МБ глобально, тоді як 100 з'єднань з 4 сортуваннями = 100 × 4 × 64 МБ = 25,6 ГБ. Починайте з 16 МБ, збільшуйте для конкретних запитів через SET LOCAL. Для OLTP-навантаження високе work_mem веде до перевитрати пам'яті та падіння продуктивності через свопінг. Наша методика: аналізуємо плани запитів, виявляємо сортування на диску, збільшуємо work_mem тільки для проблемних запитів.

effective_cache_size: проста підказка планувальнику

Підказка планувальнику про доступний кеш ОС + shared_buffers. Для сервера з 32 ГБ: 24 ГБ. Впливає на вибір між index scan і seq scan. Не резервує пам'ять, але критично важливий для правильного вибору плану. Більш детально — в офіційній документації PostgreSQL. Рекомендована установка — 75% від RAM.

maintenance_work_mem для операцій обслуговування

Для VACUUM, CREATE INDEX, ALTER TABLE. Підвищуйте тільки під час обслуговування. Значення 2 ГБ підходить для більшості задач. Не тримайте високим постійно — це збереже пам'ять.

Планувальник та моніторинг

Вартісні параметри та паралельні запити

# Cost model for SSD
random_page_cost = 1.1    # SSD: 1.1, HDD: 4.0 (default)
seq_page_cost    = 1.0

# Parallel queries (PostgreSQL 9.6+)
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_tuple_cost  = 0.1
parallel_setup_cost  = 1000.0

Моніторинг hit rate та буферного кешу

-- Cache hit ratio
SELECT
    sum(heap_blks_hit)  AS heap_hit,
    sum(heap_blks_read) AS heap_read,
    round(
        sum(heap_blks_hit)::numeric /
        nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2
    ) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Buffer usage details
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT c.relname, count(*) AS buffers,
       round(count(*) * 8192.0 / 1024 / 1024, 1) AS size_mb
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 20;

Якщо cache_hit_ratio < 99% — потрібен тюнінг shared_buffers або додавання індексу.

Тюнінг під навантаження: OLTP, аналітика, змішане

Checkpoint і WAL

checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
max_wal_size = 4GB
fsync = on
synchronous_commit = on

Профілі навантаження: порівняння налаштувань

Практичні поради

Як оптимізувати сортування: приклад

Запит повільно виконує ORDER BY по великій таблиці — сортування йде через тимчасовий файл на диску:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM events
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 100;

Якщо у виводі "Sort Method: external merge Disk: 45678kB" — потрібен індекс або більше work_mem.

CREATE INDEX CONCURRENTLY idx_events_user_date
  ON events(user_id, created_at DESC)
  INCLUDE (id, event_type, payload);

Типові помилки при налаштуванні PostgreSQL

Застосування змін

Після зміни параметрів виконайте SELECT pg_reload_conf(); для застосування.

Висновок

Правильне налаштування PostgreSQL дає значний приріст продуктивності та економію на інфраструктурі. Ми гарантуємо покращення не менше 30% (у 1.3 рази краще за дефолт) або безкоштовно переглянемо конфігурацію протягом 30 днів. Вартість послуг — від 5000 грн за базовий аудит. Економія ресурсів — до $200 на місяць за рахунок зниження навантаження на диск. Зв'яжіться з нами для консультації та замовте професійний тюнінг PostgreSQL під ключ. Оцінимо ваш проект за 2 дні.

Послуги бекенд-розробки: production-grade надійність

На production-сервері о 3:14 ночі черга Laravel Jobs перестала оброблятися — 40 000 необроблених завдань у Redis. Причина: worker упав через memory leak у статичній змінній Eloquent observer, supervisor не перезапустив через misconfigured stopwaitsecs. Ми розбирали такий інцидент на проекті з 500 RPS: діагностика 4 години, фікс — 20 хвилин. Щоб ви не втрачали гроші, пропонуємо послуги бекенд-розробки з акцентом на production-grade надійність — 10+ років досвіду, 50+ проектів, 5 років на ринку. Оцінимо ваш проект за 2 дні.

Які проблеми вирішуємо

N+1 запити: головний вбивця швидкості

N+1 — найпоширеніша причина повільних сторінок у Laravel-додатках. Стандартна історія: сторінка працювала нормально на dev з 10 записами, на production з 10 000 — 8-секундне завантаження.

Laravel Debugbar у dev-оточенні показує кількість запитів. Більше 20 — сигнал для audit.

Model::preventLazyLoading(! app()->isProduction());

Telescope для профілювання: логує всі запити, jobs, mail, notifications з деталізацією. Після впровадження eager loading час завантаження сторінки падає з 8 с до 0.3 с — у 27 разів.

Memory leak у статичних змінних

У Laravel Octane або Swoole додаток тримається в пам’яті між запитами. Статичні змінні не скидаються — призводять до неконтрольованого росту пам’яті. Використовуємо defer-функції та контейнерні біндинги для коректного скидання стану.

Неправильний connection pool

Rails, Laravel, Django відкривають нове з'єднання PostgreSQL на кожен PHP/Python процес. 100 воркерів — 100 з'єднань. PostgreSQL деградує від 200+ активних з'єднань через overhead на управління.

PgBouncer у transaction pooling: 1000 воркерів → 20–50 реальних з'єднань. Це знижує latency на 40% та зменшує витрати на хостинг на 30% — при середній вартості хостингу $2,000/міс економить $600/міс. GIN-індекс для JSONB до 100 разів швидший за B-tree при пошуку.

Як Octane справляється з високим навантаженням?

Laravel Octane (RoadRunner або Swoole) прибирає overhead bootstrap на кожен HTTP-запит. Приріст: 3–8x на синтетичних бенчмарках, 2–4x на реальних додатках. Важливо: не зберігати стан у статичних змінних — застосовуємо це на проектах >1000 RPS.

Як PostgreSQL допомагає уникнути повільних запитів?

Використовуємо composite indexes для WHERE + ORDER BY, partial indexes для фільтрів з високою селективністю, GIN-індекси для JSONB та full-text search. to_tsvector + GIN замість LIKE '%query%' — запобігає seq scan навіть на мільйонах записів. Аналізуємо плани через EXPLAIN ANALYZE та pg_stat_statements.

Як обрати стек для вашого проекту?

Node.js виправданий для realtime: Laravel публікує події в Redis Pub/Sub, Node.js підписується та транслює клієнтам. Go — для goroutines (10k з'єднань на сервер — норма), але розробка повільніша, ніж Laravel.

Чому Redis критичний для продуктивності?

Redis виконує кілька ролей:

Redis Cluster для горизонтального масштабування, Sentinel для автоматичного failover. Замовте консультацію щодо оптимізації Redis для вашого проекту.

Що входить в роботу під ключ

• Архітектурне проектування (документація API, схема БД, діаграма сервісів)
• Реалізація за узгодженим ТЗ з code review
• Налаштування CI/CD (GitHub Actions, Docker), моніторингу (Sentry, Grafana), алертингу
• Навантажувальне тестування (k6, wrk) зі звітом
• Передача вихідних кодів, доступів, інструкція з деплою
• Навчання команди замовника (2–3 сесії)
• Гарантійна підтримка 1 місяць після здачі

Орієнтири по термінах

Вартість розраховується індивідуально після аналізу вимог до навантаження, інтеграцій та бізнес-логіки. Зв'яжіться з нами для безкоштовного аудиту вашого поточного backend — отримайте план оптимізації за 2 дні. Замовте консультацію та дізнайтеся, як знизити витрати на інфраструктуру на 30% без втрати продуктивності.