We have users reporting Firebird databases that are way to big for the number of entries stored by our application.
I found an article Commit vs CommitRetaining indicating that using CommitRetaining isn't the smartest thing and may prevent GarbageCollection from working.
Our software consists of several applications, all connecting to the Firebird database. Some are bound to certain user actions and run for a few seconds or minutes only, others are background applications that can run for months.
As I thought CommitRetaining is "just as good" as Commit and I don't have to take care to start a new transaction, I used CommitRetaining everywhere.
Now I want to find out, if I need get all applications CommitRetaining-free, or is it enough, if the long-running applications are "clean", and the short-ones don't matter because when they are closed that counts as a Commit anyway?
And, just because I am curious: Does Firebird need to be free of any connection using CommitRetaining to do GC, or is each connection treated individually.
In other words: Assume 2 applications A and B running
AAAA--AAAA--AAAA-....
---BBBB--BBBB--B...
So always A or B are connected to the DB, would that prevent GC forever?
A transaction which is committed with commit retain will effectively not end until it is really committed, this means that the Oldest Interesting Transaction and Oldest Active Transaction are effectively frozen, and this inhibits most of the garbage collection of record versions, because they might remain interesting to the Oldest Active Transaction. And the longer this remains, the more back versions accumulate, which takes space and also reduces performance.
The inhibition of garbage collection does not depend on the connections themselves (in fact, without active connections reading data pages, Firebird does not collect garbage). It depends on one or more active transaction handles of a connection not having been really committed, only commit (or rollback) retained. So once you switch to using normal commits, or reduce how long a transaction lives by using a real commit regularly instead of commit retain, garbage collection should be able to move forward.
However, as long as you are using commit retain, you will probably accumulate more garbage than if you switch to real commits everywhere.
In other word, switching your long-running applications to using real commit should alleviate most of the problems with growing databases.