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

[yateen joshi]

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

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly