Subject Re: [firebird-support] Database Size Is Not Reducing
Author Ann Harrison
Post date 2015-04-06T17:06:59Z

On Apr 6, 2015, at 9:08 AM, Vishal Tiwari vishualsoft@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

I am using Firebird 2.5 database.  I have a table that has column of type Blob field, this table also contains some other columns.

The issue is, I had total 78,000 records in this table. What I did is as every record was containing image in it, I then ran an update query and set Null value to all the records for this blob column.

I then backed up the database using GBack and then restored. After restored the database there is no change in database size.   Any idea why it is so ?

No.  I assume you committed the transaction that set the blobs to null before you backed up the database and that the image blobs were of considerable size (more than 100 bytes or so).  How big was the original database?  How big is the recreated database?  Did you run gstat to see where the space is going?  

Just deleting data, without the gbak backup/restore will not reduce the size of the database.  Firebird's internal storage is complex - much more so than databases which store each table in its own file. Releasing space from the middle of a file is not possible. 

which says: 
It is important to know and remember that in contrast to other fields, BLOBs data are not stored in the table record. Table records store only BLOB_ID, whereas BLOB body is kept in separate database tables.

That's not entirely correct.  If the blob data fits on the same data page as the record, it will be stored there.  If not, it will be stored on an overflow page.  Gstat doesn't report overflow pages - whether for large blobs or records larger than a page.  Or rather it didn't when last I looked.  Someone may have fixed that.  Even if gstat doesn't report them, overflow pages belong to a table.  They are located through records in table.  Their page headers include information that identifies the table they belong to, the record or blob they contain, and their sequence in that record or blob.

Is because of this reason there is no change in database size?

No.

If it so then, I am curious to know where does Firebird store the images?

On database pages - as above.

Good luck,

Ann