PgBouncer en Rails: Connection Pooling Zonder Databasecrashes

De melding kwam op een dinsdagmiddag. De Rails-app van een klant—de hele ochtend nog prima gedraaid—begon over de hele linie PG::ConnectionBad: FATAL: sorry, too many clients already te gooien. Hun Heroku dynos waren door een verkeerspiek automatisch opgeschaald van 4 naar 12. Elke dyno draaide Puma met 5 threads. Elke thread houdt een databaseverbinding open. Dat zijn 60 verbindingen. Hun Postgres-plan stond er 25 toe.

Rekenen: fataal.

Na negentien jaar Rails heb ik dit patroon meer keren gezien dan ik kan tellen. De oplossing is PgBouncer. De valkuil is denken dat PgBouncer simpel is.

Waarom Rails Connection Pooling Niet Genoeg Is

Rails heeft ingebouwde connection pooling via ActiveRecord. De instelling pool in database.yml bepaalt hoeveel verbindingen elk proces aanhoudt. Maar dit is een per-proces pool. Elke Puma-worker, elk Sidekiq-proces, elke job-runner—allemaal houden ze hun eigen verbindingen open naar Postgres.

Op kleine schaal is dat prima. Op grotere schaal doe je de vermenigvuldiging:

10 Puma-workers × 5 threads = 50 verbindingen
4 Sidekiq-processen × 10 concurrency = 40 verbindingen
Totaal: 90 verbindingen, nog voordat je ook maar één query hebt gedraaid

Verbindingen zijn niet gratis in Postgres. Elke verbinding spawnt een backendproces op de server, met een geheugenverbruik van ruwweg 5–10 MB in de praktijk. Een Postgres-instantie met max_connections = 100 kan zichzelf uitputten voordat je applicatie serieus verkeer te verwerken krijgt.

PgBouncer plaatst zich tussen je applicatie en Postgres en hergebruikt verbindingen. Zodra een Rails-thread klaar is met een query, geeft PgBouncer die fysieke verbinding terug aan een pool—beschikbaar voor het volgende verzoek, ook als dat uit een volledig ander proces komt.

De Drie Modi

PgBouncer biedt drie pooling-modi. De verkeerde kiezen leidt tot subtiele, frustrerende bugs.

Session pooling: Een client krijgt een serververbinding voor de duur van zijn sessie. Dit helpt Rails-apps nauwelijks; Rails-verbindingen zijn van nature langlevend.

Transaction pooling: Een client krijgt een serververbinding voor de duur van één transactie. Na COMMIT of ROLLBACK keert de verbinding terug naar de pool. Dit is wat je wilt voor Rails.

Statement pooling: Één verbinding per statement. Gebruik dit nooit met Rails. Het werkt niet met transacties die meerdere statements bevatten en breekt op manieren die echt moeilijk te diagnosticeren zijn.

Gebruik transaction pooling.

PgBouncer Installeren

Op Ubuntu/Debian:

apt-get install pgbouncer

Als je Kamal gebruikt, voeg je het toe als service in je deploy.yml:

accessories:
  pgbouncer:
    image: bitnami/pgbouncer:latest
    host: db-proxy.yourdomain.com
    env:
      clear:
        POSTGRESQL_HOST: your-postgres-host
        POSTGRESQL_PORT: "5432"
        PGBOUNCER_POOL_MODE: transaction
        PGBOUNCER_MAX_CLIENT_CONN: "1000"
        PGBOUNCER_DEFAULT_POOL_SIZE: "20"
        PGBOUNCER_DATABASE: "*"
    ports:
      - "6432:6432"

Het pgbouncer.ini-bestand

Als je PgBouncer direct configureert in plaats van via omgevingsvariabelen:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp_production

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
log_connections = 0
log_disconnections = 0

De belangrijkste instellingen:

max_client_conn: Hoeveel clientverbindingen PgBouncer in totaal accepteert. Zet dit hoog (1000+). Ze zijn goedkoop.
default_pool_size: Hoeveel echte Postgres-verbindingen PgBouncer per database/gebruiker-combinatie aanhoudt. Dit is het dure getal—stem het af op je Postgres max_connections.
reserve_pool_size: Extra verbindingen in reserve voor verkeerspieken.

