By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,514 Members | 1,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,514 IT Pros & Developers. It's quick & easy.

To insert 60million rows to database

P: 12
hi all,

how to insert 60million rows to the postgres datase. i want to increase the performance as it is taking 1min to insert every 1000rows. is there any easier way to do it. thanks in advance.
Oct 12 '06 #1
Share this Question
Share on Google+
4 Replies


P: 2
hi all,

how to insert 60million rows to the postgres datase. i want to increase the performance as it is taking 1min to insert every 1000rows. is there any easier way to do it. thanks in advance.
hello dusty,actually i wanted to know that whether u have got a solution to yhis problem or not.i m facing the same problem,so u if dont mind helping me in this,then do send me the solution.thanks
Jan 25 '07 #2

Expert 100+
P: 534
The insert/delete/update performance depends on how many idexes you have on the table, make sure you have only as many as you really need.

Perhaps even more importantly, when doing a bulk insert you should consider using the COPY command instead of the SQL "Insert into my_table ..."
This should speed up the process very significantly:

http://www.postgresql.org/docs/8.0/i.../sql-copy.html
Jan 25 '07 #3

iburyak
Expert 100+
P: 1,017
For bulk inserts I would suggest dropping indexes totally and then recreate them back after insert complete.
When data inserted with indexes on table especially clustered it rebuilds indexes after each insert. If it is a clustered index it physically moves data which takes even more time.
Rebuild indexes will take some time at the end but it will be done once and not each time record inserted.
Also check if you have triggers\constraints which should be dropped\recreated if possible.
Only triggers\constraints that check data integrity should stay in place.

Bulk insert should work faster because it doesn’t use transaction log and speeds up tremendously.
If you still want to use straight insert use Commit transaction after each 1000 records.
It will help clearing transaction log.

Good Luck.
Jan 29 '07 #4

P: 7
For bulk inserts I would suggest dropping indexes totally and then recreate them back after insert complete.
This assumes that you don't have concurrent queries trying to read from those tables. If you do, and they depend on the index you won't like the effect.

When data inserted with indexes on table especially clustered it rebuilds indexes after each insert. If it is a clustered index it physically moves data which takes even more time.
This is incorrect. PostgreSQL does not dynamically maintain clustering. AFAIK there are no plans to support dynamically maintained clustering at any time in the near future.

Rebuild indexes will take some time at the end but it will be done once and not each time record inserted.
Also check if you have triggers\constraints which should be dropped\recreated if possible.
Only triggers\constraints that check data integrity should stay in place.

Bulk insert should work faster because it doesn’t use transaction log and speeds up tremendously.
If you still want to use straight insert use Commit transaction after each 1000 records.
It will help clearing transaction log.

Good Luck.
Nobody seems to have mentioned using prepared transactions yet. Which should make a significant improvement.
Jan 29 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.