Re: Options for more aggressive space reclamation in vacuuming?

On Sun, 2023-05-14 at 14:02 -0700, 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.

As a rule, normal VACUUM will not reclaim space at all (only if after the run, the last
pages of a tabel are empty). That's why you use range partitioning. Simply drop the
partition that is expired. Ideally, you wouldn't go to the trouble of deleting data at all.

Yours,
Laurenz Albe