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

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:

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".