Postgres ottimizzazione della produzione che riduce la latenza

postgresql.conf, nel suo stato predefinito, è progettato per non fallire mai un'installazione e per non assorbire il traffico. Il valore storico di shared_buffers, 128 MB, consente a Postgres di avviarsi su una macchina minima senza riservare alcuna risorsa critica. max_connections a 100 si adatta a un piccolo server condiviso. Nessuno dei due è stato scelto per il tuo carico effettivo.

Quindi il problema non è che Postgres sia impostato male di default: è che non è mai stato impostato per te. Le sezioni seguenti esaminano le impostazioni nell'ordine in cui risultano più vantaggiose, dal collo di bottiglia più comune (le connessioni) a quello più lento ad apparire (il WAL).

Il primo progetto non è memoria, sono connessioni. Ogni connessione Postgres apre un processo server dedicato che consuma RAM e tempo CPU di contesto, anche se inattivo. Aumentando max_connections per evitare errori di tipo "troppe connessioni" si sposta il problema: oltre un certo numero di connessioni attive simultanee, il conflitto della CPU degrada la latenza di tutte le richieste, comprese le più veloci.

L'approccio corretto inverte il solito ordine: ridimensionare max_connections sulla concorrenza effettiva del server, quindi assorbire la concorrenza lato applicazione con un pooler come PgBouncer in pool_mode=transaction. Il pooler multiplexa centinaia di connessioni client su una manciata di connessioni server effettivamente attive. Il nostro articolo dedicato descrive in dettaglio il meccanismo della modalità transazione e i suoi limiti (dichiarazioni preparate, LISTEN/NOTIFY) e un confronto tra PgBouncer, Supavisor e PgCat per scegliere l'implementazione.

max_connections è un parametro di contesto "postmaster": modificarlo richiede un riavvio completo del server, non un semplice ricaricamento. Sulle istanze Postgres dedicate di Aurabase (piani Pro ed Enterprise, un cluster CNPG per progetto), questa impostazione è configurata per livello di piano anziché lasciata sul valore predefinito. Il riavvio non è un'operazione banale da ripetere in produzione, il che giustifica questa scelta per fasi piuttosto che per un valore fisso. La formula per scegliere il proprio valore, con i suoi limiti, è oggetto di un articolo separato: size max_connections.

Quattro impostazioni di memoria pesano più di tutte le altre messe insieme: shared_buffers, effective_cache_size, work_mem e maintenance_work_mem. I primi tre determinano la quantità di dati che Postgres conserva in memoria prima di tornare sul disco; il quarto determina la velocità di creazione di un VACUUM o di un indice.

shared_buffers imposta la cache interna condivisa da tutte le connessioni. Il valore di riferimento comunemente documentato dal progetto PostgreSQL è pari a circa il 25% della RAM disponibile su un server di database dedicato. Oltre a ciò, i guadagni diminuiscono e la cache del disco del sistema operativo prende il sopravvento. effective_cache_size non alloca nulla: è una stima, data al query planner, della memoria totale disponibile per la cache (Postgres e sistema operativo combinati). Sottodimensionarlo spinge lo scheduler verso scansioni sequenziali mentre un indice verrebbe in gran parte memorizzato nella cache; il benchmark attuale è compreso tra il 50 e il 75% di RAM.

work_mem è la trappola più comune. Questo non è un limite globale. Ogni operazione di ordinamento o hash in una query può consumarne la propria quota e una query con più join può riservare la propria quota più volte. Un valore troppo generoso combinato con un max_connections elevato può esaurire la RAM del server sotto carico simultaneo, anche se ogni richiesta presa isolatamente sembra ragionevole. maintenance_work_mem, al contrario, può rimanere decisamente più generoso: si applica solo alle operazioni di manutenzione (VACUUM, CREATE INDEX), che raramente sono concorrenti tra loro.

Solo shared_buffers richiede un riavvio. Gli altri tre vengono ricaricati a caldo, anche per una sessione isolata: SET work_mem = '64MB'; per la durata di una singola richiesta greedy, senza alterare l'impostazione globale.

Non disattivare mai l'aspirazione automatica in produzione, nemmeno temporaneamente per "liberare risorse" durante i picchi di carico. Postgres utilizza MVCC: ogni UPDATE e ogni DELETE lasciano una linea morta che solo il vuoto può recuperare. Senza di esso, le tabelle si gonfiano, gli indici si deteriorano e i piani di esecuzione si deteriorano gradualmente, senza errori visibili finché non è tardi.

Il valore predefinito diautovacuum_vacuum_scale_factor (20% di righe morte prima dell'attivazione) è adatto per una tabella piccola, non per una tabella con molti milioni di righe ad alta intensità di scrittura. Su una tabella di 10 milioni di righe, questo 20% rappresenta 2 milioni di righe morte accumulate prima del primo passaggio. Abbassare questa soglia tabella per tabella anziché modificare il valore complessivo dell'intero database.

-- Abbassa la soglia su una tabella con molte scritture, non nel complesso
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

L'Advisor integrato in Aurabase Studio verifica questa configurazione ad ogni analisi del progetto, allo stesso modo delle tabelle senza chiavi primarie o chiavi esterne non indicizzate. Si tratta di un segnale esplicito e non di un degrado silenzioso scoperto troppo tardi.

La maggior parte dei problemi di latenza in produzione non provengono né dalla CPU né dalla RAM: provengono da un indice assente o mal scelto. Prima di toccare un singolo parametro di postgresql.conf, EXPLAIN (ANALYZE, BUFFERS) sulla query in questione rimane la diagnosi più affidabile. Riduce molto la latenza delle richieste Postgres prima di aggiungere risorse.

-- Diagnosticare una query lenta prima di toccare la configurazione
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

Un Seq Scan su una tabella con molti milioni di righe, dove era previsto un Index Scan, segnala quasi sempre un problema di indice. Tre cause si verificano più spesso: un indice mancante, un tipo di colonna incompatibile con l'indice esistente o statistiche obsolete dopo un'importazione massiccia senza ANALYZE. L'aggiunta di RAM o l'aumento di work_mem a volte nasconde questo sintomo su un piccolo volume di dati; il problema si ripresenta non appena la tabella cresce.

Per individuare queste richieste senza cercarle una per una, pg_stat_statements aggrega le statistiche di esecuzione di tutte le richieste del server. Un errore comune: l'estensione deve prima essere elencata in shared_preload_libraries, un parametro di contesto "postmaster" che richiede un riavvio. Senza questo passaggio, CREATE EXTENSION pg_stat_statements; riesce silenziosamente ma non raccoglie nulla.

# Richiede un riavvio completo (parametro "postmaster")
shared_preload_libraries = 'pg_stat_statements'

-- Una volta riavviato il server, in ogni database da controllare:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Questo è esattamente l'errore che il backend Aurabase restituisce quando questa estensione è assente: un messaggio esplicito piuttosto che una lista vuota silenziosa, che potrebbe essere confusa con "no slow query". Studio Advisor va oltre: classifica automaticamente qualsiasi richiesta con un tempo medio superiore a 150 ms come avviso, e oltre 500 ms come critica. Queste soglie si basano sulle stesse statistiche pg_stat_statements.

Un checkpoint obbliga Postgres a scrivere su disco tutte le pagine modificate in memoria rispetto alla precedente. Per impostazione predefinita, questa scrittura potrebbe concentrarsi su una finestra temporale troppo breve. Il risultato è un notevole picco nella latenza del disco dal lato dell'applicazione, il tipo di rallentamento periodico difficile da collegare a una richiesta specifica.

checkpoint_completion_target controlla la diffusione di questa scrittura nell'intervallo tra due checkpoint. Un dettaglio mancante nelle liste di controllo precedenti: PostgreSQL 14, rilasciato nel 2021, ha modificato il suo valore predefinito da 0,5 a 0,9. Su un'istanza PostgreSQL 16, come i cluster tenant Aurabase dedicati, questa impostazione è quindi già corretta per impostazione predefinita; regolarlo manualmente ha senso solo su una versione precedente alla 14. Consulta il nostro confronto PostgreSQL 16 vs 17 vs 18 per altre modifiche alla versione che influiscono sull'ottimizzazione.

max_wal_size agisce nella stessa direzione: un valore troppo basso attiva checkpoint più frequenti del previsto, anche quando checkpoint_timeout non è ancora stato raggiunto. Aumentandolo si riduce la frequenza dei checkpoint, al costo di tempi di recupero più lunghi dopo un arresto anomalo poiché ci sono più WAL da riprodurre. Un compromesso da decidere in base alla propria tolleranza all'indisponibilità, non un valore universale.

Questa lista di controllo non è un audit una tantum da verificare una volta prima di entrare in produzione. Una base che raddoppia in volume o un traffico che triplica rende obsoleti i benchmark scelti all'avvio, spesso senza errori espliciti, solo un progressivo degrado della latenza p95.

Tre segnali meritano un monitoraggio continuo. pg_stat_statements identifica le richieste che peggiorano nel tempo. pg_stat_activity segnala query bloccate o con esecuzione anormalmente lunga e il rapporto tra connessioni attive e max_connections anticipa la saturazione prima di produrre errori lato applicazione.

La scheda Osservabilità di Studio copre parte di queste basi per qualsiasi progetto Aurabase, senza strumenti di terze parti da installare. Elenca le richieste lente, consente di annullare o terminare una richiesta attiva tramite PID e visualizza un indicatore di saturazione del pool che entra in avviso oltre l'80% di utilizzo. Su un'istanza self-hosted, lo stesso monitoraggio viene creato manualmente, con pg_stat_statements attivato e uno strumento di monitoraggio esterno collegato.

Otto impostazioni, nell'ordine in cui ripagano di più, con quello che devi sapere prima di toccarle.

Le soglie qui citate, come i 150 ms e la saturazione del pool dell'80% monitorata da Aurabase Studio Advisor, sono un punto di partenza verificato dal codice, non una verità universale. La tua accusa effettiva rimane l'unico giudice finale. Per dimensionare con precisione max_connections anziché seguire una regola generale, l'articolo dedicato dettaglia la formula e i suoi limiti.

Tre domande che sorgono sistematicamente una volta applicata la checklist per la prima volta.