Does Deleting a Row in Postgres Actually Erase Personal Data?
No. A DELETE in Postgres does not wipe anything; it stamps the row version with the ID of the transaction that deleted it and leaves the bytes where they are. The row stops being visible to new queries, but the email address is still sitting in the data file.
That matters if someone has asked you to erase their data. So here is where the bytes actually live, how to see them for yourself, and what "gone" needs to mean in practice.
Watching a deleted row stay put
Postgres ships a contrib extension called pageinspect that lets you read raw table pages. Try this on a scratch database (autovacuum is switched off for the table so it does not tidy up before you look):
CREATE EXTENSION pageinspect;
CREATE TABLE people (id int PRIMARY KEY, email text);
ALTER TABLE people SET (autovacuum_enabled = off);
INSERT INTO people VALUES (1, '[email protected]');
DELETE FROM people WHERE id = 1;
SELECT lp, lp_flags, t_xmin, t_xmax,
encode(t_data, 'escape') AS data
FROM heap_page_items(get_raw_page('people', 0));
A normal SELECT * FROM people returns nothing. The page inspection should still show one item with a non-zero t_xmax and the email in the data column. I have described the shape of the output rather than pasting a run, so check it on your own version.
Why Postgres does it this way
This is MVCC. Another transaction that started before your delete may still need to see the old row, so Postgres cannot remove it at delete time. It keeps the old version until nothing can possibly need it.
Quick detour, because it is neat: an UPDATE works the same way. It writes a whole new row version and marks the old one dead. So changing an email address leaves the previous address behind exactly as a delete would. Anonymising by updating is not erasure either.
What VACUUM does, and does not, do
Vacuum (usually autovacuum) is what reclaims dead row versions. After it runs, the line pointer is freed and the space can be reused by later inserts. That is a reclaim, though, not a scrub.
- Plain VACUUM frees space inside the file for reuse. It does not promise to overwrite the old bytes, and it rarely shrinks the file.
- VACUUM FULL rewrites the live rows into a new file and deletes the old one. The old file is unlinked, not shredded, so the filesystem may still hold the blocks.
- Long-running transactions hold vacuum back, so dead rows can survive far longer than you expect.
You can test the "not a scrub" claim yourself: run VACUUM people, then search the raw page for the string.
VACUUM people;
SELECT position('[email protected]'::bytea
IN get_raw_page('people', 0)) AS offset;
A non-zero offset means the text is still physically in the page. Whether you see that depends on version and page layout, so try it rather than trusting me.
The other places the data sits
The table heap is only one copy. Walk through the others before you say a record is erased:
- Indexes. A unique index on email holds the value itself. Dead index entries are removed by vacuum, with the same caveat about the bytes.
- TOAST tables. Large values are stored out of line in a separate table, with the same dead-row lifecycle.
- The write-ahead log. Inserts and updates are written to WAL, and archived WAL can be kept for a long time.
- Replicas. Each standby has its own copy of everything above, replayed from the primary.
- Replication slots. A logical slot can retain changes containing the data until the consumer reads them.
- Logs and statistics. With
log_statementor slow query logging, literals in your SQL end up in text files. A query likeDELETE ... WHERE email = '[email protected]'logs the very value you are deleting. - Backups and dumps. Base backups,
pg_dumpfiles and snapshots all keep the old state.
Backups are their own topic, and I have written about them elsewhere on this blog. The point here is that the live database alone has several copies.
What that means under UK GDPR
Article 17 of the UK GDPR gives people a right to erasure in certain circumstances. The ICO publishes guidance on it, and its guidance on backups takes a pragmatic line: where data cannot be immediately overwritten, you should put it "beyond use" and overwrite it when the backup cycle allows. See the ICO right to erasure guidance for the exact wording.
That is regulator guidance, not legislation, and my reading is that it gives you room for dead tuples that vacuum will reclaim shortly. It is much less comfortable for data you simply never think about, such as a year of archived WAL or a debug log full of literals. Whether a given set of copies is acceptable depends on your circumstances; I am not offering legal advice.
A practical approach
- Delete the row, and delete or anonymise related rows in other tables.
- Make sure autovacuum is healthy, and check for old transactions or stale replication slots in
pg_stat_activityandpg_replication_slots. - Keep personal data out of query text: use bind parameters, so the value is not in the SQL string that gets logged.
- Set a retention period for WAL archives and logs that matches what you tell people.
- Treat backup expiry as the real deadline, and write it down.
If you need stronger guarantees, the honest option is encryption with a per-person key (crypto-shredding): destroy the key and every stray copy becomes unreadable. It costs design effort up front, but it is the only approach I know of that reaches the copies you cannot find.