Bulk insert with returning IDs performance

Question

I've got a table which I want to insert around 1000 items each query, and get their PK after creation, for later use as FK for other tables.

I've tried inserting them using returning syntax in postgresql. but it takes around 10 sec to insert

INSERT INTO table_name (col1, col2, col3) VALUES (a1,a2,a3)....(a(n-2),a(n-1),a(n)) returning id;

By removing RETURNING I get much better performance ~50ms.

I think that if I can get an atomic operation to get the first id and insert the rows at the same time I could get better performance by removing the RETURNING. but don't understand if that is possible.

In general RETURNING an ID does not slow down significantly an insert. Can you show a reproducible case? — Daniel Vérité
– Daniel Vérité, Commented Oct 23, 2013 at 14:03
@DanielVérité it seems that I get slower performance from another query and not from inserting. — shevski
– shevski, Commented Oct 24, 2013 at 4:57
@shevski Could you clarify? Does this mean that you eventually found equal performance with and without RETURNING? — Charl Botha
– Charl Botha, Commented Apr 14, 2014 at 13:34
@CharlBotha Well this is an old post, But I remember that I got similar performance — shevski
– shevski, Commented Apr 16, 2014 at 6:04

yaroslav · Accepted Answer · 2013-10-23 19:05:36Z

1

Generate id using the nextval http://www.postgresql.org/docs/9.1/static/sql-createsequence.html

CREATE TEMP TABLE temp_val AS(
    VALUES(nextval('table_name_id_seq'),a1,a2,a3), 
        (nextval('table_name_id_seq'),a1,a2,a3) 
);
INSERT INTO table_name (id, col1, col2, col3)(
    SELECT column1,column2,column3,column4 
    FROM temp_val
);
SELECT column1 FROM temp_val;

answered Oct 23, 2013 at 19:05

yaroslav

262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

piro Over a year ago

@shevski yes it is: the nextval() result won't be used by other concurrent sessions.

Collectives™ on Stack Overflow

Bulk insert with returning IDs performance

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related