Prestaties en queryoptimalisatie in PostgreSQL · Les

Doorvoer van COPY versus multi-row INSERT

Benchmark en kies ingestiemethoden die onder realistische beperkingen het maximale aantal rijen per seconde halen.

Les 1 van 413 stappen

Doorvoer van COPY versus multi-row INSERT 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.

Waarom de snelheid van gegevensinvoer belangrijk is

Wanneer je miljoenen rijen in PostgreSQL laadt, bepaalt de gekozen methode of de taak seconden of uren duurt. In deze les worden de twee belangrijkste paden voor gegevensinvoer vergeleken: COPY en een meermaals uitgevoerde INSERT.

COPY streamt rijen via één geoptimaliseerd bulkkanaal.
Meermaals uitgevoerde INSERT verpakt veel tuples in één instructie om netwerkretouren over meer rijen te verdelen.

De juiste keuze hangt af van de gegevensbron, de kosten van netwerkretouren en de manier waarop de rijen binnenkomen.

De naïeve basislijn: INSERT voor één rij

Het traagste patroon is één rij per instructie. Elke instructie betaalt voor parsen, plannen, een netwerkretour en (zonder batchverwerking) een afzonderlijke commit.

Bij duizenden rijen overheersen de kosten per instructie en stort de doorvoer in. Dit is de basislijn die elke andere methode moet verslaan.

-- Slow: one round-trip and (by default) one commit per row
INSERT INTO events (user_id, kind, payload) VALUES (1, 'click', '{}');
INSERT INTO events (user_id, kind, payload) VALUES (2, 'view',  '{}');
INSERT INTO events (user_id, kind, payload) VALUES (3, 'click', '{}');
-- ... repeated 1,000,000 times

INSERT voor meerdere rijen: netwerkretouren verdelen

Een INSERT voor meerdere rijen vermeldt veel tuples in één instructie. Je betaalt de kosten voor parsen en plannen eenmalig, verstuurt één netwerkretour en commit de hele batch samen.

Een goede batchgrootte is meestal 500 tot 5.000 rijen per instructie.
Veel hoger gaan levert afnemende meeropbrengsten op en maakt de geparseerde instructie onnodig groot.

-- One statement, one round-trip, many rows
INSERT INTO events (user_id, kind, payload) VALUES
  (1, 'click', '{}'),
  (2, 'view',  '{}'),
  (3, 'click', '{}'),
  (4, 'view',  '{}');
-- typically 500-5000 tuples per statement

COPY: de snelweg voor bulkgegevens

COPY is de speciaal ontwikkelde bulklader van PostgreSQL. Per-rij-parsing van instructies wordt volledig overgeslagen en rijen worden via een strakke lus gestreamd, waardoor dit meestal de snelste manier is om grote hoeveelheden gegevens in te voeren.

COPY ... FROM laadt gegevens in een tabel.
De opdracht leest tekst-, CSV- of binaire indelingen.

Server-side COPY FROM 'file' vereist een superuser of de rol pg_read_server_files; clients gebruiken meestal \copy.

-- Server-side COPY from a CSV file (needs file access privileges)
COPY events (user_id, kind, payload)
FROM '/data/events.csv'
WITH (FORMAT csv, HEADER true);

Client-side \copy en COPY FROM STDIN

Wanneer het bestand op de client staat (niet op de server), gebruik je psql's meta-opdracht \copy of COPY ... FROM STDIN. Deze streamen gegevens via de bestaande clientverbinding, zodat speciale rechten voor serverbestanden niet nodig zijn.

De meeste ETL-stuurprogramma's (psycopg, JDBC, libpq) bieden een streaming-API voor COPY FROM STDIN, het snelste programmatische laadpad.

-- psql meta-command: file is read on the CLIENT machine
\copy events (user_id, kind, payload) FROM 'events.csv' WITH (FORMAT csv, HEADER true)

-- Equivalent SQL that streams from the client connection
COPY events (user_id, kind, payload) FROM STDIN WITH (FORMAT csv);

Waarom COPY wint: minder werk per rij

Het verschil in doorvoer komt voort uit wat elke rij kost:

INSERT voor één rij: parsen + plannen + uitvoeren + netwerkretour + commit per rij.
INSERT voor meerdere rijen: één keer parsen + plannen per batch; er wordt nog steeds voor elke tuple een volledige syntaxisboom opgebouwd.
COPY: helemaal geen SQL-parsing per rij; waarden worden rechtstreeks naar tuples gedecodeerd.

COPY genereert ook minder WAL-records per rij werk, wat een groot deel van het snelheidsvoordeel verklaart.

Eerlijk benchmarken

Houd voor een eerlijke vergelijking al het andere constant en meet zowel de verstreken tijd als het aantal rijen per seconde. Gebruik \timing in psql of verpak het laden in een tijdgemeten testopstelling.

Laad bij elke uitvoering dezelfde dataset.
Voer tussen uitvoeringen TRUNCATE uit, zodat je telkens vanuit een lege, vergelijkbare toestand start.
Voer elke methode een paar keer uit en neem de mediaan om ruis te dempen.

