Prestaties en queryoptimalisatie in PostgreSQL · Les

WAL en checkpoints afstemmen voor ingestie

Pas WAL-instellingen en unlogged-tabellen aan om hoge schrijfsnelheden zonder I/O-blokkades vol te houden.

Les 3 van 413 stappen

WAL en checkpoints afstemmen voor ingestie is een gratis Prestaties en queryoptimalisatie in PostgreSQL-les op CoddyKit. Dit is les 3 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 WAL belangrijk is voor gegevensinvoer

Elke wijziging die je in PostgreSQL vastlegt, wordt eerst naar het Write-Ahead Log (WAL) geschreven voordat de gegevensbestanden worden bijgewerkt. Dit garandeert duurzaamheid en herstel na een crash, maar tijdens zware bulklaadbewerkingen wordt WAL een belangrijke bron van I/O.

Elke INSERT of COPY genereert WAL-records.
Periodiek schrijft een checkpoint gewijzigde pagina's uit gedeelde buffers naar schijf.
Als checkpoints te vaak plaatsvinden, betaal je voor dubbele I/O en ontstaan er onderbrekingen.

Het afstemmen van WAL en het gedrag van checkpoints is de sleutel tot hoge schrijfsnelheden zonder I/O-pieken.

Huidige WAL-instellingen bekijken

Bekijk voordat je iets wijzigt waarmee je server momenteel draait. De relevante instellingen staan in pg_settings en kunnen met SHOW worden opgevraagd.

De belangrijkste parameters voor gegevensinvoer zijn max_wal_size, checkpoint_timeout, checkpoint_completion_target en wal_compression.

SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
  'max_wal_size',
  'min_wal_size',
  'checkpoint_timeout',
  'checkpoint_completion_target',
  'wal_compression'
);

max_wal_size verhogen

Een checkpoint wordt gestart wanneer checkpoint_timeout verstrijkt of wanneer de hoeveelheid WAL max_wal_size bereikt. Tijdens een grote laadbewerking wordt de standaardwaarde (vaak 1 GB) voortdurend bereikt, waardoor checkpoint na checkpoint wordt afgedwongen.

Door max_wal_size te verhogen, kan WAL langer tussen checkpoints accumuleren.
Minder checkpoints betekent dat gewijzigde pagina's worden samengevoegd en één keer worden geschreven in plaats van herhaaldelijk.

Voor een venster voor gegevensinvoer zijn waarden van 8–32 GB gebruikelijk.

ALTER SYSTEM SET max_wal_size = '16GB';
SELECT pg_reload_conf();

I/O van checkpoints spreiden

checkpoint_completion_target bepaalt welk deel van het interval PostgreSQL gebruikt om de schrijfbewerkingen van het checkpoint te spreiden. Een waarde van 0.9 betekent dat de schrijfbewerkingen over 90% van de tijd tot het volgende checkpoint worden verdeeld, waardoor een scherpe I/O-piek wordt voorkomen.

In combinatie met een langere checkpoint_timeout verandert dit grillige checkpointstormen in een gelijkmatige, aanhoudende schrijfstroom.

ALTER SYSTEM SET checkpoint_timeout = '30min';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
SELECT pg_reload_conf();

WAL-records comprimeren

Wanneer na een checkpoint volledige-pagina-afbeeldingen worden geschreven (bij de eerste wijziging van een pagina), maken ze WAL onnodig groot. wal_compression comprimeert deze volledige-pagina-afbeeldingen. Dit kost wat CPU, maar vermindert de hoeveelheid WAL en de schijf-I/O aanzienlijk.

In moderne PostgreSQL-versies kun je het algoritme kiezen, bijvoorbeeld lz4 of zstd.
Minder geschreven WAL betekent ook snellere replicatie en minder checkpoints door de groottebeperking.

ALTER SYSTEM SET wal_compression = 'lz4';
SELECT pg_reload_conf();

Niet-gelogde tabellen: WAL volledig overslaan

Een niet-gelogde tabel schrijft helemaal geen WAL. Voor voorbereidingstabellen in een ETL-verwerkingspijplijn kan dit de doorvoer sterk verhogen, omdat je de grootste afzonderlijke schrijfkosten omzeilt.

De gegevens worden nog steeds naar schijf geschreven, maar niet duurzaam gelogd.
Afweging: de tabel wordt na een crash automatisch leeggemaakt en wordt niet naar stand-byservers gerepliceerd.

Perfect voor opnieuw op te bouwen voorbereidingsgegevens; nooit gebruiken voor het officiële systeem van record.

CREATE UNLOGGED TABLE staging_events (
  id        bigint,
  payload   jsonb,
  loaded_at timestamptz DEFAULT now()
);

Het patroon van voorbereiding naar eindtabel

Een robuust ETL-ontwerp laadt onbewerkte rijen in een snelle, niet-gelogde voorbereidingstabel, transformeert ze en verplaatst het opgeschoonde resultaat vervolgens naar de duurzame eindtabel.

