CYBERTEC Logo

GRANT VACUUM, ANALYZE in PostgreSQL 16

11.2022 / Category: , / Tags: | | | |

What is GRANT VACUUM, ANALYZE?

PostgreSQL uses table VACUUM and ANALYZE commands to optimize the database. The VACUUM command reclaims storage space and makes it available for re-use. It also updates the visibility map, which helps the query planner to quickly identify which parts of the table have live rows. The ANALYZE command collects statistics about the contents of tables, which the query planner uses to determine the most efficient execution plans.

Why GRANT VACUUM, ANALYZE?

Currently a table can only be vacuumed or analyzed by its owner or a superuser. This can now be extended to any user by means of an appropriate GRANT thanks to Nathan Bossart's patch.

Also Nathan implemented another patch which provides two new predefined roles: "pg_vacuum_all_tables" and "pg_analyze_all_tables".

Roles which have been granted these roles' privileges can perform VACUUM or ANALYZE respectively on any or all tables as if they were a superuser. This removes the need to grant superuser privilege to roles just so they can perform vacuum and/or analyze.

Both patches were committed by Andrew Dunstan and reviewed by Bharath Rupireddy, Kyotaro Horiguchi, Stephen Frost, Robert Haas, Mark Dilger, Tom Lane, Corey Huinker, David G. Johnston, and Michael Paquier.

How to GRANT VACUUM, ANALYZE?

Let's try it out!

Here's what the above SQL code is doing:

  1. Connect to a new database called "test".
  2. As "postgres" user create a new role called "stepan" and a new table called "foo".
  3. Try to vacuum "foo" as "stepan". The output shows that "stepan" can't vacuum "foo" until we grant vacuum privilege on "foo" to "stepan".
  4. As "postgres" role create a new role called "petro".
  5. Grant "pg_vacuum_all_tables" role to "petro".
  6. Now we can see that "petro" can vacuum "foo" (and every other table in all databases!) even though we didn't grant vacuum on "foo" to "petro" role. The reason "petro" can vacuum "foo" is because "petro" is a member of the "pg_vacuum_all_tables" role.

Summary

VACUUM and ANALYZE are PostgreSQL commands used to optimize the database. GRANT VACUUM, ANALYZE can be used to extend the permission to vacuum and analyze tables to any user. This also can be done by granting the user a predefined role like "pg_vacuum_all_tables" or "pg_analyze_all_tables". This removes the need to grant superuser privilege to roles just so they can perform vacuum and/or analyze.

Check our other blog posts about vacuuming and analyzing tables.

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram