COPY für Bulk-Import

COPY vs. \copy — der wichtigste Unterschied

Es gibt zwei verschiedene COPY-Mechanismen, die leicht zu verwechseln sind:

In den meisten Fällen willst du \copy — die Datei liegt auf deinem Rechner, nicht auf dem Datenbank-Server. COPY (SQL) ist nur sinnvoll, wenn du auf dem DB-Server selbst arbeitest oder Server-seitige Dateien importieren willst (z. B. Exports von einer Foreign Data Wrapper).

Import: \copy FROM

Sagen wir, du hast eine CSV-Datei users.csv mit ein paar tausend Zeilen, die in die users-Tabelle wandern sollen. Die Datei sieht so aus:

email,name
alice@example.com,Alice
bob@example.com,Bob
carol@example.com,Carol
...

Im psql liest du sie so ein:

myapp=> \copy users (email, name) FROM 'users.csv' WITH (FORMAT csv, HEADER true);
COPY 1024

Die Ausgabe COPY 1024 bestätigt: 1024 Zeilen wurden geladen. Die Spaltenliste (email, name) ist optional, aber empfohlen — sie sagt Postgres, welche Spalten in welcher Reihenfolge in der CSV stehen. Spalten der Tabelle, die nicht aufgezählt werden (z. B. id, created_at), bekommen ihren DEFAULT-Wert (also etwa automatisch generierte IDs und Zeitstempel).

Häufige Optionen:

WITH (
  FORMAT csv,         -- 'text' (default), 'csv', 'binary'
  HEADER true,        -- erste Zeile als Header behandeln und überspringen
  DELIMITER ',',      -- Trennzeichen (default für csv: ',')
  QUOTE '"',          -- Quoting-Zeichen (default für csv: '"')
  ESCAPE '\',         -- Escape-Zeichen
  NULL '',            -- Welcher String wird als NULL interpretiert?
  ENCODING 'UTF8'
)

NULL '' ist häufig nützlich: leere Felder in der CSV werden als NULL eingelesen, statt als leerer String.

Export: \copy TO

myapp=> \copy users TO 'users-export.csv' WITH (FORMAT csv, HEADER true);
COPY 1024

Statt einer Tabelle kann auch eine Query als Quelle dienen:

\copy (SELECT id, email FROM users WHERE deleted_at IS NULL)
      TO 'active-users.csv'
      WITH (FORMAT csv, HEADER true);

stdout/stdin als Pseudo-Pfade funktionieren auch — praktisch in Skripten:

psql -c "\copy users TO STDOUT WITH (FORMAT csv, HEADER true)" \
    > users.csv

cat users.csv | psql -c "\copy users FROM STDIN WITH (FORMAT csv, HEADER true)"

Geschwindigkeit — was COPY so schnell macht

COPY ist typischerweise 10–50× schneller als äquivalente INSERT-Statements. Gründe:

• Kein SQL-Parser pro Zeile.
• Daten werden in größeren Blöcken in den Storage geschrieben.
• Trigger und Constraints feuern weiterhin, aber pro Zeile in einer engen Schleife.
• Bei WAL_level=minimal und „COPY in derselben Transaktion wie CREATE TABLE” entfällt sogar das WAL-Logging.

Größenordnungen: einzelne INSERTs schaffen oft 5–10k Zeilen/Sekunde. COPY kommt auf 100k+ Zeilen/Sekunde, mit binary format und ohne Indexe sogar Millionen.

Performance-Tipps für riesige Imports

Bei mehreren Millionen Zeilen lohnen sich noch ein paar Tricks:

BEGIN;

-- Indexe und Constraints temporaer abschalten
ALTER TABLE big_table DROP CONSTRAINT IF EXISTS big_table_pkey;
DROP INDEX IF EXISTS big_table_x_idx;

-- Bulk-Import
\copy big_table FROM 'data.csv' WITH (FORMAT csv);

-- Indexe wiederherstellen
ALTER TABLE big_table ADD PRIMARY KEY (id);
CREATE INDEX big_table_x_idx ON big_table (x);

COMMIT;

Indexe nach dem Import in einem Rutsch zu erstellen ist deutlich schneller, als sie pro Zeile aktualisieren zu lassen.

Weitere Hebel für Hardcore-Imports:

• synchronous_commit = off für die Session — keine fsync-Pause pro Commit (Risiko: Datenverlust bei Crash).
• maintenance_work_mem hochsetzen — beschleunigt das Index-Bauen am Ende.
• Wenn möglich: parallel laden (mehrere COPYs in mehreren Sessions auf disjunkte Daten-Slices).

ON CONFLICT mit COPY? — der Staging-Trick

