Prestaties en queryoptimalisatie in PostgreSQL · Les

Waarom verbindingen duur zijn in PostgreSQL

Begrijp de geheugen- en planningskosten per backend waardoor connection pooling op schaal essentieel is.

Les 1 van 413 stappen

Waarom verbindingen duur zijn in PostgreSQL is een gratis Prestaties en queryoptimalisatie in PostgreSQL-les op CoddyKit. Dit is les 1 van 4. Je kunt 3 lessen uit dit leerpad gratis volledig lezen — daarna ontgrendelt CoddyKit PRO alle lessen, plus praktische oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject Prestaties en queryoptimalisatie in PostgreSQL. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.

Eén verbinding, één proces

PostgreSQL gebruikt een model met één proces per verbinding. Elke clientverbinding wordt afgehandeld door een eigen toegewijd besturingssysteemproces, een backend genoemd, dat wordt afgesplitst van de postmaster zodra de verbinding wordt geaccepteerd.

Dit ontwerp is robuust en eenvoudig, maar heeft wel echte kosten: een proces is veel zwaarder dan een thread. In tegenstelling tot databases die veel sessies verdelen over een threadpool, betaalt PostgreSQL voor elke geopende verbinding een prijs per proces, ongeacht of die actief een query uitvoert of niets doet.

Je kunt in de catalogus rechtstreeks één backend per verbinding zien:

SELECT pid, usename, application_name, state
FROM pg_stat_activity
WHERE backend_type = 'client backend';

De kosten van afsplitsen

Een verbinding openen is niet gratis. Voor elke nieuwe backend moet PostgreSQL:

Een nieuw besturingssysteemproces afsplitsen van de postmaster
Verbinding maken met gedeeld geheugen en de lokale geheugencontext instellen
De client authenticeren en de database en rol valideren
Bij de eerste toegang catalogus- en relatiecache-items laden

Deze voorbereiding kan enkele milliseconden duren voordat één query wordt uitgevoerd. Een toepassing die voor elk HTTP-verzoek een verbinding opent en sluit, betaalt deze heffing duizenden keren per minuut. Zo veranderen steeds wisselende verbindingen in meetbare extra latentie en CPU-belasting.

Geheugen per backend wordt niet gedeeld

Naast de gedeelde bufferpool reserveert elke backend zijn eigen privégeheugen. De belangrijkste instellingen per verbinding zijn lokaal voor elke sessie:

work_mem — geheugen voor elke sorteer-, hash- of groepsbewerking
temp_buffers — geheugen voor tijdelijke tabellen
Catalogus- en plancaches die groeien naarmate de sessie meer objecten gebruikt

Belangrijk is dat work_mem wordt toegewezen per bewerking, per verbinding. Eén complexe query met meerdere sorteringen en hash-joins kan tegelijkertijd meerdere keren work_mem gebruiken.

SHOW work_mem;
SHOW temp_buffers;
SHOW shared_buffers;

Waarom work_mem zich vermenigvuldigt

Het gevaar van work_mem is dat dit geen limiet per verbinding is — het is een toewijzing per bewerking. Een plan met drie sorteringen en twee hash-joins kan vijf keer tegelijk om work_mem vragen.

Schat het slechtste geval globaal als volgt:

peak_RAM ≈ max_connections × work_mem × avg_operations_per_query

Met max_connections = 500, work_mem = 16MB en enkele sorteringen per query kun je theoretisch tientallen gigabytes aan tijdelijk geheugen bereiken — nog voordat je gedeelde buffers of de paginacache van het besturingssysteem meetelt.

-- Rough back-of-envelope ceiling
SELECT
  current_setting('max_connections')::int AS max_conn,
  current_setting('work_mem') AS work_mem,
  current_setting('max_connections')::int
    * (pg_size_bytes(current_setting('work_mem')) / 1024 / 1024)
    AS naive_worst_case_mb;

Inactieve verbindingen kosten nog steeds iets

Een veelvoorkomende misvatting is dat een inactieve verbinding niets kost. Dat is niet zo. Zelfs een backend die niets doet:

Houdt een procesplek en de privégeheugencaches ervan bezet
Neemt een plek in die meetelt voor max_connections
Moet worden bezocht door achtergrondscans van pg_stat_activity en door snapshotlogica
Kan, als de status idle in transaction is, oude rijversies vasthouden en vacuum blokkeren

Het opsporen van lang bestaande inactieve sessies en sessies die in een transactie inactief zijn, is een van de eerste zaken die je op een slecht presterende server moet controleren:

SELECT pid, state, wait_event_type,
       now() - state_change AS idle_for
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction')
ORDER BY idle_for DESC;

Snapshots en de zichtbaarheidsheffing

Het MVCC-model van PostgreSQL betekent dat elke query een snapshot maakt van de transacties die zichtbaar zijn. Voor het opbouwen en bijhouden van die snapshot moet de lijst met momenteel actieve backends worden gescand.

Deze administratie wordt duurder naarmate het aantal verbindingen groeit. Historisch gezien (vóór PostgreSQL 14) schaaldе GetSnapshotData() met het totale aantal verbindingen, waardoor duizenden grotendeels inactieve backends overhead toevoegden aan elke actieve transactie.

De les: meer verbindingen gebruiken niet alleen meer geheugen — ze maken ook het gedeelde coördinatiewerk zwaarder voor iedereen.

De grens van CPU-planning

Backends zijn echte besturingssysteemprocessen, dus de kernelplanner moet ze in tijdsblokken over je CPU-kernen verdelen. Wanneer het aantal uitvoerbare backends veel groter is dan het aantal kernen, bereik je een grens:

Meer contextwisselingen verbruiken CPU aan overhead in plaats van aan querywerk
De cachelokaliteit neemt af doordat processen over kernen heen en weer worden geschoven
Conflicten om vergrendelingen op gedeelde structuren nemen toe met de gelijktijdigheid

Daarom bereikt de doorvoer vaak eerst een piek en neemt daarna af wanneer de gelijktijdigheid het aantal kernen overschrijdt. Een machine met 16 kernen heeft zelden voordeel van 400 gelijktijdig actieve queries.

SELECT count(*) AS active_queries
FROM pg_stat_activity
WHERE state = 'active'
  AND backend_type = 'client backend';

max_connections realistisch instellen

Het is verleidelijk om max_connections zeer hoog in te stellen "voor de zekerheid", maar dat werkt averechts. Elke mogelijke verbinding reserveert administratie in het gedeelde geheugen en verhoogt de druk op geheugen en planning.

Een praktische vuistregel voor een CPU-gebonden werklast is ongeveer:

active_connections ≈ cores × 2 to cores × 4

Je wilt max_connections net hoog genoeg instellen voor de werkelijke gelijktijdigheid plus wat reserve — niet om duizenden threads van de toepassing op te vangen die elk een rechtstreekse verbinding openen.

SHOW max_connections;

SELECT count(*) AS current_connections
FROM pg_stat_activity;

Waar het geld naartoe gaat: een klein model

Hier is een zelfstandige manier om de limiet per verbinding te berekenen met alleen constanten. Hiermee schat je het maximale tijdelijke geheugen voor query's voor een hele verzameling verbindingen — je hebt geen server of tabellen nodig, dus je kunt dit overal uitvoeren.

Het doel is om de vermenigvuldiging zichtbaar te maken: het aantal verbindingen maal het geheugen per bewerking maal het aantal bewerkingen per query is het getal dat mensen vaak verrast.

WITH params AS (
  SELECT 400  AS max_conn,
         16   AS work_mem_mb,
         3    AS avg_ops_per_query,
         8192 AS shared_buffers_mb
)
SELECT
  max_conn,
  work_mem_mb,
  avg_ops_per_query,
  max_conn * work_mem_mb * avg_ops_per_query AS worst_case_query_mb,
  shared_buffers_mb
    + max_conn * work_mem_mb * avg_ops_per_query AS total_ceiling_mb
FROM params;

De oplossing: poolen, niet vermenigvuldigen

