Prestaties en queryoptimalisatie in PostgreSQL · Les

Lees- en analysetaken uitbesteden

Stuur zwaar rapportageverkeer naar logische replica’s om de OLTP-latentie van de primary te beschermen.

Les 2 van 413 stappen

Lees- en analysetaken uitbesteden is een gratis Prestaties en queryoptimalisatie in PostgreSQL-les op CoddyKit. Dit is les 2 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.

Waarom leesbewerkingen überhaupt offloaden?

Op een drukke PostgreSQL-primary bedient dezelfde instantie zowel OLTP-verkeer (kleine, snelle, latentiegevoelige schrijfbewerkingen en gerichte leesbewerkingen) als analytisch verkeer (grote sequentiële scans, aggregaties en rapportage-joins).

Het probleem: één zware rapportagequery kan gedeelde buffers volledig vullen, veelgebruikte OLTP-pagina's verdringen en de diepte van de I/O-wachtrij vergroten. Je p99-latentie voor het afrekenen verdubbelt plotseling omdat iemand een kwartaalomzetrapport uitvoert.

Doel: houd de primary slank voor transactioneel werk.
Strategie: stuur zware lees- en analytische query's naar een replica die een kopie van de gegevens bevat.

Deze les richt zich op het gebruik van logische replicatie om doelgericht ingerichte leesreplica's voor analyses te bouwen.

Fysieke versus logische replicatie

PostgreSQL biedt twee replicatiemodellen, en de juiste keuze maken is de belangrijkste beslissing voor het ontlasten van leesbewerkingen.

Fysieke (streaming)replicatie: verstuurt WAL byte voor byte. De replica is een exacte kloon op blokniveau — hetzelfde schema, dezelfde indexen, dezelfde bloat. Ideaal voor HA en het opschalen van leesbewerkingen voor identieke query's.
Logische replicatie: decodeert WAL naar wijzigingen op rijniveau (INSERT/UPDATE/DELETE) en speelt die af via SQL. De abonnee is een onafhankelijke database — je kunt andere indexen, extra kolommen, gematerialiseerde totalen of zelfs een andere hoofdversie toevoegen.

Voor het ontlasten van analyses is logische replicatie bijzonder geschikt: het analyseknooppunt kan zware rapportage-indexen bevatten die de primaire database nooit hoeft te onderhouden.

Een publicatie instellen

Op de primaire database (de uitgever) moet je wal_level = logical instellen en een publicatie maken — een benoemde verzameling tabellen waarvan de wijzigingen worden gestreamd.

Je kunt alle tabellen, een deelverzameling of zelfs gefilterde rijen en kolommen publiceren (PostgreSQL 15+). Voor het ontlasten van analyses publiceer je meestal precies de feit- en dimensietabellen die de rapporten nodig hebben.

-- On the primary: declare what gets replicated
-- (requires wal_level = logical in postgresql.conf)
CREATE PUBLICATION analytics_pub
  FOR TABLE orders, order_items, customers
  WITH (publish = 'insert, update, delete');

-- Inspect existing publications
SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete
FROM pg_publication;

De abonnee maken

Maak op een afzonderlijke host — je analysereplica — een overeenkomend schema (minstens de gepubliceerde tabellen) en daarna een abonnement. Het abonnement maakt verbinding met de uitgever en start met een eerste gegevenskopie, gevolgd door continue streaming.

Omdat de abonnee onafhankelijk is, kun je deze de configuratie geven die analyses nodig hebben: meer work_mem, meer maintenance_work_mem en instellingen die geschikt zijn voor parallelle verwerking — zonder de OLTP-primaire database aan te raken.

-- On the analytics node: subscribe to the primary's publication
CREATE SUBSCRIPTION analytics_sub
  CONNECTION 'host=primary.db port=5432 dbname=shop user=repl password=secret'
  PUBLICATION analytics_pub
  WITH (copy_data = true, streaming = on);

-- Watch initial sync + streaming status
SELECT subname, srrelid::regclass AS rel, srsubstate
FROM pg_subscription_rel
JOIN pg_subscription ON oid = srsubid;

Alleen voor analyses bedoelde indexen op de replica toevoegen

Hier komt de meerwaarde van logische replicatie naar voren. De primaire database behoudt alleen de sobere indexen die OLTP nodig heeft. De analyse-abonnee voegt brede, dure indexen toe die elke schrijfbewerking op de primaire database zouden vertragen, maar rapporten op de replica versnellen.

