Re: delete and pg_toast = shrink database?

Sometimes yes, and sometimes no.

On Thu, Feb 27, 2025 at 6:51 PM Jean-Paul POZZI <jp(dot)pozzi(at)izzop(dot)net> wrote:

> Hello,
>
>
>
> As a precaution, save the data to be purged so that you can possibly use
> it in the future.
>
>
>
>
>
> Regards
>
> JP P
>
>
>
> ------------------------------
> *De:* "Ron Johnson" <ronljohnsonjr(at)gmail(dot)com>
> *À:* "Pgsql-admin" <pgsql-admin(at)lists(dot)postgresql(dot)org>
> *Envoyé:* vendredi 28 février 2025 00:39
> *Objet:* 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!
>

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!