Prestandaoptimering och frågeoptimering i PostgreSQL · Lektion

Finjustera WAL och checkpoints för inläsning

Justera WAL-inställningar och ologgade tabeller för att upprätthålla höga skrivhastigheter utan I/O-stopp.

Lektion 3 av 413 steg

Finjustera WAL och checkpoints för inläsning är en gratis lektion i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit. Detta är lektion 3 av 4. Du kan läsa vilka 3 lektioner som helst i den här lärvägen kostnadsfritt i sin helhet – därefter låser CoddyKit PRO upp alla lektioner, plus praktisk övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Den ingår i lärvägen för Prestandaoptimering och frågeoptimering i PostgreSQL, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Varför WAL är viktigt vid inläsning

Varje ändring som ni bekräftar i PostgreSQL skrivs först till Write-Ahead Log (WAL) innan datafilerna uppdateras. Detta garanterar beständighet och återställning efter krascher, men under tunga massinläsningar blir WAL en stor källa till I/O.

Varje INSERT eller COPY genererar WAL-poster.
Med jämna mellanrum skriver en checkpoint smutsiga sidor från delade buffertar till disk.
Om checkpoints körs för ofta får ni dubbel I/O och stopp i arbetet.

Att justera WAL- och checkpoint-beteendet är nyckeln till att upprätthålla höga skrivhastigheter utan I/O-toppar.

Granska aktuella WAL-inställningar

Innan ni ändrar något bör ni se vilka inställningar servern kör med. De relevanta parametrarna finns i pg_settings och kan läsas med SHOW.

De viktigaste parametrarna för inläsning är max_wal_size, checkpoint_timeout, checkpoint_completion_target och 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'
);

Höja max_wal_size

En checkpoint utlöses antingen när checkpoint_timeout löper ut eller när WAL-volymen når max_wal_size. Under en stor inläsning nås standardvärdet, ofta 1 GB, hela tiden, vilket tvingar fram checkpoint efter checkpoint.

Att höja max_wal_size gör att WAL kan ackumuleras längre mellan checkpoints.
Färre checkpoints innebär att smutsiga sidor slås samman och skrivs en gång i stället för upprepade gånger.

Under ett inläsningsfönster är värden som 8–32 GB vanliga.

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

Sprida ut checkpoint-I/O

checkpoint_completion_target styr hur stor del av intervallet PostgreSQL använder för att sprida ut checkpoint-skrivningarna. Ett värde på 0.9 innebär att skrivningarna fördelas över 90 % av tiden fram till nästa checkpoint, vilket undviker en kraftig I/O-topp.

Tillsammans med ett längre checkpoint_timeout omvandlar detta ryckiga checkpoint-stormar till ett jämnt, kontinuerligt skrivflöde.

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

Komprimera WAL-poster

När fullständiga sidbilder skrivs efter en checkpoint, vid den första ändringen av en sida, blir WAL större. wal_compression komprimerar dessa fullständiga sidbilder och byter lite CPU mot betydligt mindre WAL-volym och disk-I/O.

I moderna versioner av PostgreSQL kan ni välja algoritm, till exempel lz4 eller zstd.
Mindre mängd skriven WAL innebär även snabbare replikering och färre checkpoints på grund av storleksgränsen.

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

Ologgade tabeller: hoppa över WAL helt

En ologgad tabell skriver inte alls till WAL. För staging-tabeller i en ETL-pipeline kan detta öka genomströmningen kraftigt, eftersom den största enskilda skrivkostnaden försvinner.

Data skrivs fortfarande till disk, men loggas inte beständigt.
Avvägning: tabellen töms automatiskt efter en krasch och replikeras inte till standbys.

Perfekt för staging-data som kan byggas om, men aldrig för systemets primära datakälla.

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

Mönstret från staging till slutlig tabell

En robust ETL-design läser in råa rader i en snabb ologgad staging-tabell, transformerar dem och flyttar sedan det rensade resultatet till den beständiga slutliga tabellen.

Den omfattande COPY-inläsningen går mot den ologgade tabellen med full hastighet.
Den slutliga INSERT ... SELECT-satsen skriver WAL endast en gång, för validerade data.

Ni får hög hastighet där beständighet inte spelar någon roll och säkerhet där den gör det.

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

TRUNCATE staging_events;

Göra en ologgad tabell beständig

Om en staging-tabell behöver bli beständig efter att inläsningen är klar kan ni konvertera den på plats i stället för att kopiera raderna. När ni ställer in den på LOGGED skrivs tabellen om och WAL-loggning börjar.