BRIN-indexen voor enorme, alleen-aanvullende tijdreeks-feitentabellen.
Dekkende/gedeeltelijke indexen die zijn afgestemd op dashboardquery's.
Expressie-indexen voor predicates die specifiek voor rapporten zijn.

Het onderhouden hiervan kost het analyseknooppunt extra schrijfbewerkingen — maar de primaire database betaalt daar nooit voor.

-- These indexes live ONLY on the analytics subscriber
CREATE INDEX brin_orders_created
  ON orders USING brin (created_at) WITH (pages_per_range = 32);

CREATE INDEX idx_orders_report
  ON orders (customer_id, created_at)
  INCLUDE (total_amount, status)
  WHERE status <> 'cancelled';

Leesbewerkingen op applicatieniveau routeren

Ontlasten helpt alleen als het verkeer daadwerkelijk de replica bereikt. Je routeert op de applicatie- of proxyniveau:

Splitsing op verbindingsniveau: een pool voor lezen en schrijven naar de primaire database, en een alleen-lezenpool naar het analyseknooppunt.
Op basis van een proxy: PgBouncer/Pgpool of een servicemesh stuurt rapportageverkeer met SELECT door op basis van gebruiker, schema of querytag.

Een helder patroon is een speciale analytics_ro-databaserol met statement-time-outs en een lagere prioriteit, zodat een ontspoord rapport nooit de transactionele SLA's in gevaar kan brengen.

-- Give reporting sessions a safety harness on the replica
ALTER ROLE analytics_ro SET statement_timeout = '120s';
ALTER ROLE analytics_ro SET work_mem = '256MB';
ALTER ROLE analytics_ro SET default_transaction_read_only = on;

-- Confirm where a session actually landed (run on each node)
SELECT pg_is_in_recovery() AS is_replica, current_setting('work_mem');

Vooraf totalen berekenen met gematerialiseerde weergaven

De replica is de ideale plek voor gematerialiseerde weergaven die dure totalen vooraf berekenen. Dashboards lezen dan een kleine samenvattingstabel in plaats van bij elke keer laden miljoenen feitenrijen te doorzoeken.

Ververs ze volgens een schema op het analyseknooppunt. Met REFRESH MATERIALIZED VIEW CONCURRENTLY voorkom je dat lezers tijdens het opnieuw opbouwen worden geblokkeerd (hiervoor is een unieke index op de weergave vereist).

-- Lives on the analytics replica only
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       count(*)            AS order_count,
       sum(total_amount)   AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1;

CREATE UNIQUE INDEX ON daily_revenue (day);

-- Scheduled refresh that does not block dashboard readers
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Replicatieachterstand begrijpen

Logische replicatie is standaard asynchroon: de abonnee loopt met enige vertraging achter op de primaire database. Voor analyses is dat vrijwel altijd prima — een rapport met gegevens die twee seconden oud zijn, is acceptabel. Voor UI-stromen met "je eigen schrijfbewerking teruglezen" is dat niet zo.

Beslisregel: stuur eventueel consistente, aggregatiezware leesbewerkingen naar de replica; houd leesbewerkingen na schrijven en leesbewerkingen voor de exact actuele status op de primaire database.

Controleer de achterstand vanuit het replicatieslot van de uitgever — een vastgelopen abonnee zorgt ervoor dat WAL zich ophoopt en kan de schijf van de primaire database vullen.

-- On the primary: how far behind is each logical slot?
SELECT slot_name,
       active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
       ) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Beperken tot wat analyses nodig hebben

Je hebt zelden elke kolom of rij nodig op het analyseknooppunt. Met PostgreSQL 15+ kan een publicatie rijfilters en kolomlijsten toepassen, waardoor de gerepliceerde gegevensset en de kosten van WAL-decodering kleiner worden.

Rijfilter: alleen rijen repliceren die relevant zijn voor rapportage (bijvoorbeeld niet-gearchiveerde bestellingen).
Kolomlijst: brede of gevoelige kolommen uitsluiten (PII, grote JSON-blobs) die de rapporten nooit gebruiken.

Een kleinere gerepliceerde voetafdruk betekent een snellere eerste synchronisatie en minder netwerk- en decodeeroverhead.

