Mike Matrigali wrote:
The split description below describes Derby behavior. Derby does not merge "almost" empty pages - only empty pages. Derby maintains a separate file per table and a separate file per index. Derby maintains a free page pool per file and only can use the free pages in the same file to fill space requests.
I was talking in generalities, each specific database works differently, some store the entire database in a single file, like Firebird does others store each table in a file, like PostgreSQL does, and others store each item in a separate file, as it appears Derby does.
Personally I don't think it really matters, if your compressing a database to save space, then there is a bigger issue, like too small a hard drive. Back in the days when $10,000 would buy you 10MB of storage (roughly 1,000,000 per GB) saving space was a big deal, now, when $100 buys you 100GB ($1 per GB) it's often a false economy to waste the time fussing with it, just to save disk space, especially when the guy doing the fussing is worth $40/hour. Compression should be used because it's good for the database, not to save disk space.
It also ends up being a false economy, unless the database is static, because your just going to start adding to it again, and extending a file, typically means a kernel request, and that is going to be much slower then simply adding a page in the file already.
