Re: [GENERAL] Large empty table, balanced INSERTs and DELETEs, not being vacuumed

[Michael Paquier]

On Sat, Oct 22, 2016 at 6:02 AM, Vick Khera  wrote:
> On Fri, Oct 21, 2016 at 4:53 PM, Jason Dusek  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Large empty table, balanced INSERTs and DELETEs, not being vacuumed

[Vick Khera]

On Fri, Oct 21, 2016 at 4:53 PM, Jason Dusek  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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Large empty table, balanced INSERTs and DELETEs, not being vacuumed

[Jason Dusek]

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
​