Bruce,

>
> > Could be cooperative garbage collection with Classic as well. Is the
> > large table frequently updated with larger batches?
> >
>
> Yes, everyday on the server it grows 100K records.
>
>

New records don't cause garbage collection. "Garbage collection" is the
process of removing record versions that are so old that no running
transaction can reference them. A newly inserted record has only one
version (duh!) and doesn't invalidate any other version. Updates and
deletes create new versions. When the transaction that updated or deleted
the record commits, it sets the stage for garbage collection. When all
concurrent transactions end, the older versions of the record are no longer
useful and become garbage. (We use graphic language at Firebird. The old
record versions neither smell nor attract flies and rodents.) Sooner or
later, the old versions are removed so the space they used can be
reallocated.

There are three types of garbage collection: cooperative, thread-based, and
mixed. Cooperative is the only mode available in Classic or Super Classic.
In cooperative garbage collection, when a transaction reads a row, it
checks for unneeded old versions and removes them immediately. In
thread-based garbage collection, when a transaction finds a row that has
unneeded old versions, it puts that row's record number on the queue for
the garbage collection thread. Eventually the garbage collection thread
comes by and cleans up. In the mixed mode of garbage collection (80%
certainty), when a transaction reads a row with unneeded old versions, it
cleans them immediately if all the versions are on one page. If the back
versions are on a different page, the transaction puts the record number of
the row on the garbage collect list and the garbage collection thread
cleans up.

The problem with cooperative garbage collection is that after a large
update or delete operation, some unfortunate transaction that's just trying
to collect data for a report can get saddled with lots of I/O removing
garbage. The problem with thread-based garbage collection is that in a
heavily laden system, the garbage collector thread doesn't get enough
cycles, and the information density of the database goes down. The mixed
mode has both problems. See also Postgres: on-line and off-line
vacuuming.


Good luck,

Ann


[Non-text portions of this message have been removed]