Koppelen aan Rails

Wijs database.yml naar PgBouncer in plaats van rechtstreeks naar Postgres:

production:
  adapter: postgresql
  host: pgbouncer-host
  port: 6432
  database: myapp_production
  username: myapp
  password: <%= ENV["DB_PASSWORD"] %>
  pool: 5
  prepared_statements: false
  advisory_locks: false

Die laatste twee instellingen zijn verplicht in transaction mode. Sla je ze over, dan krijg je een vervelende dag.

De Valkuilen van Transaction Mode

Transaction pooling is krachtig en zal je volledig verrassen als je niet begrijpt wat het kapotmaakt.

Prepared statements. Standaard slaat Rails query-plannen op aan de serverzijde via prepared statements. In session mode werkt dit prima—elke client heeft zijn eigen serververbinding. In transaction mode krijg je elke keer een andere serververbinding. Prepared statements van verbinding A zijn niet zichtbaar op verbinding B. Rails gooit intermitterende PG::InvalidSqlStatementName-fouten, onder load, wanneer je het het minst verwacht.

Oplossing: prepared_statements: false in database.yml.

Advisory locks. Rails gebruikt intern Postgres advisory locks—onder andere voor migraties. Advisory locks zijn sessiegebonden: ze horen bij een specifieke backendverbinding. Met transaction pooling heb je niet de garantie dat je dezelfde backend krijgt tussen aanroepen door.

Oplossing: advisory_locks: false in database.yml. Rails valt terug op een lock-tabel in de database.

SET-variabelen. Als je SET LOCAL of SET gebruikt voor sessiegebonden parameters—search_path, tijdzone, wat dan ook—dan blijven die niet bewaard over transacties heen wanneer PgBouncer de onderliggende verbinding roteert. De instelling verdwijnt zodra de transactie committed.

Als je per-request configuratie nodig hebt, stel die dan in aan het begin van elke transactie, niet eenmalig bij het openen van de verbinding.

LISTEN/NOTIFY. Asynchrone notificaties zijn sessiegebonden. Gebruik LISTEN niet via PgBouncer. Als je Action Cable met een Postgres-adapter gebruikt, wijs die dan rechtstreeks naar Postgres, PgBouncer omzeilend:

# config/cable.yml
production:
  adapter: postgresql
  # Rechtstreeks naar Postgres, niet via PgBouncer
  url: <%= ENV["DIRECT_DATABASE_URL"] %>

Je Pool Monitoren

Zodra PgBouncer draait, verbind je met de adminconsole om te zien wat er gebeurt:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

Vervolgens:

SHOW POOLS;

Je ziet cl_active (clientverbindingen in gebruik), sv_active (serververbindingen in gebruik) en cl_waiting (clients die wachten op een verbinding). Als cl_waiting structureel boven nul zit, is je default_pool_size te klein.

SHOW STATS;

Toont aanvraagsnelheden, gemiddelde querytijden en totale doorgevoerde bytes. Nuttig voor het opsporen van anomalieën en capaciteitsplanning.

Koppel deze metrics aan je observability-stack. De pgbouncer_exporter voor Prometheus werkt goed en maakt alles uit SHOW POOLS beschikbaar als Prometheus-metrics waar je op kunt alerteren.

default_pool_size Afstellen

Dit is het getal dat er echt toe doet. Te laag instellen leidt tot verbindingswachtrijen onder load. Te hoog instellen put je Postgres max_connections uit.

Een redelijk startpunt:

default_pool_size = (max_connections - superuser_reserved_connections) / aantal_pools

Als Postgres max_connections = 100 heeft, je 3 reserveert voor superuser-verbindingen, en je één applicatiedatabase hebt, kom je op 97. Laat in de praktijk meer marge—je wilt ruimte voor directe verbindingen tijdens migraties en beheerwerkzaamheden.

Voor een typische productieapp op een bescheiden Postgres-instantie:

Postgres: max_connections = 100
PgBouncer: default_pool_size = 20, reserve_pool_size = 5
Rails/Puma: pool: 5
Sidekiq: concurrency: 10

