{"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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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":28,"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 v28.8 (Yoast SEO v28.8) - 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=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/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":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"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}]}}