Greg Smith <gsmith@xxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
