postgresql database insert takes too long

Question

Is this slow? I have a table with 4 columns ID Surname Coach City

I have a add data button, that adds 300 rows, each containing data in each column, so a total of 1200 records.

It takes just over 4 minutes to add the data physically (that means from when i hit the button to when it displays the message box completed).

I am on ADSL2+ in australia, speed test done just after this and got 28ms ping 14.12Mbps Download 0.49Mbps Upload

I know Australian internet is a joke, but this seems REALLY slow!

Is this just because of my internet upload speeds?

are you doing these inserts inside a transaction? (set autocommit off) — nurettin, Jan 30 '14 at 13:04
See [How to speed up insertion performance in postgresql](http://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql) — Craig Ringer, Jan 31 '14 at 04:17
are you trying to insert data in your local system??? and you could please send me the insertion code so that i can help you but as i am also using postgres for a long an handels almost 2395.833333333 bytes/second that is 23 rcord having 20 columns over 1mbps line so it canot be slow — smn_onrocks, Jan 31 '14 at 04:49

score 3 · Answer 1 · edited May 23 '17 at 11:44

Doing individual inserts, in individual transactions, is the worst possible performance you're going to get. You're incurring at least one network round trip per inserted row, and at 300 rows that's going to be significant - that's about 10s spent on latency alone, and that's if you're only doing one round trip per insert, and if your 28ms latency holds for larger packets. That's not considering time on COMMITs, either.

If your database driver sends separate BEGIN and COMMIT commands, that's another 20s spent on network latency alone, for 300 BEGINs and 300 COMMITs.

Do multi-valued inserts in chunks, within a single transaction. Or better still, stream the data with COPY. That way you minimize round-trips to reduce the impact of network latency, and reduce the impact of commit costs too.

See How to speed up insertion performance in PostgreSQL for more information and useful links.

You should be able to do a batch like this in a few hundred milliseconds.

score 2 · Answer 2 · answered Jan 30 '14 at 13:46

Its possible that there are indexes on table you insert into. Remember that each index needs to be updated when insert operation is performed.

You can try:

select * from pg_indexes where tablename = 'your_table';

to list all your indexes.

Additionally opening/closing connection for each insert will decrease performance. Have you considered single-insert SQL query?

INSERT INTO "table" ( col1, col2, col3)
  VALUES ( 1, 2, 3 ) , ( 3, 4, 5 ) , ( 6, 7, 8 );

score 1 · Answer 3 · answered Jan 30 '14 at 13:45

You will probably benefit most by reducing the number of insert statements you are using, as the ping time becomes less relevant when you reduce the number of requests and responses.

See the example here: http://www.postgresql.org/docs/9.0/static/dml-insert.html

postgresql database insert takes too long

3 Answers3