How do you load a large volume of rows into a database efficiently?
You put the rows into one statement, or you use the tool that the engine gives you for exactly this job.
CREATE TABLE events (
id INTEGER PRIMARY KEY,
source VARCHAR(40) NOT NULL,
payload VARCHAR(200) NOT NULL
);
-- Many rows in one statement.
INSERT INTO events (id, source, payload) VALUES
(1, 'web', 'opened the page'),
(2, 'web', 'clicked the button'),
(3, 'app', 'opened the page');
-- A large load, in batches, inside one transaction.
BEGIN;
INSERT INTO events (id, source, payload) VALUES
(4, 'web', 'closed the page'),
(5, 'app', 'clicked the button');
COMMIT;
So what are the three ways, and which one should you use?
The first way is the many rows in one insert statement. It works everywhere and it is fast enough for most loads.
The second way is the command that the engine gives you for loading a file. PostgreSQL has COPY, MySQL has LOAD DATA INFILE, and SQLite has .import. These are the fastest of all, because the engine reads the file directly and skips the whole query parsing step.
PostgreSQL COPY events (id, source, payload) FROM '/tmp/events.csv' WITH (FORMAT csv, HEADER true);
MySQL LOAD DATA INFILE '/tmp/events.csv' INTO TABLE events FIELDS TERMINATED BY ',';
SQLite .mode csv
.import /tmp/events.csv events
The third way is an insert with a select, which is what you use when the rows are already in the database somewhere else.
So which one do you reach for? The command that loads a file when the rows are in a file. The many rows statement when the rows come from your program. And the insert with a select when the rows are already in another table.
The next question in this topic looks at the trade-offs.