{"id":13867,"date":"2020-03-29T13:54:35","date_gmt":"2020-03-29T11:54:35","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/"},"modified":"2020-03-29T13:54:35","modified_gmt":"2020-03-29T11:54:35","slug":"postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/","title":{"rendered":"PostgreSQL 13 &#8211; Autovacuum can now be triggered based on inserts"},"content":{"rendered":"<p>A common issue with insert only tables in PostgreSQL is, that autovacuum well never kick in as the formula for autovacuum considers obsoleted tuples since the last vacuum but not the number of inserted tuples. Because of this you usually run a manual vacuum when a table is bulk loaded right after the load or you schedule regular vacuums for tables you know are insert only. PostgreSQL 13 will finally solve that issue because autovacuum will now be also triggered based on the amount of inserts into a table (more on the exact formula below). As usual let&#8217;s do a small, reproducible, demo to see how that works.<\/p>\n<p><!--more--><\/p>\n<p>I have the latest (as of today) PostgreSQL development version running in the default configuration, except for some logging parameters I usually change. The table I&#8217;ll be playing with is this one:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table t1 ( a int, b text, c date );\nCREATE TABLE\npostgres=# d t1\n                 Table \"public.t1\"\n Column |  Type   | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a      | integer |           |          | \n b      | text    |           |          | \n c      | date    |           |          | \n<\/pre>\n<p>When I bulk load that table in PostgreSQL before 13, autovacuum will never kick in. With PostgreSQL 13 we have two new configuration parameters that control autovacuum when it comes to inserts:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# show autovacuum_vacuum_insert_threshold;\n autovacuum_vacuum_insert_threshold \n------------------------------------\n 1000\n(1 row)\n\npostgres=# show autovacuum_vacuum_insert_scale_factor;\n autovacuum_vacuum_insert_scale_factor \n---------------------------------------\n 0.2\n(1 row)\n\npostgres=# \n<\/pre>\n<p>The formula is:<br \/>\n<code><br \/>\nvacuum insert threshold = autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor * number of tuples<br \/>\n<\/code><\/p>\n<p>When we start with an empty table autovacuum should kick in when we insert more than 1000 rows:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# insert into t1 (a,b,c) select i, i::text, now() from generate_series(1,1001) i;\nINSERT 0 1001\n<\/pre>\n<p>Wait some time (autovacuum_naptime is 1 minute in the default configuration, so this is the maximum you will need to wait) and check pg_stat_user_tables for the last autovacuum:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select last_autovacuum, autovacuum_count from pg_stat_user_tables where relname = 't1';\n       last_autovacuum        | autovacuum_count \n------------------------------+------------------\n 2020-03-29 15:25:42.75171+02 |                1\n(1 row)\n<\/pre>\n<p>Perfect, that works. This is really a great improvement as you do not need to take special care of mostly insert only tables that do not receive a lot of updates or deletes. In addition you can probably skip all the manual vacuums after bulk loading.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A common issue with insert only tables in PostgreSQL is, that autovacuum well never kick in as the formula for autovacuum considers obsoleted tuples since the last vacuum but not the number of inserted tuples. Because of this you usually run a manual vacuum when a table is bulk loaded right after the load or [&hellip;]<\/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-13867","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 13 - Autovacuum can now be triggered based on inserts - 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-13-autovacuum-can-now-be-triggered-based-on-inserts\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 13 - Autovacuum can now be triggered based on inserts\" \/>\n<meta property=\"og:description\" content=\"A common issue with insert only tables in PostgreSQL is, that autovacuum well never kick in as the formula for autovacuum considers obsoleted tuples since the last vacuum but not the number of inserted tuples. Because of this you usually run a manual vacuum when a table is bulk loaded right after the load or [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-29T11:54:35+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=\"2 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-13-autovacuum-can-now-be-triggered-based-on-inserts\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 13 &#8211; Autovacuum can now be triggered based on inserts\",\"datePublished\":\"2020-03-29T11:54:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/\"},\"wordCount\":278,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/\",\"name\":\"PostgreSQL 13 - Autovacuum can now be triggered based on inserts - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2020-03-29T11:54:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 13 &#8211; Autovacuum can now be triggered based on inserts\"}]},{\"@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 &amp;\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 13 - Autovacuum can now be triggered based on inserts - 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-13-autovacuum-can-now-be-triggered-based-on-inserts\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 13 - Autovacuum can now be triggered based on inserts","og_description":"A common issue with insert only tables in PostgreSQL is, that autovacuum well never kick in as the formula for autovacuum considers obsoleted tuples since the last vacuum but not the number of inserted tuples. Because of this you usually run a manual vacuum when a table is bulk loaded right after the load or [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/","og_site_name":"dbi Blog","article_published_time":"2020-03-29T11:54:35+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 13 &#8211; Autovacuum can now be triggered based on inserts","datePublished":"2020-03-29T11:54:35+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/"},"wordCount":278,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/","name":"PostgreSQL 13 - Autovacuum can now be triggered based on inserts - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-03-29T11:54:35+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-autovacuum-can-now-be-triggered-based-on-inserts\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 13 &#8211; Autovacuum can now be triggered based on inserts"}]},{"@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 &amp;\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\/13867","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=13867"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13867\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13867"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13867"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13867"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13867"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}