Re: delete and pg_toast = shrink database?

On Thu, Feb 27, 2025 at 4:19 PM Edwin UY <edwin(dot)uy(at)gmail(dot)com> wrote:

> 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.
<Redacted> lobster!