Bulk Data Transfer Protocols (COPY, LOAD DATA)

Relational databases are optimized for transactional workloads where individual rows are inserted, updated, or deleted within the scope of ACID guarantees.
But loading millions or billions of rows through standard INSERT statements is brutally inefficient.
Every single-row INSERT incurs parsing overhead, query planning, constraint checking, index maintenance, WAL (write-ahead log) flushing, and lock acquisition.
Bulk data transfer protocols exist to bypass or amortize these per-row costs, often achieving throughput improvements of 10x to 100x over naive row-at-a-time ingestion.

This article examines the internal mechanics of bulk loading in PostgreSQL (COPY) and MySQL (LOAD DATA INFILE), focusing on the protocol-level and storage-engine-level optimizations that make them fast.

Why Row-at-a-Time INSERT Is Slow

To appreciate bulk protocols, consider what a standard INSERT INTO t VALUES (...) does:

1. Parse the SQL text into an AST.
2. Analyze and rewrite the query, resolving table and column references.
3. Plan the insertion (trivial for inserts, but the planner still runs).
4. Execute: acquire row-level locks, perform constraint checks (NOT NULL, CHECK, foreign keys, uniqueness), insert the heap tuple, update every secondary index, and write WAL records.
5. Commit: flush the WAL to durable storage (if synchronous_commit is on).

For a batch of N rows sent as individual statements, each step repeats N times.
Network round trips alone can dominate, if the client-server round trip is 0.5ms, inserting one million rows takes at minimum ~500 seconds just in network latency, ignoring all other work.

The COPY Protocol (PostgreSQL)

PostgreSQL's COPY command operates in two modes: COPY ... FROM (server reads a file) and COPY ... FROM STDIN (client streams data over the connection).
The latter is what client libraries like libpq use via the PQputCopyData API.

Wire Protocol

When the client issues COPY ... FROM STDIN, PostgreSQL enters a special sub-protocol within its frontend/backend message flow.
The client sends CopyData (message type 'd') messages, each containing a chunk of the input stream.
The server accumulates these chunks and parses them according to the specified format (text, CSV, or binary).
The client signals completion with CopyDone (message type 'c'), and the server responds with a CommandComplete message indicating the row count.

This eliminates per-row round trips entirely, and the client streams data as fast as the network and server can absorb it.

Internal Processing Pipeline

Once the server receives the raw byte stream, the processing path diverges significantly from the standard executor:

1. Input parsing: The CopyFrom function reads the stream, splits it into fields using the delimiter, and applies input functions to convert text representations into internal Datum values. In binary mode, fields are encoded in a defined network format (e.g., big-endian integers) rather than text, avoiding costly text-to-binary conversion for types such as numeric, timestamp, or uuid.

2. Batch insertion into the heap: PostgreSQL uses a BulkInsertState that tracks a target block and extends the relation in multi-block chunks. This reduces the per-tuple overhead of locating free space in the heap compared to standard heap_insert, which searches the free space map on every call. Buffer pins are still released between pages, but the target block hint avoids repeated free-space-map lookups.

3. WAL optimization: For unlogged tables, or when wal_level = minimal and the heap relation was created or truncated in the same transaction, PostgreSQL can skip WAL for heap data writes entirely, writing directly to heap files. In the minimal-WAL case, the relation is not yet visible to any other transaction, so it is safe to forgo WAL for data pages. If indexes also exist on the relation and were created in the same transaction, their data pages may similarly bypass WAL; however, the practical and recommended approach is to load data first into a table without indexes, then build indexes afterward, entirely avoiding incremental index WAL writes during the load. For logged tables with pre-existing indexes, all index page writes do go through WAL.

4. Index maintenance: Each tuple still requires index insertion for every index on the target table. This is a major cost. For this reason, a common pattern is to drop indexes before COPY, then rebuild them afterward using CREATE INDEX, which uses a sort-based bulk index build rather than incremental insertion.

5. Constraint checking: NOT NULL and CHECK constraints are verified per-tuple. Foreign key triggers, if present, are deferred and fired at commit time. Uniqueness is enforced via the index insertion.

Performance Knobs

Disabling fsync, increasing checkpoint_completion_target, setting max_wal_size high, and using UNLOGGED tables are common techniques to accelerate bulk loads.
The maintenance_work_mem parameter governs the memory available for post-load index builds.

LOAD DATA INFILE (MySQL/InnoDB)

MySQL's LOAD DATA INFILE is the equivalent bulk ingestion command.
Its internals differ because of InnoDB's clustered index architecture.

Clustered Index Implications

InnoDB stores table data in primary key order within a B+tree (the clustered index).
Inserting rows in primary key order is critical; random-order inserts cause page splits, buffer pool churn, and excessive random I/O.
If the input data is not sorted by primary key, LOAD DATA performance degrades significantly compared to sorted input.

Change Buffer Optimization

For secondary indexes, InnoDB uses the change buffer (formerly the insert buffer).
When a secondary index page is not in the buffer pool, InnoDB records the pending index modification in the change buffer rather than performing a random read to fetch the page.
These buffered changes are merged lazily when the page is eventually read.
During bulk loads, this dramatically reduces random I/O for secondary index maintenance.

Redo Log and Doublewrite Buffer

Each row inserted through LOAD DATA still generates redo log entries, but InnoDB batches them.
The innodb_log_buffer_size parameter controls the in-memory buffer for redo records.
Larger buffers reduce the frequency of log flushes.
Setting innodb_flush_log_at_trx_commit = 0 or 2 during the load sacrifices durability guarantees but can double throughput by avoiding synchronous disk flushes on every commit.

