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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
