On 5/10/2012 2:00 PM, Lee Hachadoorian wrote:
On Thu, May 10, 2012 at 2:42 PM, Andy Colson<andy@xxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
