Connection pooling Postgres su una VPS da 4 GB

Perché una piccola VPS esaurisce la RAM prima di raggiungere max_connections

Il connection pooling di Postgres su una VPS non serve ad aumentare la velocità. Serve a mantenere operativo un server con 4 GB di RAM, perché ogni connessione PostgreSQL è un processo separato del sistema operativo, con una propria memoria privata. Un pooler mantiene un numero ridotto e fisso di processi backend effettivi dietro un numero elevato di connessioni client, meno costose.

Il valore predefinito di max_connections è 100. Si tratta di un limite, non di una quantità di memoria disponibile. PostgreSQL non verifica se la macchina è effettivamente in grado di mantenere 100 backend che eseguono query reali. Per questo il sistema esaurisce le risorse prima. Il kernel OOM killer seleziona un processo e, quando seleziona un backend, PostgreSQL riavvia l'intero cluster per mantenere coerente la memoria condivisa. Nel log compare server process (PID 1234) was terminated by signal 9: Killed, seguito da terminating any other active server processes. Tutte le connessioni aperte vengono interrotte, comprese quelle sane.

Il server esaurisce la memoria perché ogni connessione è un processo e perché work_mem viene assegnato per ogni operazione di ordinamento o hash, non per ogni connessione. Entrambi i fattori si moltiplicano.

Ogni connessione è un processo e ogni processo consuma memoria

PostgreSQL usa un processo per ogni connessione. Il postmaster crea un processo backend quando un client si connette e quel backend rimane attivo fino alla disconnessione del client. Non è un thread. Dispone di proprie tabelle delle pagine, proprie cache del catalogo e propri piani di query memorizzati. Queste cache crescono quando la connessione accede a più tabelle ed esegue query più diverse. Per questo una connessione di lunga durata in un'applicazione ORM soggetta a un carico elevato consuma più memoria di una connessione appena aperta.

La memoria condivisa è realmente condivisa. shared_buffers è una singola allocazione per l'intero cluster, mappata in ogni backend. La memoria privata non è condivisa. Per questo top può trarre in inganno: il resident set size (RSS) di un backend include le pagine condivise che quel backend ha utilizzato. Sommando l'RSS di 50 backend, si contano quindi shared_buffers 50 volte.

Misurare invece la parte privata. Il PSS (proportional set size) divide ogni pagina condivisa per il numero di processi che la mappano, mentre l'USS (unique set size) conta soltanto le pagine appartenenti esclusivamente a quel processo.

sudo apt update
sudo apt install -y smem
sudo smem -k -P '^postgres'

La colonna USS indica la memoria che verrebbe liberata se quel backend terminasse. Questo è il costo reale della singola connessione. I valori pubblicati indicano di solito pochi megabyte per un backend inattivo e valori diverse volte superiori per un backend che ha eseguito query ORM ampie. Considerali valori pubblicati tipici, non i valori del tuo sistema. L'unico valore su cui basare la pianificazione è quello misurato sul tuo server con il tuo carico di lavoro.

È facile non considerare un'allocazione per sessione. temp_buffers ha come valore predefinito 8MB e viene allocato per sessione la prima volta che la sessione accede a una tabella temporanea. Non viene restituito fino alla chiusura della sessione.

work_mem viene assegnato per operazione, non per connessione

È in questo punto che i calcoli diventano problematici. work_mem ha un valore predefinito di 4MB e la documentazione di PostgreSQL è chiara sul significato: "una query complessa può eseguire contemporaneamente diverse operazioni di ordinamento e hashing; in genere ogni operazione può utilizzare una quantità di memoria pari a questo valore prima di iniziare a scrivere i dati in file temporanei". Un piano con tre nodi di ordinamento può quindi utilizzare tre volte work_mem all'interno dello stesso backend, nello stesso momento.

