Thread: Vacuum, per table or entire database? what is better?

Vacuum, per table or entire database? what is better?

From
"yateen joshi"
Date:

Hi ,

 

I am developing a script that will delete old data from various tables in a database periodically. The script deletes data from all the tables, one by one, in single go. I have two options of running vacuum here –

One is to run vacuum analyze each time the table data is deleted, or another is to run vacuum analyze after the data is deleted from all the tables. Please note that data has been deleted from all the tables and not just a few tables. Also, the size of data that will be deleted may be different at different instances and differs from table to table. Which one would work faster and better, per table or entire database?

 

Please respond.

 

Thanks in advance,

 

Regards,

 

Yateen V. Joshi

 

Re: Vacuum, per table or entire database? what is better?

From
Richard Huxton
Date:
yateen joshi wrote:
> Hi ,
>
> I am developing a script that will delete old data from various tables
> in a database periodically. The script deletes data from all the tables,
> one by one, in single go. I have two options of running vacuum here -
> One is to run vacuum analyze each time the table data is deleted, or
> another is to run vacuum analyze after the data is deleted from all the
> tables. Please note that data has been deleted from all the tables and
> not just a few tables. Also, the size of data that will be deleted may
> be different at different instances and differs from table to table.
> Which one would work faster and better, per table or entire database?

If you're going to vacuum all the tables anyway, it makes no practical
difference, you're just deciding when to do the work.

Oh - and I'd not bother analysing if you're going to bulk-reload the
tables again. Analyse them as you reload.

--
   Richard Huxton
   Archonet Ltd