{"id":20770,"date":"2022-11-30T12:40:39","date_gmt":"2022-11-30T11:40:39","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=20770"},"modified":"2022-12-14T10:45:22","modified_gmt":"2022-12-14T09:45:22","slug":"postgresql-16-more-flexibility-for-vacuum-and-analyze","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/","title":{"rendered":"PostgreSQL 16: More flexibility for vacuum and analyze"},"content":{"rendered":"\n

Update 14-Dec-2022<\/strong>: Please see this post,<\/a> the content here is not anymore up to date.<\/p>\n\n\n\n

<\/p>\n\n\n\n

While PostgreSQL 15.1 was released some weeks<\/a> ago PostgreSQL 16 is already under active development. Two days ago two interesting features have been committed which give you more flexibility when it comes to vacuum<\/a> and analyze<\/a>. Before we dive into that lets have a look at how that works currently in PostgreSQL 15 and before.<\/p>\n\n\n\n

To start with, we need to users, one of them having a simple table:<\/p>\n\n\n

\npostgres=# select version();\n                                                       version                                                        \n----------------------------------------------------------------------------------------------------------------------\n PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit\n(1 row)\n\npostgres=# create user u1 with login password 'u1';\nCREATE ROLE\npostgres=# create user u2 with login password 'u2';\nCREATE ROLE\n\ufeffpostgres=# grant all on schema public to u1;\nGRANT\npostgres=# \\c postgres u1\nYou are now connected to database "postgres" as user "u1".\npostgres=> create table t1 ( a int );\nCREATE TABLE\npostgres=> \n<\/pre><\/div>\n\n\n
When it comes to vacuum and analyze who is allowed to perform these operations on the t1 table, which is owned by u1? Of course the owner itself:<\/p>\n\n\n
\npostgres=> select current_user;\n current_user \n--------------\n u1\n(1 row)\n\npostgres=> analyze t1;\nANALYZE\npostgres=> vacuum t1;\nVACUUM\npostgres=> \n<\/pre><\/div>\n\n\n
The only other user who is allowed to do that is the superuser, but not u2 or any other user which is not a superuser:<\/p>\n\n\n
\npostgres=> \\c postgres u2\nYou are now connected to database "postgres" as user "u2".\npostgres=> analyze t1;\nWARNING:  skipping "t1" --- only table or database owner can analyze it\nANALYZE\npostgres=> vacuum t1;\nWARNING:  skipping "t1" --- only table or database owner can vacuum it\nVACUUM\npostgres=> \\c postgres postgres\nYou are now connected to database "postgres" as user "postgres".\npostgres=# analyze t1;\nANALYZE\npostgres=# vacuum t1;\nVACUUM\n<\/pre><\/div>\n\n\n
That’s all the options you have currently. PostgreSQL 16 will give you more flexibility with this, so lets do the same setup in the current development version of PostgreSQL:<\/p>\n\n\n
\npostgres=# select version();\n                                               version                                                \n------------------------------------------------------------------------------------------------------\n PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit\n(1 row)\npostgres=# create user u1 with login password 'u1';\nCREATE ROLE\npostgres=# create user u2 with login password 'u2';\nCREATE ROLE\npostgres=# grant all on schema public to u1;\nGRANT\npostgres=# \\c postgres u1\nYou are now connected to database "postgres" as user "u1".\npostgres=> create table t1 ( a int );\nCREATE TABLE\npostgres=> \n<\/pre><\/div>\n\n\n
By default, the behavior is exactly the same as in PostgreSQL 15. Only the owner of the table and a superuser are allowed to vacuum and analyze the t1 table. But what you can do starting with PostgreSQL 16 is, to grant these operations to another user:<\/p>\n\n\n
\npostgres=> grant analyze on t1 to u2;\nGRANT\npostgres=> grant vacuum on t1 to u2;\nGRANT\npostgres=> \\c postgres u2\nYou are now connected to database "postgres" as user "u2".\npostgres=> analyze t1;\nANALYZE\npostgres=> vacuum t1;\nVACUUM\npostgres=> \n<\/pre><\/div>\n\n\n
Of course this does not grant the right to see the data nor does it give any other permissions:<\/p>\n\n\n
\npostgres=> select * from t1;\nERROR:  permission denied for table t1\npostgres=> \n<\/pre><\/div>\n\n\n
In addition two this, PostgreSQL 16 will come with two additional predefined roles:<\/p>\n\n\n
\npostgres=> select * from pg_roles where rolname in ('pg_vacuum_all_tables','pg_analyze_all_tables');;\n        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid  \n-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------\n pg_vacuum_all_tables  | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4549\n pg_analyze_all_tables | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4550\n(2 rows)\n<\/pre><\/div>\n\n\n
These roles, when granted to someone, also give the permissions for the vacuum and analyze operations.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Update 14-Dec-2022: Please see this post, the content here is not anymore up to date. While PostgreSQL 15.1 was released some weeks ago PostgreSQL 16 is already under active development. Two days ago two interesting features have been committed which give you more flexibility when it comes to vacuum and analyze. Before we dive into […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[2602],"type_dbi":[],"class_list":["post-20770","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql-2"],"acf":[],"yoast_head":"\nPostgreSQL 16: More flexibility for vacuum and analyze - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 16: More flexibility for vacuum and analyze\" \/>\n<meta property=\"og:description\" content=\"Update 14-Dec-2022: Please see this post, the content here is not anymore up to date. While PostgreSQL 15.1 was released some weeks ago PostgreSQL 16 is already under active development. Two days ago two interesting features have been committed which give you more flexibility when it comes to vacuum and analyze. Before we dive into […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-30T11:40:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-12-14T09:45:22+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 16: More flexibility for vacuum and analyze\",\"datePublished\":\"2022-11-30T11:40:39+00:00\",\"dateModified\":\"2022-12-14T09:45:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/\"},\"wordCount\":262,\"commentCount\":0,\"keywords\":[\"postgresql\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/\",\"name\":\"PostgreSQL 16: More flexibility for vacuum and analyze - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2022-11-30T11:40:39+00:00\",\"dateModified\":\"2022-12-14T09:45:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 16: More flexibility for vacuum and analyze\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 16: More flexibility for vacuum and analyze - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 16: More flexibility for vacuum and analyze","og_description":"Update 14-Dec-2022: Please see this post, the content here is not anymore up to date. While PostgreSQL 15.1 was released some weeks ago PostgreSQL 16 is already under active development. Two days ago two interesting features have been committed which give you more flexibility when it comes to vacuum and analyze. Before we dive into […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/","og_site_name":"dbi Blog","article_published_time":"2022-11-30T11:40:39+00:00","article_modified_time":"2022-12-14T09:45:22+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 16: More flexibility for vacuum and analyze","datePublished":"2022-11-30T11:40:39+00:00","dateModified":"2022-12-14T09:45:22+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/"},"wordCount":262,"commentCount":0,"keywords":["postgresql"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/","name":"PostgreSQL 16: More flexibility for vacuum and analyze - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-11-30T11:40:39+00:00","dateModified":"2022-12-14T09:45:22+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-flexibility-for-vacuum-and-analyze\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 16: More flexibility for vacuum and analyze"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/20770","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=20770"}],"version-history":[{"count":3,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/20770\/revisions"}],"predecessor-version":[{"id":21031,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/20770\/revisions\/21031"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=20770"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=20770"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=20770"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=20770"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}