Re: Recommendation to run vacuum FULL in parallel

On 4/3/19 3:45 PM, Perumal Raj wrote:
> Hi Stephen
>
> Thanks for the response ,
>
> Version : 9.2
> We never ran VACUUM FULL in the past, All we are doing just manual vacuum
> ( Weekly ) .
> Based on the Observation ( test run ) , we were able to reclaim 150 GB out
> of 500 GB .
>
> We are heading to a planned down time soon , So thinking to run FULL
> during that time .
>
> Reason behind to run FULL : 1. Reclaim unused space which postgres never
> using it.

Did you purge a *lot* of records?

>                                                 2. Considering  FULL may
> increase the performance.

Maybe. But choose your tables wisely.

>                                                 3. Daily backup size and
> time  will be reduced after reclaiming 150GB.

How are you currently performing backups?  (The size won't change if you're
using pg_dump, and it won't change much if you're using pgbackrest with the
compression option -- thought it will probably run faster.)

Bottom line:

1. choose your tables wisely.
2. make sure you have enough disk space.
3. Either autovacuum more aggressively or explicitly vacuum certain tables
from a cron job.

>
> Thanks,
> Raj
>
>
> On Wed, Apr 3, 2019 at 1:16 PM Stephen Eilert <contact(at)stepheneilert(dot)com
> <mailto:contact(at)stepheneilert(dot)com>> wrote:
>
> > Ideally VACUUM FULL should not require a giant lock on the table.
>
> It is a massively expensive operation, regardless. Not sure if it is
> something you want to run in production outside a maintenance window.
>
> I would argue that frequent vacuum full is an antipattern. This will
> become a matter of superstition in your company.
>
> If db size growth is a problem, make autovacuum more agressive. Or run
> your manual vacuum job (not full) more often than a week. Daily, if
> you have to. This will not reclaim disk space as reported by the OS,
> but it should make the space available for new row versions, so db
> should mostly stop growing from the OS point of view(mostly, because
> you may be adding new data, right?). If it is still a problem, then
> there may be something else going on.
>
> Which PG version is that?
>
>
> — Stephen
> On Apr 3, 2019, 10:02 AM -0700, Perumal Raj <perucinci(at)gmail(dot)com
> <mailto:perucinci(at)gmail(dot)com>>, wrote:
>> Hi All
>>
>> Thanks for all your valuable  inputs,
>>
>> Here is some more data,
>>
>> Though we have 150 GB free space spread across 500 Tables , Every
>> alternative day DB is growing with 1 GB rate.
>> Also,We have manual vacuum job scheduled to run weekly basis, So
>> seems to be space is not reusing all the time ?
>>
>> So conclude the requirement here , The only way to parallelism is
>> multiple script. And no need to do REINDEX exclusively.
>> Question : Do we need to consider  Table dependencies while preparing
>> script in order to avoid table locks during vacuum full ?
>>
>> At present Maintenance work memory set to 20 GB.
>> Question : Do we need to tweak any other parameters ?
>>
>> Note:
>> We are planning this activity with Application Downtime only.
>>
>> Let me know if i missed anything.
>>
>> Regards,
>> Raj
>>
>>
>>
>>
>>
>> On Wed, Apr 3, 2019 at 8:42 AM rihad <rihad(at)mail(dot)ru
>> <mailto:rihad(at)mail(dot)ru>> wrote:
>>
>> > And future updates can reuse it, too (an update is very similar
>> to an
>> > insert+delete).
>>
>>
>> Hm, then it's strange our DB takes 6 times as much space compared to
>> freshly restored one (only public schema is considered).
>>
>> > Not if autovacuum has a chance to run between updates.
>>
>> Ours is run regularly, although we had to tweak it down not to
>> interfere
>> with normal database activity, so it takes several hours each run
>> on the
>> table. We did that by setting autovacuum_vacuum_scale_factor =
>> 0.05 from
>> default 0.2.
>>
>>

--
Angular momentum makes the world go 'round.