Le operazioni hash possono utilizzare una quantità ancora maggiore. hash_mem_multiplier ha un valore predefinito di 2.0, quindi un hash join o un'aggregazione hash può utilizzare work_mem moltiplicato per due, cioè 8MB con le impostazioni predefinite. Le query parallele aumentano ulteriormente il consumo, perché ogni parallel worker è un processo distinto con la propria disponibilità di memoria.

Esegui i calcoli per una VPS da 4 GB. Imposta shared_buffers su 1 GB, lascia work_mem a 4MB e consenti a 100 connessioni di eseguire ciascuna una query con due nodi hash. Si tratta di 100 volte 16MB, quindi di 1.6 GB di memoria privata oltre a 1 GB di shared buffers, prima di considerare la page cache e tutto il resto presente sul server. Ora aumenta work_mem a 64MB perché il server dispone di RAM sufficiente. Le stesse 100 connessioni arrivano a 100 volte 256MB. Non viene generato alcun avviso. Te ne accorgi quando interviene l'OOM killer.

Puoi verificare se work_mem è troppo piccolo invece di procedere per tentativi. Imposta log_temp_files = 0 in postgresql.conf ed esegui un reload. Ogni spill su disco scriverà quindi una riga con il nome e la dimensione del file, ad esempio temporary file: path "base/pgsql_tmp/pgsql_tmp1234.0", size 20971520. Gli spill frequenti indicano che un valore più alto di work_mem potrebbe essere utile. Se non si verificano spill, aumentarlo non produce alcun vantaggio e consuma memoria che non hai.

L'aritmetica dei pool che causa davvero i problemi

Nessuno configura intenzionalmente 240 connessioni. Si configura un pool da 20 connessioni, poi si esegue l'applicazione in più istanze.

[
  {
    "config": "1 worker",
    "backends": 20
  },
  {
    "config": "4 web workers",
    "backends": 80
  },
  {
    "config": "4 web + 2 background",
    "backends": 120
  },
  {
    "config": "2 hosts x 4 workers",
    "backends": 160
  },
  {
    "config": "3 hosts x 4 workers",
    "backends": 240
  }
]

Quattro worker Gunicorn, ciascuno con un pool da 20 connessioni, richiedono 80 backend. Aggiungendo due worker per i job in background, il totale diventa 120. Aumentando a 3 hosts x 4 workers, l'applicazione richiede 240 backend a fronte di un max_connections pari a 100. Nessuna di queste 5 configurazioni è configurata in modo errato se considerata singolarmente. Il pool è associato al processo e nessun componente dell'applicazione può vedere il totale.

Anche i valori predefiniti delle librerie spingono nella stessa direzione. Il valore predefinito di QueuePool in SQLAlchemy è pool_size=5 con max_overflow=10, quindi 15 connessioni per processo. HikariCP usa 10 come valore predefinito. Django non disponeva di un pool integrato prima della versione 5.1 e usava una connessione per processo worker. Per questo le applicazioni Django incontrano il problema più tardi e poi tutto insieme, quando qualcuno imposta CONN_MAX_AGE o abilita la nuova opzione "pool": True. Se esegui un'applicazione Django dietro Gunicorn e nginx, devi moltiplicare per il numero di worker Gunicorn, non per il numero di server.

Che cosa cambia davvero il connection pooling di Postgres su un VPS

Un pooler è un processo che comunica con l'applicazione tramite il protocollo wire di PostgreSQL e, dall'altro lato, mantiene un numero ridotto di connessioni reali al server. Non rende più veloce alcuna query. Cambia chi sostiene il costo di una connessione e quanti backend reali sono presenti.

Migliorano due aspetti. L'apertura della connessione non richiede più un fork e le ricerche nel catalogo necessarie per riempire la cache vuota del backend, perché è il pooler a gestire direttamente la connessione del client. Soprattutto, il numero di backend reali non dipende più dal numero di connessioni dell'applicazione: 500 client possono condividere 20 backend.

