Re: calling vacuum from a script

On 06/22/10 8:44 PM, Janet Jacobsen wrote:
> Hi. Is it possible to vacuum a table (vacuum full analyze)
> from a script. Currently I run the Postgres client and then
> run vacuum, but I'd like to automate the vacuum by calling
> it from a (Perl) script.

postgres since 8.1 has had autovacuum, autovacuum automatically
analyzes, too. You shouldn't need to vacuum in a script.

in answer to your specific question, yes. perl can connect to postgres
quite easily, using DBI w/ DBD-PgSQL, then you can just execute a query
like "VACUUM ANALYZE tablename;"

If your tables have become badly fragmented, CLUSTER on the most used
index (usually the primary key) works much better than VACUUM FULL,
although do note, it uses a global table lock, so you can't be accessing
the table at the same time.