cancel
Showing results for 
Search instead for 
Did you mean: 

Re: How to plan maintenance activity ?

Level 2 Traveller

Re: How to plan maintenance activity ?

As a beginner, I would like to know about the index maintenance in particular.

 

There is a particular scenario where my free disk space is 2 TB and particular table size is 2.5 TB. And I am unable to vaccum this table alone. All the remaining tables are vaccummed. Suggest how to handle this scenario.

2 REPLIES
EDB Team Member

Re: How to plan maintenance activity ?


anbumech wrote:

As a beginner, I would like to know about the index maintenance in particular.

 

There is a particular scenario where my free disk space is 2 TB and particular table size is 2.5 TB. And I am unable to vaccum this table alone. All the remaining tables are vaccummed. Suggest how to handle this scenario.


Some information about your environment details will help us to give you better assistance. Like PG version/OS version, autovacuum(on/off) etc.,

 

Its always challenging to do any maintenance on such a vertically grown table. As you have mentioned there's a free space on the server, is it possible to slice the data into small chunks using table partitioning and later you can schedule maintenance. 

 

--Raghav

EDB Team Member

Re: How to plan maintenance activity ?

You mention that you are "unable to vaccum this table alone".  What happens when you try?

 

Plain old VACUUM shouldn't require any extra disk space, so the fact that the amount of free disk space remaining is smaller than the table shouldn't be a problem.


VACUUM FULL does require extra disk space, but see http://rhaas.blogspot.com/2014/03/vacuum-full-doesnt-mean-vacuum-but.html

 

If you need to use VACUUM FULL but don't have enough disk space for the operation to complete, one thing you could try doing is removing all of the table's indexes before running VACUUM FULL, and then recreate them after the operation is done.  If that still doesn't work, then you may need to consider adding more disk space.

 

There is also a utility called pg_repack available, but EnterpriseDB does not support it.