Re: Multiple COPY statements

On Thu, May 10, 2012 at 2:42 PM, Andy Colson <andy@squeakycode.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