Re: Options for more aggressive space reclamation in vacuuming?

On Sun, May 14, 2023 at 5:03 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com> 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.
>

You delete and reinsert every row every day, or just some lesser number of
rows each day? In one giant transaction daily, or in a series of short
mostly non-overlapping transactions throughout the day?

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

Are these sizes for the entire relation size, or just for the core table
(not TOAST, not indexes)? Index bloat is harder to prevent than table
bloat, but is easier to deal with after the fact (reindex concurrently)

> These tables get auto-vacuumed but clearly it's not, well,
> aggressively reclaiming space.
>

50 GB out of 200 GB seems roughly in accord with the default setting
of autovacuum_vacuum_scale_factor. So maybe just lowering that from 0.2
to, say, 0.05 would be good enough, either globally or just for these
tables.

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

Since you rarely get new data once the month is over, you could lock the
partition against concurrent changes (but still allow reads) once it is no
longer "on the run" while you copy the data into a new tightly-packed table
and index that and add constraints. Then you need only a very brief access
exclusive lock while you detach/drop the old partition and attach the new
table in its place. Of course you can't drop the weaker lock while you
acquire the stronger one lest the old table change during the gap so lock
management can be a bit tedious, but it is surely less restrictive than a
VACUUM FULL.

Cheers,

Jeff

>