{"id":7932,"date":"2016-05-18T04:06:39","date_gmt":"2016-05-18T02:06:39","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/"},"modified":"2016-05-18T04:06:39","modified_gmt":"2016-05-18T02:06:39","slug":"a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/","title":{"rendered":"A look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations"},"content":{"rendered":"

Vacuum in PostgreSQL is one of the most important points to consider when managing a PostgreSQL instance. Usually vacuum<\/a> is running in the background and just gets the job done. But, as always, there are situations when you need to get a closer look at what is going on. PostgreSQL 9.6 (currently in Beta1<\/a>) introduced a new view<\/a> which allows to see the progress of the vacuum worker processes<\/a>. Lets have a short look at it. <\/p>\n

<\/p>\n

To have something available for vacuum to do lets generate some data: <\/p>\n

\n(postgres@[local]:5432) [postgres] > create table t1 ( a int, b varchar(40), c timestamp );\nCREATE TABLE\ninsert into t1 ( a, b, c )\nselect aa, bb, cc\n  from generate_series(1,10000000) aa\n     , md5(aa::varchar) bb\n     , now() cc;\nNSERT 0 10000000\n<\/pre>\n
Vacuuming this table right now does not make any sense as there are no tuples to cleanup. We’ll need to delete or update some data for this. Before doing this I’ll disable autovacuum so I can kick it off manually when I am ready with my data:<\/p>\n
\n(postgres@[local]:5432) [postgres] > show autovacuum;\n autovacuum \n------------\n on\n(1 row)\n\n(postgres@[local]:5432) [postgres] > alter system set autovacuum='off';\nALTER SYSTEM\n(postgres@[local]:5432) [postgres] > select * from pg_reload_conf();\n pg_reload_conf \n----------------\n t\n(1 row)\n\n(postgres@[local]:5432) [postgres] > \n<\/pre>\n
Having a look at the log file confirms that the autovacuum launcher process was shut down:<\/p>\n
\n2016-05-17 07:33:02.648 CEST - 4 - 2703 -  - @ LOG:  received SIGHUP, reloading configuration files\n2016-05-17 07:33:02.653 CEST - 5 - 2703 -  - @ LOG:  parameter \"autovacuum\" changed to \"off\"\n2016-05-17 07:33:02.655 CEST - 2 - 2709 -  - @ LOG:  autovacuum launcher shutting down\n<\/pre>\n
Time to delete some data from the test table:<\/p>\n
\ndelete from t1\n where mod(a,7) = 0\n;\nDELETE 1428571\n<\/pre>\n
This should result in some dead tuples which we can check by querying pg_stat_user_tables<\/a>:<\/p>\n
\n(postgres@[local]:5432) [postgres] > select n_tup_del\n                                          , n_live_tup\n                                          , n_dead_tup \n                                       from pg_stat_user_tables \n                                      where relname='t1';\n n_tup_del | n_live_tup | n_dead_tup \n-----------+------------+------------\n   1571428 |    8571429 |    1428571\n(1 row)\n<\/pre>\n
The amount of dead tuples corresponds to the number of rows we deleted. Now we can start vacuum on the table and check the new pg_stat_progress_vacuum for what is going on in a seconds session.<\/p>\n
Session 1:<\/p>\n
\npostgres@[local]:5432) [postgres] > vacuum verbose t1;\n<\/pre>\n
Session 2:<\/p>\n
\n(postgres@[local]:5432) [postgres] > x\nExpanded display is on.\n(postgres@[local]:5432) [postgres] > select * from pg_stat_progress_vacuum;\n<\/pre>\n
The result of the above command (executed twice with some seconds in between) is:<\/p>\n
\n(postgres@[local]:5432) [postgres] > select * from pg_stat_progress_vacuum;\n-[ RECORD 1 ]------+--------------\npid                | 3051\ndatid              | 13322\ndatname            | postgres\nrelid              | 16388\nphase              | scanning heap\nheap_blks_total    | 93458\nheap_blks_scanned  | 1568\nheap_blks_vacuumed | 1567\nindex_vacuum_count | 0\nmax_dead_tuples    | 291\nnum_dead_tuples    | 16\n\n\n(postgres@[local]:5432) [postgres] > select * from pg_stat_progress_vacuum;\n-[ RECORD 1 ]------+--------------\npid                | 3051\ndatid              | 13322\ndatname            | postgres\nrelid              | 16388\nphase              | scanning heap\nheap_blks_total    | 93458\nheap_blks_scanned  | 90618\nheap_blks_vacuumed | 90617\nindex_vacuum_count | 0\nmax_dead_tuples    | 291\nnum_dead_tuples    | 15\n<\/pre>\n
Pretty cool. Now we can estimate how long the operation will take or how much work is already done (when heap_blks_scanned comes close to heap_blks_total work should almost be done). According to the documentation and to the thread on hackers<\/a> this is only the first view providing progress information…more will probably come in future releases.<\/p>\n","protected":false},"excerpt":{"rendered":"
Vacuum in PostgreSQL is one of the most important points to consider when managing a PostgreSQL instance. Usually vacuum is running in the background and just gets the job done. But, as always, there are situations when you need to get a closer look at what is going on. PostgreSQL 9.6 (currently in Beta1) introduced […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-7932","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nA look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations - 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\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations\" \/>\n<meta property=\"og:description\" content=\"Vacuum in PostgreSQL is one of the most important points to consider when managing a PostgreSQL instance. Usually vacuum is running in the background and just gets the job done. But, as always, there are situations when you need to get a closer look at what is going on. PostgreSQL 9.6 (currently in Beta1) introduced […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-18T02:06:39+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\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"A look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations\",\"datePublished\":\"2016-05-18T02:06:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/\"},\"wordCount\":301,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/\",\"name\":\"A look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-05-18T02:06:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations\"}]},{\"@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":"A look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations - 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\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/","og_locale":"en_US","og_type":"article","og_title":"A look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations","og_description":"Vacuum in PostgreSQL is one of the most important points to consider when managing a PostgreSQL instance. Usually vacuum is running in the background and just gets the job done. But, as always, there are situations when you need to get a closer look at what is going on. PostgreSQL 9.6 (currently in Beta1) introduced […]","og_url":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/","og_site_name":"dbi Blog","article_published_time":"2016-05-18T02:06:39+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\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"A look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations","datePublished":"2016-05-18T02:06:39+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/"},"wordCount":301,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/","url":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/","name":"A look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-05-18T02:06:39+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-progress-reporting-for-vacuum-operations\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"A look at PostgreSQL 9.6 \u2013 progress reporting for VACUUM operations"}]},{"@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\/7932","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=7932"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7932\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7932"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7932"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7932"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7932"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}