2

Postgres delete doesn't actually remove the record from the table file.

Once the delete is committed and the last transaction is closed, no postgres process can see the record any more, the deleted record is now liable to be vacuumed.

Does vacuum (or some other system) overwrite the deleted data or is some of it left sitting in the table file?

Obviously vacuum full and cluster will both re-write the files omitting the deleted record, but I'm particularly interested if there are any details about what ordinary vacuum will or, will not, do to fields stored in the heap and TOAST files.

2

Citing the reference:

VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system

Without FULL, the database files are not even truncated, so data may still be in the files. With FULL, deleted data should be gone from the files (since they are recreated). However, data may still be in deleted parts of the underlying file system of course.

In any case, if it is important to you that data is destroyed, you must probably erase or destroy the disk.

  • yeah, but are the touched heap pages are re-organized during regular vacuum? is the the free space zeroed out? – Jasen Aug 9 '18 at 8:57
  • erase or destroy the disk seems a little excessive when a single customer just demands their personal data be removed. – Jasen Aug 9 '18 at 8:59
  • OK that's true :) I don't know for sure, but I'd assume pages are not zeroed or moved / reorganized because this technically does not make sense for the vacuum task. A garbage collection will most likely only flag space as available for re-use. – Moritz Both Aug 9 '18 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.