PostgreSQL
Shared Buffers и Buffer Cache
PostgreSQL на машине с 64GB RAM и shared_buffers = 128MB (дефолт!) - это спорткар с ручным тормозом. Все запросы ходят на диск, хотя данные могли бы находиться в памяти. Но просто поставить shared_buffers = 32GB тоже неправильно: начнётся double buffering, OS потеряет page cache, и всё станет медленнее. Buffer cache - тема где детали решают всё.
- **Cloudflare (2020):** при анализе медленных запросов к PostgreSQL обнаружили что hit ratio = 94% выглядит хорошо, но pg_statio показывал что именно индексы читаются с диска. Увеличение shared_buffers с 2GB до 8GB (при 32GB RAM) подняло hit ratio для индексов до 99.5% и снизило p99 latency с 45ms до 8ms.
- **Basecamp:** после добавления bulk import (ночные ETL-задачи) заметили что hit ratio утром стабильно падал до 80%. pg_buffercache показал что ETL вытеснял весь рабочий кеш. Решение: pg_prewarm для критических таблиц в cron после ETL.
- **Supabase:** документировали что effective_cache_size = 128MB (дефолт) на серверах с 8GB RAM заставляет планировщик предпочитать seq scan для средних таблиц. Правильная настройка effective_cache_size = 6GB изменила планы для 30% самых частых запросов.
Shared Buffers: главный кеш PostgreSQL
shared_buffers - разделяемая память PostgreSQL, где кешируются страницы таблиц и индексов. Все backend-процессы работают с одним пулом буферов. Размер критически влияет на производительность: страница в памяти = 0 disk I/O.
Команда Heroku рекомендует shared_buffers = 25% RAM как стартовую точку и никогда не выше 40%. Выше этого порога эффект уменьшается из-за накладных расходов на управление большим буферным пулом и уменьшение памяти для OS page cache, который тоже полезен.
Почему увеличение shared_buffers выше 40% RAM часто не улучшает производительность?
Buffer Manager: как страницы попадают в кеш
Buffer manager - компонент PostgreSQL, управляющий пулом буферов. При запросе страницы сначала проверяется буферный пул (buffer hit). Если страницы нет - она загружается с диска (buffer miss) и помещается в свободный слот.
Что означает 'dirty buffer' в контексте buffer manager?
Clock Sweep: алгоритм вытеснения
Когда все буферы заняты и нужен новый, buffer manager вытесняет существующий. PostgreSQL использует алгоритм clock sweep - упрощённую версию LRU без дорогостоящего поддержания порядка.
Большой sequential scan (dump таблицы, batch export) может вымыть весь кеш: миллионы страниц с usage_count=1 вытеснят все горячие страницы приложения. PostgreSQL частично решает это через ring buffer для больших сканов - они используют отдельный маленький пул и не портят основной кеш.
Почему PostgreSQL использует clock sweep вместо классического LRU?
effective_cache_size: подсказка для планировщика
effective_cache_size - не ограничение памяти, а подсказка для query planner. Планировщик использует это значение для оценки вероятности того, что страница уже в кеше (shared_buffers или OS page cache). Влияет на выбор между index scan и sequential scan.
effective_cache_size = 4GB на сервере с 32GB RAM. Какой главный эффект?
pg_buffercache: заглянуть в кеш
Расширение pg_buffercache позволяет просматривать текущее содержимое shared_buffers: какие таблицы занимают больше всего буферов, какие страницы dirty, какие pinned. Незаменимо для диагностики cache pollution.
Команда Datadog использует pg_buffercache для обнаружения cache pollution после bulk import: если через 5 минут после крупной загрузки данных hit ratio упал с 99% до 70% - bulk данные вытеснили рабочий кеш. Решение: SET LOCAL synchronize_seqscans = on и pg_prewarm для критических таблиц.
Высокий cache hit ratio (99%+) гарантирует хорошую производительность
Cache hit ratio показывает долю обращений к буферу, которые не потребовали чтения с диска. Но 99% hit ratio при неверных запросах (full table scan вместо index) означает что 99% медленного полного сканирования кешируется. Правильные запросы с 95% hit ratio могут быть быстрее.
Hit ratio - метрика количества, не качества доступа. Нужно смотреть в связке: hit ratio + pg_stat_statements для медленных запросов + EXPLAIN ANALYZE для конкретных планов. Высокий hit ratio при плохих планах просто означает что база быстро делает неправильные вещи.
Из pg_buffercache видно что 60% буферов занято одной системной таблицей pg_attribute. Что это означает?
Итоги
- **shared_buffers = 25% RAM как стартовая точка:** выше 40% эффект снижается из-за double buffering. Важно правильно установить и эффективно мониторить hit ratio через pg_statio.
- **Clock sweep вытесняет холодные страницы:** большие seq scan используют ring buffer и не вымывают основной кеш. Но bulk operations могут создавать cache pollution - диагностика через pg_buffercache.
- **effective_cache_size - подсказка планировщику:** не ограничивает память, но критически влияет на выбор плана. Устанавливать в 75% RAM; занижение ведёт к избыточным seq scan.
Связанные темы
Buffer cache - центральный компонент, связанный с хранением и фоновыми процессами:
- WAL и Durability — WAL позволяет откладывать flush dirty buffers на диск: достаточно что WAL записан. bgwriter и checkpointer управляют записью dirty buffers из кеша
- Storage: pages, tuples, TOAST — Buffer cache оперирует именно 8KB страницами - unit of storage. Каждый buffer соответствует одной странице таблицы или индекса
- Background Workers и процессы — bgwriter проактивно записывает dirty buffers; checkpointer записывает все dirty buffers при checkpoint. Оба освобождают буферы для buffer manager
Вопросы для размышления
- На сервере с 128GB RAM PostgreSQL используется только для OLTP с горячим набором данных ~10GB. Как бы выглядели оптимальные значения shared_buffers, effective_cache_size и random_page_cost для NVMe SSD?
- pg_buffercache показывает usagecount=5 для страниц pg_index (системный каталог). Это хорошо или плохо? Когда системные каталоги в кеше - нормально, а когда это проблема?
- Что произойдёт с производительностью сразу после рестарта PostgreSQL, пока кеш холодный? Какие механизмы (pg_prewarm, wal replay) помогают прогреть кеш быстрее?