From: Bob Lunney <bob_lunney@yahoo.com>
To: Mike Blackwell <mike.blackwell@rrd.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Savepoints in transactions for speed?
Date: Tue, 27 Nov 2012 18:16:23 -0600
Message-ID: <2000E26E-35FC-4801-90F3-3CFBDA7B41DF@yahoo.com> (raw)
In-Reply-To: <CANPAkgs_LvZV-fabTNN-ahiVcwSBAyOnArw1v+QbCePg7on6ZA@mail.gmail.com>
References: <CANPAkgs_LvZV-fabTNN-ahiVcwSBAyOnArw1v+QbCePg7on6ZA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Mike,
Is there anything that the 1.5 million rows have in common that would allow you to use partitions? if so, you could load the new data into a partition at your leisure, start a transaction, alter the partition table with the old data to no longer inherit from the parent, alter the new partition table to
inherit from the parent, commit, then drop the old table. This operation would be very fast, the users probably won't even notice.
Bob Lunney
On Nov 27, 2012, at 4:04 PM, Mike Blackwell <mike.blackwell@rrd.com> wrote:
> I need to delete about 1.5 million records from a table and reload it in one transaction. The usual advice when loading with inserts seems to be group them into transactions of around 1k records. Committing at that point would leave the table in an inconsistent state. Would issuing a savepoint every 1k or so records negate whatever downside there is to keeping a transaction open for all 1.5 million records, or just add more overhead?
>
> The data to reload the table is coming from a Perl DBI connection to a different database (not PostgreSQL) so I'm not sure the COPY alternative applies here.
>
> Any suggestions are welcome.
>
>
> Mike
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bob_lunney@yahoo.com, mike.blackwell@rrd.com
Subject: Re: Savepoints in transactions for speed?
In-Reply-To: <2000E26E-35FC-4801-90F3-3CFBDA7B41DF@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox