Subject Re: [firebird-support] Re: How to compact database?
Author Helen Borrie
At 06:40 AM 14/09/2004 +0000, you wrote:
>Nigel Weeks wrote:
> > Firebird databases don't reduce in filesize when records are
>deleted, for a
> > few reasons:
> > (AFAIK)
> > Filesystem fragmentation would increase if file sizes were
> > all the time.
>Absolutely, and I wouldn't expect the database to change all the time
>in this way, it'd be inefficient. It must already grow in 'blocks'
>because I can add records and the db file size doesn't change, and I
>have never removed any records.
>Anyway, I thought there may be a manual option to compact the
>database. Afterall, if there was - for example - 90% empty space in
>the database and it never grows (it's reference data only) then it
>would be a shame if it's always 'so big'.

If that's the case, then just back it up and restore it. The restored
version will be compacted!!

>And a smaller file may be
>less likely to be fragmented by crappy old windows ;-)

Nope, Windows doesn't have anything to do with the distribution of data to
pages controlled by Firebird. All Windows (or whatever OS is running) is
required to do is allocated evenly-sized chunks of diskspace on
request. After that, the OS is out of the picture.

>Some databases - when you request this type of action - take the
>opportunity to reorganise and optimise their internal structures.

Yup - those are file-served databases, which of course depend on the OS to
provide filesystem, file management, blahblahbah. The only "filish" thing
about a Firebird database is that it phsyical perimeters are "known" to the
filesystem. Logically, it's not necessarily a single file at all, but
many, located on many different disks/partitions. The OS and the
filesystem know nothing about the dependencies between these physical
chunks of a database.

> > Prevents another call to the operating system for more disk space - it's
> > already present in the database file.
> >
> > If you need to reclaim space after your conversion process, simply do a
> > backup and restore.
>This what I thought I may have to do, this is no problem, just thought
>I may have missed a 'compact' feature ;-)

You don't need it as much as you think you do. The page manager is very
intelligent about re-using space. "Compaction" (or, rather, releasing
space vacated by obsolete records) happens in the background all the
time.You see initial noticeable growth because each table and each index
(plus a few other things) needs its own "initial page". Large, dynamic
tables tend to "grow" if the page manager frequently finds it doesn't have
a suitably-sized space for a new record or record version, or if you delete
a lot of stuff frequently and don't sweep. If you are careful to give your
databases clean beds to sleep in, they won't keep growing. You can adjust
the page size during a backup-and-restore. Sometimes database file
overgrowth is nothing more than just a bad choice of page size..

Incidentally, if a database, once established, grows faster than the ebb
and flow of records would account for, you're seeing a good indication that
you have a "naughty" application that is trapping garbage...