Система баз данных после установки намеренно настроена скромно: она обязана завестись на любой машине, от крошечного контейнера до большого сервера, и потому молчит о потенциале железа. Разница между дефолтным PostgreSQL и настроенным под конкретный сервер измеряется не процентами, а разами: одни и те же запросы на одних и тех же данных укладываются либо в десятки миллисекунд, либо в секунды ожидания диска. Ниже разобраны три главных параметра памяти, различия типов нагрузки, расчёт для типового сервера и методы проверки результата. Всё применимо к свежим веткам PostgreSQL, включая 16-ю.
Откуда берутся значения по умолчанию и почему они малы
Дефолты PostgreSQL консервативны до смешного. Буферный кэш shared_buffers по умолчанию равен 128 мегабайтам, объём работы одного оператора work_mem измеряется четырьмя мегабайтами, а подсказка планировщику effective_cache_size заявляет о четырёх гигабайтах доступного кэша. На сервере с гигабайтами памяти эти числа выглядят издевательски, но у них есть резон: установка обязана стартовать везде и не отобрать память у соседних процессов.
Параметр max_connections со значением 100 продолжает ту же логику. Сто соединений по четыре мегабайта на оператор сортировки это уже приличный аппетит, поэтому база сознательно держит пользователей в узких рамках. За рамки выходит тот, кто осознанно перенастроил систему под своё железо и свою нагрузку.
Посмотреть текущие значения без чтения файлов можно прямо в SQL: команда SHOW ALL выведет весь список, а функция current_setting подскажет по конкретному параметру. У каждого значения есть и контекст применения: одни живут до полной перезагрузки сервера, другие подхватываются перечитыванием конфигурации, третьи меняются на уровне одной сессии. Эта классификация подсказывает, насколько смело можно экспериментировать на живой базе.
Важно понимать и механику применения правок. Часть параметров подхватывается простым перечитыванием конфигурации, часть требует полной перезагрузки службы. Буферный кэш относится ко вторым, поэтому изменение shared_buffers планируют на техническое окно, а эксперименты с work_mem можно крутить на живой базе хоть каждый час.
Карта памяти PostgreSQL и главный разделитель shared_buffers
Буферный кэш это собственная область памяти PostgreSQL, куда база складывает прочитанные с диска страницы. Классическая отправная точка для неё, четверть оперативной памяти сервера, проверена годами: на выделенной машине под базу берут 25 процентов, на сервере с сайтом и почтой снижают до 15-20. Растить долю сверх трети почти бессмысленно: операционная система кэширует те же файлы на своём уровне, и страницы начинают дублироваться в двух кэшах одновременно.
Второй параметр, effective_cache_size, память вообще не занимает. Это честная подсказка планировщику: сколько кэша, своего и операционного, сервер готов видеть под баками. Планировщик пользуется ею при выборе между чтением по индексу и полным сканом. Заниженная подсказка заставляет его избегать индексов там, где они быстрее, завышенная работает мягче, но лгать всё же не стоит. Ориентиром служит сумма свободной памяти и буферного кэша, на типовых серверах это 50-75 процентов оперативки.
Третий резерв, maintenance_work_mem, обслуживает служебные операции: сборку индексов, очистку таблиц, импорт данных. Дефолтные 64 мегабайта заставляют автоваккум ходить по большой таблице вечно. Поднятие до 256-512 мегабайт на вечернем окне сокращает время обслуживания в разы, а памяти это стоит только в моменты, когда обслуживание идёт.
work_mem на весах сортировок и хэшей
work_mem это лимит памяти на один оператор сортировки, хэш-таблицу или агрегат одного запроса. Коварство параметра в множителях: запрос с тремя сортировками съест три лимита, а сто активных соединений умножают всё ещё на сто. Арифметика простая и отрезвляющая: сто соединений, в каждом по паре активных сортировок, work_mem 32 мегабайта, и в худшем случае база запросит шесть гигабайт только на промежуточные результаты.
Практика расставляет значения по типам запросов. Для коротких транзакций сайта хватает 4-16 мегабайт. Для отчётов с большими GROUP BY разумны десятки мегабайт. Для аналитики на гигабайтных выборках поднимают до сотен, но жёстко ограничивают число параллельных соединений. Сигнал о слишком маленьком значении виден в журнале: PostgreSQL сливает промежуточные результаты во временные файлы, и рост их числа и размера это прямое приглашение к правке.
Для наблюдения в реальном времени есть представление pg_stat_activity: запросы, стоящие в сортировке или хэше, видны глазами, и по ним оценивается реальная потребность в памяти. Порядок действий выглядит так: включить логирование временных файлов, неделю собирать статистику, поднимать значение только тем ролям, где сортировки действительно тяжёлые. PostgreSQL позволяет задать work_mem на уровне отдельной роли командой ALTER ROLE, и это культурнее глобальной правки на всю систему.
Типы нагрузки OLTP, OLAP и смешанные сценарии
OLTP, транзакционная нагрузка с тысячами коротких запросов, любит маленький work_mem, побольше соединений и хороший кэш. Здесь главное не память на оператор, а скорость прохода по индексам и честная подсказка effective_cache_size. Число соединений разумно ограничивать, а наружу ставить пул: база не любит сотни одновременных клиентов, ей комфортнее десятки. Пул соединений решает задачу дёшево: приложение держит сотни логических клиентов, а база видит десяток реальных сессий, и все умолчания памяти оказываются адекватными без единой правки конфигурации.
OLAP, аналитическая нагрузка с тяжёлыми запросами, переворачивает картину. Соединений мало, зато каждый запрос сортирует и хэширует гигабайты. Здесь поднимают work_mem, включают и настраивают параллельные воркеры: параметры max_worker_processes, max_parallel_workers и max_parallel_workers_per_gather управляют тем, сколько процессоров база готова бросить на один запрос. Автоваккум для баз с постоянной записью тоже усиливают, иначе очистка не будет поспевать за изменениями.
Смешанный сценарий, сайт днём и отчёты ночью, решается расписанием и раздельными ролями: тяжёлые запросы уходят на реплику или в отдельное окно, а база живёт с компромиссными значениями. Отдельно существует случай базы размером в сотни гигабайт на скромном железе: там тюнинг начинается не с postgresql.conf, а с индексов и разбиения таблиц, конфиг лишь помогает не усугублять.
Соседние ручки управления от автоваккуума до контрольных точек
Память соседствует с другими лимитами, и часть тюнинга живёт рядом. Автоваккуум по умолчанию включён и настроен нейтрально: он чистит понемногу, но на базе с интенсивной записью не поспевает, и таблицы обрастают мёртвыми строками. Для пишущих баз увеличивают число рабочих процессов автоваккуума, снижают паузу между циклами и поднимают лимит стоимости, чтобы служба не засыпала на середине работы.
Журнал упреждающей записи и контрольные точки настроены так, чтобы скорее сохранить данные, чем разогнать запись. Параметр checkpoint_completion_target со значением 0.9 растягивает сброс страниц на весь интервал, сглаживая пики ввода-вывода, а увеличение max_wal_size отодвигает контрольные точки дальше, реже вынуждая базу сбрасывать большие объёмы за раз. На пишущих базах эти два значения дают ощутимое сглаживание нагрузки без риска для данных.
Буфер журнала wal_buffers в свежих версиях выставляется автоматически: при двух гигабайтах shared_buffers система сама отведёт максимум из шестнадцати мегабайтов, и руками туда лезть нужды нет. А вот параметры целостности, fsync и full_page_writes, отключают только в экспериментах с одноразовыми данными: экономия на записи мнимая, а риск остаться с повреждённой базой после сбоя питания реален.
Практический расчёт для сервера на 8 гигабайт памяти
Сценарий: выделенный сервер, 8 гигабайт оперативной памяти, SSD-диск, база сайта с элементами отчётности. Расчёт выглядит так: четверть памяти под буферный кэш, два гигабайта; свободную память плюс кэш операционной системы заявляем планировщику, шесть гигабайт; на оператор отводим 16 мегабайт, ограничив соединения разумной сотней; служебным операциям отдаём полгигабайта в вечерние часы. Для SSD снижаем стоимость случайного чтения и поднимаем параллелизм ввода-вывода.
Современный способ правок через SQL не требует редактирования файлов вручную:
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET effective_cache_size = '6GB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '512MB';
ALTER SYSTEM SET random_page_cost = '1.1';
ALTER SYSTEM SET effective_io_concurrency = '200';
SELECT pg_reload_conf();Функция перечитывания подхватит всё, кроме shared_buffers: буферный кэш меняется только полной перезагрузкой службы. Значение random_page_cost снижено с дефолтных 4.0 до 1.1, потому что случайное чтение с SSD дёшево, а effective_io_concurrency в 200 говорит планировщику о диске, способном держать много параллельных запросов. Эти два параметра не про память, но на SSD они меняют планы запросов сильнее, чем любой из её настроек.
Формула переносится на любое железо пропорционально. На 16 гигабайтах это shared_buffers 4 гигабайта, effective_cache_size 12 гигабайтов и work_mem 32 мегабайта, на 32 гигабайтах 8 и 24 соответственно. Число соединений пересчитывается от приложения, а не от памяти: сначала реальный пик из pg_stat_activity, потом запас в полтора раза, и только потом числа попадают в конфигурацию.
Три числа проверяются после применения правок:
- доля попаданий в буферный кэш из представления pg_stat_database по полям blks_hit и blks_read;
- время и частота типовых запросов из расширения pg_stat_statements до и после правок;
- количество и объём временных файлов, о которых база пишет в журнал при включённом логировании.
Включение логирования и снятие статистики делаются командами:
ALTER SYSTEM SET log_temp_files = 0;
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();Первая строка приводит в журнал все временные файлы, нулевое значение означает отсутствие порога, и каждая сортировка, не влезшая в память, попадает в лог с размером. Вторая включает протокол запросов медленнее полсекунды: за неделю такой журнал рисует карту узких мест без единого внешнего инструмента. Доля попаданий в кэш считается из статистики баз: попадания делятся на сумму попаданий и чтений с диска, и значение выше 99 процентов на читающей базе говорит, что памятью база обеспечена.
Проверка результата и типичные ошибки тюнинга
Правка без измерения это гадание. До изменений снимается базовая линия: тяжёлые запросы из pg_stat_statements, время ответа типовых страниц, статистика временных файлов. После недельной работы линии сравниваются, и только разница имеет право называться результатом.
Типичные ошибки повторяются из треда в тред. Чужой конфиг копируется целиком без понимания нагрузки: настройки сервера отчётности переезжают на базу интернет-магазина, и короткие запросы начинают страдать. work_mem поднимается до сотен мегабайт "на всякий случай", и первый же пик одновременных сортировок уводит сервер в подкачку. effective_cache_size принимают за реальное выделение памяти и выставляют больше, чем стоит на сервере, надеясь на чудо. И наконец, меняют shared_buffers без перезагрузки, честно ждут эффекта и не получают его, потому что значение ещё не применено. Отдельная ловушка поджидает тех, кто правит конфигурацию вручную: команды ALTER SYSTEM пишут значения в отдельный файл postgresql.auto.conf, и он перекрывает основной конфигурационный. Аккуратно отредактированный postgresql.conf при живом значении из ALTER SYSTEM просто не вступит в силу, и поиск причины занимает вечер. Дешевле выбрать один способ правок и не смешивать оба.
Здоровый тюнинг скучен: замер, одна-две правки, неделя наблюдения, снова замер. Скучный подход даёт базе ровно то, что она умеет использовать, а серверу оставляет память для остальной работы. Именно так дефолтные 128 мегабайт превращаются в гигабайты пользы без экспериментов над продакшеном.