COPY selbst kennt kein ON CONFLICT. Wenn du beim Bulk-Import Duplikate ignorieren oder upserten willst, lädt man erst in eine Staging-Tabelle und führt dann einen INSERT … SELECT … ON CONFLICT aus:

CREATE TEMPORARY TABLE staging_users (
    email text,
    name  text
);

\copy staging_users FROM 'users.csv' WITH (FORMAT csv, HEADER true)

INSERT INTO users (email, name)
SELECT email, name FROM staging_users
ON CONFLICT (email) DO UPDATE
    SET name = EXCLUDED.name;

Ein Statement mehr, aber: die Geschwindigkeit von COPY plus die Konflikt-Logik von ON CONFLICT. Auf 1 Million Zeilen 5-10× schneller als zeilenweise Inserts mit Konflikt-Behandlung.

Aus Anwendungen heraus

Die meisten Postgres-Treiber haben eine Stream-Variante für COPY. Beispiel mit node-postgres:

import { from as copyFrom } from 'pg-copy-streams';
import { createReadStream } from 'fs';

const stream = client.query(copyFrom(
  `COPY users (email, name) FROM STDIN WITH (FORMAT csv, HEADER true)`
));
createReadStream('users.csv').pipe(stream);

In Go: database/sql allein kann’s nicht — der pgx-Treiber hat die CopyFrom-API:

copyCount, err := conn.CopyFrom(
    ctx,
    pgx.Identifier{"users"},
    []string{"email", "name"},
    pgx.CopyFromRows(rows),
)

Praxis-Beispiele

Schritt-für-Schritt: 1 Million Zeilen importieren

head -3 events.csv
# event_id,user_id,event_type,payload,occurred_at
# 1,42,login,"{}",2026-05-01T12:00:00Z
# 2,42,view,"{\"page\":\"/home\"}",2026-05-01T12:00:05Z

wc -l events.csv
# 1000001 events.csv

myapp=> \timing
Timing is on.

myapp=> BEGIN;
BEGIN

myapp=> ALTER TABLE events DROP CONSTRAINT events_pkey;
ALTER TABLE
Time: 12.483 ms

myapp=> DROP INDEX events_user_id_idx;
DROP INDEX
Time: 8.921 ms

myapp=> \copy events FROM 'events.csv' WITH (FORMAT csv, HEADER true)
COPY 1000000
Time: 18432.117 ms (00:18.432)

myapp=> ALTER TABLE events ADD PRIMARY KEY (event_id);
ALTER TABLE
Time: 4523.812 ms

myapp=> CREATE INDEX events_user_id_idx ON events (user_id);
CREATE INDEX
Time: 6128.404 ms

myapp=> COMMIT;
COMMIT

Gesamtzeit für 1 Million Zeilen: ca. 30 Sekunden. Mit normalen INSERTs wären das mehrere Stunden.

COPY zwischen zwei Datenbanken via Pipe

psql -h source.example.com -U app -d source_db \
    -c "\copy (SELECT * FROM users WHERE created_at >= '2026-01-01') TO STDOUT WITH (FORMAT csv)" \
| psql -h target.example.com -U app -d target_db \
    -c "\copy users FROM STDIN WITH (FORMAT csv)"

Stream geht durch die Pipe, ohne dass die ganze Datenmenge auf der Platte landet. Praktisch für Migrationen oder selektive Replikation.

Staging + UPSERT für Sync mit Duplikaten

BEGIN;

CREATE TEMPORARY TABLE staging_users (
    email text,
    name  text,
    updated_at timestamptz
) ON COMMIT DROP;

\copy staging_users FROM 'feed.csv' WITH (FORMAT csv, HEADER true)

INSERT INTO users (email, name, updated_at)
SELECT email, name, updated_at FROM staging_users
ON CONFLICT (email) DO UPDATE
    SET name = EXCLUDED.name,
        updated_at = EXCLUDED.updated_at
WHERE users.updated_at < EXCLUDED.updated_at;

COMMIT;

ON COMMIT DROP räumt die Staging-Tabelle automatisch nach dem Commit auf — saubere Variante für wiederkehrende Sync-Jobs. Der WHERE-Schutz verhindert, dass ältere Feed-Daten neuere Werte überschreiben.

Weiterführende Ressourcen

Externe Quellen

• COPY – PostgreSQL Documentation
• psql \copy – PostgreSQL Documentation
• Populating a Database (Bulk-Loading-Tipps)
• Release Notes 17: COPY ON_ERROR ignore
• pg-copy-streams – npm

Verwandte Artikel

• INSERT, UPDATE, DELETE
• UPSERT mit ON CONFLICT
• psql – Einstieg
• Predefined Roles (pg_read_server_files)