Ytelse og spørringsoptimalisering i PostgreSQL · leksjon

Gjennomstrømming for COPY kontra INSERT med flere rader

Benchmark og velg inntaksmetoder som maksimerer antall rader per sekund under realistiske begrensninger.

Leksjon 1 av 413 trinn

Gjennomstrømming for COPY kontra INSERT med flere rader er en gratis leksjon i Ytelse og spørringsoptimalisering i PostgreSQL på CoddyKit. Dette er leksjon 1 av 4. Du kan lese valgfritt 3 leksjoner fra denne læringsstien gratis i sin helhet – deretter låser CoddyKit PRO opp alle leksjoner, samt praktisk øving med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i Ytelse og spørringsoptimalisering i PostgreSQL, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Ytelse og spørringsoptimalisering i PostgreSQL inneholder totalt 4 leksjoner.

Hvorfor innlastingshastighet er viktig

Når De laster inn millioner av rader i PostgreSQL, avgjør metoden De velger om jobben tar sekunder eller timer. I denne leksjonen sammenlignes de to viktigste innlastingsmetodene: COPY og flerrads-INSERT.

COPY strømmer rader gjennom én enkelt, optimalisert bulkbane.
Flerrads-INSERT pakker mange tupler inn i én setning for å fordele kostnaden ved rundturer.

Det riktige valget avhenger av datakilden, kostnaden ved rundturer og hvordan radene ankommer.

Det naive utgangspunktet: INSERT med én rad

Det tregeste mønsteret er én rad per setning. Hver setning må betale for parsing, planlegging, en nettverksrundtur og (uten batching) en separat commit.

Ved tusenvis av rader dominerer kostnaden per setning, og gjennomstrømmingen kollapser. Dette er utgangspunktet som alle andre metoder slår.

-- 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

Flerrads-INSERT: Fordele kostnaden ved rundturer

En flerrads-INSERT lister mange tupler i én setning. De betaler parse- og plankostnaden én gang, sender én nettverksrundtur og gjør commit for hele batchen samlet.

En god batchstørrelse er vanligvis 500 til 5 000 rader per setning.
Ved betydelig større batcher blir gevinsten mindre, samtidig som den parsede setningen blir unødvendig stor.

-- 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: Motorveien for bulkinnlasting

COPY er PostgreSQLs spesialbygde bulkinnlaster. Den hopper helt over parsing av setninger per rad og strømmer rader gjennom en tett løkke, noe som vanligvis gjør den til den raskeste måten å laste inn store datamengder på.

COPY ... FROM laster data inn i en tabell.
Den leser tekst-, CSV- eller binærformat.

Server-side COPY FROM 'file' krever superbruker eller rollen pg_read_server_files; klienter bruker vanligvis \copy i stedet.

-- 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);

Klientbasert \copy og COPY FROM STDIN

Når filen ligger på klienten (ikke serveren), bruker De psqls \copy-metakommando eller COPY ... FROM STDIN. Disse strømmer data over den eksisterende klienttilkoblingen, så det kreves ingen spesielle rettigheter til serverfiler.

De fleste ETL-drivere (psycopg, JDBC, libpq) tilbyr et strømmende COPY FROM STDIN-API, som er den raskeste programatiske innlastingsmetoden.

-- 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);

Hvorfor COPY vinner: mindre arbeid per rad

Forskjellen i gjennomstrømming skyldes hva hver rad koster:

Enkel INSERT: parsing + planlegging + kjøring + rundtur + commit per rad.
Flerrads-INSERT: parsing + planlegging én gang per batch, men bygger fortsatt et komplett parsetre for hvert tuppel.
COPY: ingen SQL-parsing per rad; verdiene dekodes direkte til tupler.

COPY genererer også færre WAL-poster per rad med arbeid, noe som utgjør en stor del av hastighetsfordelen.

Rettferdig benchmarking

For å sammenligne metodene på en ærlig måte må De holde alt annet konstant og måle både veggklokketid og rader per sekund. Bruk \timing i psql, eller pakk innlastingene inn i et tidsmålt testoppsett.

Last inn det samme datasettet hver gang.
Bruk TRUNCATE mellom kjøringene, slik at De starter fra en tom og sammenlignbar tilstand.
Kjør hver metode noen ganger og bruk medianen for å dempe støy.

\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

Transaksjoner og kostnader ved commit

En vanlig årsak til at INSERT med én rad er tregt, er én commit per rad. Hver commit tvinger frem en WAL-flushing (fsync) til disk. Når mange innsettinger pakkes inn i én transaksjon, reduseres tusenvis av fsync-operasjoner til én.