L'attesa è la funzionalità principale, ed è l'aspetto che spesso crea resistenza. Senza un pooler, 500 query simultanee ricevono ciascuna un backend e vengono eseguite contemporaneamente su due core CPU. Di conseguenza, sono tutte lente e l'intera memoria viene utilizzata nello stesso momento. Con un pooler, 20 query vengono eseguite e le altre attendono alcuni millisecondi. Ogni query in esecuzione riceve così una quota effettiva della CPU e termina prima. Una coda davanti a un pool ridotto è più efficace dell'assenza di una coda davanti a un pool grande.

Un pooler non limita le altre risorse della macchina. Se Postgres condivide il VPS con un application server o con un database vettoriale sullo stesso VPS, il pooler protegge Postgres dall'applicazione, ma non fa altro. Imposta un limite rigido anche per i servizi vicini: puoi limitare la memoria e la CPU che un servizio può utilizzare con systemd, così un processo fuori controllo non può causare anche l'arresto del database. La posizione del database determina il modo in cui imposti questi limiti. Questa è la differenza pratica tra eseguire Postgres in Docker o direttamente sull'host.

Pooling per sessione e pooling per transazione

Un'unica impostazione determina tutto il resto: pool_mode.

Nel pooling per sessione, una connessione al server viene assegnata a un client per l'intera durata della connessione del client e viene rilasciata quando il client si disconnette. Tutto funziona perché il pooler è un semplice proxy. Si risparmia il costo della connessione, ma niente altro. Se l'applicazione apre 200 connessioni, servono comunque 200 backend.

Nel pooling per transazione, una connessione al server viene assegnata a un client soltanto per la durata di una transazione. Al raggiungimento di COMMIT o ROLLBACK, la connessione torna nel pool e viene assegnata al successivo client in attesa. È questo che consente di gestire 500 client con 20 backend. È anche ciò che causa i problemi, per progettazione: l'istruzione successiva può essere eseguita su un backend diverso da quello utilizzato per l'istruzione precedente.

L'impostazione predefinita di PgBouncer è pool_mode = session. Installandolo senza modificare nulla, si ottiene il vantaggio economico, ma non il beneficio del pooling. Una terza modalità, statement, restituisce la connessione dopo ogni singola istruzione e rifiuta le transazioni composte da più istruzioni. Lasciarla invariata, a meno che non sia chiaro il motivo per cui si desidera usarla.

Quale modalità di transazione presenta incompatibilità e perché

Tutto ciò che segue non funziona per lo stesso motivo. Si tratta di stato conservato all'interno di un singolo backend, mentre il transaction pooling non garantisce l'uso dello stesso backend due volte.

• SET e RESET a livello di sessione. SET search_path, SET statement_timeout, SET TIME ZONE e SET ROLE vengono eseguiti sul backend che ha gestito quella istruzione e non sono più disponibili nella transazione successiva. Usare SET LOCAL all'interno di una transazione esplicita: l'ambito è limitato a quella transazione ed è quindi sicuro.
• LISTEN. La consegna delle notifiche appartiene al backend che ha eseguito LISTEN, e quel backend viene assegnato a un altro client non appena termina la transazione. NOTIFY continua a funzionare in transaction mode, creando un errore difficile da diagnosticare: l'invio riesce, ma la ricezione non avviene mai. Se serve LISTEN, aprire una connessione aggiuntiva direttamente sulla porta 5432, evitando il pooler.
• Advisory lock a livello di sessione. pg_advisory_lock() viene mantenuto dalla sessione e rilasciato quando la sessione termina. Con il transaction pooling, la chiamata di sblocco viene eseguita su un backend diverso, quindi il lock resta attivo finché PgBouncer non ritira quella connessione al server; per impostazione predefinita ciò avviene dopo server_lifetime, cioè un'ora. Usare pg_advisory_xact_lock(), che viene rilasciato al termine della transazione dallo stesso backend che lo ha acquisito.
• PREPARE e DEALLOCATE, le istruzioni SQL. Non sono mai disponibili in transaction mode.
• I cursori WITH HOLD e qualsiasi cursore lato server che si prevede debba restare disponibile oltre la transazione in cui è stato creato.
• Tabelle temporanee che devono sopravvivere a un commit. CREATE TEMP TABLE ... ON COMMIT PRESERVE ROWS crea la tabella nello schema temporaneo di un backend, mentre la transazione successiva potrebbe essere eseguita su un backend diverso.
• LOAD.

