Developer Maneshwar continues his deep dive into SQLite's internal architecture, moving beyond the initial page to explore space management and crash recovery. The post explains the database's hidden layers, focusing on how it handles deleted data and maintains integrity during write operations. This technical breakdown covers the core mechanics that keep SQLite fast and reliable over time.

SQLite never immediately returns deleted pages to the operating system. Instead, it places inactive pages into a freelist—a linked structure stored inside the database file itself. This inventory allows the engine to reuse space without growing the file, managed through trunk pages that point to leaf pages. This design explains why database files often expand but rarely shrink automatically without manual intervention.

To ensure data safety, SQLite relies on journal files for crash recovery. When a transaction begins, the system creates a rollback journal containing before-images of modified pages. This mechanism guarantees atomicity, allowing SQLite to restore a consistent state if a write fails. Developers can choose different journal retention modes, though the default deletes the file upon successful commit to save space.