Re: Multiple COPY statements

On 5/10/2012 2:00 PM, Lee Hachadoorian wrote:
> On Thu, May 10, 2012 at 2:42 PM, Andy Colson<andy(at)squeakycode(dot)net> wrote:
>> On 5/10/2012 1:10 PM, Lee Hachadoorian wrote:
>>>
>>> 2) Is there a performance hit to doing a COPY to more than one table
>>> in the same transaction?
>>
>>
>> No, I don't think so. I assume you are the only user hitting the
>> import_table, so holding one big transaction wont hurt anything.
>
> Actually what I mean is that there are multiple import tables,
> import_table1 ... import_table100. But it is true that I would be the
> only user hitting the import tables.
>
>>> Any other advice will be appreciated.
>>
>>
>> To really speed it up, you'd need to run multiple concurrent connections
>> each doing COPY's. Maybe up to the number of cores you have. (of course
>> you dont want each connection to fire off truncates, but concurrent should
>> trump "skip wall" in terms of speed).
>>
>> If import_table is just a temp holding stot you can look into temp and/or
>> unlogged tables.
>
> Yes, it is a staging table, data needs to be manipulated before
> shunting to its desired destination. I think unlogged tables will be
> helpful, and if I understand correctly then I wouldn't need to use the
> BEGIN; TRUNCATE; COPY...; END; trick. And would unlogged + concurrent
> connections work together?
>
> --Lee
>

Oh yes. concurrent + unlogged would be the best of all worlds.

-Andy