Le prepared statement a livello di protocollo sono l'unico elemento che ha cambiato comportamento. PgBouncer 1.21.0 ha aggiunto il supporto in transaction mode e la versione 1.24.0 lo ha abilitato per impostazione predefinita impostando max_prepared_statements a 200. Le versioni precedenti lo lasciano a 0, quindi disabilitato. Ubuntu 24.04 include PgBouncer 1.22.0: la funzionalità è presente, ma è necessario impostare manualmente max_prepared_statements. Se non si è certi del comportamento della build, l'impostazione sicura è sul client: psycopg 3 smette di usare le prepared statement lato server quando si imposta prepare_threshold su None.

Django definisce una propria variante di questo problema. La documentazione specifica che "l'uso di un pooler di connessioni in transaction pooling mode (ad esempio PgBouncer) richiede la disabilitazione dei cursori lato server per quella connessione", perché "i cursori lato server sono accessibili solo nella connessione in cui sono stati creati". Impostare DISABLE_SERVER_SIDE_CURSORS su True nella voce relativa a quel database, altrimenti ogni chiamata .iterator() diventa un errore intermittente che si manifesta soltanto sotto carico.

La transaction mode è una scelta valida, ma comporta un contratto preciso. Leggere l'elenco, verificare la compatibilità dell'ORM e della libreria per i job in background, quindi procedere al cambio.

Installare PgBouncer e indirizzarvi l'applicazione

La configurazione seguente deve essere eseguita sul proprio server: Ubuntu 24.04, con PostgreSQL già in ascolto su 127.0.0.1 sulla porta 5432.

sudo apt update
sudo apt install -y pgbouncer
pgbouncer --version

Ubuntu 24.04 include PgBouncer 1.22.0 nei pacchetti. La versione upstream è 1.25.2 ad agosto 2026. Verificare quale versione è installata, perché il comportamento delle prepared statement descritto sopra dipende da questo.

Creare un ruolo il cui unico compito sia accedere alla console di amministrazione di PgBouncer, quindi creare il file delle password. PgBouncer richiede i secret SCRAM (meccanismo di autenticazione con risposta a una challenge e salt) contenuti in pg_authid, e solo un superuser può leggere quella tabella.

sudo -u postgres psql -c "CREATE ROLE pgb_admin LOGIN PASSWORD 'change-this'"
sudo -u postgres psql -At -c \
  'SELECT format($$"%s" "%s"$$, rolname, rolpassword) FROM pg_authid WHERE rolpassword IS NOT NULL' \
  > /tmp/userlist.txt
sudo install -o postgres -g postgres -m 640 /tmp/userlist.txt /etc/pgbouncer/userlist.txt
rm /tmp/userlist.txt

Copiare i secret invece di riscrivere le password è ciò che rende possibile questa configurazione. PgBouncer può riutilizzare un secret SCRAM per autenticarsi a PostgreSQL solo quando anche il client si è autenticato con SCRAM, quando il secret nel file corrisponde byte per byte a quello in pg_authid (stesso salt e stesso numero di iterazioni, non soltanto la stessa password) e quando la riga [databases] non imposta un user=. Aggiungere user=appuser a quella riga obbliga PgBouncer a usare invece una password in chiaro. Confermare che il proprietario del file corrisponda all'account con cui viene eseguito il servizio, usando systemctl show pgbouncer -p User. La rotazione di una password in PostgreSQL richiede la rigenerazione di questo file, altrimenti la connessione successiva restituisce password authentication failed.

Ora scrivere /etc/pgbouncer/pgbouncer.ini.

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgb_admin
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
min_pool_size = 5
max_db_connections = 80
max_prepared_statements = 200
ignore_startup_parameters = extra_float_digits

