4 ms·
From personal experience: PostgreSQL's COPY commands aren't really all that performant, indexes or no. Our project saw SIGNIFICANTLY better performance with ba
by karlmdavis 9y ago
From personal experience: PostgreSQL's COPY commands aren't really all that performant, indexes or no.
Our project saw SIGNIFICANTLY better performance with batched multi-threaded INSERTs. If you can run a few hundred load threads and manage the concurrency correctly (not trivial), it will chew through big loads like a monster.
If I ever have the time/excuse, I want to go back and try a multi-threaded COPY. But if you need speed and have a choice between multi-threaded INSERTs or a single-threaded COPY, go with the INSERTs every time.
- davidgould 9y agoCOPY of course is single threaded. If you can split your input data and run multiple copy operations you can get similar increases although I would never suggest hundreds of threads. Depending on your IO system something like twice as many threads as CPU threads is probably going to work better. On a one for one basis COPY IN will be faster than inserts: - COPY uses a special optimization in the access method: instead of running the full insert logic (find a target page, lock it, insert, unlock page) per row, it batches all the rows that will fit on the target page. - COPY overall has shorter code paths than regular inserts.
- imtringued 9y agoWhy not just use regular batched inserts with unnest to turn several array parameters into a table instead of using some arcane hard to use SQL command?