Re: Speed while runnning large transactions.

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> 2) Test if an upgrade to PG 8.4 improves your situation. There is some
> new code in that version (labeled in the release notes as "Track
> transaction snapshots more carefully") that has improved problems in this
> area quite a bit for me. There's a bit more detail about the change at
> http://archives.postgresql.org/pgsql-committers/2008-05/msg00220.php , all
> of the other descriptions I found of it require a lot of internals
> knowledge to read.

It's not really that complex. Pre-8.4, VACUUM would always assume that
every transaction still needed to be able to access now-dead rows that
were live as of the transaction's start. So rows deleted since the
start of your oldest transaction couldn't be recycled.

As of 8.4, the typical case is that an open transaction blocks deletion
of rows that were deleted since the transaction's current *statement*
started. So this makes a huge difference if you have long-running
transactions that consist of a series of not-so-long statements.
It also means that transactions that sit "idle in transaction" are
not a hazard for VACUUM anymore --- an idle transaction doesn't
block deletion of anything.

The hopefully-not-typical cases where we don't do this are:

1. A transaction executing in SERIALIZABLE mode still has the old
behavior, because it uses its first snapshot throughout the transaction.

2. DECLARE CURSOR captures a snapshot, so it will block VACUUM as long
as the cursor is open. (Or at least it's supposed to ... given
discussion yesterday I fear this may be broken in 8.4 :-()

regards, tom lane