listen_addr = 127.0.0.1 mantiene il pooler fuori da Internet pubblico. Questo è importante perché un pooler raggiungibile dall'esterno è un endpoint di autenticazione che non si intendeva pubblicare. max_client_conn indica quante connessioni applicative PgBouncer accetta; il costo è ridotto, quindi il valore può essere elevato. default_pool_size indica quanti backend reali può mantenere una determinata coppia database-utente; questo è il valore costoso. max_db_connections limita l'intero database a 80 connessioni, lasciando margine sotto max_connections per psql, backup e monitoraggio. ignore_startup_parameters = extra_float_digits impedisce a PgBouncer di rifiutare i driver, incluso il driver JDBC, che inviano quel parametro al momento della connessione.

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer --no-pager
sudo journalctl -u pgbouncer -n 20 --no-pager

Un avvio corretto registra una riga che indica che PgBouncer è in ascolto su 127.0.0.1:6432. Se l'avvio non riesce a causa del file delle password, il log registra il percorso che non è stato possibile leggere. Nella maggior parte dei casi il problema riguarda modalità o proprietario del file, non la sintassi. Modificare quindi la stringa di connessione dell'applicazione sostituendo la porta 5432 con la porta 6432 e riavviare l'applicazione. Non è necessario modificare altro nell'applicazione.

Come verificare che il pool stia svolgendo correttamente il proprio lavoro

PgBouncer dispone di una console di amministrazione accessibile tramite un database virtuale chiamato pgbouncer.

psql -h 127.0.0.1 -p 6432 -U pgb_admin pgbouncer

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;

SHOW POOLS è il valore da monitorare. cl_active indica i client attualmente associati a una connessione al server, cl_waiting i client in coda in attesa di una connessione, sv_active e sv_idle i backend effettivamente in uso e liberi, mentre maxwait indica da quanto tempo, in secondi, il client in testa alla coda è in attesa. In condizioni normali, un sistema in buona salute ha cl_waiting a 0 e maxwait a 0. Un valore di maxwait che supera uno o due secondi indica che il pool è troppo piccolo oppure che le query sono troppo lente. Le due cause richiedono interventi diversi.

Prima di aumentare default_pool_size, verifica quale delle due condizioni si applica.

SELECT state, count(*) FROM pg_stat_activity
  WHERE backend_type = 'client backend' GROUP BY state;

Se la maggior parte dei backend rimane in idle in transaction, il problema non è la dimensione del pool. L'applicazione apre una transazione e poi esegue al suo interno un'operazione lenta, ad esempio una chiamata HTTP, quindi ogni backend resta occupato senza eseguire query. idle_in_transaction_session_timeout interromperà queste connessioni, ma la correzione effettiva va apportata al codice dell'applicazione. Se invece tutti i backend sono in active, il pool è realmente saturo e le query devono essere sottoposte a EXPLAIN (ANALYZE, BUFFERS) prima di aumentare il numero di connessioni disponibili.

Per il dimensionamento, il riferimento iniziale più citato è l'euristica di HikariCP: circa il doppio del numero di core più uno. Su una VPS con 2 core, il risultato è 5. Usa questo valore come punto di partenza documentato, imposta default_pool_size su un valore vicino e modificalo in base a maxwait. I pool di piccole dimensioni possono sembrare inadeguati, ma di solito offrono risultati migliori: un backend in coda non consuma risorse, mentre un backend in esecuzione consuma CPU e memoria e aumenta la contesa sui lock.

Scelta tra PgBouncer, PgDog e Pgpool-II

PgBouncer è la scelta per il caso ordinario: un server PostgreSQL, un VPS e un'applicazione che apre più connessioni di quante il sistema possa gestire. Svolge una sola funzione, la configurazione è contenuta in un unico file ini ed è disponibile nei pacchetti Debian e Ubuntu. Gestisce le connessioni in un singolo thread, una capacità sufficiente per un carico tipico di un VPS; diventa un limite solo su macchine molto più grandi.

