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