I want to increae the insertion rate in postgreSQL. Currently I am using the COPY command of postgres to insert data from a csv file. Can any one help me with a better approach?? Also can any one tell any file format other than CSV to increase the insertion rate??
There are a couple things you can do. Copy isn't parallelized so you can split your file and run multiple instances.
But generally the problem is indexes on the table you are copying to. You'll get some improvement by dropping the index, copying the data then recreating the index. But there is a pgFoundry project called pg_buikload that inserts the data and the index at the same time. You should check it out.
I'd recommend sticking w/ csv personally. But do a copy from a table to a file and you'll see that pg uses a tab separated format. I'm sure it requires less quoting because tabs will occur less often than commas in your data. But I doubt it will have much impact.