PgDog è da valutare quando la decisione di instradamento deve avvenire nello stesso hop di rete del connection pooling. Si presenta come un proxy per il ridimensionamento di PostgreSQL, è scritto in Rust e supporta il pooling delle transazioni e delle sessioni, oltre alla separazione delle letture e delle scritture tramite l'analisi delle query. Supporta anche lo sharding con instradamento su più shard e il commit a due fasi. Sceglilo quando hai un primary e una o più repliche e vuoi inviare le letture alle repliche senza modificare l'applicazione per renderla consapevole della loro esistenza. Ci sono due aspetti da considerare. È distribuito con licenza AGPLv3, quindi la clausola relativa all'uso tramite rete è una questione di licenza da chiarire con il responsabile aziendale di questa decisione prima di portarlo in produzione; secondo il progetto, l'uso interno e le modifiche private non fanno scattare un obbligo di pubblicazione del codice sorgente. È inoltre un progetto giovane, con release settimanali e numeri di versione 0.x, quindi fissa un release tag invece di seguire main.

git clone https://github.com/pgdogdev/pgdog
cd pgdog
cargo build --release
./target/release/pgdog --config pgdog.toml --users users.toml

La compilazione dal codice sorgente richiede una toolchain Rust stable aggiornata, CMake e un compilatore C/C++. Sono disponibili anche binari Linux precompilati e pacchetti Debian nella pagina delle release, oltre a un'immagine container all'indirizzo ghcr.io/pgdogdev/pgdog. La configurazione è suddivisa in due file. Il primo contiene le impostazioni generali e una voce per ogni database, scritta qui come array TOML di tabelle inline, in modo da distinguere facilmente le due forme.

databases = [
  { name = "appdb", host = "127.0.0.1" },
]

[general]
port = 6432
default_pool_size = 10

Il secondo contiene una voce per ogni utente, nella stessa forma di array.

users = [
  { name = "appuser", database = "appdb", password = "change-this" },
]

PgDog è in ascolto per impostazione predefinita sulla porta 6432, la stessa usata da PgBouncer, quindi i due servizi non possono usare entrambi il valore predefinito sullo stesso host.

Pgpool-II, alla versione 4.7.2 a giugno 2026, offre pooling e bilanciamento del carico, con un watchdog per il failover automatico. Le funzionalità aggiuntive introducono ulteriori modalità di errore; prima di sceglierlo, è quindi necessario capire il suo modello di pooling. Pgpool-II crea in anticipo num_init_children processi figlio e ogni processo memorizza nella cache fino a max_pool connessioni ai server, quindi il limite dei backend è num_init_children moltiplicato per max_pool. Ogni processo figlio serve un client alla volta, quindi il numero di client accettabili è uguale a num_init_children ed è fissato all'avvio; anche un client inattivo occupa comunque un processo figlio. Imposta num_init_children su 100 e max_pool su 4: avrai autorizzato 400 backend, che è esattamente il problema che hai installato un pooler per risolvere. Scegli Pgpool-II quando ti servono il suo failover e il suo instradamento delle query, quindi esegui con attenzione questa moltiplicazione. Se ti serve soltanto ridurre il numero di backend, offre più componenti di quanti ne richieda il problema.

La questione del proxy gestito e l'equivalente self-hosted

Le piattaforme gestite offrono questa funzione come prodotto separato. AWS mette RDS Proxy davanti a RDS, mentre Supabase mette il proprio pooler, Supavisor, davanti a Supabase Postgres. Entrambi svolgono la funzione descritta qui: mantengono le connessioni dei client con un costo ridotto e assegnano loro un numero inferiore di backend reali. Supavisor è open source e può essere eseguito in modalità self-hosted, quindi la scelta non è tra software proprietario e software gratuito.

