Как оптимизировать работу PostgreSQL под оборудование в Linux

Вопрос:

Как оптимизировать работу СУБД PostgreSQL под оборудование в Linux?

Ответ:

Все параметры работы СУБД PostgreSQL хранятся в специальном файле, который располагается как правило в каталоге

/etc/postgresql/14/main/postgresql.conf

В данном файле указываются параметры оптимизации. Рекомендуется указывать собственные параметры оптимизации в конце файла.

Далее идет список параметров, рекомендуемые значения и комментарии

Параметр	Рекомендуемое значение	Комментарии
shared_buffers	RAM/4	Объем памяти для совместного кеша страниц, разделяется между всеми процессами Postgres, рекомендуемое значение - четверть доступного объема памяти
temp_buffers	256MB	Верхний лимит для временных таблиц в каждой сессии, рекомендуется фиксированное значение.
work_mem	RAM/32..64	Указывает объем памяти, который может быть использован для запроса прежде, чем будут задействованы временные файлы на диске. Применяется для каждого соединения и каждой операции, поэтому итоговый объем используемой памяти может существенно превосходить указанное значение.
effective_io_concurrency	2 для RAID, 200 для SSD, 500..1000 для NVMe	Допустимое число одновременных операций ввода/вывода. Для жестких дисков указывается по количеству шпинделей, для массивов RAID5/6 следует исключить диски четности. Для SATA SSD это значение рекомендуется указывать равным 200, а для быстрых NVMe дисков его можно увеличить до 500-1000
synchronous_commit	off	Позволяет серверу не ждать сохранения данных на диске, прежде чем сообщить клиенту об успешном завершении операции. Позволяет достаточно безопасно повысить производительность работы. В случае внезапного выключения питания могут быть потеряны несколько последних транзакций, но сама база останется в рабочем состоянии, также, как и при штатной отмене потерянных транзакций.
seq_page_cost	1.0	Стоимость последовательного чтения с диска, является относительным числом, вокруг которого определяются все остальные переменные стоимости, данное значение является значением по умолчанию.
random_page_cost	1.5..2.0 для RAID, 1.1..1.3 для SSD	Стоимость случайного чтения с диска, чем ниже это число, тем более вероятно использование сканирования по индексу, нежели полное считывание таблицы, однако не следует указывать слишком низких, не соответствующих реальной производительности дисковой подсистемы, значений, иначе вы можете получить обратный эффект, когда производительность упрется в медленный случайный доступ.
effective_cache_size	RAM - shared_buffers	Определяет эффективный размер кеша, который может использоваться при одном запросе. Этот параметр не влияет на размер выделяемой памяти, не резервирует ее, а служит для ориентировочной оценки доступного размера кеша планировщиком запросов. Чем он выше, тем большая вероятность использования сканирования по индексу, а не последовательного сканирования.
row_security	off	Отключает политику защиты на уровне строк, данная опция не используется платформой и ее отключение дает некоторое повышение производительности.
escape_string_warning	off	Специфичная опция под 1С
standard_conforming_strings	off	Специфичная опция под 1С

Для производительных SSD можно использовать значения:

seq_page_cost = 0.5

random_page_cost = 0.5

А для NVme:

seq_page_cost = 0.1

random_page_cost = 0.1

Для применения изменений необходимо перезапустить службу используя команду

sudo service postgresql restart