Re: Predefined role pg_maintenance for VACUUM, ANALYZE, CHECKPOINT.

Greetings,

* Jeff Davis (pgsql(at)j-davis(dot)com) wrote:
> On Sun, 2021-10-24 at 21:32 +0000, Bossart, Nathan wrote:
> > My initial reaction was that members of pg_maintenance should be able
> > to do all of these things (VACUUM, ANALYZE, CLUSTER, REINDEX, and
> > CHECKPOINT).
>
> What about REFRESH MATERIALIZED VIEW? That seems more specific to a
> workload, but it's hard to draw a clear line between that and CLUSTER.

Let's not forget that there are already existing non-superusers who can
run things like REFRESH MATERIALIZED VIEW- the owner.

> > Maybe one
> > option is to have two separate roles, one for commands that require
> > lower lock levels (i.e., ANALYZE and VACUUM without TRUNCATE and
> > FULL), and another for all of the maintenance commands.
>
> My main motivation is CHECKPOINT and database-wide VACUUM and ANALYZE.
> I'm fine extending it if others think it would be worthwhile, but it
> goes beyond my use case.

I've been wondering what the actual use-case here is. DB-wide VACUUM
and ANALYZE are already able to be run by the database owner, but
probably more relevant is that DB-wide VACUUMs and ANALYZEs shouldn't
really be necessary given autovacuum, so why are we adding predefined
roles which will encourage users to do that?

I was also contemplating a different angle on this- allowing users to
request autovacuum to run vacuum/analyze on a particular table. This
would have the advantage that you get the vacuum/analyze behavior that
autovacuum has (giving up an attempted truncate lock if another process
wants a lock on the table, going at a slower pace rather than going all
out and sucking up lots of I/O, etc).

I'm not completely against this approach but just would like a bit
better understanding of why it makes sense and what things we'll be able
to say about what this role can/cannot do.

Lastly though- I dislike the name, it seems far too general. I get that
naming things is hard but maybe we could find something better than
'pg_maintenance' for this.

Thanks,

Stephen