The insert-autovacuum tuning covers 4 hypertables out of 51, because the list is hand-maintained

[erikdarlingdata]

Erik recalled tuning use2's autovacuum to be more aggressive on the insert path, since the store's workload is overwhelmingly append, and wondered whether it had been lost in an upgrade. It was not lost — it was codified. But checking that turned up how little of the store it actually covers.

The tuning is intact and identical on all three boxes

collect.procedure_stats     autovacuum_vacuum_insert_scale_factor=0.02, autovacuum_vacuum_insert_threshold=10000
collect.query_stats         same
collect.query_store_stats   same
collect.pg_statement_stats  same
collect.query_plan_dim      (none)
collect.query_text_dim      (none)

Nothing local is involved and nothing can be lost on upgrade: every autovacuum_* line in postgresql.conf is commented out on all three boxes, postgresql.auto.conf is empty on all three, and there are no ALTER SYSTEM overrides. The tuning lives entirely in per-table reloptions that PgTableTuning.ApplyAsync re-applies on every service start, which is why it survives cluster rebuilds and version upgrades. That design is right and worth keeping.

The coverage, though

hypertables with the insert tuning:      4
hypertables without it:                 47

The untuned set includes tables considerably larger than some of the tuned ones:

hypertable	size
perfmon_stats	5330 MB
spinlock_stats	3707 MB
wait_stats	2773 MB
index_object_stats	1907 MB
query_snapshots	1259 MB
file_io_stats	1227 MB
system_health_events	700 MB
collection_log	639 MB

Every one of these is a pure-insert collector fact table — structurally identical to the four that are tuned. PgTableTuning's own comment states the reasoning, and it applies verbatim to all of them:

the per-table autovacuum_vacuum_insert_scale_factor = 0.02 override keeps the visibility map current on the pure-insert hypertable chunks (the Postgres default 0.2 leaves the day's hot chunk stale before the daily TimescaleDB rollover, degrading the Index Only Scan back to heap fetches)

Why four and not fifty-one

The list is hand-maintained, and the four entries are the tables that happened to be the subject of an EXPLAIN investigation. The file says as much, about a table that had already been missed once:

It was simply missed when the PostgreSQL collectors landed, since this list is hand-maintained rather than derived from the catalog.

So the miss rate is the point rather than any individual omission. pg_statement_stats was missed when the PostgreSQL collectors landed; query_plan_dim is missed now (#2404, and it needs the dead-tuple knob rather than this one); the next collector will be missed on the same terms. A hand-maintained list of "which of our tables are append-heavy" is a list that describes a property every collector fact table has by construction.

Proposal: derive it

Apply the insert tuning to every hypertable that lacks it, resolved from timescaledb_information.hypertables at startup rather than from a literal list. Same idempotence and same per-table failure isolation as today, but a new collector is covered the moment it exists.

Two things worth stating because they bound the risk:

The threshold does the protecting on small tables. 0.02 + 10000 means a low-rate hypertable needs 10,000 inserts before the scale factor is even consulted, so this does not make autovacuum thrash on the many small ones — it only bites where there is genuine insert volume, which is exactly where it is wanted.

And it should apply only to what it is reasoned about. Hypertables are pure-insert by construction in this store; plain tables are not, which is why query_plan_dim gets the other knob under #2404 rather than being swept in here. The derivation should be scoped to hypertables, not to "every table in collect".

[erikdarlingdata]

Fixed on dev by #2406. The sweep resolves hypertables lacking the option from timescaledb_information.hypertables and ALTERs those, per-table failure-isolated, with idempotence in the predicate so a re-run selects nothing rather than re-ALTERing 51 tables on every service start.

The four literal statements stay: a plain-PostgreSQL store has no TimescaleDB catalog to read, and those four are the measured floor for that case. They now share a single const with the sweep — two spellings would tune a store two different ways depending on which path reached a table first, and because the sweep's predicate matches on insert_scale_factor, it would treat the other spelling as already-tuned and make the divergence permanent and invisible.

Deliberately scoped to hypertables rather than everything in collect. Insert-driven autovacuum governs the visibility map on append-only data; a plain table whose churn is DELETEs needs autovacuum_vacuum_scale_factor, which is a different knob for a different failure — that is query_plan_dim under #2402, fixed separately in #2404. Sweeping every table here would have silently applied the inert knob to the one table that most needed the other one.

Worth recording why this happened, since it is the third instance today of the same shape: a fact that has to be remembered rather than derived drifts. The hand-maintained tuning list, the checksum step that was fixed in one workflow and not its twin (#2383/#2398), and the per-job checkout refs that ignored the dispatched branch (#2399) were all correct when written and all decayed because nothing made them stay true. Replacing the list with a catalog query was the point of this fix rather than appending 47 entries to it.