I'm implementing my own simple database with disk storage, and I'm not sure how to go about modifying and deleting entries.
The problem is that as you delete a record from arbitrary position within a file, a "hole" is left there. As you insert a new entry, you may or may not be able to plug it into the hole. Modifying an entry in-place may be possible if the new value is smaller, leaving another hole. Or the new one may be larger, so one has to insert it some place else and delete the old one. Another hole.
If implemented like this, the database file starts looking like Swiss cheese after a while. The obvious solution is to run optimization every now and then to compact the file, but that is a tedious and not trivial to implement task as well. For instance, if the file is much larger than the amount of RAM, and you must carefully juggle the records in the file.
My question is: are there other approaches to database storage file management? And how do the big database management systems store the data on persistent storage? How do they deal with these problems?
I tried Googling but didn't get much info, possibly because I don't even know the right keywords.