Обсуждение: Large empty table, balanced INSERTs and DELETEs, not being vacuumed
Hi All,
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?
Kind Regards,
Jason Dusek
On Fri, Oct 21, 2016 at 4:53 PM, Jason Dusek <jason.dusek@gmail.com> wrote: > 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? Did auto-vacuum actually succeed in vacuuming this table? Check your logs. You may need to make auto vacuum more log-verbose first.
On Sat, Oct 22, 2016 at 6:02 AM, Vick Khera <vivek@khera.org> wrote: > On Fri, Oct 21, 2016 at 4:53 PM, Jason Dusek <jason.dusek@gmail.com> wrote: >> 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? > > Did auto-vacuum actually succeed in vacuuming this table? Check your > logs. You may need to make auto vacuum more log-verbose first. Yeah. If you are using 9.5 or newer versions, you could set log_autovacuum_min_duration to 0 for this relation and avoid a lot of noise in your logs. pg_stat_user_tables and pg_stat_all_tables also contain information regarding the last time autovacuum has been run on a relation. -- Michael