I'm doing \copy from file into table. There are two files one with 7 million lines and the other with around 24 million and the data goes into separate table. There are only three columns in each file and four in each table (the primary key, id serial is the fourt). The data is about 150 MB and 450 MB and takes from 5 to 20 minutes to load into the database.
What I'm wondering about is what parameters to tweak to improve the operation and shorten the time of the \copy ? I think I have tweaked most of the available in postgresql.conf, that is shared_buffer, temp_buffers, work_mem, maintenance_work_mem, max_fsm_pages. Maybe someone could point out the one really related to \copy ?
I would hope that there is some way for me to improve the operation. I used to do the operation on a MySQL server and simple time measurements gives me a difference of a multiple 3 to 4, where the MySQL is faster.
I would also be satisfied to know if this is an expected difference.
Regards, Sigurgeir
