At 09:13 PM 25/07/2005 +0000, you wrote:

>And in previous mail, I wrote that I dropped two BLOB fields which store
>images and DB became 1.72 MB only for 3000 records.

The choice of whether to store blobs inside the database, or externally as
files, depends on several factors.

1) Text blobs often store very small amounts of data - perhaps 5Kb or less
- that is updated frequently. You would want to store these blobs in the
database to ensure that they will be backed up when the database is backed up.

2) At the other end of the scale, binary blobs - such as images, audio
files and PDF documents - are often very large and are never edited. If
you store them in the database, they will cause the database to grow
unpredictably large (see my comments in another post about blob page
allocation) and make the backup and restore processes very long.

3) In between, you might have certain types of text or binary objects that
are predictable in size, that you want to keep as one with the database.

4) You might have a requirement - as you appear to - to keep the size of
the database file to some limit. In this case, you will almost certainly
want to store blobs of large or unpredictable size *outside* the
database. This will be true, even if you are storing objects that might be
updated by another application - such as an image or a word processor
document - since Firebird *never* updates blobs. "Updating" a blob causes
a completely new blob to be stored, with a completely new blob id. The old
blob data will be preserved on its original pages until it is able to be
garbage collected.

The same is true of blobs that were stored on records that were
deleted. The blob pages will not be released until the transaction in
which the deletion occurred becomes "not interesting".

You mentioned that you expect your database to have about 8000 records
eventually, in one table. That is a very small table. Provided you take
good care with transaction management in your applications, and you store
the unpredictably-sized blobs externally in files, your database size will
stabilise eventually. Because you must allow disk space for a certain
level of multi-version storage (which you will determine over time) you
should not try to estimate the final size of the database file according to
your estimate of the size of 8000 records.

If your queries use ordering (ORDER BY) or grouping (GROUP BY) and you
don't have much RAM available, you also need to allow a reasonable amount
of temporary space on disk for the intermediate files that the engine will
create for sorts. You can (and should) configure this space in
firebird.conf. It does not have to be on the same drive or partition as
the one where your database file is located.

If you store your media objects externally as files, they can also be on a
different disk or partition.

In another posting, I mentioned that a Firebird database is stored in one
logical file. That logical file can be made of a number of individual
physical files. If the idea of limiting the physical size of your database
files appeals to you - or is necessary because of the file size limits
imposed by your OS and filesystem - then study the documentation for
instructions about splitting a database into multiple files.

The logical file can be multiple physical files spread across more than one
locally-controlled disk. It might offer an alternative way for you to keep
file sizes under control, as the file size for every file except the last
one can be explicitly limited. You might also like to explore storing your
external image files on a separate disk - even a removeable one (if you
trust your users not to remove it!!)

./heLen