My application is running on 7.4. We have one huge table that drives
our application, and also a tiny (single-row) table used to maintain
some aggregate information. Each transaction inserts or updates 1-2
rows in the huge table, and updates the tiny table.
We vacuum the entire database once a week, and the tiny table every
2000 transactions.
I'm trying to understand some odd behavior observed regarding the tiny
table: The tiny table's disk file is usually 8K or 16K. During the
weekly vacuum, the tiny table bloats. It's still one row, but the size
of the file grows. I've seen it get as high as 1M. But then after the
vacuum, it returns to its normal size.
1) Why does the tiny table bloat during a vacuum? Is it because the
scan of the huge table is run as a transaction, forcing maintenance of
dead versions of the tiny table's one row?
2) Why does the bloat resolve itself? We're not doing any full
vacuums.
We're in the process of upgrading to 8.3.4, so I'd appreciate any
throughs on whether and how this behavior will change with the newer
release.
Jack Orenstein