Faster insert for multiple rows

As you all probably know the fastest way to get data into the database is the COPY statement, but there is also a means to speed up inserting multiple rows. PostgreSQL supports inserting multiple rows with one INSERT statment. The syntax is as follows:

INSERT INTO tablename (column, column...) VALUES (row1_val1, row1_val2...), (row2_val1, row2_val2)..;

I did some quick tests to compare the performance difference between multiple insert statments inside one transaction versus one multirow insert. The testset was 100K records and i run the tests for several times, the magnitude of difference in performance was constant over the tests, showing that multi-row insert is approximately 4 times faster than normal insert statements. Multi-row insert needs the statment to be parsed, prepared and changes written to WAL only once therefore resulting in less overhead.

Test:

tmpdb=# create table things (things_id serial primary key, thing text);
NOTICE:  CREATE TABLE will create implicit sequence "things_things_id_seq" for serial column "things.things_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "things_pkey" for table "things"
CREATE TABLE

Multi-row insert SQL file

insert into things (thing) values ('thing nr. 0'),
('thing nr. 1'),
('thing nr. 2'),
('thing nr. 3'),
...
('thing nr. 99999),
('thing nr. 100000);

Multiple insert statments SQL file

begin;
insert into things (thing) values ('thing nr. 0');
insert into things (thing) values ('thing nr. 1');
insert into things (thing) values ('thing nr. 2');
....
insert into things (thing) values ('thing nr. 99999');
insert into things (thing) values ('thing nr. 100000');
commit;

About these ads

About this entry