Hi,
DB is currently 500G. About 75% of this is pg_toast.
Had advised the application team to check for data purging as there are some very old data.
I've been in that same situation. Had to write the purge job myself...
Is it correct to assume that doing so should shrink the database size?
No. DELETE + VACUUM frees space in the data files. (This is good once you get a regular purge process running, since the post-purge vacuum will free up space for the next records. The tables' free space percentages will then hover between relatively high and low ranges, unless insert volume is growing.)
I believe I have to run vacuum full/table at some stage to really shrink it?
Also, no. But then yes.
VACUUM FULL (use pg_repack instead!)
1. copies the remaining data to a new (temporary) table and rebuilds the indices,
2. deletes the old files,
3. renames the temporary table to the original name.
Thus, you'll temporarily need more disk space.
-- Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
