Heavily modified big table bloat even in auto vacuum is running

From: Haribabu kommi <haribabu(dot)kommi(at)huawei(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Heavily modified big table bloat even in auto vacuum is running
Date: 2013-10-11 12:57:17
Message-ID: 8977CB36860C5843884E0A18D8747B0372BC84DA@szxeml558-mbs.china.huawei.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

vacuum is not happening on a heavily modified big table even if the dead tuples are more than configured threshold.

This is because during at the end of vacuum, the number of dead tuples of the table is reset as zero, because
of this reason the dead tuples which are occurred during the vacuum operation are lost. Thus to trigger a next vacuum
on the same table, the configured threshold number of dead tuples needs to be occurred.
The next vacuum operation is taking more time because of more number of dead tuples, like this it continues and it leads
to Table and index bloat.

To handle the above case instead of directly resetting the dead tuples as zero, how if the exact dead tuples
are removed from the table stats. With this approach vacuum gets triggered frequently thus it reduces the bloat.
Patch for the same is attached in the mail.

please let me know is there any problem in this approach.

Regards,
Hari babu.

Attachment Content-Type Size
vacuum_fix_v1.patch application/octet-stream 3.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2013-10-11 13:08:43 Re: logical changeset generation v6.2
Previous Message Robert Haas 2013-10-11 12:43:43 Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE