# Postgres Vacuum in Function

After googleing a bit, it's plainly clear you cannot run vacuum from a function:

Postgres mailing - Vacuum behaviour in plpgsql function

You can't run VACUUM from inside a function because of memory-management limitations. In current sources there is an error check to prevent you from trying.

Stackoverflow - VACUUM cannot be executed from a function or multi-command string

Once the data is loaded, I would like to "vacuum analyze" the affected tables, both to recover the space from the deleted records and to accurately reflect the new contents. [...] When I run this, I get:

ERROR: VACUUM cannot be executed from a function or multi-command string

But I really need to "automatically" run vacuum whenever a certain function is run: this function updates a lot of records (if not all of them) so the number of dead rows increases quite fast and other query performance decrease a lot. The problem is the function is called from another program. Any best practice? Should I manually add the vacuum statement into a separeted piece of code after the function execution, someting like

sql = SELECT my_function() <program.execute(sql)> sql = VACUUM [FULL | ANALYZE] my_updated_table <program.execute(sql)>

• How often is the function run? Are you able to go for a VACUUM FULL, exclusively locking the table for the duration? If there's a large number of dead rows, can you instead use CLUSTER? – Avarkx Jul 31 '17 at 14:13
• Well, the function is run, more or less, twice a week. I think I can go for a VACUUM FULL exclusive lock. Yes, there's a large number of dead rows (last time 50% ~ 4GB). I've tried to manually run cluster (from the client PgAdmin III) but I get the error ERROR: there is no previously clustered index for table "my_table" – Bruno Zamengo Jul 31 '17 at 14:43

If you can't get away with an exclusive lock on the table, you also might be fine with a simple ANALYZE <tablename>;, as that will only take a read lock and should update the statistics for the table and reduce ( if not entirely eliminate ) performance degradation for the other queries. Then you can let the regular autovacuum daemon do its thing ( or whatever your current custom vacuum solution might be ) normally.
If your table doesn't even have a clustered index, you will first need to do an ALTER TABLE <tablename> CLUSTER ON <indexname>;. You should probably have some kind of clustered index anyway: Having a massive heap table and poor performance against it often goes hand in hand.
As far as best practices go, I would be highly inclined to recommend the ANALYZE approach insofar as doing an automatic statistics update is by far preferable to an automatic index rebuild or virtually any form of VACUUM FULL being run without an admin actively watching it.