Dit betekent dat je applicatie honderden virtuele verbindingen kan hebben die 20 fysieke verbindingen delen. Op elk moment draaien er niet meer dan 20 queries tegelijk op Postgres—wat voor de meeste workloads ruimschoots voldoende is.

De pool-instelling in database.yml Blijft Relevant

Iets wat mensen vaak missen: ook met PgBouncer bepaalt je Rails pool-instelling hoeveel verbindingen elk proces aanhoudt naar PgBouncer. Stel die gelijk aan je Puma thread-count. Als Puma 5 threads draait, stel pool: 5 in. Hoger instellen verspilt alleen resources.

production:
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

De pool-waarde afleiden uit dezelfde omgevingsvariabele die Puma’s concurrency bepaalt is idiomatisch en correct. Verander je de concurrency, dan volgt de poolgrootte automatisch.

De Setup Testen

Verifieer voor je naar productie gaat dat de configuratie daadwerkelijk werkt:

# Voer dit meerdere keren uit in de rails console
ActiveRecord::Base.connection_pool.with_connection do |conn|
  result = conn.execute("SELECT pg_backend_pid()")
  puts result.first["pg_backend_pid"]
end

Met PgBouncer in transaction mode zou je verschillende PIDs moeten zien bij opeenvolgende aanroepen. Zie je steeds dezelfde PID, dan ga je ofwel niet via PgBouncer (controleer de poort—6432 vs 5432) of je zit in session mode.

Je kunt ook verifiëren dat de valkuilen correct zijn uitgeschakeld:

conn = ActiveRecord::Base.connection
puts conn.prepared_statements  # moet false zijn

Wanneer PgBouncer Niet Genoeg Is

PgBouncer lost verbindingsuitputting prachtig op. Maar het helpt niet als je queries traag zijn. Als elk verzoek 500ms wacht op een query, put je je pool toch nog uit—alleen langzamer.

PgBouncer geeft je speelruimte. Het vervangt geen goede queryprestaties. Die twee problemen vragen aparte oplossingen: PgBouncer voor verbindingsbeheer, goede indexen en queryoptimalisatie voor doorvoer.

De dinsdagmiddagklant waar ik mee begon? We voegden PgBouncer toe, brachten hun fysieke verbindingsaantal terug van 60 naar 15, en hun applicatie verwerkte de verkeersspiek zonder hapering. Daarna keken we naar hun slow query log. Dat is een apart verhaal.

Veelgestelde Vragen

Heb ik PgBouncer nog nodig als ik een beheerde Postgres-service gebruik met hoge verbindingslimieten?

Hogere limieten betekenen alleen dat je Postgres trager uitput. Je wilt PgBouncer nog steeds voor het daadwerkelijke hergebruik van verbindingen—het vermindert de geheugendruk op de Postgres-server en laat je applicatieprocessen vrij schalen. Sommige beheerde services (Heroku, Supabase) bieden PgBouncer als ingebouwde feature. Gebruik het.

Kan ik PgBouncer gebruiken met Rails-databasemigraties?

Migraties hebben advisory locks nodig, die sessiegebonden zijn. Draai migraties ofwel met een directe Postgres-verbinding (stel DATABASE_URL in om PgBouncer te omzeilen tijdens deploys), of houd advisory_locks: false en laat Rails zijn fallback-mechanisme gebruiken. De meeste teams gebruiken een aparte DATABASE_URL voor migraties en de PgBouncer-URL voor de applicatie.

Werkt PgBouncer met Rails multi-db?

Ja, maar je hebt een PgBouncer-pool nodig voor elke database. Definieer meerdere entries in de sectie [databases] van pgbouncer.ini, één per Rails-database. Elk krijgt zijn eigen default_pool_size.

Wat is de performance-overhead van PgBouncer zelf?

Minimaal. PgBouncer is geschreven in C en verwerkt tienduizenden verbindingen per seconde op bescheiden hardware. Het voegt minder dan 1ms latentie toe voor verbindingsroutering. De afweging is het waard op elke serieuze schaal.

Problemen met verbindingsuitputting of databaseprestaties? TTB Software is gespecialiseerd in Rails-infrastructuur en Postgres-tuning. Negentien jaar in de loopgraven—we hebben dit exacte probleem meer keren opgelost dan we kunnen tellen.