PostgreSQL su VPS: quanta RAM serve e come calcolare work_mem

Questo è un aggiornamento della guida "PostgreSQL su un VPS, quanta RAM serve davvero". Aggiungiamo qui il tuning di work_mem, il parametro più confuso e più citato nelle richieste di supporto su database lenti. Versione testata: PostgreSQL 16 e 17, Ubuntu 24.04.

Cosa controlla work_mem

Il parametro work_mem fissa la quantità di memoria che il motore riserva a una singola operazione di ordinamento o di hash table, prima di scrivere i dati temporanei su disco. Secondo la documentazione ufficiale di PostgreSQL, work_mem sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files, e il valore di default è quattro megabyte (4MB).

Il punto che genera più errori di sottodimensionamento o sovradimensionamento: il valore non è per connessione, è per operazione. Una query complessa può eseguire più operazioni di sort e hash contemporaneamente, ciascuna delle quali può usare fino a work_mem prima di scrivere su disco, e più sessioni in esecuzione possono farlo in parallelo: il consumo totale di memoria può quindi essere molte volte il valore impostato.

Per le operazioni di hash table il limite effettivo non è work_mem ma un suo multiplo: il limite di memoria per una hash table si calcola moltiplicando work_mem per hash_mem_multiplier, che rende possibile usare più memoria del valore base di work_mem. Su PostgreSQL 16 e 17 il valore di default di hash_mem_multiplier è 2.0, che fa usare alle operazioni hash il doppio del valore base di work_mem, ed è spesso efficace con carichi misti.

Leggere il valore attuale

Da psql:

SHOW work_mem;

Restituisce il valore in vigore per la sessione corrente (ereditato dal file di configurazione o da eventuali ALTER ROLE/SET di sessione).

Come calcolarlo per un VPS

Non esiste una formula ufficiale PostgreSQL per work_mem: è un parametro da tarare sul carico, non da impostare a tabella. Un punto di partenza ragionevole, usato comunemente nel tuning pratico, è dividere la RAM libera dopo aver riservato shared_buffers per il numero massimo di connessioni concorrenti attese, con un margine per il fatto che ogni connessione può aprire più operazioni di sort/hash in parallelo. Su un VPS con 4 GB di RAM, shared_buffers impostato al 25% (circa 1 GB, valore indicativo per sistemi dedicati secondo la documentazione ufficiale) e max_connections a 50, un valore di partenza prudente è nell'ordine di 8-16 MB, da alzare solo dopo aver verificato con EXPLAIN che le query spillano su disco.

Il parametro non richiede riavvio del servizio: ha contesto "user", quindi si applica a connessioni successive dopo un reload.

Applicare il valore con ALTER SYSTEM

ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

Per applicarlo a un solo ruolo, senza toccare il resto del server:

ALTER ROLE nome_utente SET work_mem = '32MB';

Per la sola sessione corrente, utile per testare un valore senza impatto permanente:

SET work_mem = '32MB';

Verificare l'effetto con EXPLAIN (ANALYZE, BUFFERS)

Il modo corretto per sapere se il nuovo valore serve davvero è confrontare il piano di esecuzione prima e dopo, sulla stessa query:

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ORDER BY ...;

Nell'output, cercare la riga del nodo Sort o Hash: se compare Sort Method: external merge Disk: NNNNkB la query sta scrivendo su disco e work_mem per quell'operazione è insufficiente. Dopo aver alzato il valore e rilanciato pg_reload_conf(), si ripete lo stesso EXPLAIN sulla stessa query: se il metodo passa a quicksort o Memory senza riga Disk, l'aumento ha avuto effetto. La sezione BUFFERS mostra inoltre shared hit/read e temp read/written: una diminuzione dei blocchi temp tra le due esecuzioni è la controprova numerica, non impressionistica, che il tuning ha funzionato.

Un valore troppo alto non dà benefici oltre la soglia che elimina lo spill su disco: aumenta solo il rischio di esaurire la RAM quando più connessioni eseguono operazioni pesanti in contemporanea. Per questo ogni aumento va verificato, non solo dichiarato.