Re: Options for more aggressive space reclamation in vacuuming?

I think our use-case here is nothing special: just very large tables that
are deleted from and re-inserted with some frequency. They store monthly
data and exist as partitions. As an example, July of 2022 (summer months
are peak) had 1,630,558,336 rows. We delete from this and insert daily. We
rarely get new, relevant data once the month is over. The n_dead_tup
from pg_stat_all_tables here was over 7m rows, and clearing that out gave
us back nearly 50GB, and the file size estimate on this partition was ~200
GB. These tables get auto-vacuumed but clearly it's not, well,
aggressively reclaiming space.

Given that they are partitions, VACUUM FULL locks the parent table, so new
writes/deletes can't happen, and anyway people like to run queries against
these tables quite frequently.

Glad to add any detail I can.

On Sun, May 14, 2023 at 11:32 AM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> 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
>
>>

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>