Prestaties en queryoptimalisatie in PostgreSQL · Les

Indexen en constraints uitstellen tijdens het laden

Verwijder indexen en constraints rond bulk loads en bouw ze daarna opnieuw op om write amplification sterk te verminderen.

Les 2 van 413 stappen

Indexen en constraints uitstellen tijdens het laden 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 bulklading traag wordt

Wanneer je miljoenen rijen laadt in een tabel die al indexen en beperkingen heeft, betaalt PostgreSQL een verborgen heffing op elke afzonderlijke rij.

Elke index moet worden bijgewerkt (splitsingen van B-tree-pagina's, WAL-schrijfbewerkingen).
Elke vreemde sleutel activeert een opzoekactie in de tabel waarnaar wordt verwezen.
Elke unieke beperking en elke controlebeperking wordt per rij gevalideerd.

Dit werk per rij heet schrijfversterking: één logische INSERT wordt omgezet in veel fysieke schrijfbewerkingen. De kernoptimalisatie in deze les is dit werk uit te stellen — laad eerst de onbewerkte gegevens en bouw daarna indexen en valideer beperkingen eenmalig in bulk.

De kosten per index

Het onderhouden van een B-tree-index tijdens het laden is niet gratis. Voor elke ingevoegde rij moet PostgreSQL de boom doorlopen, de bladpagina vinden, deze eventueel splitsen en de wijziging naar WAL loggen.

Dezelfde index nadat de gegevens aanwezig zijn opbouwen is veel goedkoper: PostgreSQL sorteert alle sleutels in één keer en schrijft compacte, sequentiële pagina's. Een tabel met 5 indexen die rij voor rij wordt geladen, veroorzaakt ongeveer 6 keer zoveel schrijfwerk als het laden van alleen de heap.

De conclusie: minder aanwezige indexen tijdens het laden = minder versterking.

Patroon: verwijderen, laden, opnieuw opbouwen

Het klassieke ETL-patroon voor een tabel waarin een grote hoeveelheid gegevens wordt geladen, is:

Verwijder de secundaire indexen.
Laad de gegevens (COPY is het snelst).
Bouw de indexen in één bewerking opnieuw op.

Hieronder staat de basisstructuur. Let op dat we de primaire sleutel voorlopig behouden en alleen secundaire indexen verwijderen die tijdens het laden zelf niet nodig zijn.

DROP INDEX idx_orders_customer_id;
DROP INDEX idx_orders_created_at;

COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

COPY is beter dan INSERT voor het laden

Wanneer de indexen uit de weg zijn, doet de laadwijze ertoe. COPY streamt rijen met één opdracht en minimale overhead per rij, terwijl duizenden afzonderlijke INSERT-opdrachten elk kosten met zich meebrengen voor parseren, plannen en retourcommunicatie.

Kies voor ETL-doorvoer bij voorkeur COPY (of \copy vanuit psql) in plaats van invoegen per rij. Als je INSERT moet gebruiken, bundel dan veel rijen per opdracht.

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

Validatie van externe sleutels uitstellen

Externe sleutels worden tijdens het laden per rij gevalideerd, waarbij telkens een indexopzoeking in de bovenliggende tabel wordt uitgevoerd. Je kunt dit vermijden door de beperking eerst NOT VALID te maken, de gegevens te laden en daarna in bulk te valideren.

ADD CONSTRAINT ... NOT VALID voegt de externe sleutel toe zonder bestaande rijen te controleren. Nieuwe rijen worden bij het invoegen nog steeds gecontroleerd. Als je werk per rij echt wilt overslaan, verwijder je de externe sleutel en voeg je deze na het laden opnieuw toe, of laad je de gegevens voordat je de externe sleutel toevoegt.

-- Add the FK without scanning existing rows
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  NOT VALID;

-- Later, validate all rows in one bulk pass
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;

Waarom NOT VALID en daarna VALIDATE helpt

Een externe sleutel op de normale manier toevoegen vereist een ACCESS EXCLUSIVE-vergrendeling en doorzoekt de hele tabel terwijl schrijfbewerkingen worden geblokkeerd. De aanpak in twee stappen splitst dit op:

ADD ... NOT VALID is snel en vereist alleen kort een sterke vergrendeling om de beperking vast te leggen.
VALIDATE CONSTRAINT doorzoekt de tabel onder een zwakkere SHARE UPDATE EXCLUSIVE-vergrendeling, waardoor gelijktijdig lezen en schrijven mogelijk blijft.

Voor laadbewerkingen betekent dit dat je de dure validatie één keer uitvoert nadat alle gegevens aanwezig zijn, in plaats van per rij.

DEFERRABLE-beperkingen binnen een transactie

PostgreSQL ondersteunt ook DEFERRABLE-beperkingen, waarbij de controle wordt uitgesteld tot het einde van een transactie (COMMIT). Dit verschilt van het verwijderen van een beperking: de controle wordt nog steeds uitgevoerd, maar later.

Dit is nuttig wanneer rijen binnenkomen in een volgorde die tijdelijk een externe sleutel of unieke beperking schendt, bijvoorbeeld rijen voor onderliggende entiteiten vóór de bovenliggende entiteiten binnen één transactie.

ALTER TABLE order_items
  ADD CONSTRAINT fk_items_order
  FOREIGN KEY (order_id) REFERENCES orders (id)
  DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  -- insert children and parents in any order;
  -- FK is checked only at COMMIT
COMMIT;

Uitgestelde controle versus verwijderde beperking

Houd rekening met de afweging:

DEFERRABLE INITIALLY DEFERRED valideert nog steeds elke rij, alleen bij COMMIT in plaats van bij INSERT. Dit lost problemen met de volgorde op, maar verwijdert de validatiekosten niet.
Verwijderen en opnieuw toevoegen (of NOT VALID + VALIDATE) verwijdert werk per rij volledig en valideert opnieuw in één efficiënte scan.

Voor maximale doorvoer bij enorme laadbewerkingen wint verwijderen en opnieuw opbouwen. Voor correctheid bij lastige invoegvolgordes is deferrable het juiste hulpmiddel.

De index opnieuw opbouwen afstemmen

Indexen opnieuw opbouwen na een laadbewerking is zelf een bewerking waarbij veel wordt gesorteerd. Twee instellingen maken dit veel sneller voor de sessie die het laden uitvoert:

maintenance_work_mem — meer geheugen betekent minder samenvoegingen van externe sorteringen bij het opbouwen van indexen.
max_parallel_maintenance_workers — hiermee kan één CREATE INDEX meerdere CPU's gebruiken.

Verhoog deze waarden voor de laadsessie en bouw daarna de indexen op.

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

Een volledige ETL-volgorde

Voor een grote incrementele laadbewerking in een bestaande tabel is een robuuste volgorde van bewerkingen:

Verwijder secundaire indexen.
Verwijder dure externe sleutels of schakel ze uit.
Verhoog maintenance_work_mem.
Laad via COPY.
Bouw de indexen opnieuw op.
Voeg externe sleutels opnieuw toe en voer VALIDATE uit.
Voer ANALYZE uit zodat de planner over actuele statistieken beschikt.

ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;
DROP INDEX idx_orders_created_at;

SET maintenance_work_mem = '1GB';
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

CREATE INDEX idx_orders_created_at ON orders (created_at);
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);

