อัตราการส่งผ่านของ COPY เทียบกับ INSERT หลายแถว

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

Test your understanding of the core ingestion decision.

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.