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.
> 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?
On
Apr 3, 2019, 10:02 AM -0700, Perumal Raj < perucinci@xxxxxxxxx>,
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
> 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.
|