-- Publish only relevant rows and a subset of columns (PG 15+)
CREATE PUBLICATION analytics_pub
  FOR TABLE orders (id, customer_id, created_at, total_amount, status)
    WHERE (status <> 'draft' AND created_at >= '2024-01-01');

Controleren of het plan de indexen van de replica gebruikt

Controleer na het maken van analytische indexen en gematerialiseerde weergaven op de abonnee of de planner ze daadwerkelijk gebruikt. Voer EXPLAIN (ANALYZE, BUFFERS) op het analyseknooppunt uit voor je echte rapportagequery's.

Je wilt indexscans/bitmapscans op de rapportagespecifieke indexen zien en idealiter zien dat de dashboardquery de gematerialiseerde weergave gebruikt in plaats van de onbewerkte feitentabel. Vergelijk de buffers voor shared read vóór en na — die afname is de I/O die je van de primaire database hebt weggehaald.

-- Run on the analytics replica, not the primary
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT day, revenue
FROM daily_revenue
WHERE day >= current_date - interval '30 days'
ORDER BY day;

Operationele veiligheidsmaatregelen

Een architectuur voor het ontlasten van leesbewerkingen is maar zo goed als de foutafhandeling ervan. Bouw veiligheidsmaatregelen in:

Meldingen over achterstand: stuur een alarm wanneer retained_wal of de toepasachterstand een drempel overschrijdt — een dode abonnee kan via het slot de schijf van de primaire database vullen.
Sequenties en DDL worden niet gerepliceerd: logische replicatie kopieert rijgegevens, geen sequentiewaarden of schemamutaties. Beheer DDL bewust aan beide kanten.
Let op conflicten: laat applicaties nooit schrijven naar gerepliceerde tabellen op de abonnee, anders mislukt het toepassen.
Capaciteit: het analyseknooppunt kan kleiner zijn qua aantal verbindingen, maar heeft RAM en schijfruimte nodig voor de extra indexen en gematerialiseerde weergaven.

Snelle controle: het doel voor ontlasting kiezen

Een dashboard voert elke keer dat een manager het opent een aggregatie van 9 seconden uit over 200 miljoen bestelrijen, waardoor de latentie van het afrekenen via OLTP op de primaire database in het gedrang komt. Het rapport verdraagt gegevens die enkele seconden verouderd zijn. Wat is de beste aanpak?

Beantwoord dit in de interactieve les

Samenvatting: leesbewerkingen ontlasten met logische replicatie

Je hebt geleerd hoe je de latentie van primaire OLTP beschermt door zwaar rapportageverkeer naar een speciaal daarvoor ingerichte logische replica te routeren.

Logisch boven fysiek wanneer het analyseknooppunt eigen indexen, kolommen of totalen nodig heeft.
Publiceer met een PUBLICATION precies de tabellen (en rijen/kolommen) die rapporten nodig hebben; gebruik ze met een SUBSCRIPTION.
Voeg alleen voor analyses bedoelde indexen en gematerialiseerde weergaven toe op de abonnee, zodat de primaire database nooit voor rapportagestructuren betaalt.
Routeer leesbewerkingen via pools/proxy's en een beperkte alleen-lezenrol met statement-time-outs.
Stuur alleen leesbewerkingen die verouderde gegevens verdragen en aggregaties uitvoeren naar de replica; houd leesbewerkingen na schrijven op de primaire database.
Controleer de achterstand van het replicatieslot — een vastgelopen abonnee kan de schijf van de primaire database vullen.

Het resultaat: dashboards krijgen snelle, speciale infrastructuur en je transactionele SLA's blijven veilig.

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 “Lees- en analysetaken uitbesteden” gratis?

Ja — je kunt hier op het web alle 3 lessen van het leerpad Prestaties en queryoptimalisatie in PostgreSQL, waaronder “Lees- en analysetaken uitbesteden”, 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 “Lees- en analysetaken uitbesteden”?

Stuur zwaar rapportageverkeer naar logische replica’s om de OLTP-latentie van de primary te beschermen. 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 2 van 4.

Hoe lang duurt de les “Lees- en analysetaken uitbesteden”?

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

Publicaties, subscriptions en replica identity
Lees- en analysetaken uitbesteden
Major-version-upgrades met vrijwel geen downtime
Replication lag en slot-bloat monitoren

← Terug naar Prestaties en queryoptimalisatie in PostgreSQL