Greetings, * Jeff Davis (pg...@j-davis.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
signature.asc
Description: PGP signature