Själva konverteringen genererar WAL för hela tabellen, så gör detta en gång i slutet.
Att återgå till UNLOGGED före nästa inläsning undviker återigen WAL per rad.

ALTER TABLE staging_events SET LOGGED;

COPY slår radvis INSERT

Även när WAL är rätt inställt spelar hur ni läser in data roll. COPY samlar rader i betydligt färre och större WAL-poster än tusentals enskilda INSERT-satser och undviker omkostnaden för parsning och planering per sats.

Kombinera COPY med en ologgad staging-tabell för att nå den högsta hållbara inläsningshastigheten.

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

Övervaka checkpoint-belastningen

För att veta om justeringarna fungerade bör ni övervaka checkpoint-statistiken. Den viktigaste signalen är förhållandet mellan requested-checkpoints, som utlöses av storleken, och timed-checkpoints.

Många requested-checkpoints innebär att max_wal_size fortfarande är för litet för er inläsning.
Huvudsakligen timed-checkpoints innebär att WAL-volymen bekvämt ryms inom budgeten.

I nyare versioner finns dessa räknare i pg_stat_checkpointer; äldre versioner använder pg_stat_bgwriter.

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

Återställa efter inläsningen

Aggressiva inläsningsinställningar är bra under ett inläsningsfönster, men förlänger återställningen och ökar diskförbrukningen efteråt. När batchen är klar bör ni återställa försiktiga värden och tvinga fram en ren checkpoint så att nästa kraschåterställning går snabbt.

Sänk max_wal_size och checkpoint_timeout till värdena för normal drift.
Kör en manuell CHECKPOINT för att skriva allt omedelbart.

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

Snabbtest

Ni läser in 200 miljoner rader i en staging-tabell som kan byggas om och som senare ska valideras och kopieras till den beständiga tabellen. Vilket val minskar WAL-skrivvolymen mest direkt under inläsningen?

Besvara frågan i den interaktiva lektionen

Sammanfattning

För att upprätthålla höga skrivhastigheter utan I/O-stopp:

Höj max_wal_size och checkpoint_timeout så att checkpoints körs mer sällan, och ställ in checkpoint_completion_target nära 0.9 för att sprida ut skrivningarna.
Aktivera wal_compression för att minska storleken på fullständiga sidbilder.
Använd UNLOGGED-staging-tabeller för data som kan byggas om och hoppa över WAL, och flytta sedan validerade rader till den beständiga tabellen.
Föredra COPY framför radvisa infogningar.
Övervaka pg_stat_checkpointer för requested- och timed-checkpoints och återställ försiktiga värden efter inläsningen.

Gratis att börja

Lär dig SQL med en AI-lärare – gratis

Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.

Kurser: 22
Lektioner: 88

Börja lära dig gratis Visa hela kursen

Vanliga frågor

Är lektionen ”Finjustera WAL och checkpoints för inläsning” gratis?

Ja – du kan läsa vilka 3 lektioner som helst i lärvägen Prestandaoptimering och frågeoptimering i PostgreSQL, inklusive ”Finjustera WAL och checkpoints för inläsning”, kostnadsfritt i sin helhet här på webben. Därefter låser CoddyKit PRO upp alla lektioner, plus interaktiv övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Vad lär jag mig i ”Finjustera WAL och checkpoints för inläsning”?

Justera WAL-inställningar och ologgade tabeller för att upprätthålla höga skrivhastigheter utan I/O-stopp. Ni övar på Prestandaoptimering och frågeoptimering i PostgreSQL med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.

Behöver jag någon erfarenhet för att börja lära mig Prestandaoptimering och frågeoptimering i PostgreSQL?

Du behöver inga förkunskaper. Utbildningen i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 3 av 4.

Hur lång tid tar lektionen ”Finjustera WAL och checkpoints för inläsning”?

De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.

Kan jag skriva och köra kod i den här Prestandaoptimering och frågeoptimering i PostgreSQL-lektionen?

Ja. Varje Prestandaoptimering och frågeoptimering i PostgreSQL-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.

Alla lektioner i den här kursen

Genomströmning för COPY kontra flerrads-INSERT
Skjuta upp index och begränsningar under inläsning
Finjustera WAL och checkpoints för inläsning
Upserts i stor skala med ON CONFLICT

← Tillbaka till Prestandaoptimering och frågeoptimering i PostgreSQL