Subject Re: Firebird and vacuum?
Author Roman Rokytskyy
> Am I correct in understanding that the garbage collection is only
> effected, once a restore is performed, after the backup?

In PostgreSQL simple VACUUM simply marks the deleted records or old
versions of records as free space and engine can store new data there.
The VACUUM FULL moves tuples across the blocks, so at the end the
space occupied on disk is smaller. In PosgteSQL 8.x there is new
auto-vacuum, which is somewhat similar to our garbage collection.
PostgreSQL 7.x required manual intervention.

In Firebird garbage collection marks the space occupied by old record
versions or deleted records as free and engine can store new data
there. It is equivalent to simple VACUUM. In this case space is
available to new record versions, but it does not reduce size of the
database file. Only just new inserts or updates won't cause database
file to grow until all reclaimed space is reused.

There are two types of garbage collection - background thread and
cooperative (see also firebird.conf and release notes).

In cooperative mode, when transaction (e.g. some SELECT statement)
accesses some database page and notices that there are some old record
versions, it also does the garbage collection, in other words, it
marks the space as free.

When background thread performs garbage collection, it visits the
database pages page by page. In cooperative mode, only garbage on the
visited pages is collected. The simple SELECT * FROM some_table
effectively cleans all garbage on the pages used by that table, but
not more.

When you do backup and restore, the gbak process reads only current
records from the database and stores them in .fbk file. Since gbak can
be considered as a big loop that reads all records from all tables
(i.e. "SELECT * FROM <each table>"), it can perform _cooperative_
garbage collection (but it has also switch to turn it off, it is
really not needed when you're going to restore the database later).

When you restore the database from .fbk file, only the latest versions
of records are put into new database, which in turn occupies less
space on disk. So backup/restore with gbak is somewhat equivalent to
VACUUM FULL.

Hope this helps.

Roman