Rails PgBouncer: Transaction Pooling, Prepared Statements en Connection Sizing in Productie

De Rails-applicatie draaide twee jaar lang prima. Toen verdubbelde het verkeer in één kwartaal en Postgres begon connecties te weigeren tijdens de ochtendpiek. De eerste reflex van de CTO was Postgres verticaal opschalen — grotere instance, meer max_connections. Ik vroeg hoeveel Puma workers ze draaiden over hoeveel dyno’s. Zestien dyno’s, vijf workers, vijf threads. Achthonderd potentiële databaseconnecties enkel vanuit de app. Postgres stond op 300.

We hebben de database niet opgeschaald. We zetten PgBouncer ervoor in transaction pooling mode, brachten Rails’ connectieaantal terug naar een realistisch getal, en de ochtendpiek werd een non-event. Totale kosten: één middag en ongeveer veertig dollar per maand voor een kleine pooler-instance.

Na negentien jaar Rails is PgBouncer met transaction pooling het stukje database-infrastructuur met de hoogste hefboomwerking dat ik aan groeiende Rails-apps toevoeg. Het is ook het stuk dat het vaakst verkeerd geconfigureerd is op manieren die een jaar lang werken en daarna om drie uur ‘s nachts ontploffen.

Waarom Rails-apps Rails PgBouncer Transaction Pooling Nodig Hebben

Postgres-connecties zijn duur. Elke connectie is een geforkt proces met eigen geheugen — meestal 5 tot 15 megabytes resident memory zodra de connectie is opgewarmd. Een Postgres-instance met max_connections = 300 reserveert ergens tussen de 1,5 en 4,5 gigabytes alleen al voor connectie-slots, vóór er ook maar één query is uitgevoerd.

Rails maakt dit erger omdat elke Puma worker zijn eigen connection pool heeft. Een bescheiden deployment van tien dyno’s keer vijf workers keer vijf threads is 250 connecties op piek, terwijl de meeste van die connecties het grootste deel van de tijd niets doen. De connecties worden tussen requests opengehouden via Rails’ ActiveRecord::ConnectionAdapters::ConnectionPool, en ze worden zelden volledig benut.

PgBouncer in transaction pooling mode doorbreekt deze 1:1-mapping. De Rails-app verbindt met PgBouncer, PgBouncer onderhoudt een veel kleinere pool van echte Postgres-connecties, en een Postgres-connectie wordt slechts uitgeleend voor de duur van één transactie. Een typische Rails-app met 250 client-connecties naar PgBouncer kan comfortabel draaien tegen 20 tot 40 echte Postgres-connecties, omdat op elk moment maar een fractie van de clients daadwerkelijk SQL uitvoert.

Dit is geen marginale optimalisatie. Het is het verschil tussen een Postgres-instance die twee gigabytes RAM gebruikt voor connecties en één die tweehonderd megabytes gebruikt.

De Drie PgBouncer Pooling Modes

PgBouncer biedt drie modes. Kies de verkeerde en je verliest óf connectie-efficiëntie óf je breekt je applicatie op subtiele manieren.

Session pooling wijst een Postgres-connectie toe aan een client voor de hele duur van de client-connectie. De poolgroottes zijn 1:1 met client-connecties, wat betekent dat je weinig wint. Session pooling is bruikbaar als je session-level features nodig hebt (temp tables, SET LOCAL, prepared statements beheerd door de server), maar het is niet de mode die je redt onder load.

Transaction pooling wijst een Postgres-connectie toe aan een client alleen voor de duur van een transactie. Op het moment dat de transactie commit of rollback krijgt, gaat de connectie terug naar de pool en kan aan een andere client uitgereikt worden. Dit is de high-leverage mode. Het breekt ook diverse dingen die Rails standaard verwacht, en daarom eindigen de meeste “we probeerden PgBouncer en onze app brak”-verhalen hier.

Statement pooling geeft de connectie terug na elke statement, zelfs binnen een transactie. Statement pooling is in wezen incompatibel met Rails omdat Rails alles in transacties wikkelt en aanneemt dat statements binnen een transactie op dezelfde backend draaien.

