PostgreSQL su VPS: come calcolare e impostare work_mem (guida aggiornata)

Questo è un aggiornamento alla guida "PostgreSQL su un VPS: quanta RAM serve davvero". Il dimensionamento generale della RAM (quanto assegnare a shared_buffers, quanto lasciare al sistema operativo) resta valido. Qui aggiungiamo un parametro diverso, che spesso genera confusione proprio perché non è "quanta RAM ha il VPS" ma "quanta RAM può prendere una singola operazione prima di scrivere su disco": work_mem.

Esempi testati su Ubuntu 24.04 con PostgreSQL 16 (gli stessi comandi valgono su PostgreSQL 17).

Cos'è work_mem

work_mem è il parametro che 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, secondo la documentazione ufficiale. Non è la memoria totale assegnata a PostgreSQL: è il tetto per singola operazione di ordinamento o hash. una query complessa può eseguire più operazioni di ordinamento e hash contemporaneamente, e ciascuna di queste può usare fino a questa quantità di memoria prima di iniziare a scrivere su file temporanei; inoltre più sessioni possono fare lo stesso in parallelo. Di conseguenza il consumo totale può essere un multiplo del valore impostato.

Il valore di default è quattro megabyte (4MB), invariato tra PostgreSQL 16 e 17.

Un dettaglio che la guida originale non trattava: le operazioni basate su hash (hash join, aggregazioni hash) non sono limitate solo da work_mem. Il limite di memoria per una hash table si ottiene moltiplicando work_mem per hash_mem_multiplier, e il valore di default di hash_mem_multiplier è 2.0, il che fa sì che le operazioni basate su hash usino il doppio della quantità base di work_mem. Vuol dire che con work_mem = 16MB una hash join può arrivare a usare fino a 32 MB prima di spillare su disco.

Come vederlo

SHOW work_mem;

Risponde con il valore attivo nella sessione corrente (es. 4MB).

La formula di calcolo

Non esiste un valore universale: dipende da quanta RAM è libera dopo aver tolto shared_buffers e il margine per il sistema operativo, da quante connessioni concorrenti il server deve reggere (max_connections) e da quante operazioni di sort/hash mediamente gira ogni query.

Formula di partenza, prudente:

work_mem = RAM_disponibile_per_le_query / (max_connections × operazioni_medie_per_query)

Esempio con un VPS da 8 GB, dopo aver riservato 2 GB a shared_buffers e sistema operativo: restano 6 GB. Con max_connections = 100 e una stima di 2 operazioni medie per query, il calcolo dà 6144 MB / (100 × 2) = circa 30 MB. In pratica, su un VPS entry-level con poche connessioni concorrenti, valori tra 8 MB e 32 MB sono il punto di partenza più comune prima di affinare osservando i log.

Il rischio opposto, un valore troppo alto, è reale: con molte connessioni attive e query complesse il consumo si moltiplica ed è la causa più frequente di out-of-memory su VPS con poca RAM. Meglio partire bassi e salire per gradi, osservando gli effetti.

Come impostarlo

Due modi equivalenti. Nel file postgresql.conf (tipicamente in /etc/postgresql/16/main/postgresql.conf su Ubuntu 24.04):

work_mem = 16MB

Oppure da riga di comando psql, senza toccare il file:

ALTER SYSTEM SET work_mem = '16MB';

ALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, which is read in addition to postgresql.conf, secondo la documentazione ufficiale di PostgreSQL 16.

In entrambi i casi il parametro ha contesto "user": non serve riavviare il server, basta un reload. i valori impostati con ALTER SYSTEM diventano effettivi dopo il successivo reload della configurazione del server, oppure dopo il successivo riavvio per i parametri che possono essere modificati solo all'avvio. Per work_mem basta:

pg_ctl reload -D /var/lib/postgresql/16/main

oppure, da dentro psql:

SELECT pg_reload_conf();

Se si vuole cambiare il valore solo per la sessione corrente, senza toccare la configurazione globale:

SET work_mem = '64MB';

Utile per testare un valore su una singola query pesante prima di renderlo permanente, oppure per assegnarlo a un ruolo specifico con ALTER ROLE nome_ruolo SET work_mem = '32MB';.

Verificare l'effetto con EXPLAIN ANALYZE

Il modo corretto per sapere se il valore scelto basta è leggere il piano di esecuzione di una query lenta, prima e dopo la modifica:

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

Nell'output, la riga del nodo di ordinamento (Sort) riporta il metodo usato. Se compare Sort Method: external merge Disk: ...kB, vuol dire che l'operazione non è entrata nel work_mem disponibile ed è passata su disco, con un costo in tempo di risposta. Se dopo aver alzato work_mem lo stesso nodo mostra Sort Method: quicksort Memory: ...kB, l'operazione resta in RAM. È la verifica pratica: non un numero teorico, ma la riga del piano di esecuzione letta prima e dopo il cambio, sulla stessa query.

La guida fa riferimento a PostgreSQL 16/17 su Ubuntu 24.04; i percorsi dei file di configurazione possono variare con altre distribuzioni.