Bulk Load Optimizations

The SET FOREIGN_KEY_CHECKS = 0 and SET UNIQUE_CHECKS = 0 session variables allow skipping constraint verification during the load, with the understanding that the data is known to be clean.
Disabling the binary log (SET sql_log_bin = 0) eliminates replication overhead during the load.
For high-performance parallel loading, the MySQL Shell utilities util.importTable() and util.loadDump() provide chunked parallel ingestion that significantly outperforms single-threaded LOAD DATA INFILE.

Walkthrough

The following walkthrough illustrates the internal pipeline of a PostgreSQL COPY FROM operation at a systems level.

COPY target_table FROM STDIN WITH (FORMAT csv);

Step 1: Parser
  - Recognize COPY command, extract table name, column list, options.
  - No query plan generated. Direct dispatch to CopyFrom().

Step 2: Open BulkInsertState
  - Allocate a BulkInsertState struct.
  - Record the target relation and initial target block hint.

Step 3: Stream Processing Loop
  FOR each chunk received via CopyData messages:
    Split chunk into lines using the record delimiter (\n).
    FOR each line:
      a. Tokenize by field delimiter (comma for CSV).
      b. Apply input functions: text -> Datum conversion per column.
      c. Form a HeapTuple from the Datum array.
      d. Check NOT NULL constraints.
      e. Check CHECK constraints.
      f. Fire BEFORE ROW INSERT triggers (if any).
         (Triggers may modify or abort the tuple before insertion.)
      g. Call heap_insert() with BulkInsertState:
         - If current target page has space, insert tuple, mark page dirty.
         - If not, extend relation (multi-block chunk), pin new page, insert.
         - Write WAL record (unless WAL is skippable for this relation).
      h. For each index on the table:
         - Extract index key from tuple.
         - Call index_insert() on the index relation.
      i. Fire AFTER ROW INSERT triggers (fires per-row immediately
         after heap insertion; distinct from AFTER STATEMENT triggers
         which fire at step 4).
      j. Increment processed row counter.

Step 4: CopyDone received
  - Release BulkInsertState.
  - Fire AFTER STATEMENT triggers.
  - Fire deferred constraint triggers (FK checks).
  - Update pg_class.reltuples statistics estimate.
  - Return CommandComplete with row count.

Step 5: Transaction Commit
  - Flush WAL to disk.
  - Release all locks.

In this pipeline, the critical savings come from: no parse/plan cycle per row, amortized heap free-space lookup, streamed network transfer, and, when conditions allow, WAL bypass for heap data.

Binary vs. Text Format

Both PostgreSQL and MySQL support binary or semi-binary bulk formats.
PostgreSQL's binary COPY format encodes each field as a 4-byte length prefix followed by the field's network-format binary representation (e.g., big-endian for integer types).
This avoids text-to-internal conversion overhead.
The tradeoff is portability: binary formats are version-specific, and care must be taken with endianness and type representation across platforms.
Text mode is slower but universally compatible and human-readable.

MySQL's LOAD DATA is text-oriented.
The MySQL Shell's util.importTable() and util.loadDump() utilities provide higher-performance alternatives with parallel chunked loading.

Comparison with External Table Approaches

Some systems (Oracle External Tables, Greenplum gpfdist, ClickHouse File engine) take a different approach: instead of streaming data through the client-server protocol, they expose files directly to the query engine.
The database reads files in parallel from local disk or distributed filesystems.
This avoids protocol overhead entirely but requires the data to be accessible from the server's filesystem, which introduces security and operational constraints.

Key Points

• Bulk protocols amortize per-row costs (parsing, planning, network round trips) across the entire data stream, yielding order-of-magnitude throughput gains over individual INSERT statements.
• PostgreSQL's COPY uses a streaming sub-protocol that eliminates per-row client-server round trips and maintains a BulkInsertState to reduce heap free-space lookup overhead.
• InnoDB's clustered index design makes primary key ordering of input data critical for LOAD DATA performance; random key order causes excessive page splits.
• InnoDB's change buffer defers secondary index page reads during bulk inserts, converting random I/O into sequential I/O during later merge operations.
• Dropping and rebuilding indexes around a bulk load is often faster than maintaining them incrementally, because sort-based index construction outperforms repeated B-tree insertions — and also avoids index WAL writes during the load itself.
• WAL bypass is possible in PostgreSQL for heap data when loading into tables created or truncated within the same transaction at wal_level = minimal, eliminating a major I/O bottleneck. The recommended pattern is to also create indexes after the load, so no index WAL writes occur during ingestion.
• Disabling constraint checks (foreign keys, and uniqueness) during loading shifts validation costs out of the hot path, but places correctness responsibility on the operator.
• BEFORE ROW INSERT triggers fire before heap insertion and may modify or abort individual tuples; row-level AFTER ROW INSERT triggers fire immediately after each row's insertion; AFTER STATEMENT triggers fire once at the end of the COPY command.

References

PostgreSQL Global Development Group. "PostgreSQL 16 Documentation: COPY." https://www.postgresql.org/docs/current/sql-copy.html

Oracle Corporation. "MySQL 8.0 Reference Manual: LOAD DATA Statement." https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Ramakrishnan, Raghu and Johannes Gehrke. "Database Management Systems." 3rd Edition, McGraw-Hill, 2003.