Resources about File structures

Question

I'd like to learn advanced techniques about file structures.

• How does database software arrange the data in files?
• How do they manage to add/edit/delete records?
• How does index (hash, tree, other?) works?
• How do you store variable length data?
• How do you create transactions, so that even if the computers crash, to file is never in an incorrect states.
• How do you manages locks, so that more than one application/users can use the file?

Things like that. Are there some books or online resources you could suggest to me?

Answer 1

One concrete reference (but not necessarily the easiest starting point) is the source code to a real database, and several are in the wild with open source.

Some starting points include:

• SQLite
• MySQL

The file formats used by the classic dBase II and its clones (aka xBase) are apparently well known, and one source of documentation is here.

Answer 2

A textbook for a college course on advanced databases might be helpful. I have (or at least used to have) Database Systems by Date; not sure how good it is for sure though (I never sat down to read it, as learning database internals hasn't been high on my priority list).

A lot of it comes down to careful choices, letting some space go unused (e.g. deleted record locations), and periodic cleanup (PostgreSQL requires periodic VACUUM operations to recover space used by deleted rows).