Що таке work_mem і чому сортування раптом іде на диск?

work_mem - скільки пам'яті може використати одна операція запиту: сортування (ORDER BY, DISTINCT, Merge Join), хеш-таблиця (Hash Join, GROUP BY через хеш). За замовчуванням - 4 МБ.

Якщо даних більше, операція переходить на тимчасові файли на диску - на порядки повільніше.

Як побачити в плані:

Sort Method: external merge  Disk: 285440kB     -- сортування на диску
Sort Method: quicksort  Memory: 3120kB          -- у пам'яті
Hash ... Batches: 16  Memory Usage: 4096kB      -- хеш поділено на частини через нестачу пам'яті

Ще - лог тимчасових файлів (log_temp_files = 0) і temp_files/temp_bytes у pg_stat_database.

Чому не поставити 1 ГБ - пастка множення. Ліміт діє на кожну операцію, а не на з'єднання:

• один запит може мати кілька сортувань і хешів одночасно;
• паралельний запит множить на кількість воркерів;
• хеш-операції можуть брати work_mem × hash_mem_multiplier (за замовчуванням ×2);
• і все це - на кожне з сотень з'єднань.

200 з'єднань × 3 операції × 256 МБ = 150 ГБ потенційно - сервер піде в OOM під навантаженням.

Як налаштовувати:

• Глобально - помірне значення (16-64 МБ залежно від пам'яті й кількості з'єднань).
• Точково більше - для важких звітів і аналітики:

SET LOCAL work_mem = '512MB';   -- лише в поточній транзакції
ALTER ROLE reporting SET work_mem = '256MB';

• Для обслуговування (CREATE INDEX, VACUUM) - окремий параметр maintenance_work_mem.

Але спершу - сам запит: часто сортування великого набору не потрібне взагалі - вистачить індексу, що віддає рядки в потрібному порядку, чи фільтра, що зменшує набір до сортування.

Докладніше в документації: work_mem