{"id":15254,"date":"2020-11-25T07:21:52","date_gmt":"2020-11-25T06:21:52","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/"},"modified":"2020-11-25T07:21:52","modified_gmt":"2020-11-25T06:21:52","slug":"postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/","title":{"rendered":"PostgreSQL 14: Add the number of de-allocations to pg_stat_statements?"},"content":{"rendered":"<p>In the <a href=\"https:\/\/www.dbi-services.com\/blog\/will-postgresql14-finally-come-with-schema-variables\/\" target=\"_blank\" rel=\"noopener noreferrer\">last post we had a look at an interesting patch (schema variables)<\/a> that is currently waiting to be committed for PostgreSQL 14. Another patch, that currently is in the same state is named <a href=\"https:\/\/commitfest.postgresql.org\/30\/2736\/\" target=\"_blank\" rel=\"noopener noreferrer\">[PATCH] Add features to pg_stat_statements<\/a>. As this does not give much information what the patch is about, here a short summary: When you reach the maximum number of allowed statements to track, <a href=\"https:\/\/www.postgresql.org\/docs\/current\/pgstatstatements.html\" target=\"_blank\" rel=\"noopener noreferrer\">pg_stat_statements<\/a> will de-allocate entries to free space for other statements. As de-allocations use resources as well and frequent de-allocations might also mean that pg_stat_statement is not configured the right way for your workload, this patch provides the following information: how many de-allocations happened.<\/p>\n<p><!--more--><\/p>\n<p>In the default configuration pg_stat_statements tracks a maximum of 5000 statements:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# show pg_stat_statements.max;\n pg_stat_statements.max \n------------------------\n 5000\n(1 row)\n<\/pre>\n<p>The lowest value allowed for pg_stat_statements.max is 100:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# alter system set pg_stat_statements.max=5;\nERROR:  5 is outside the valid range for parameter \"pg_stat_statements.max\" (100 .. 2147483647)\npostgres=# alter system set pg_stat_statements.max=100;\nALTER SYSTEM\n<\/pre>\n<p>As this change requires a restart it should be set carefully:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select context from pg_settings where name = 'pg_stat_statements.max';\n  context   \n------------\n postmaster\n(1 row)\n\npostgres=# ! pg_ctl restart\nwaiting for server to shut down.... done\nserver stopped\nwaiting for server to start....2020-11-20 01:00:54.656 CET - 1 - 102610 -  - @ LOG:  redirecting log output to logging collector process\n2020-11-20 01:00:54.656 CET - 2 - 102610 -  - @ HINT:  Future log output will appear in directory \"pg_log\".\n done\nserver started\npostgres=# show pg_stat_statements.max;\nFATAL:  terminating connection due to administrator command\nserver closed the connection unexpectedly\n        This probably means the server terminated abnormally\n        before or while processing the request.\nThe connection to the server was lost. Attempting reset: Succeeded.\npostgres=# show pg_stat_statements.max;\n pg_stat_statements.max \n------------------------\n 100\n(1 row)\n<\/pre>\n<p>Lets generate some random statements and the check what additional information we get with this patch:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select 'create table t'||i||'( a int, b int, c int)' from generate_series(1,101) i; gexec\n                ?column?                 \n-----------------------------------------\n create table t1( a int, b int, c int)\n create table t2( a int, b int, c int)\n create table t3( a int, b int, c int)\n create table t4( a int, b int, c int)\n create table t5( a int, b int, c int)\n create table t6( a int, b int, c int)\n create table t7( a int, b int, c int)\n create table t8( a int, b int, c int)\n create table t9( a int, b int, c int)\n...\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\npostgres=# \n<\/pre>\n<p>This created 101 tables and should have forced pg_stat_statements to de-allocate some older statements. There is a new, very simple view which provides this information:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select * from pg_stat_statements_info;\n dealloc \n---------\n       3\n(1 row)\n<\/pre>\n<p>Nice. This will give you an idea if pg_stat_statements.max is not properly configured for your environment. If you manually re-set pg_statements the de-allocation counter will also be reset:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select * from pg_stat_statements_info;\n dealloc \n---------\n       3\n(1 row)\n\npostgres=# select pg_stat_statements_reset();\n pg_stat_statements_reset \n--------------------------\n \n(1 row)\n\npostgres=# select * from pg_stat_statements_info;\n dealloc \n---------\n       0\n(1 row)\n<\/pre>\n<p>Update 2020-Nov-17: <a href=\"https:\/\/t.co\/gugH2sPDjl?amp=1\" target=\"_blank\" rel=\"noopener noreferrer\">This was finally committed<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the last post we had a look at an interesting patch (schema variables) that is currently waiting to be committed for PostgreSQL 14. Another patch, that currently is in the same state is named [PATCH] Add features to pg_stat_statements. As this does not give much information what the patch is about, here a short [&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-15254","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.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 14: Add the number of de-allocations to pg_stat_statements? - 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-14-add-the-number-of-de-allocations-to-pg_stat_statements\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 14: Add the number of de-allocations to pg_stat_statements?\" \/>\n<meta property=\"og:description\" content=\"In the last post we had a look at an interesting patch (schema variables) that is currently waiting to be committed for PostgreSQL 14. Another patch, that currently is in the same state is named [PATCH] Add features to pg_stat_statements. As this does not give much information what the patch is about, here a short [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-11-25T06:21:52+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-14-add-the-number-of-de-allocations-to-pg_stat_statements\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 14: Add the number of de-allocations to pg_stat_statements?\",\"datePublished\":\"2020-11-25T06:21:52+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\\\/\"},\"wordCount\":238,\"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-14-add-the-number-of-de-allocations-to-pg_stat_statements\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\\\/\",\"name\":\"PostgreSQL 14: Add the number of de-allocations to pg_stat_statements? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-11-25T06:21:52+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 14: Add the number of de-allocations to pg_stat_statements?\"}]},{\"@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 14: Add the number of de-allocations to pg_stat_statements? - 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-14-add-the-number-of-de-allocations-to-pg_stat_statements\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 14: Add the number of de-allocations to pg_stat_statements?","og_description":"In the last post we had a look at an interesting patch (schema variables) that is currently waiting to be committed for PostgreSQL 14. Another patch, that currently is in the same state is named [PATCH] Add features to pg_stat_statements. As this does not give much information what the patch is about, here a short [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/","og_site_name":"dbi Blog","article_published_time":"2020-11-25T06:21:52+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-14-add-the-number-of-de-allocations-to-pg_stat_statements\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 14: Add the number of de-allocations to pg_stat_statements?","datePublished":"2020-11-25T06:21:52+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/"},"wordCount":238,"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-14-add-the-number-of-de-allocations-to-pg_stat_statements\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/","name":"PostgreSQL 14: Add the number of de-allocations to pg_stat_statements? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-11-25T06:21:52+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-add-the-number-of-de-allocations-to-pg_stat_statements\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 14: Add the number of de-allocations to pg_stat_statements?"}]},{"@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\/15254","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=15254"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15254\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15254"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15254"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15254"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15254"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}