The One Open Transaction That Bloated Our Firebird Database

A Firebird database that grows steadily even though the actual row count is stable is almost never a storage bug. It is usually a garbage collection problem, and the root cause is often a single transaction someone forgot to commit. The useful takeaway: Firebird's multi-generational architecture makes cleanup conditional on every transaction finishing, so transaction hygiene is an operational concern, not just a code-style preference.

Why deleted rows don't disappear

Firebird implements MVCC (multi-version concurrency control) through what it calls a multi-generational architecture. When you UPDATE or DELETE a row, the old version is not overwritten or removed. It is marked as garbage and left in place, because other transactions that started earlier may still need to see it. Each transaction reads a snapshot consistent with its own start point.

This differs from systems you may know. PostgreSQL has autovacuum, a dedicated background process that reclaims dead tuples. InnoDB keeps old versions in an undo tablespace with its own purge threads. Firebird has no equivalent always-on vacuum daemon. Old versions are reclaimed in three ways: cooperatively, when ordinary queries walk past garbage rows; during a sweep, which is a full-database garbage collection pass; and during a logical backup with gbak, which reads every current row and, in doing so, triggers cooperative cleanup.

The Oldest Active Transaction is the gatekeeper

Whether a garbage version can be collected depends on one counter: the Oldest Active Transaction, or OAT. Any version still potentially visible to the oldest running transaction must be kept. If one transaction stays open for hours — an idle connection in a pool, a forgotten isql session, a service that never commits its read — the OAT stops advancing, and every version created after that point becomes uncollectable. Updates keep appending new versions, pages keep being allocated, and the file grows.

The classic symptom is a large and growing gap between the Oldest Active and Next Transaction counters in the database header. You can read the header with:

gstat -h /data/mydb.fdb

Run this on the server (or against a local copy) as a user with filesystem access to the database file. Look for Oldest transaction, Oldest active, Next transaction, and the Sweep interval. A small gap between oldest active and next is healthy; a gap in the tens of thousands on a busy system means versions are piling up. The commonly cited default sweep interval is 20,000 transactions, but confirm it from your own gstat -h output rather than assuming — it is a per-database setting and may have been changed.

Finding the culprit with MON$ tables

Firebird's monitoring tables (available since 2.1, and the reason to prefer them over guessing) let you identify the offending attachment with one query. Run this in isql or your admin tool as SYSDBA or a user with sufficient privileges, connected to the affected database:

SELECT t.MON$TRANSACTION_ID,
       t.MON$TIMESTAMP AS TRX_STARTED,
       a.MON$USER,
       a.MON$REMOTE_ADDRESS,
       a.MON$REMOTE_PROCESS
FROM MON$TRANSACTIONS t
JOIN MON$ATTACHMENTS a
  ON a.MON$ATTACHMENT_ID = t.MON$ATTACHMENT_ID
WHERE t.MON$TRANSACTION_ID <> CURRENT_TRANSACTION
ORDER BY t.MON$TIMESTAMP;

The first rows are your oldest open transactions, with the user, client address, and process that owns them. Cross-check the transaction ID against the Oldest active value from gstat -h. If they match, you have found the session stalling garbage collection. Column names and state codes should be verified against the documentation for your Firebird version before you rely on them in scripts.

Sweeping: the fix and its cost

Once the stuck transaction is committed, rolled back, or its attachment killed, you can reclaim space explicitly:

gfix -sweep /data/mydb.fdb

Run this on the server with database owner or SYSDBA credentials. A sweep walks the database and collects every version that has become garbage. Two honest caveats. First, sweeping is I/O- and CPU-intensive; on a large, busy production database it can visibly contend with normal load, so schedule it for a quiet window or test it on a restored copy first. Second, a sweep reclaims pages for reuse inside the file — it does not necessarily shrink the file on disk. Only a backup-and-restore cycle (gbak) compacts the file itself.

The automatic sweep interval is a trade-off dial. Set it very low and the server does frequent, wasteful sweep work; set it very high and garbage accumulates between sweeps, slowing down queries that must skip over dead versions. Many production sites disable automatic sweeps entirely (interval 0) and run gfix -sweep from a scheduled job at night, which converts an unpredictable background cost into a controlled one. Note also that a gbak logical backup performs garbage collection as a side effect (unless you pass -g to skip it), while nbackup's file-level incremental backups do not — verify this distinction on your version before building a backup strategy around it.

What actually fixes it permanently

Sweeps treat the symptom; the cure is application-side. Keep transactions short. Commit or roll back promptly, including read-only transactions — in Firebird a long-lived read transaction pins the OAT just as effectively as a write. Audit connection pools for idle-in-transaction sessions, and prefer hard commits over commit-retaining in long-running services, since commit-retaining keeps the original transaction context alive.

The actionable version: today, run gstat -h on your production database and note the gap between oldest active and next transaction. If it is large, run the MON$ query, find the attachment, and fix that code path. Then schedule a regular sweep and re-check the header weekly. Firebird's architecture is forgiving of many things, but not of transactions that never end.