I have been experimenting with database programming recently, and that i observed something a bit alarming.
I required a binary flat file held in a proprietary, non-compressed format that holds several various kinds of records, built schemas to represent exactly the same records, and submitted the information right into a Firebird database. The initial flat file involved 7 Megabytes. The database has ended 70 Megabytes!
I'm able to realize that there's some overhead to explain the tables themselves, and I have got a couple of minimal indices (mostly PKs) and FKs on various tables, and all sorts of that's going to consider some space, but an issue of 10 just appears a bit absurd. Does anybody have ideas in regards to what might be bloatedness up this database badly, and just how I possibly could bring the dimensions lower?
From Firebird FAQ:
Many customers question why they do not obtain disk space when they remove lots of records from database.
This is because it's an costly operation, it might require lots of disk creates and memory - much like doing refragmentation of hard disk drive partition. The various components of database (pages) which were utilized by such data are marked as empty and Firebird will reuse them the next time it must write new data.
If disk space is crucial for you personally, you will get the area back by doing backup after which restore. Since you are doing the backup to revive immediately, it's smart to make use of the "hinder garbage collection" or "avoid using garbage collection" switch (-G in isql), which can make backup go Much Faster. Garbage collection can be used to wash your database, and because it is a maintenance task, it's frequently done along with backup (as backup needs to go throught entire database anyway). However, you are soon likely to ditch that database file, and there is you don't need to fix it up.
Gstat may be the tool to look at table dimensions etc, maybe you'll have some hints what's using space.
Additionally, you may even have multiple pictures or any other garbage in database file, it is dependent how you add data towards the database. The database file never reduces instantly, but backup/restore cycle will get eliminate junk and empty space.