From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Options for more aggressive space reclamation in vacuuming?
Date: Sun, 14 May 2023 17:37:17 -0500
Message-ID: <766eee62-9095-2b12-8ec3-b7037a59f006@gmail.com> (raw)
In-Reply-To: <CAOC+FBWjFWyYCn89UdSCdCCu2LEYNoAtx4zoYeWM9Wbdf2UwDQ@mail.gmail.com>
References: <CAOC+FBWt3mBTg9rC_M0XThP9Tn9TvdYskucBTqJEDR1d8aNrsQ@mail.gmail.com>
<CAMkU=1zaXmEtvFyt-hGyGvryj0K5OQyR880BTnhw47ux8ZeiNQ@mail.gmail.com>
<CAOC+FBUcQqr7xErhyRaf8XGnPv11ssEA98bw7TZzr8tVxZrQdg@mail.gmail.com>
<c99e39f7-dd80-5d6c-0f71-0c83fa30687c@gmail.com>
<CAOC+FBWjFWyYCn89UdSCdCCu2LEYNoAtx4zoYeWM9Wbdf2UwDQ@mail.gmail.com>
Nope. Bog-standard VACUUM. It marks space free, and Postgresql uses it for
new records.
On 5/14/23 17:26, Wells Oliver wrote:
> 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@gmail.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.janes@gmail.com> wrote:
>>
>> On Sun, May 14, 2023 at 1:46 PM Wells Oliver
>> <wells.oliver@gmail.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.oliver@gmail.com <mailto:wellsoliver@gmail.com>
>
> --
> Born in Arizona, moved to Babylonia.
>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>
--
Born in Arizona, moved to Babylonia.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Options for more aggressive space reclamation in vacuuming?
In-Reply-To: <766eee62-9095-2b12-8ec3-b7037a59f006@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox