From: | Lee Hachadoorian <Lee(dot)Hachadoorian+L(at)gmail(dot)com> |
---|---|
To: | Andy Colson <andy(at)squeakycode(dot)net> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Multiple COPY statements |
Date: | 2012-05-10 19:00:52 |
Message-ID: | CANnCtnLsSiVWWuCmF8yMuK+NUzwtYxGM8pkNfdah=juy5rd0mw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-05-10 19:08:27 | Re: Custom Domain; migration from 8.4 to 9.1 and COLLATE |
Previous Message | Andy Colson | 2012-05-10 18:42:38 | Re: Multiple COPY statements |