0Pricing
PostgreSQL Performance & Query Optimization · 강의

COPY와 다중 행 INSERT 처리량 비교

실제 제약 조건에서 초당 행 수를 극대화하는 수집 방식을 벤치마크하고 선택하는 방법을 배웁니다.

COPY와 다중 행 INSERT 처리량 비교은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 1번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 PostgreSQL Performance & Query Optimization 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

Why Ingestion Speed Matters

When you load millions of rows into PostgreSQL, the method you choose determines whether the job takes seconds or hours. This lesson benchmarks the two main ingestion paths: COPY and multi-row INSERT.

  • COPY streams rows through a single, optimized bulk path.
  • Multi-row INSERT packs many tuples into one statement to amortize round-trips.

The right choice depends on data source, round-trip cost, and how the rows arrive.

The Naive Baseline: Single-Row INSERT

The slowest pattern is one row per statement. Each statement pays for parsing, planning, a network round-trip, and (without batching) a separate commit.

At thousands of rows, the per-statement overhead dominates and throughput collapses. This is the baseline every other method beats.

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

Multi-Row INSERT: Amortizing Round-Trips

A multi-row INSERT lists many tuples in one statement. You pay parse and plan cost once, send one network round-trip, and commit the whole batch together.

  • A good batch size is typically 500 to 5,000 rows per statement.
  • Going much higher gives diminishing returns and bloats the parsed statement.
-- 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: The Bulk Highway

COPY is PostgreSQL's purpose-built bulk loader. It bypasses per-row statement parsing entirely and streams rows through a tight loop, making it usually the fastest way to ingest large volumes.

  • COPY ... FROM loads data into a table.
  • It reads text, CSV, or binary formats.

Server-side COPY FROM 'file' requires superuser or the pg_read_server_files role; clients usually use \copy instead.

-- 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 and COPY FROM STDIN

When the file lives on the client (not the server), use psql's \copy meta-command or COPY ... FROM STDIN. These stream data over the existing client connection, so no special server file privileges are needed.

Most ETL drivers (psycopg, JDBC, libpq) expose a streaming COPY FROM STDIN API that is the fastest programmatic load path.

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

Why COPY Wins: Less Per-Row Work

The throughput gap comes from what each row costs:

  • Single INSERT: parse + plan + execute + round-trip + commit per row.
  • Multi-row INSERT: parse + plan once per batch; still builds a full parse tree for every tuple.
  • COPY: no SQL parsing per row at all; values are decoded directly into tuples.

COPY also generates fewer WAL records per row of work, which is a large part of its speed advantage.

Benchmarking Fairly

To compare methods honestly, hold everything else constant and measure wall-clock time plus rows per second. Use \timing in psql, or wrap loads in a timed harness.

  • Load the same dataset each run.
  • TRUNCATE between runs so you start from an empty, comparable state.
  • Run each method a few times and take the median to dampen noise.
\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

Transactions and Commit Costs

A common reason single-row INSERTs are slow is one commit per row. Each commit forces a WAL flush (an fsync) to disk. Wrapping many inserts in a single transaction collapses thousands of fsyncs into one.

Both COPY and multi-row INSERT already commit per-statement, but if you script many statements, wrap them in one explicit transaction.

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;

Indexes, Triggers, and Constraints Slow Loads

The fastest ingestion method still crawls if every inserted row must update five indexes and fire triggers. A classic bulk-load tactic is to load first, then build indexes.

  • Drop or disable non-essential indexes, then recreate them after the load.
  • Building an index once over the full table is far cheaper than maintaining it row by row.
  • Disable expensive triggers during the load when the data is trusted.
-- 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 Tables and Staging

For transient staging data, an UNLOGGED table skips WAL writes entirely, which can dramatically speed up loads. The trade-off: unlogged tables are not crash-safe and are truncated after a crash.

A robust ETL pattern loads into an unlogged or temporary staging table with COPY, transforms it, then inserts the clean result into the durable target.

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

Choosing Under Real Constraints

Pick the method that fits how the data arrives:

  • Bulk file or stream available? Use COPY / \copy — it is the throughput winner.
  • Rows arrive programmatically in code? Prefer your driver's COPY FROM STDIN; if unavailable, fall back to multi-row INSERT in batches of 500-5,000.
  • Need per-row conflict handling (ON CONFLICT)? COPY can't do that — use multi-row INSERT, or COPY to a staging table then 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;

Quick Check: Maximize Throughput

Test your understanding of the core ingestion decision.

Recap

You can now benchmark and choose ingestion methods deliberately:

  • COPY / \copy is the throughput winner for bulk file and streaming loads; it skips per-row parsing and minimizes WAL.
  • Multi-row INSERT (500-5,000 rows per statement) beats single-row inserts and is the fallback when you need ON CONFLICT.
  • Wrap loads in one transaction to avoid per-row fsync costs.
  • Drop indexes, disable triggers, use UNLOGGED staging tables to remove per-row overhead, then rebuild.
  • Benchmark fairly: same data, TRUNCATE between runs, \timing, take the median rows/second.

자주 묻는 질문

“COPY와 다중 행 INSERT 처리량 비교” 강의는 무료인가요?

네 — “COPY와 다중 행 INSERT 처리량 비교” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“COPY와 다중 행 INSERT 처리량 비교”에서 뭘 배우나요?

실제 제약 조건에서 초당 행 수를 극대화하는 수집 방식을 벤치마크하고 선택하는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 1번째 강의입니다.

“COPY와 다중 행 INSERT 처리량 비교” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. COPY와 다중 행 INSERT 처리량 비교
  2. 로드 중 인덱스와 제약 조건 지연
  3. 수집을 위한 WAL과 체크포인트 조정
  4. ON CONFLICT를 활용한 대규모 업서트
← PostgreSQL Performance & Query Optimization(으)로 돌아가기