L'equivalente self-hosted di un proxy gestito non è un concetto diverso. È lo stesso concetto, ma con il file di configurazione sotto il tuo controllo: PgBouncer in modalità transaction, sullo stesso VPS del database, in ascolto su 127.0.0.1. Esistono due differenze concrete. Un proxy gestito si trova a un hop di rete di distanza, quindi aggiunge latenza e continua a mantenere le connessioni dei client mentre il database sottostante viene riavviato. PgBouncer sull'host del database aggiunge un hop di loopback, con un costo quasi nullo, e si arresta quando si arresta quell'host. Se vuoi mantenere le connessioni durante un riavvio, ti serve anche un meccanismo di failover. È in questo scenario che il watchdog di Pgpool-II o gli health check di PgDog giustificano la loro maggiore complessità.

Esiste un'ulteriore opzione. Se il numero di connessioni è il fattore che complica maggiormente il deployment, un database embedded non ha un modello di connessioni da gestire con un pool. È una libreria integrata nel processo, non un server in ascolto su una porta. Per un singolo application server con un volume di scritture moderato, eseguire SQLite in produzione su un VPS elimina completamente questo problema invece di richiedere la gestione di un pool. Quando ti serve un server reale, dimensiona il pool prima della macchina.

FAQ

Ho ancora bisogno di PgBouncer se la mia applicazione dispone già di un pool di connessioni?

In genere sì, perché un pool di connessioni applicativo è associato al singolo processo e non può vedere quelli degli altri processi. Quattro worker Gunicorn, ciascuno con un pool di 20 connessioni ai backend 80, più due worker in background, arrivano a 120. PgBouncer è l’unico componente che vede il totale e può limitarlo. La configurazione corretta usa entrambi: un pool ridotto all’interno di ogni worker, così le richieste non devono creare ogni volta una connessione TCP, e PgBouncer in modalità transaction, che limita il numero effettivo di connessioni ai backend a valle.

Che cosa si rompe esattamente quando passo PgBouncer alla modalità transaction?

Qualsiasi componente che mantiene lo stato in un unico backend tra transazioni. I cursori a livello di sessione SET, RESET, LISTEN e WITH HOLD, le istruzioni SQL PREPARE e DEALLOCATE, i lock consultivi a livello di sessione, le tabelle temporanee che devono sopravvivere a un commit e LOAD. NOTIFY continua a funzionare, facendo sembrare i problemi di LISTEN un errore di consegna anziché un problema del connection pool. In Django, imposta DISABLE_SERVER_SIDE_CURSORS su True. In psycopg 3, imposta prepare_threshold su None oppure usa PgBouncer 1.22 o una versione successiva con max_prepared_statements maggiore di 0. Sostituisci pg_advisory_lock() con pg_advisory_xact_lock().

Quale valore deve avere default_pool_size su un VPS con 2 core?

Più piccolo di quanto sembri corretto. L’euristica HikariCP comunemente pubblicata è circa il doppio del numero di core più uno, quindi all’incirca 5 su due core; è un punto di partenza, non una risposta definitiva. Impostalo, quindi leggi maxwait e cl_waiting in SHOW POOLS sotto carico reale. Se entrambi valgono zero, il pool è sufficientemente grande. Un maxwait in aumento indica che i client sono in coda. Prima di aumentare il numero, controlla pg_stat_activity: i backend bloccati in idle in transaction indicano un bug applicativo che più connessioni finirebbero soltanto per nascondere.

PgBouncer o PgDog?

PgBouncer è adatto a un singolo server PostgreSQL su una singola VPS, lo scenario della maggior parte dei deployment. È incluso nei pacchetti di Ubuntu, il suo comportamento è ben documentato e l’intera configurazione si trova in un unico file ini. PgDog è indicato quando il read/write splitting tra repliche o lo sharding devono avvenire nello stesso passaggio del pooling, in modo che l’applicazione non debba conoscere la topologia. Prima di adottare PgDog, chiarisci la questione AGPLv3 con il responsabile delle licenze della tua organizzazione e fissa una release specifica, perché il progetto usa ancora numeri di versione 0.x e pubblica una nuova release ogni settimana.