Future: a background worker to schedule columnar vacuum and analyze, which autovacuum cannot reach

[jdatcmd]

Future work, deliberately not scheduled. Filing it so the gap is recorded where it
is visible rather than discovered by a user, per #395.

The gap

pgColumnar has maintenance operations that PostgreSQL's own autovacuum daemon will
never invoke, because they are extension functions rather than table-AM callbacks:

operation	reached by autovacuum?
pgcolumnar_relation_vacuum (visibility map, retire fully-deleted groups)	yes, it is the AM callback
pgcolumnar.vacuum() (the space-reclaiming rewrite)	no
pgcolumnar.vacuum_sorted() / clustering decay	no
a future pgcolumnar.analyze() (#414)	no

So the cheap online part is automatic and the expensive part is manual. A user who
never runs the functions accumulates reclaimable space and, if #414 goes ahead,
increasingly stale statistics.

The asymmetry matters: a table that is never rewritten wastes disk, which is visible
and survivable. Statistics that are never refreshed produce bad plans, which is
invisible and is felt as "the database got slower" with no obvious cause. That is the
argument for solving scheduling before adding more manual maintenance functions, and
it is why #414 is gated on this.

What it would be

A background worker registered from _PG_init, in the shape of core's autovacuum
launcher: a launcher that wakes on an interval and starts per-database workers, each
deciding what needs attention and doing it.

The decisions that need answering first, none of which are code

1. What triggers work. Autovacuum uses row-change counters from the statistics
   collector. Our triggers are different in kind: reclaimable fraction (dead rows over
   live), clustering decay (pgcolumnar.sort_status, which already measures it), and
   staleness for analyze. Each needs a threshold and a GUC, and thresholds invented
   without measurement are how a daemon becomes a support burden.
2. Not fighting autovacuum. The AM callback already runs under autovacuum and does
   the online work. A worker that also rewrites the same table takes
   AccessExclusiveLock, and doing that on a schedule the user did not choose is a very
   different proposition from doing it when they type it. This is the part I would
   most want argued before any code: an extension that takes an exclusive lock on a
   user's table on its own initiative needs a strong default of not doing so.
3. Cost control. Autovacuum has vacuum_cost_delay and friends precisely because
   unbounded background I/O is worse than no background maintenance. We would need an
   equivalent, and would need to respect the existing settings rather than invent
   parallel ones.
4. Registration cost. A background worker means shared_preload_libraries becomes
   effectively required for the feature, which changes installation. It should be
   possible to run pgColumnar without it and lose only this.
5. Failure behaviour. A worker that errors must not restart in a tight loop, and
   must report where it failed in a way an administrator can act on.

Why a worker rather than the alternatives

• Documentation plus cron works today and is what we should keep recommending
  until this exists. It is honest and it is what docs/administration.md should say.
• A pg_cron dependency pushes the problem onto another extension and is not
  available everywhere.
• Doing more inside the AM callback is tempting, since autovacuum already calls it,
  but the callback runs under ShareUpdateExclusiveLock and the expensive operations
  need AccessExclusiveLock. Escalating a lock inside a callback autovacuum invoked is
  not something I would want to do.

That third option is worth re-examining anyway: some of what the functions do today may
be reachable under the weaker lock, which would shrink this issue rather than solve it.
Worth a look before committing to a daemon.

Scope

Nothing here is scheduled. The next step, whenever it is taken, is the measurement in
(1): what fraction of reclaimable space and clustering decay actually costs a query
enough to be worth an automatic rewrite. Until that number exists, any threshold is a
guess.

Related: #414 (custom analyze, gated on this), the lazy-versus-eager maintenance split
recorded in docs/limitations.md.

[jdatcmd]

Constraint, not an open question: a worker uses the lazy paths and never escalates

Point (2) above says an extension taking AccessExclusiveLock on its own initiative
"needs to be argued before any code". That was too weak. It is a constraint, and the
audit says we already have everything needed to satisfy it.

What the tree actually does today

Four AccessExclusiveLock sites exist, all in columnar_vacuum.c, and every one is
a rewrite:

function	lock	why
pgcolumnar.vacuum()	AccessExclusive	rewrites the relation into full stripes
pgcolumnar.vacuum_sorted()	AccessExclusive	sorted rewrite
pgcolumnar.cluster()	AccessExclusive	Z-order reorg
pgcolumnar.truncate()	conditional, see below	end truncation only

Everything else already runs under ShareUpdateExclusiveLock, concurrent with readers
and writers: recluster(), compact(), compact_rewrite(), projection maintenance,
and pgcolumnar_relation_vacuum, which is the callback autovacuum already invokes and
which marks the visibility map and retires fully-deleted row groups.

So the lazy half exists and is the half a daemon wants.

truncate() is the pattern to copy

if (pgcolumnar_enable_end_truncation &&
    ConditionalLockRelation(rel, AccessExclusiveLock))
        result = pgcolumnar_do_end_truncation(rel);

It holds SUEL for the real work, then tries for the strong lock conditionally and
skips the optimisation if it cannot have it. It never waits, so it can never block a
user's table for the sake of a background tidy-up.

The rule this issue should carry

1. A worker calls only the ShareUpdateExclusiveLock paths. compact() and
   recluster() already reclaim space and restore clustering online. Those are the
   verbs a daemon gets.
2. It never calls vacuum(), vacuum_sorted() or cluster(). Those rewrite, they
   need the exclusive lock for a real reason, and choosing when to take a table offline
   is the user's decision, not a daemon's default.
3. Where an exclusive lock would genuinely help, it is ConditionalLock or nothing,
   on the truncate() model: take it if free, skip if not, never queue behind a reader.

That also shrinks the design. Point (2) was the part I expected to be contentious, and
it turns out to be settled by work already done: the lazy verbs exist, so the worker is
a scheduler over existing safe operations rather than a new class of automatic
maintenance.

What remains genuinely open is unchanged: what thresholds trigger the work, and whether
the numbers justify a daemon at all rather than documentation and cron.

[ChronicallyJD]

Taking this alongside #414. One thing to record, and my read on sequencing.

The circular gate should be broken, and #414 should go first

#414 says it is gated on this issue. This issue lists a future pgcolumnar.analyze() among
the things that justify a daemon. Neither can move while each defers to the other.

I measured #414's prize on the bench today: 6,302 ms -> 268 ms, 23.5x, on a 3M-row
20-column 1237 MB table, and confirmed there is no AM-side fix (details in
#414). That is worth shipping opt-in with the documented staleness caveat — the same
contract pgcolumnar.vacuum() already carries — rather than holding it behind a daemon that
is correctly unscheduled.

That does not weaken this issue. It makes its case concrete: once #414 exists there is a
real, measured 23.5x that a user only gets if they remember to run it, which is exactly the
"quietly rots" failure this issue is about.

The constraint in the comment above is the right shape, and it makes this testable

Your follow-up settled the contentious part: a worker calls only the
ShareUpdateExclusiveLock verbs (compact(), recluster()), never
vacuum()/vacuum_sorted()/cluster(), and where a strong lock would help it is
ConditionalLock-or-skip on the truncate() model.

Worth noting what that buys beyond safety: it makes the daemon unit-testable without a
daemon. The part that needs testing is the policy — given reclaimable fraction,
clustering decay and staleness, act or not? — which is a pure function of table statistics.
That can have a test suite before any background worker exists, and the thresholds can be
measured into it rather than invented.

If this is ever built, I would want the policy function landed and tested first, with the
worker as a thin scheduler over it. That also keeps the "documentation plus cron" option
live, since the same policy function can back a SQL-callable
pgcolumnar.maintenance_due(rel) that a cron job or a human can consult.

What I am not proposing

Any threshold. The open question in the issue is unchanged and correct: what fraction of
reclaimable space or clustering decay actually costs a query enough to justify the work.
I have not measured that and will not invent a number for it. My only claim here is about
sequencing and testability.