\timing on

TRUNCATE events;
-- run method A (multi-row INSERT batches), note the time

TRUNCATE events;
-- run method B (COPY FROM), note the time

-- rows_per_second = row_count / elapsed_seconds

Transacties en commitkosten

Een veelvoorkomende reden waarom INSERTs voor één rij traag zijn, is één commit per rij. Elke commit forceert een WAL-flush (een fsync) naar schijf. Door veel inserts in één transactie te plaatsen, breng je duizenden fsyncs terug tot één.

Zowel COPY als INSERT voor meerdere rijen committen al per instructie, maar als je veel instructies scriptt, plaats je ze in één expliciete transactie.

BEGIN;
INSERT INTO events (user_id, kind) VALUES (1, 'click');
INSERT INTO events (user_id, kind) VALUES (2, 'view');
-- ... many statements, ONE fsync at the end
COMMIT;

Indexen, triggers en beperkingen vertragen het laden

Zelfs de snelste methode voor gegevensinvoer kruipt vooruit als elke ingevoegde rij vijf indexen moet bijwerken en triggers moet activeren. Een klassieke techniek voor bulklading is eerst laden en daarna indexen bouwen.

Verwijder of schakel niet-essentiële indexen uit en maak ze na het laden opnieuw.
Een index één keer over de volledige tabel bouwen is veel goedkoper dan hem rij voor rij bijhouden.
Schakel dure triggers tijdens het laden uit wanneer de gegevens betrouwbaar zijn.

-- Bulk-load pattern: strip overhead, load, then rebuild
DROP INDEX IF EXISTS idx_events_user_id;

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

CREATE INDEX idx_events_user_id ON events (user_id);

UNLOGGED-tabellen en staging

Voor tijdelijke staginggegevens slaat een UNLOGGED-tabel WAL-schrijfbewerkingen volledig over, wat het laden aanzienlijk kan versnellen. De keerzijde: niet-gelogde tabellen zijn niet crashbestendig en worden na een crash afgekapt.

Een robuust ETL-patroon laadt met COPY in een niet-gelogde of tijdelijke stagingtabel, transformeert de gegevens en voegt het schone resultaat daarna in de duurzame doeltabel in.

-- Fast, non-durable staging area for ETL
CREATE UNLOGGED TABLE events_staging (
  user_id integer,
  kind    text,
  payload jsonb
);

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

INSERT INTO events SELECT * FROM events_staging WHERE kind IS NOT NULL;

Kiezen onder echte beperkingen

Kies de methode die past bij de manier waarop de gegevens binnenkomen:

Is er een bulkbestand of een stream beschikbaar? Gebruik COPY / \copy — dit wint op doorvoer.
Komen rijen programmatisch in code binnen? Geef de voorkeur aan COPY FROM STDIN van je stuurprogramma; als dat niet beschikbaar is, gebruik je INSERT voor meerdere rijen in batches van 500-5.000.
Heb je afhandeling van conflicten per rij nodig (ON CONFLICT)? COPY kan dat niet — gebruik INSERT voor meerdere rijen, of COPY naar een stagingtabel en voer daarna een UPSERT uit.

-- COPY has no ON CONFLICT; stage then upsert when you need it
COPY events_staging FROM STDIN WITH (FORMAT csv);

INSERT INTO events AS e (user_id, kind, payload)
SELECT user_id, kind, payload FROM events_staging
ON CONFLICT (user_id, kind) DO UPDATE
  SET payload = EXCLUDED.payload;

Korte controle: maximaliseer de doorvoer

Test of je de belangrijkste beslissing rond gegevensinvoer begrijpt.

Beantwoord dit in de interactieve les

Samenvatting

Je kunt nu bewust methoden voor gegevensinvoer benchmarken en kiezen:

COPY / \copy wint op doorvoer bij het laden van bulkbestanden en streams; per-rij-parsing wordt overgeslagen en WAL wordt geminimaliseerd.
INSERT voor meerdere rijen (500-5.000 rijen per instructie) is sneller dan inserts voor één rij en is de terugvaloptie wanneer je ON CONFLICT nodig hebt.
Plaats het laden in één transactie om fsynckosten per rij te vermijden.
Verwijder indexen, schakel triggers uit en gebruik UNLOGGED-stagingtabellen om overhead per rij te verwijderen en bouw daarna alles opnieuw op.
Benchmark eerlijk: dezelfde gegevens, TRUNCATE tussen uitvoeringen, \timing en neem de mediaan van het aantal rijen per seconde.

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 “Doorvoer van COPY versus multi-row INSERT” gratis?

Ja — je kunt hier op het web alle 3 lessen van het leerpad Prestaties en queryoptimalisatie in PostgreSQL, waaronder “Doorvoer van COPY versus multi-row INSERT”, 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 “Doorvoer van COPY versus multi-row INSERT”?

Benchmark en kies ingestiemethoden die onder realistische beperkingen het maximale aantal rijen per seconde halen. 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 “Doorvoer van COPY versus multi-row INSERT”?

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