Voor Rails-apps is transaction pooling het antwoord. De rest van deze post gaat over hoe je transaction pooling laat werken zonder je applicatie te breken.

Prepared Statements: De Eerste Valkuil

Wanneer Rails een geparameteriseerde query uitvoert, vraagt het Postgres om een statement te prepareren en voert dat prepared statement vervolgens uit met de werkelijke parameters. Het prepared plan leeft op de Postgres-backend die het prepareerde. De volgende keer dat Rails dezelfde query uitvoert, draait het het eerder geprepareerde statement op naam — wat een parse- en planfase scheelt.

Dit is een prachtige optimalisatie in normale werking en een ramp achter PgBouncer transaction pooling. Het prepared statement leeft op een specifieke Postgres backend connection. De volgende query van dezelfde Rails-worker kan gerouteerd worden naar een andere backend die nog nooit van dat statement heeft gehoord. Je krijgt ERROR: prepared statement "a3" does not exist en een verwarde engineer die om 3 uur ‘s nachts gepiept wordt.

Twee manieren om dit op te lossen. De juiste hangt af van je PgBouncer-versie.

Als je op PgBouncer 1.21 of nieuwer zit, schakel server-side prepared statement support in en laat Rails’ prepared statements aan staan:

; pgbouncer.ini
[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

PgBouncer houdt prepared statements per client bij en speelt ze opnieuw af op welke backend hij ook routeert. Dit is het moderne antwoord en degene die ik standaard gebruik voor nieuwe deployments.

Als je op een oudere PgBouncer zit (managed services lopen hier vaak achter — check de documentatie van je provider), schakel prepared statements aan de Rails-kant uit:

# config/database.yml
production:
  adapter: postgresql
  prepared_statements: false
  advisory_locks: false

prepared_statements: false zorgt dat Rails de query-tekst elke keer meestuurt. Je verliest een kleine performance-optimalisatie. Je wint dat je niet gepiept wordt. Voor de meeste apps is de afweging duidelijk.

advisory_locks: false is om dezelfde reden belangrijk als prepared statements — Rails gebruikt advisory locks voor schema-migraties en with_advisory_lock-blokken, en PostgreSQL session-level advisory locks leven op een specifieke backend connection. Transactie-gebonden advisory locks (pg_advisory_xact_lock) werken prima via PgBouncer omdat ze vrijgegeven worden bij het einde van de transactie. Session-gebonden advisory locks niet.

Pools Sizen: Wiskunde Die Echt Werkt

Connection pool sizing is waar de meeste teams óf overprovisioneren en connecties verspillen, óf onderprovisioneren en requests in de wachtrij bij de pooler zetten. De wiskunde is niet ingewikkeld maar vraagt begrip van beide lagen.

De variabelen:

• N: aantal Rails-processen (dyno’s × Puma workers)
• T: threads per Puma worker
• C: ActiveRecord pool size per proces
• P: PgBouncer default_pool_size per database+user
• M: Postgres max_connections

Rails opent maximaal C connecties per proces. Het heeft C ≥ T nodig of threads gaan in de wachtrij voor connecties — je ziet dit als ActiveRecord::ConnectionTimeoutError. Ik begin met C = T + 1 en pas aan als ik timeouts zie.

PgBouncer’s per-database pool size P is het aantal echte Postgres-backends dat die database+user-combinatie bedient. Het juiste getal is empirisch afgeleid van je werkelijke transactieprofiel, maar een goed startpunt is P = 20 voor de meeste Rails-apps. Je kunt hier agressief zijn — onder transaction pooling kunnen twintig backends honderden client-connecties comfortabel bedienen als je transacties kort zijn.

De beperking die bijt is N × C ≤ PgBouncer max_client_conn en P × (aantal databases × users) ≤ M. Met één database en één user, P ≤ M − (gereserveerd voor admin, replicatie, etc.).

Uitgewerkt voorbeeld voor de app uit de opening:

• 16 dyno’s × 5 Puma workers = 80 processen
• 5 threads per worker, dus C = 6, totaal potentieel aantal client connecties = 480
• PgBouncer max_client_conn = 1000 (ruimte om te groeien)
• PgBouncer default_pool_size = 25
• Postgres max_connections = 100 (omlaag van 300)
• 25 backends bedienden 480 clients comfortabel; de pool-wachttijd bleef onder de 5ms op piek

De meest contra-intuïtieve les hier: je wilt bijna altijd minder Postgres-connecties dan je denkt. Postgres is een tuple-voor-tuple systeem; meer gelijktijdige connecties dan je CPU-aantal betekent meer context-switching, meer lock-contention en meer geheugendruk. Een moderne Postgres-instance met 8 CPU-cores is gelukkiger met 40 actieve connecties dan met 200.

Health Checks en Statement Timeouts

Transaction pooling gaat ervan uit dat transacties kort zijn. Als een transactie een connectie dertig seconden vasthoudt, kan die connectie dertig seconden lang geen andere clients bedienen, en je effectieve poolgrootte daalt. Eén langdraaiende migratie of één blijvend hangende transactie kan de hele app uithongeren.

Zet timeouts op elke laag:

; pgbouncer.ini
query_wait_timeout = 60    ; hoe lang een client wacht op een server-connectie
server_idle_timeout = 600  ; idle backends worden gesloten
server_lifetime = 3600     ; recycle backends na een uur

# config/database.yml
production:
  variables:
    statement_timeout: 30000              # 30s
    lock_timeout: 5000                    # 5s
    idle_in_transaction_session_timeout: 60000  # 60s

idle_in_transaction_session_timeout is degene die je redt van het “ik startte een transactie in een Rails-console twee uur geleden en liep weg”-scenario. Zonder dit houdt een vergeten transactie locks onbeperkt vast en kom je er pas achter wanneer writes beginnen op te stapelen.

Voor langdraaiende operaties — batchjobs, migraties, ETL — verbind direct met Postgres en omzeil PgBouncer, of gebruik een aparte PgBouncer-pool die voor dat specifieke doel in session mode draait.

LISTEN/NOTIFY en PgBouncer

Deze laat teams die ActionCable met de Postgres-adapter gebruiken, of welke gem dan ook die op Postgres pub/sub leunt, struikelen. LISTEN registreert een listener op een specifieke Postgres-backend. Onder transaction pooling kan de volgende oproep van dezelfde Rails-worker op een andere backend landen die niet luistert, en de NOTIFY bereikt je subscriber nooit.

Drie opties:

1. Gebruik Redis voor pub/sub in plaats van Postgres LISTEN/NOTIFY. Dit is wat ik standaard doe voor ActionCable in productie. De Postgres-adapter is prima voor development; Redis is prima voor productie.
2. Gebruik Solid Cable dat polling gebruikt in plaats van LISTEN/NOTIFY en zonder ceremonie via PgBouncer werkt.
3. Omzeil PgBouncer voor de connectie die LISTEN nodig heeft. Verbind direct met Postgres op een aparte database-URL alleen voor pub/sub-clients. Dit is wat ik aanbeveel als je een niet-Rails consumer hebt die echt push-semantiek nodig heeft.

Het verkeerde antwoord is “laat LISTEN/NOTIFY aan staan via PgBouncer en hoop”. Het werkt in tests omdat er maar één backend in de pool zit. Het faalt in productie wanneer de pool groeit.

Deployment Patronen Die Ik Vertrouw

Voor Heroku-achtige platforms draait de managed pgbouncer buildpack PgBouncer als sidecar in elke dyno. De Rails-app verbindt met localhost, PgBouncer stuurt door naar Postgres. Dit is de eenvoudigste deployment en degene waar ik als eerste naar grijp bij het migreren van een bestaande app.

Voor Kubernetes, draai PgBouncer als sidecar in elke Rails-pod, niet als gedeelde deployment. Sidecar-PgBouncer houdt connectie-routing lokaal en vermijdt een netwerkhop tussen Rails en de pooler. Het nadeel is meer totale PgBouncer-processen, maar elk is klein en ze coördineren prima omdat ze allemaal naar dezelfde Postgres wijzen.

Voor VPS / bare metal is één PgBouncer-instance per Postgres-primary, op hetzelfde netwerk als de database, de standaard. Voeg een tweede PgBouncer toe voor read replicas als je read replicas gebruikt.

Log voor elke deployment elke minuut PgBouncer-stats en alert op deze:

• cl_waiting > 5 langer dan een minuut (clients die in de wachtrij staan betekent dat je pool te klein is)
• sv_active consistent op default_pool_size (je pool is volledig verzadigd; transacties kunnen traag worden)
• avg_wait_us > 1000 (clients die langer dan een milliseconde wachten is een geurtje)

Ik heb een Datadog-dashboard-template die ik in elke Rails-met-PgBouncer-setup laat vallen die deze drie tracket. Het vangt problemen weken voordat ze incidenten worden.

Wanneer PgBouncer Het Verkeerde Antwoord Is

Niet elke Rails-app heeft PgBouncer nodig. Als je minder dan 50 totale connecties vanuit Rails draait, weegt de operationele kosten van PgBouncer niet op tegen de besparing. Als je transacties lang zijn (analytics-workloads, lange aggregaties), levert transaction pooling minder op omdat je backends sowieso even lang vasthoudt.

Als je zo weinig connecties hebt dat Postgres’ ingebouwde pooling volstaat, laat PgBouncer dan achterwege. Voeg het toe wanneer je de drempel overschrijdt waarbij Postgres-connectie-slots de beperking worden — ergens rond de 100 tot 200 actieve connecties vanuit Rails-workers — niet eerder.

Het andere geval waarin ik PgBouncer vermijd, is wanneer een app zwaar session-level state gebruikt: door de applicatie beheerde custom prepared statements, session-level advisory locks voor coördinatie, of LISTEN/NOTIFY als kernpatroon. De kosten om daar omheen te werken zijn soms hoger dan de kosten van het direct opschalen van Postgres-connecties. Weet wat je opgeeft voordat je de pooler toevoegt.

FAQ

Werkt PgBouncer met Rails prepared statements?

Ja, maar alleen op PgBouncer 1.21 of nieuwer met max_prepared_statements ingesteld. Op oudere PgBouncer-versies moet je Rails prepared statements uitschakelen met prepared_statements: false in database.yml. Anders zie je ERROR: prepared statement does not exist omdat het prepared plan op een specifieke Postgres-backend leeft waar de volgende query mogelijk niet naartoe gerouteerd wordt.

Welke pool size moet ik gebruiken voor PgBouncer met Rails?

Begin met default_pool_size = 20 voor de meeste Rails-apps. Het juiste getal is empirisch afgeleid van je workload, maar twintig backends kunnen onder transaction pooling honderden client-connecties comfortabel bedienen omdat Rails-transacties doorgaans kort zijn. Contra-intuïtief presteren kleinere Postgres-connectie-aantallen vaak beter dan grotere, omdat Postgres een tuple-voor-tuple systeem is dat lijdt onder te veel concurrency.

Waarom krijgt mijn Rails-app “prepared statement does not exist”-fouten met PgBouncer?

Dit gebeurt wanneer Rails prepared statements aan staan en PgBouncer in transaction pooling mode draait op een versie ouder dan 1.21. Het prepared statement leeft op een specifieke Postgres backend connection; de volgende query kan gerouteerd worden naar een andere backend die dat statement nog nooit gezien heeft. Los het op door óf PgBouncer te upgraden en max_prepared_statements te zetten, óf door prepared_statements: false in database.yml te zetten.

Kan ik ActionCable gebruiken met PgBouncer in transaction pooling mode?

Niet met de standaard Postgres-adapter, omdat LISTEN/NOTIFY een persistente sessie op een specifieke backend vereist. Gebruik Solid Cable dat polt in plaats van LISTEN/NOTIFY te gebruiken, schakel over naar Redis voor ActionCable pub/sub, of omzeil PgBouncer voor de specifieke connectie die LISTEN nodig heeft.

Hulp nodig bij het sizen van een Rails Postgres-deployment of bij een connection pool die zich in productie misdraagt? TTB Software is gespecialiseerd in Rails performance en infrastructuur. Wij doen dit al negentien jaar.