De bulkbewerking met COPY gebruikt de niet-gelogde tabel op volle snelheid.
De uiteindelijke INSERT ... SELECT schrijft WAL slechts één keer, voor gevalideerde gegevens.

Je krijgt snelheid waar duurzaamheid niet belangrijk is en veiligheid waar die wel belangrijk is.

INSERT INTO events (id, payload, loaded_at)
SELECT id, payload, loaded_at
FROM staging_events
WHERE payload IS NOT NULL;

TRUNCATE staging_events;

Een niet-gelogde tabel promoveren

Als een voorbereidingstabel na het laden duurzaam moet worden, kun je deze ter plaatse converteren in plaats van de rijen te kopiëren. Door deze in te stellen op LOGGED wordt de tabel opnieuw geschreven en wordt het loggen naar WAL gestart.

De conversie zelf genereert WAL voor de hele tabel, dus voer deze één keer aan het einde uit.
Door vóór de volgende laadbewerking terug te gaan naar UNLOGGED, vermijd je opnieuw WAL per rij.

ALTER TABLE staging_events SET LOGGED;

COPY is beter dan INSERT per rij

Zelfs met afgestemd WAL is hoe je laadt belangrijk. COPY bundelt rijen in veel minder, grotere WAL-records dan duizenden afzonderlijke INSERT-opdrachten en vermijdt de overhead van parseren en plannen per opdracht.

Combineer COPY met een niet-gelogde voorbereidingstabel om de hoogst aanhoudbare invoersnelheid te bereiken.

COPY staging_events (id, payload)
FROM '/data/events.csv'
WITH (FORMAT csv, HEADER true);

Druk op checkpoints bewaken

Bekijk de statistieken van checkpoints om te weten of je afstemming heeft gewerkt. Het belangrijkste signaal is de verhouding tussen aangevraagde checkpoints (gestart door de omvang) en geplande checkpoints.

Veel requested-checkpoints betekent dat max_wal_size nog steeds te klein is voor je laadbewerking.
Vooral timed-checkpoints betekent dat de hoeveelheid WAL ruim binnen het budget blijft.

In nieuwere versies staan deze tellers in pg_stat_checkpointer; oudere versies gebruiken pg_stat_bgwriter.

SELECT num_timed, num_requested,
       buffers_written, write_time, sync_time
FROM pg_stat_checkpointer;

Na het laden terugzetten

Agressieve instellingen voor gegevensinvoer zijn tijdens een laadvenster geweldig, maar kosten daarna onnodig hersteltijd en schijfruimte. Zet na het voltooien van de batch behoudende waarden terug en forceer een schoon checkpoint, zodat het herstel na de volgende crash snel verloopt.

Verlaag max_wal_size en checkpoint_timeout terug naar de waarden voor de normale toestand.
Voer handmatig CHECKPOINT uit om alles onmiddellijk weg te schrijven.

ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET checkpoint_timeout = '5min';
SELECT pg_reload_conf();
CHECKPOINT;

Korte controle

Je laadt 200 miljoen rijen in een opnieuw op te bouwen voorbereidingstabel die daarna wordt gevalideerd en naar de duurzame tabel wordt gekopieerd. Welke keuze vermindert de hoeveelheid geschreven WAL tijdens het laden het meest direct?

Beantwoord dit in de interactieve les

Samenvatting

Voor hoge schrijfsnelheden zonder I/O-onderbrekingen:

Verhoog max_wal_size en checkpoint_timeout, zodat checkpoints minder vaak plaatsvinden, en stel checkpoint_completion_target in op ongeveer 0.9 om de schrijfbewerkingen te spreiden.
Schakel wal_compression in om volledige-pagina-afbeeldingen te verkleinen.
Gebruik UNLOGGED-voorbereidingstabellen om WAL over te slaan voor opnieuw op te bouwen gegevens en verplaats gevalideerde rijen daarna naar de duurzame tabel.
Geef de voorkeur aan COPY boven invoegen per rij.
Bewaar pg_stat_checkpointer in de gaten voor aangevraagde versus geplande checkpoints en zet na het laden behoudende waarden terug.

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 “WAL en checkpoints afstemmen voor ingestie” gratis?

Ja — je kunt hier op het web alle 3 lessen van het leerpad Prestaties en queryoptimalisatie in PostgreSQL, waaronder “WAL en checkpoints afstemmen voor ingestie”, 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 “WAL en checkpoints afstemmen voor ingestie”?

Pas WAL-instellingen en unlogged-tabellen aan om hoge schrijfsnelheden zonder I/O-blokkades vol te houden. 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 3 van 4.

Hoe lang duurt de les “WAL en checkpoints afstemmen voor ingestie”?

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

Doorvoer van COPY versus multi-row INSERT
Indexen en constraints uitstellen tijdens het laden
WAL en checkpoints afstemmen voor ingestie
Upserts op schaal met ON CONFLICT

← Terug naar Prestaties en queryoptimalisatie in PostgreSQL