I recently came across an interesting problem relating vacuuming, triggers and table partitioning.
We have a “virtual table” with a BEFORE trigger that redirects writes to its child tables. This was all fine well and good until we wanted to use RETURNING to get the id back — the trigger returns NULL to prevent the row from being inserted.
We modified the trigger to RETURN NEW and then added an AFTER trigger that deletes it (WHERE ctid = NEW.ctid). This was working great until a few days later. The plan for queries against the partitioned table naturally includes it (there is no constraint preventing writes to it) and this table was many gigabytes in size with indexes to match! But SELECT count(*) ... returned 0 rows. The queries became very slow, searching an index full of deleted rows. Running TRUNCATE ONLY ... fixed the problem.
This is really only a temporary fix, though. We can have a cron job running in the background running TRUNCATE ONLY ... but this seems like the kind of thing that auto-vacuuming should have handled for us, before the problem got “too large”. Are there auto-vacuum settings that we can set, globally or on the table, to address this situation?
