Как рассчитать значение maintenance_work_mem для заданного количества мертвых строк

Вопрос

Как рассчитать значение maintenance_work_mem для заданного количества мертвых строк чтобы минимизировать количество повторных сканирований индексов для больших таблиц в СУБД PostgreSQL ?

Ответ

Для расчета значения maintenance_work_mem в PostgreSQL, которое минимизирует повторные сканирования индексов при очистке мёртвых строк (dead tuples), выполните следующие шаги:

1. Определите пиковое количество мёртвых строк в таблицах

• Запрос для поиска таблиц с максимальным числом мёртвых строк:

SELECT schemaname, relname, n_dead_tup

FROM pg_stat_all_tables

ORDER BY n_dead_tup DESC

LIMIT 10;

• Зафиксируйте максимальное значение n_dead_tup (например, N_dead_max = 10 000 000).

2. Рассчитайте минимальный требуемый объём памяти

• Формула:

min_maintenance_work_mem = (N_dead_max * 6) * 1.2

• 6 байт — размер одного идентификатора кортежа (TID) в памяти.
• 1.2 — коэффициент запаса (20%) для учёта накладных расходов.
• Пример:
• Для 10 000 000 строк:

(10 000 000 * 6) * 1.2 = 72 000 000 байт ≈ 69 MB.

Установите значение с учётом ресурсов сервера

• Рекомендации:
• Значение должно быть не меньше рассчитанного минимума (иначе возможны множественные сканирования индексов).
• Верхняя граница: 10–15% от ОЗУ сервера, но не более доступной памяти. Например:
• Сервер с 64 ГБ ОЗУ: maintenance_work_mem = 6–10 GB.
• Учитывайте параллельные процессы:

Общая память ≥ maintenance_work_mem * autovacuum_max_workers

(по умолчанию autovacuum_max_workers = 3).