Re: Improve COPY performance into table with indexes.

On 4/3/20 9:52 AM, Adrian Klaver wrote:
> On 4/2/20 9:42 PM, James Brauman wrote:
>> I am using the COPY command to insert 10 million rows from a CSV file
>> into a database table and I am finding the performance is
>> unacceptable. When the COPY command is executed the disk I/O is
>> extremely high which leads to degraded query performance for other
>> queries being executed on the database.
>>
>> I have tried removing the indexes from the database table and this
>> dramatically improved performance (both reducing the execution time
>> and reducing disk I/O).
>
I've had excellent results dealing similar data quantity with the
earlier suggestion of writing to a staging table.  Simply splitting the
insert statements to cover 1/16[1] of the data turned untenable into
real-time.

[1] I split on an id column which is of UUID type.  Any arbitrary
splitting that reduces the footprint sufficiently will do.