PostgreSQL vacuum:

PostgreSQL vacuum: if our administrator runs vacuum manually or a terabyte database to recover about %10 of the space,  how much of performance hit can we expect to see from the database while the vacuum process is running ?

Will all other processes slow to where the DB is unusable until the vacuum completes ?
No impact at all ?   Just want no surprises !

Thanks
sidwelleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArneLoviusCommented:
as per https://www.postgresql.org/docs/9.1/static/sql-vacuum.html

Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed.

If plain VACUUM without full is run, the database will remain available, there will be a performance impact, but the level of impact is down to the headroom available on the processor(s) and storage used for the database.

If VACUUM FULL is used, as exclusive locks are taken on each table, there will be an impact to availability as well as significant disk IO performance impact.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
earth man2Commented:
It depends
 do you have autovacuum enabled ?
 on the version of Postgresql.

If you have a version that takes advantage of multicpu architectures then it will run worker processes in low priority.

If you run forever when the oid rolls over then that table will vacuum anyways.

If you had a failover server you could try it on that before you experiment  on your production environment.
0
sidwelleAuthor Commented:
  • SELECT version();   --PostgreSQL 9.4.9
  • Currently we have vacuum enabled with conservative setting of %20.

Ok, that's good Info:
1.   The language on the support sites talks about the tables of the Postgress DB being held in individual files ?
If we were to issue a 'vacuum full' command, would the recovery process re-write each table 1 at a time and 0 out the previous table ?
How much extra space do we need to have available to issue 'vacuum full' and not run out of disk space ? (At least as much as the largest table ? or the whole D.B. ?)

2.    Can 'vacuum full' be cancelled once called if it is taking too long and we need to get production back up and running on that table ?

Thank You.
0
earth man2Commented:
Maybe now is the time to partition your data into more manageable chunks ?
0
sidwelleAuthor Commented:
Thanks, I will post a follow up question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.