PostgreSQL su un VPS: dimensionare memoria e disco

Partire dal volume utile

La memoria di un server di database serve prima di tutto da cache: una pagina letta in memoria si serve molto più in fretta di una pagina letta su disco, anche NVMe. La domanda non è quindi solo «quanto pesa il database?», ma «quale parte viene letta spesso?». Un database da 50 GB di cui si consultano solo gli ultimi dodici mesi lavora forse su 8 GB.

Su un database esistente, due query danno il volume totale e le tabelle più grandi, indici compresi:

SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT relname AS nome,
       pg_size_pretty(pg_total_relation_size(relid)) AS dimensione_con_indici
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

Per un database ancora da creare, stimate tabella per tabella: numero di righe attese a un anno × dimensione media di una riga, poi aggiungete gli indici, che pesano comunemente dal 30 al 100% dei dati. Esempio: 2 milioni di righe al mese, da 500 byte ciascuna, fanno 12 GB di dati a un anno, e da 18 a 24 GB con gli indici.

La memoria: le impostazioni di partenza

PostgreSQL tiene le pagine lette su due livelli: la propria cache (shared_buffers) e la cache dei file del sistema. La sua documentazione raccomanda di partire dal 25% della memoria per shared_buffers su un server dedicato, e osserva che oltre il 40% il guadagno diventa raro: il resto va a vantaggio della cache del sistema e degli ordinamenti.

Questi valori presuppongono un database da solo sul server. Se l'applicazione gira accanto, togliete prima la sua memoria (processi, cache, compilazione) e applicate le percentuali al resto: un server da 8 GB che fa girare un'applicazione da 3 GB si regola come un database da 5 GB.

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
random_page_cost = 1.1

shared_buffers ha effetto solo al riavvio di PostgreSQL; gli altri parametri si ricaricano senza interruzione, con SELECT pg_reload_conf();.

Per sapere se la memoria basta, leggete il tasso di lettura dalla cache. Su un'applicazione gestionale o un sito, supera il 99%:

SELECT round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS tasso_cache
FROM pg_stat_database
WHERE datname = current_database();

Le connessioni: un processo ciascuna

PostgreSQL apre un processo per connessione, e ne accetta 100 per impostazione predefinita (max_connections). Ogni processo occupa qualche megabyte a riposo, di più quando ordina o fa l'hash dei dati (work_mem, a volte più volte nella stessa query). Alzare max_connections a 500 per stare dietro a un'applicazione che apre molte connessioni significa esporre il server alla mancanza di memoria al primo picco.

La risposta giusta è un pooler di connessioni: PgBouncer, in modalità transaction, fa condividere qualche decina di connessioni reali a centinaia di connessioni dell'applicazione. Anche i framework hanno il proprio pool (SQLAlchemy, Prisma, HikariCP): regolatene la dimensione sul numero di vCPU del server del database piuttosto che sul numero di utenti.

Il disco: volume, margine e log delle transazioni

Il disco di un server PostgreSQL contiene più delle sole tabelle:

• I dati e gli indici, e la loro crescita in un anno.
• Il log delle transazioni (WAL): 1 GB per impostazione predefinita tra due checkpoint (max_wal_size), di più durante un'importazione massiccia, e ancora di più se l'archiviazione del log accumula ritardo.
• I file temporanei degli ordinamenti che superano work_mem.
• Lo spazio per una riorganizzazione: VACUUM FULL e la ricostruzione di un indice scrivono una copia completa prima di liberare quella vecchia.
• Le esportazioni locali, se tenete dei backup sul server prima di inviarli altrove.

La regola: il volume a un anno, più almeno il 30% di margine. È una necessità: quando il disco che ospita il log delle transazioni si riempie, PostgreSQL si ferma per proteggere i suoi dati. Tenete d'occhio l'occupazione, e ampliate prima dell'80%.

Sul fronte delle prestazioni, ogni commit di una transazione attende la scrittura del log su disco: la latenza dello storage decide la velocità di scrittura. È qui che un SSD NVMe fa la differenza, molto più che sulle letture, già servite dalla memoria.

Il processore: una query, un core

Una query PostgreSQL viene eseguita su un solo core, tranne le letture di grandi dimensioni che il motore parallelizza. Il numero di vCPU decide quindi il numero di query pesanti elaborate nello stesso momento: 2 vCPU bastano a un'applicazione gestionale al lancio, da 4 a 8 vanno bene per i database molto sollecitati e i report pesanti.

Prima di aggiungere vCPU, cercate le query che li consumano. L'estensione pg_stat_statements le classifica per tempo totale:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls, round(total_exec_time) AS ms_totale,
       round(mean_exec_time::numeric, 1) AS ms_medio, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Stesso server dell'applicazione, o server separato?

Al lancio, il database sullo stesso server dell'applicazione è la scelta giusta: nessuna latenza di rete tra i due, una connessione tramite socket Unix, un solo server da salvare e da monitorare. Si separano quando i due si contendono le risorse: una compilazione o un picco di traffico che svuota la cache del database, un database che ha bisogno di tutta la memoria per i suoi dati, oppure requisiti di backup diversi.

Sicurezza e backup

• Ascolto locale: listen_addresses = 'localhost' finché si collega solo l'applicazione del server. Se un altro server deve accedervi, aprite la porta 5432 al suo solo indirizzo, nel firewall e in pg_hba.conf.
• Autenticazione: metodo scram-sha-256 in pg_hba.conf, un ruolo per applicazione, senza privilegi di superutente.
• Backup logico quotidiano con pg_dump, copiato fuori dal server.
• Ripristino a un istante preciso: l'archiviazione continua del log (pgBackRest, WAL-G) permette di tornare al minuto esatto prima di un errore, mentre un'esportazione quotidiana riporta al giorno prima.
• Ripristino testato: un backup si dimostra ripristinandolo, su un database di test, a intervalli regolari.

pg_dump -Fc -d mio_db -f mio_db-$(date +%F).dump
createdb mio_db_test
pg_restore -d mio_db_test mio_db-2026-10-29.dump