Horton

Re: Trigger more frequent autovacuums of heavy insert tables

Melanie Plageman <melanieplageman@gmail.com>

View thread View on postgr.es
From: Melanie Plageman <melanieplageman@gmail.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, Peter Geoghegan <pg@bowt.ie>, David Rowley <dgrowley@gmail.com>
Date: 2024-10-23T21:21:14Z
Lists: pgsql-hackers

Attachments

On Tue, Oct 22, 2024 at 3:12 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> The attached patch does this.

I realized that I broke relation_statistics_update(). Attached v2 is fixed.

> I've estimated the unfrozen percentage of the table by adding a new
> field to pg_class, relallfrozen, which is updated in the same places
> as relallvisible.

While my relallfrozen column correctly appears in pg_class, I noticed
that it seems like catalog/pg_class_d.h did not have my column added
(this file is auto-generated), despite my adding relallfrozen to
catalog/pg_class.h. Is there something else I have to do when adding a
new column to pg_class?

> At the end of the benchmark, the patched version of Postgres had
> emitted twice as many FPIs as master.

This was meant to say the reverse -- _master_ did twice as many FPIs
as the patch

- Melanie

Commits

  1. doc: Update formula for vacuum insert threshold.

  2. Redefine pg_class.reltuples to be -1 before the first VACUUM or ANALYZE.

  3. Measure the number of all-visible pages for use in index-only scan costing.