Deleted Database Records Remain Recoverable in SQLite Files Despite DELETE Operations

A routine request to delete personal data often ends with a simple DELETE FROM clients WHERE id = ... command. The application reports success because the row disappears from query results and exports. However, the underlying database file still contains the full record, including names and card numbers, which can be recovered with basic tools such as grep.

SQLite behavior and the secure_delete pragma

SQLite marks the page containing the deleted row as free for reuse but does not overwrite its contents when secure_delete is set to 0. The following sequence demonstrates the problem: a database is created with 200 rows, 46 rows are deleted, and the table reports only 154 remaining rows. Searching the file nevertheless finds the deleted name and card number.

The pragma secure_delete = 1 forces the database engine to overwrite freed space with zeros at the moment of deletion. The same search then returns zero matches. The default value depends on the compile-time flag SQLITE_SECURE_DELETE, which varies across distributions and platforms. Therefore the same application can behave differently on a server, in a mobile build, or on a desktop client.

Other database systems exhibit similar issues

• PostgreSQL marks row versions as dead; physical removal occurs only during VACUUM or VACUUM FULL. Write-ahead logs also retain previous page versions.
• MySQL with InnoDB releases pages without overwriting them and keeps historical values in undo logs and the binary log.

In every major system, executing DELETE is not equivalent to erasing the data from storage media.

Real-world impact on data protection obligations

Companies responding to deletion requests typically confirm only that the row is absent from the live table. When a database file is later seized or leaked, previously deleted records remain readable. Backup copies created before the deletion continue to hold the data for their entire retention period. Replicas used for reporting and analytics frequently receive the record in a daily export and keep it indefinitely. Mobile application database dumps sent to support teams commonly contain every record the user ever deleted.

Recommended controls

For SQLite, enable immediate overwriting with PRAGMA secure_delete = ON and run VACUUM after large deletions. For server databases, the only reliable approach is to encrypt sensitive fields with a key that is destroyed when deletion is requested; old backups then become unreadable. Organizations should also store less data, enforce retention periods directly in the schema, and include backups, replicas, and analytics exports in every deletion procedure.