Re: Options for more aggressive space reclamation in vacuuming?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


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.

Laurenz Albe

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux