From: | Paul Smith <paul(at)pscs(dot)co(dot)uk> |
---|---|
To: | Wells Oliver <wells(dot)oliver(at)gmail(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | "pgsql-admin" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Options for more aggressive space reclamation in vacuuming? |
Date: | 2023-05-14 22:08:27 |
Message-ID: | 1881c4ecf78.2923.9bfe8bcc586ac955e423c0e3d5444448@pscs.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Have you tuned the autovacuum settings? We've found the defaults to not be
great for large tables
Eg autovacuum_vacuum_scale_factor defaults to 0.2, so 20% of the tuples
need to be deleted/updated before a vacuum us triggered. If there are a
billion rows, that means there needs to be 200 million deleted rows to
trigger the autovacuum
https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM
Maybe try changing autovacuum_vacuum_scale_factor to 0.001 or something for
that large table
Paul
On 14 May 2023 22:03:41 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. 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
From | Date | Subject | |
---|---|---|---|
Next Message | Wells Oliver | 2023-05-14 22:19:23 | Re: Options for more aggressive space reclamation in vacuuming? |
Previous Message | Wells Oliver | 2023-05-14 21:02:26 | Re: Options for more aggressive space reclamation in vacuuming? |