Re: Options for more aggressive space reclamation in vacuuming?

Just to be clear, you regularly vacuum full, i.e. locking full-on hard-core
all-out vacuum?

On Sun, May 14, 2023 at 3:25 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

> We regularly vacuum, and *definitely* vacuum after an archive-delete.
>
> On 5/14/23 16:02, Wells Oliver wrote:
>
> 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>
>
>
> --
> Born in Arizona, moved to Babylonia.
>

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