De oplossing voor al deze kosten is connection pooling. In plaats van elke applicatiedraad een eigen onbewerkte backend te geven, houdt een pooler een kleine set actieve PostgreSQL-verbindingen beschikbaar en multiplexeert deze veel clients daarover.

Backends worden hergebruikt, dus de kosten van forken, authenticatie en het opwarmen van de cache worden één keer betaald, niet per verzoek
Actieve backends blijven ongeveer gelijk aan het aantal CPU-kernen, zodat je de planningslimiet vermijdt
Het totale private geheugen wordt begrensd door de poolgrootte, niet door het aantal clients

PgBouncer is precies daarom de standaard lichtgewicht pooler — dit is het onderwerp van de rest van deze cursus.

Verbindingsdruk vaststellen

Meet eerst waar je staat voordat je een pool afstemt. Een snelle gezondheidsopname groepeert de huidige sessies op status, zodat je kunt zien hoeveel er echt actief zijn en hoeveel inactief.

Als je honderden inactieve verbindingen en slechts een handvol actieve verbindingen ziet, betaal je de volledige geheugenkosten per backend en planningskosten voor capaciteit die je nooit gebruikt — een schoolvoorbeeld van een situatie waarin poolen nuttig is.

SELECT state,
       count(*) AS sessions,
       max(now() - state_change) AS oldest
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY sessions DESC;

Korte controle

Test je begrip van de kosten per verbinding in PostgreSQL.

Beantwoord dit in de interactieve les

Samenvatting: waarom verbindingen duur zijn

Belangrijkste punten uit deze les:

PostgreSQL gebruikt een model met één proces per verbinding — elke verbinding is een geforkte OS-backend, geen goedkope draad.
Bij het openen van een verbinding betaal je echte kosten voor forken, authenticatie en het opwarmen van de cache voordat er een query wordt uitgevoerd.
work_mem en temp_buffers gelden per backend en per bewerking, waardoor het geheugen toeneemt met het aantal verbindingen en bewerkingen per query.
Inactieve verbindingen houden nog steeds slots en geheugen vast en voegen overhead voor snapshots toe; inactieve verbindingen binnen een transactie kunnen vacuum blokkeren.
Te veel actieve backends veroorzaken contextwisselingen en lock-concurrentie, waardoor de doorvoer rond het aantal CPU-kernen zijn maximum bereikt.
De oplossing is connection pooling (PgBouncer): hergebruik een kleine set actieve backends in plaats van één backend per client.

Gratis beginnen

Leer SQL met een AI-tutor — gratis

Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.

Cursussen: 22
Lessen: 88

Gratis beginnen met leren Bekijk de volledige cursus

Veelgestelde vragen

Is de les “Waarom verbindingen duur zijn in PostgreSQL” gratis?

Ja — je kunt hier op het web alle 3 lessen van het leerpad Prestaties en queryoptimalisatie in PostgreSQL, waaronder “Waarom verbindingen duur zijn in PostgreSQL”, gratis volledig lezen. Daarna ontgrendelt CoddyKit PRO alle lessen, plus interactieve oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.

Wat leer ik in “Waarom verbindingen duur zijn in PostgreSQL”?

Begrijp de geheugen- en planningskosten per backend waardoor connection pooling op schaal essentieel is. Je oefent met Prestaties en queryoptimalisatie in PostgreSQL door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.

Heb ik ervaring nodig om met Prestaties en queryoptimalisatie in PostgreSQL te beginnen?

Ervaring vooraf is niet nodig. Prestaties en queryoptimalisatie in PostgreSQL op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 1 van 4.

Hoe lang duurt de les “Waarom verbindingen duur zijn in PostgreSQL”?

De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.

Kan ik code schrijven en uitvoeren in deze les over Prestaties en queryoptimalisatie in PostgreSQL?

Ja. Elke les over Prestaties en queryoptimalisatie in PostgreSQL bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.

Alle lessen in deze cursus

Waarom verbindingen duur zijn in PostgreSQL
Transaction- versus session-poolingmodi
Pools afstemmen op het aantal cores
Verzadiging en wachtrijen in pools diagnosticeren

← Terug naar Prestaties en queryoptimalisatie in PostgreSQL