Re: Options for more aggressive space reclamation in vacuuming?

On Sun, May 14, 2023 at 1:46 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com> wrote:

> Noticed over the last few days how much more space VACUUM FULL reclaims
> (for obvious reasons) and I am wondering if there are any solutions for a
> more fruitful VACUUM without the locks of FULL?
>

There are lots of theories which are all compatible with the minimal amount
of info given here. If the space is freed from the indexes, then just
reindexing (which can be done concurrently in recent versions) could
reclaim the space. If it is from the table itself then something like
pg_squeeze (https://github.com/cybertec-postgresql/pg_squeeze/) might help.
Or you can implement your own squeezing, by using a CTE with a ctid scan to
delete tuples from the end of the table and reinsert them in the front,
then use regular vacuum to truncate the now-free space off the end of the
table.

I would think the more fruitful VACUUM would be to prevent the bloat from
occuring in the first place but without knowing the root cause I can't
offer anything but platitudes there.

Cheers,

Jeff

>