databasesqlitefilesystems

How does SQLite3 implement its 'secure-delete' without the ability to randomly write file in the middle?


When secure_delete is on, SQLite overwrites deleted content with zeros. 1

How could SQLite 'overwrite deleted content with zeros'? These deleted content might be in the middle of the database file.

Does SQLite implement this mechanism by rewriting every bytes after the deleted content? This way, it might introduce massive disk IO. For example, when the deleted content has 2GB data after it, this secure-delete process would require rewriting all of the following 2GB data to the filesystem. That would take a lot of time.


Solution

  • When the option is on, SQlite scribblles zeros into the RAM copy of the blocks where the deleted data used to be, and affected other blocks of the BTREE structure. With the option off it just leaves the old data, after marking that part of the block as free for re-use. Scribbling zeros into RAM is relatively inexpensive.

    Then, the "dirty" (changed) block gets pushed to SSD / HDD via the usual mechanisms. Whether or not the option is on doesn't change how the block must be handled after it is changed. And pushing data to SSD / HDD is far far far slower than writing to RAM. So the difference is hard to measure.

    If you don't turn on the option, a cybercreep might be able to dump your .sqlite file and find vestiges of deleted rows.