From: Jeff Davis <pgsql@j-davis.com>
To: Mike Blackwell <mike.blackwell@rrd.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Savepoints in transactions for speed?
Date: Wed, 28 Nov 2012 15:28:51 -0800
Message-ID: <1354145331.1766.84.camel@sussancws0025> (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>
On Tue, 2012-11-27 at 16:04 -0600, Mike Blackwell 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?
A large transaction isn't really a big problem for postgres, and 1.5M
records should be processed quickly anyway.
The main problem with a long-running delete or update transaction is
that the dead tuples (deleted tuples or the old version of an updated
tuple) can't be removed until the transaction finishes. That can cause
temporary "bloat", but 1.5M records shouldn't be noticeable.
Adding subtransactions into the mix won't help, but probably won't hurt,
either. The transaction will still run just as long, and you still can't
delete the tuples ahead of time (unless you abort a subtransaction). If
you *do* use subtransactions, make sure to release them as quickly as
you create them (don't just use ROLLBACK TO, that still leaves the
savepoint there); having 1500 open subtransactions might cause
performance problems elsewhere.
Regards,
Jeff Davis
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: pgsql@j-davis.com, mike.blackwell@rrd.com
Subject: Re: Savepoints in transactions for speed?
In-Reply-To: <1354145331.1766.84.camel@sussancws0025>
* 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