PostgreSQL: Need to chose between COPY'ing from a file or from stdin

Question

I'm inserting millions of records, coming from C++ structures. With regular inserts, I'm getting a terrible performance, with the database taking up 98% of program time, even after config optimization. I'm reading that I should use COPY to import it from a CSV-file.

Now I'm not so sure if writing to a CSV-file first and then rewriting it to the DB will be much of an improvement, since the writing will double. I've looked at piping the STDIN, but at first glance it seems like there's also much overhead, and it's limited

If I've got a string in CSV-format, what would be the quickest way to write that data to my DB?

Thank you in advance,

CX

Are these millions of records the same every time? Is your database not persistent? — robert, Sep 03 '12 at 18:32
The same columns, different data. The database is persistent, afaik. — Breina, Sep 03 '12 at 18:41
Do you have any indexes on the table? Triggers? Constraints? Are you batching the inserts inside transactions? — mu is too short, Sep 03 '12 at 18:49
No indexes besides PK, no triggers, tons of FK's. No batching. — Breina, Sep 03 '12 at 19:07
Read this: http://stackoverflow.com/questions/758945/whats-the-fastest-way-to-do-a-bulk-insert-into-postgres — Daniel Vérité, Sep 03 '12 at 19:34
What sort of overhead do you expect from piping from stdin? And how it is limited? — dezso, Sep 03 '12 at 22:25

Tometzky · Accepted Answer · 2012-09-06T12:13:20.877

I assume you need to do it once on an offline database. If you need to do it on an online database there's not much you can do besides using a your-program-generating-a-copy-command | psql or PQputCopyData from your program.

Disable all other access to database, terminate all client connections.
Backup a database without your bulk data, as this procedure is dangerous (a crash, reboot or power failure during this will make your database corrupted beyond repair).
Shutdown database.
Move pg_xlog directory from data directory to a tmpfs (RAM-disk), symlink it.
Run postgres -F -c full_page_writes=off -c checkpoint_segments=128 …. It will need about 2GB of free RAM more than usually — be prepared.
Drop primary key contraint from your table.
Drop foreign key constraints from your table.
Load data using COPY from stdin and a pipe or PQputCopyData.
Run analyze.
Recreate primary key constraint.
Recreate foreign key constraints (a file generated by pg_dump before dropping them will have suitable commands near the end).
Shutdown database.
Delete pg_xlog symlink and move pg_xlog directory back to data directory.
Run sync command on server.
Start database normally.

This is based on Populating a Database guide from documentation.

PostgreSQL: Need to chose between COPY'ing from a file or from stdin

1 Answers1