Part 187: High-Performance Bulk Data Loading: COPY & Unlogged Tables

Ingesting millions of records in seconds instead of hours.

1. The Cost of Individual INSERT Statements

Executing 1,000,000 separate INSERT INTO ... VALUES (...) statements requires: - 1,000,000 network round trips. - 1,000,000 transaction log syncs (WAL flushes). - Constant B-Tree index balancing on every row. - Total time: ~45 minutes.

2. The Native Bulk Copy Protocol

The database bulk protocol streams raw binary or CSV blocks directly into table heap pages:

-- PostgreSQL COPY command (Streams directly from file/stdin):
COPY raw_telemetry (device_id, temp, recorded_at)
FROM '/data/sensor_readings.csv'
WITH (FORMAT csv, HEADER true, BUFFER_SIZE 65536);

-- MySQL LOAD DATA INFILE:
LOAD DATA INFILE '/data/sensor_readings.csv'
INTO TABLE raw_telemetry
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 ROWS;

Total time: ~3.5 seconds (750x faster!).

3. The 4 Golden Rules of Bulk Ingestion

1. Drop Indexes & Foreign Keys First: Rebuilding an index in bulk with CREATE INDEX after ingestion is 10x faster than updating the B-Tree for every incoming row.
2. Use Unlogged Staging Tables: sql -- Bypasses WAL logging completely! CREATE UNLOGGED TABLE staging_data (LIKE final_table);
3. Disable Autocommit: Wrap batches in explicit BEGIN ... COMMIT blocks of 50,000 rows.
4. Run ANALYZE Immediately After: Updates optimizer histograms so future queries don't choose bad plans.

Key Takeaways

• Always load into an unlogged staging table via COPY, index it, and then swap into production.

In Part 188, we explore Change Data Capture (CDC): WAL Streaming & Debezium!