Every night at 01:00 a partner drops a set of CSV files on an object store: about 40 gigabytes, roughly 300 million rows of clickstream-shaped events, to be loaded, deduplicated, and aggregated before 06:00. I have run this feed twice, once against MySQL 8.0 and later against PostgreSQL 15 after a migration. The first naive version on each platform did exactly what every junior DBA tries: chunked INSERT statements in a loop. On MySQL that took 52 minutes and generated a binlog storm that put the replica twenty minutes behind before the load even finished. On PostgreSQL the same approach took about as long and generated WAL at a rate that made the archive fall behind.
Both engines have a native bulk path that makes the INSERT loop look silly: LOAD DATA INFILE on MySQL, COPY on PostgreSQL. The load dropped to single-digit minutes on each. But the two commands are not the same tool with different spelling. They differ on who reads the file, on the security model around that question, on how the load interacts with replication, and on what you can safely disable to go faster. This article is the comparison, plus the tuning sequence I actually run on both.
Why is the native loader so much faster than batched INSERTs?
Because it skips most of the per-statement machinery. A batched INSERT pays parsing, optimization, transaction boundaries, and per-batch commit durability every few hundred rows. The native loaders parse the file once, stream rows through a dedicated code path, and amortize everything else. The numbers from our feed tell the story: 52 minutes of INSERT batches became about 7 minutes of LOAD DATA on MySQL, and a similar INSERT loop became about 6 minutes of COPY on PostgreSQL, on comparable hardware with comparable durability settings.
Here is the shape of both commands for the same file:
-- MySQL 8.0
LOAD DATA INFILE '/var/lib/mysql-files/events_20250804.csv'
INTO TABLE events_staging
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(event_time, user_id, event_type, payload);
-- PostgreSQL 15
COPY events_staging (event_time, user_id, event_type, payload)
FROM '/srv/feeds/events_20250804.csv'
WITH (FORMAT csv, HEADER true);Both want the data loaded into a staging table first, and I want to be emphatic about that pattern because both engines reward it: load raw into an unindexed staging table with the native loader, then validate, deduplicate, and merge into the real table with set-based SQL inside a transaction. The native loaders are fast precisely because they do not want to be your constraint enforcer or your deduplicator; a duplicate key during LOAD DATA or COPY is either an error that aborts the load or a silently skipped row, and neither behavior is a merge. Staging turns the merge into an INSERT ... SELECT with a NOT EXISTS or an ON CONFLICT clause, which is set-based, logged once, and testable.
Who reads the file, and why is LOCAL a security story?
LOAD DATA INFILE reads a file on the database server's filesystem, requires the FILE privilege, and on any sensibly configured MySQL 8.0 is fenced by secure_file_priv, which restricts server-side file reads to one designated directory. LOAD DATA LOCAL INFILE is the dangerous cousin: the client sends the file, which inverts the trust relationship. A rogue or compromised server can request any file the client process can read, and there is a decade of proof-of-concept attacks built on exactly that, which is why local_infile defaults to off in current MySQL releases and many client drivers disable it by default on their side as well. Our feed pipeline runs with LOCAL disabled everywhere; the loader host copies files into the secure_file_priv directory over an authenticated channel, and the database reads them server-side.
PostgreSQL draws the same line with different names. COPY FROM 'path' reads a server-side file and requires superuser or membership in pg_read_server_files. The psql \copy meta-command reads a client-side file and streams it over the protocol as COPY FROM STDIN, but the security posture differs from MySQL LOCAL in a way that matters: the client chooses the file and pushes it; the server cannot request arbitrary client files, because the protocol has no such request. The practical playbook is symmetric anyway. Prefer server-side reads from a fenced directory for scheduled loads, use client-side streaming (\copy or a driver that speaks the COPY protocol) when the file legitimately lives elsewhere, and never enable MySQL's local_infile on a production client to save yourself a file copy.
-- PostgreSQL: what client-side streaming looks like in the protocol
-- psql \copy expands to this over the wire; drivers expose the same
COPY events_staging (event_time, user_id, event_type, payload)
FROM STDIN WITH (FORMAT csv);What does the load do to replication and durability?
On MySQL, LOAD DATA is binlogged as the file content plus the statement, and replicas fetch the file through the replication stream and re-execute the load locally. That means a 40GB LOAD DATA moves roughly 40GB through the binlog, and the replica's apply thread re-does the parse and insert work serially. Our original 52-minute INSERT-loop load actually put the replica less far behind than the 7-minute LOAD DATA did at first, because the replica had to replay the entire load through one applier thread before it could continue. The fix was not slower loads; it was parallel applier workers, which by then we should have had on anyway, plus loading into a staging table that the replica can apply without contending with the merge step.
On PostgreSQL, COPY generates WAL like any other write, one record stream for all the rows, and the standby applies it as it arrives; there is no file transfer and no re-parse, so standby apply is cheap relative to MySQL's re-execution. The interesting lever is when WAL can be skipped entirely: if wal_level is minimal and the table was created or truncated in the same transaction as the COPY, PostgreSQL can skip WAL for the bulk data. Minimal WAL is rare in production because it rules out replication and PITR, so treat that optimization as a data-warehouse trick, not an OLTP one. The realistic PostgreSQL optimizations are different: COPY into an UNLOGGED staging table (no WAL for the staging step, at the cost of crash durability and no standby copy of the staging data), and COPY ... FREEZE for freshly created or truncated tables, which sets tuples frozen at load time and spares vacuum work later. The deeper treatment of these knobs is in the COPY bulk-loading guide.
What do you disable to go faster, and what do you get back?
The classic MySQL advice, disable keys during the load, is a MyISAM answer to an InnoDB question. ALTER TABLE ... DISABLE KEYS only affects MyISAM non-unique indexes; on InnoDB it does nothing, and there is no supported way to defer InnoDB secondary index maintenance during a load. What you can do on MySQL: load into a staging table with only the indexes you need for the merge, load in primary-key order (a pre-sorted file loads measurably faster because the clustered index inserts append cleanly instead of splitting pages), drop foreign keys on the staging table, and keep unique_checks on unless you can prove the file is clean, because turning them off buys speed and sells you duplicates you will pay for later. The session-level foreign_key_checks and unique_checks switches are legal but session-scoped, so a connection pool will happily run half your load with checks on and half without; I have cleaned up that exact mess.
On PostgreSQL, the honest accelerators are the staging-table pattern, UNLOGGED staging, dropping the staging table's indexes and rebuilding them after the load (cheap on staging, and the rebuild uses maintenance_work_mem, which you should raise for the load window), and loading partitions in parallel when the target is partitioned. Triggers and foreign keys still fire during COPY, row by row, so a staging table should have neither. One thing neither engine gives you: an online, index-maintaining, constraint-checking, replication-free fast path into the live table. Every bulk-load recipe that promises that is moving the cost somewhere you have not looked yet, usually to the replica.
-- PostgreSQL: the staging-to-live merge, set-based and transactional
BEGIN;
COPY events_staging (event_time, user_id, event_type, payload)
FROM '/srv/feeds/events_20250804.csv'
WITH (FORMAT csv, HEADER true);
INSERT INTO events (event_time, user_id, event_type, payload)
SELECT s.event_time, s.user_id, s.event_type, s.payload
FROM events_staging s
ON CONFLICT (user_id, event_time, event_type) DO NOTHING;
TRUNCATE events_staging;
COMMIT;How does the load window look to monitoring?
A nightly bulk load is a scheduled stress test, and it should be watched like one. On the PostgreSQL side, the signals are WAL generation rate during the window, replication lag on the standby, the merge step's row counts and timing in pg_stat_statements, and the autovacuum wave the load schedules for tomorrow morning, and MonPG's PostgreSQL monitoring keeps all of that in one place, so a load window that creeps from 6 minutes to 40 over a quarter is a trend line, not a surprise outage at 05:30.
MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the load-window counters are the ones it will surface: binlog volume per load, replica apply lag through the replay of the load event, and the digest timing that tells you the merge statement is the new bottleneck. Until then, the MySQL monitoring page tracks that work, and the core lesson holds on both engines: the native loader is the fastest write path you have, the file's trust boundary is a security decision, and the replica pays for every row you load, whichever way it finds out about them.