Subject Re: [firebird-support] Database size
Author Helen Borrie
At 01:59 AM 17/11/2004 +0000, you wrote:

>Greetings to everyone here in the group.
>Can anyone explain to me explain to me how the database size affected? It
>affects by number of columns,number of data and/or the size of datatype

Yes, of course. The size of a row affects both the size of the table and
the amount of data that can be stored on one database page. However, do
not expect the database file to grow and shrink according to the size of
tables, or even the number of rows inserted and deleted. The server
manages the storage of data in blocks of disk called "pages", according to
its own rules.

The basic "unit" for storing every sort of data in the database (table,
index, various inventory pages, etc.) is the page. The size of a page is
determined by the page_size parameter declared in the CREATE DATABASE
statement. If you don't declare a page size, it will be 4Kb by default.
You can change the page size at any time by making a gbak backup and
restoring that backup using the -page_size switch.

When you first create an "empty" database, the system creates a number of
tables, indexes and other objects for storing the metadata of the
database. These tables are the system tables. It requests, from the
operating system, as many pages as it needs to create these tables and the
metadata of the system tables themselves. Hence, a new, "empty" database
will have at least one page for each system table and for the indexes. At
the default 4Kb page size, 146 pages are allocated and the empty database
is 584 Kb.

Because of its multi-generational architecture (MGA), Firebird stores
multiple versions of the same record. When a record is updated and
committed, the engine will save the old version (the "delta") somewhere on
the disk and will try to write the new version into the same space
"vacated" by the old version. If the space is insufficient, and it does
not have a sufficiently large block of space available on another existing
page for this table, it will request a new page from the operating system.

Good "housekeeping" on the disk will ensure that the engine continually
recycles vacant space whenever it needs to write a new record version for a
table. The engine does its own clean-up of obsolete record versions
continually, as long as the database is being maintained by well-behaved
applications. There are tools available to do manual clean-ups as well.

The engine never gives disk space back to the operating system -- hence,
you will never see the database file(s) getting smaller. When you perform
a backup with gbak and restore the backup as a new file, you will see a
smaller file, because the restore freshly organises all of the tables and