Autocommit, isolation level, and vacuum behavior

[Jack Orenstein <jack.orenstein@xxxxxxx>]

I'm trying to understand the effect of autocommit on vacuum behavior (postgres 
8.3, if it matters). Let's suppose you have two tables, BIG and TINY in a 
database accessed through JDBC.  BIG has lots of rows. There are inserts, 
updates, and every so often there is a scan of the entire table. The scan is 
slow, e.g. one row every 30 seconds. TINY has one row, which contains summary 
information from BIG. It is updated every time that BIG is inserted or updated. 
BIG is vacuumed weekly, and TINY is vacuumed every 1000 updates.

What I'm observing is that as my test program runs, transactions (insert/update 
BIG; update TINY) gets slower and slower, and the file storing the TINY table 
gets very big. I'm guessing that the long-running scan of BIG forces versions of 
the one row in TINY to accumulate, (just in case the TINY table is viewed, the 
connection has to have the correct view). As these accumulate, each update to 
TINY takes more and more time, and everything slows down.

I wrote a little JDBC test program to test this theory.  Long scans (with the 30 
second sleep) and with autocommit = false produces the problem described. 
Shorter scans (e.g. no sleep between rows of the BIG scan) produce better 
results. Also, if the scan is done on a connection with autocommit = true, 
everything works fine -- no slowdown, and no bloat of the TINY file.

Am I on the right track -- does autocommit = false for the BIG scan force 
versions of TINY to accumulate? I played around with a JDBC test program, and so 
far cannot see how the autocommit mode causes variations in what is seen by the 
scan. The behavior I've observed is consistent with the SERIALIZABLE isolation 
level, but 1) I thought the default was READ COMMITTED, and 2) why does the 
accumulation of row versions have anything to do with autocommit mode (as 
opposed to isolation level) on a connection used for the scan?

Jack Orenstein