Både COPY og flerrads-INSERT gjør allerede commit per setning, men hvis De skripter mange setninger, bør De pakke dem inn i én eksplisitt transaksjon.

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;

Indekser, utløsere og begrensninger gjør innlasting tregere

Selv den raskeste innlastingsmetoden går svært sakte hvis hver innsatte rad må oppdatere fem indekser og utløse utløsere. En klassisk strategi for bulkinnlasting er å laste inn først og bygge indeksene etterpå.

Fjern eller deaktiver ikke-essensielle indekser, og opprett dem på nytt etter innlastingen.
Det er langt billigere å bygge en indeks én gang over hele tabellen enn å vedlikeholde den rad for rad.
Deaktiver kostbare utløsere under innlastingen når dataene er pålitelige.

-- 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-tabeller og staging

For midlertidige staging-data hopper en UNLOGGED-tabell helt over WAL-skriving, noe som kan gjøre innlastingen betydelig raskere. Ulempen er at unlogged-tabeller ikke tåler krasj og tømmes etter en krasj.

Et robust ETL-mønster er å laste inn i en unlogged eller midlertidig staging-tabell med COPY, transformere dataene og deretter sette det rensede resultatet inn i det varige målet.

-- 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;

Velge under reelle begrensninger

Velg metoden som passer til hvordan dataene ankommer:

Finnes det en bulkfil eller strøm? Bruk COPY / \copy — dette gir best gjennomstrømming.
Ankommer radene programmatisk i kode? Foretrekk driverens COPY FROM STDIN; hvis det ikke er tilgjengelig, bruk flerrads-INSERT i batcher på 500–5 000 rader.
Trenger De konflikthåndtering per rad (ON CONFLICT)? COPY kan ikke gjøre dette — bruk flerrads-INSERT, eller COPY til en staging-tabell og deretter UPSERT.

-- 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;

Kort kontroll: Maksimer gjennomstrømmingen

Test forståelsen Deres av den grunnleggende beslutningen om innlasting.

Besvar den i den interaktive leksjonen

Oppsummering

De kan nå måle og velge innlastingsmetoder på en bevisst måte:

COPY / \copy gir best gjennomstrømming ved innlasting fra bulkfiler og strømmer; den hopper over parsing per rad og minimerer WAL.
Flerrads-INSERT (500–5 000 rader per setning) er bedre enn innsetting av enkeltrader og fungerer som reserve når De trenger ON CONFLICT.
Pakk innlastingene inn i én transaksjon for å unngå fsync-kostnader per rad.
Fjern indekser, deaktiver utløsere og bruk UNLOGGED-stagingtabeller for å fjerne kostnader per rad, og bygg deretter indeksene på nytt.
Benchmark rettferdig: samme data, TRUNCATE mellom kjøringene, \timing, og bruk medianen for rader per sekund.

Gratis å komme i gang

Lær deg SQL med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs: 22
Leksjoner: 88

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «Gjennomstrømming for COPY kontra INSERT med flere rader» gratis?

Ja – du kan lese valgfritt 3 av leksjonene i læringsstien Ytelse og spørringsoptimalisering i PostgreSQL, inkludert «Gjennomstrømming for COPY kontra INSERT med flere rader», gratis i sin helhet her på nettet. Deretter låser CoddyKit PRO opp alle leksjoner, samt interaktiv øving med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Kurset i Ytelse og spørringsoptimalisering i PostgreSQL inneholder totalt 4 leksjoner.

Hva lærer jeg i «Gjennomstrømming for COPY kontra INSERT med flere rader»?

Benchmark og velg inntaksmetoder som maksimerer antall rader per sekund under realistiske begrensninger. Du øver på Ytelse og spørringsoptimalisering i PostgreSQL med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med Ytelse og spørringsoptimalisering i PostgreSQL?

Ingen tidligere erfaring er nødvendig. Ytelse og spørringsoptimalisering i PostgreSQL på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 1 av 4.

Hvor lang tid tar leksjonen «Gjennomstrømming for COPY kontra INSERT med flere rader»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne Ytelse og spørringsoptimalisering i PostgreSQL-leksjonen?

Ja. Alle Ytelse og spørringsoptimalisering i PostgreSQL-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

Gjennomstrømming for COPY kontra INSERT med flere rader
Utsette indekser og begrensninger under lasting
Justere WAL og sjekkpunkter for inntak
Upserts i stor skala med ON CONFLICT

← Tilbake til Ytelse og spørringsoptimalisering i PostgreSQL