ANALYZE orders;

Vergeet ANALYZE niet

Na een grote laadbewerking zijn de tabelstatistieken waarop de planner vertrouwt verouderd — de planner kan nog steeds denken dat de tabel klein is. Dat leidt tot slechte plannen, zoals sequentiële scans terwijl een index beter zou zijn, of een verkeerde volgorde van joins.

Voer altijd ANALYZE (of VACUUM ANALYZE) uit op pas geladen tabellen voordat je er query's op uitvoert. Indexen opnieuw opbouwen werkt de statistieken van de planner niet bij; alleen ANALYZE doet dat.

ANALYZE orders;
-- or to also reclaim space and freeze:
VACUUM ANALYZE orders;

Korte controle

Test je begrip van de afwegingen rond doorvoer.

Beantwoord dit in de interactieve les

Samenvatting

Belangrijkste punten over het uitstellen van indexen en beperkingen tijdens bulklaadbewerkingen:

Actieve indexen en beperkingen veroorzaken schrijfversterking — één INSERT wordt vele fysieke schrijfbewerkingen.
Het beste patroon is verwijderen, laden, opnieuw opbouwen: verwijder secundaire indexen en externe sleutels, laad met COPY en maak ze daarna in één bewerking opnieuw aan.
ADD CONSTRAINT ... NOT VALID, gevolgd door VALIDATE CONSTRAINT, verplaatst de controle van externe sleutels uit het pad per rij naar één bulkscan onder een lichtere vergrendeling.
DEFERRABLE INITIALLY DEFERRED stelt controles alleen uit tot COMMIT — het lost problemen met de volgorde van invoegen op, maar elimineert de validatiekosten niet.
Verhoog maintenance_work_mem en max_parallel_maintenance_workers om het opnieuw opbouwen te versnellen.
Sluit altijd af met ANALYZE, zodat de planner de nieuwe gegevens ziet.

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 “Indexen en constraints uitstellen tijdens het laden” gratis?

Ja — je kunt hier op het web alle 3 lessen van het leerpad Prestaties en queryoptimalisatie in PostgreSQL, waaronder “Indexen en constraints uitstellen tijdens het laden”, 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 “Indexen en constraints uitstellen tijdens het laden”?

Verwijder indexen en constraints rond bulk loads en bouw ze daarna opnieuw op om write amplification sterk te verminderen. 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 “Indexen en constraints uitstellen tijdens het laden”?

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