How do I securely delete a row from a database?

Question

For compliance reasons, when I delete a user's personal information from the database in my current project, the relevant rows need to be really, irrecoverably deleted.

The database we are using is postgres 8.x,

Is there anything I can do, beyond running COMPACT/VACUUM regularly?

Thankfully, our backups will be held by others, and they are allowed to keep the deleted information.

Answer 1

"Irrecoverable deletion" is harder than it sounds, and extends beyond your database. For example, are you planning on going back to all previous instances of your database on tape/backup where this row also exists, and deleting it there too?

Consider a regular deletion and the periodic VACUUMing that you mentioned before.

Answer 2

To accomplish the "D" in ACID, relational databases use a transaction log type system for changes to the database. When a delete is made that delete is made to a memory copy of the data (buffer cache) and then written to a transaction log file in synchronous mode. If the database were to crash the transaction log would be replayed to bring the system back to the correct state. So a delete exists in multiple locations where it would have to be removed. Only at some later time is the record "deleted" from the actual data file on disk (and any indexes). This amount of time varies depending on the database.