{"id":25572,"date":"2023-06-05T10:11:33","date_gmt":"2023-06-05T08:11:33","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=25572"},"modified":"2023-06-05T10:12:43","modified_gmt":"2023-06-05T08:12:43","slug":"postgresql-16-more-statistics-in-pg_stat__tables-indexes","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/","title":{"rendered":"PostgreSQL 16: More statistics in pg_stat_*_tables"},"content":{"rendered":"\n<p>PostgreSQL 16 is already in feature freeze and Beta1 has been released on May 25th. This is the time you should start testing and report any issues back to the community. There are plenty of new features, as usual, and in this post we&#8217;ll take a look a additional statistics in the pg_stat_*_tables catalog views.<\/p>\n\n\n\n<p>Let&#8217;s create a small simple table and an index:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3,5]; title: ; notranslate\" title=\"\">\npostgres=# create table t1 ( a int, b text );\nCREATE TABLE\npostgres=# insert into t1 select i, i::text from generate_series(1,500000) i;\nINSERT 0 500000\npostgres=# create index i1 on t1 (a);\nCREATE INDEX\n<\/pre><\/div>\n\n\n<p>Some of the statistics which are already available in versions before PostgreSQL 16 are these:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select seq_tup_read,n_tup_ins,n_live_tup from pg_stat_all_tables where relname = &#039;t1&#039;;\n seq_tup_read | n_tup_ins | n_live_tup \n--------------+-----------+------------\n       500000 |    500000 |     500000\n(1 row)\n<\/pre><\/div>\n\n\n<p>This tells is that 500&#8217;000 tuples have been read, inserted and are active, which of course is not a big surprise as we&#8217;ve just inserted them. One additional statistic which has been added to PostgreSQL 16 is the time of the last sequential scan for a given table:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select last_seq_scan from pg_stat_all_tables where relname = &#039;t1&#039;;\n         last_seq_scan         \n-------------------------------\n 2023-06-05 09:50:11.252371+02\n(1 row)\n<\/pre><\/div>\n\n\n<p>This is already populated and corresponds to the time we&#8217;ve created the tuples. Another statistics is the time of the last index scan:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select last_idx_scan from pg_stat_all_tables where relname = &#039;t1&#039;;\n last_idx_scan \n---------------\n \n(1 row)\n<\/pre><\/div>\n\n\n<p>This is empty as of now, as we did not read via the index, but will be populated once we do so:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,7]; title: ; notranslate\" title=\"\">\npostgres=# select * from t1 where a = 5;\n a | b \n---+---\n 5 | 5\n(1 row)\n\npostgres=# select last_idx_scan from pg_stat_all_tables where relname = &#039;t1&#039;;\n         last_idx_scan         \n-------------------------------\n 2023-06-05 09:58:49.721493+02\n(1 row)\n<\/pre><\/div>\n\n\n<p>This is already nice for tracking access times but there is another very interesting statistic which made it into PostgreSQL 16. When PostgreSQL is updating a tuple and this tuple cannot be placed into the same block, the new version of the tuple is added to another block. This is usually nothing you want to happen too often as it avoids <a href=\"https:\/\/www.postgresql.org\/docs\/current\/storage-hot.html\" target=\"_blank\" rel=\"noreferrer noopener\">HOT<\/a> updates. The new statistic &#8220;n_tup_newpage_upd&#8221; tells you how often a tuple needed to be created in a another block instead of the current block of the tuple. As we did not specify the fill factor when we created the table above we have the default of one hundred. This means that an update of a tuple in all blocks except the last one will move the tuple to a another block:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3]; title: ; notranslate\" title=\"\">\npostgres=# update t1 set b = &#039;xxx&#039; where a = 5;\nUPDATE 1\npostgres=# select n_tup_newpage_upd from pg_stat_all_tables where relname = &#039;t1&#039;;\n n_tup_newpage_upd \n-------------------\n                 1\n(1 row)\n<\/pre><\/div>\n\n\n<p>Really nice.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL 16 is already in feature freeze and Beta1 has been released on May 25th. This is the time you should start testing and report any issues back to the community. There are plenty of new features, as usual, and in this post we&#8217;ll take a look a additional statistics in the pg_stat_*_tables catalog views. [&hellip;]<\/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,198],"tags":[2602],"type_dbi":[],"class_list":["post-25572","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql-2"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 16: More statistics in pg_stat_*_tables - 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-statistics-in-pg_stat__tables-indexes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 16: More statistics in pg_stat_*_tables\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL 16 is already in feature freeze and Beta1 has been released on May 25th. This is the time you should start testing and report any issues back to the community. There are plenty of new features, as usual, and in this post we&#8217;ll take a look a additional statistics in the pg_stat_*_tables catalog views. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-06-05T08:11:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-05T08:12:43+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-16-more-statistics-in-pg_stat__tables-indexes\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 16: More statistics in pg_stat_*_tables\",\"datePublished\":\"2023-06-05T08:11:33+00:00\",\"dateModified\":\"2023-06-05T08:12:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\\\/\"},\"wordCount\":318,\"commentCount\":1,\"keywords\":[\"postgresql\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\\\/\",\"name\":\"PostgreSQL 16: More statistics in pg_stat_*_tables - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-06-05T08:11:33+00:00\",\"dateModified\":\"2023-06-05T08:12:43+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 16: More statistics in pg_stat_*_tables\"}]},{\"@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 16: More statistics in pg_stat_*_tables - 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-statistics-in-pg_stat__tables-indexes\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 16: More statistics in pg_stat_*_tables","og_description":"PostgreSQL 16 is already in feature freeze and Beta1 has been released on May 25th. This is the time you should start testing and report any issues back to the community. There are plenty of new features, as usual, and in this post we&#8217;ll take a look a additional statistics in the pg_stat_*_tables catalog views. [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/","og_site_name":"dbi Blog","article_published_time":"2023-06-05T08:11:33+00:00","article_modified_time":"2023-06-05T08:12:43+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-16-more-statistics-in-pg_stat__tables-indexes\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 16: More statistics in pg_stat_*_tables","datePublished":"2023-06-05T08:11:33+00:00","dateModified":"2023-06-05T08:12:43+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/"},"wordCount":318,"commentCount":1,"keywords":["postgresql"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/","name":"PostgreSQL 16: More statistics in pg_stat_*_tables - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-06-05T08:11:33+00:00","dateModified":"2023-06-05T08:12:43+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-16-more-statistics-in-pg_stat__tables-indexes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 16: More statistics in pg_stat_*_tables"}]},{"@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\/25572","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=25572"}],"version-history":[{"count":5,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/25572\/revisions"}],"predecessor-version":[{"id":25580,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/25572\/revisions\/25580"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=25572"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=25572"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=25572"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=25572"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}