{"id":1135,"date":"2025-07-11T09:00:00","date_gmt":"2025-07-11T14:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=1135"},"modified":"2025-07-10T22:08:03","modified_gmt":"2025-07-11T03:08:03","slug":"postgresql-pg-visibility-heap-page-diagnostics","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=1135","title":{"rendered":"PostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage"},"content":{"rendered":"

PostgreSQL\u2019s pg_visibility<\/code>: Peeking Under the Hood of Table Storage<\/strong><\/p>\n

When most people think about PostgreSQL internals, they picture tables, indexes, and perhaps the VACUUM process. Tucked away in the contrib extensions, however, is a tool that exposes what really sits on disk: the pg_visibility<\/code> extension. By querying it, you see how PostgreSQL tracks visibility and freezing at the page level\u2014information that directly affects vacuum efficiency and index-only scans.<\/p>\n

Installing the extension<\/h2>\n

pg_visibility<\/code> ships with the server source but is not installed automatically. Enable it in a database with:<\/p>\n

CREATE EXTENSION pg_visibility;<\/code><\/pre>\n
No restart is required, and the new functions are available immediately.<\/p>\n
What the extension shows<\/h2>\n
pg_visibility()<\/code> returns one row per heap page in a table and reports:<\/p>\n
    \n
  • blkno<\/strong> \u2013 block number (the page\u2019s position in the file)<\/li>\n
  • all_visible<\/strong> \u2013 every tuple on the page is visible to all transactions, so the visibility map bit is set<\/li>\n
  • all_frozen<\/strong> \u2013 every tuple is frozen and immune to wrap-around cleanup<\/li>\n
  • has_dead<\/strong> \u2013 at least one dead tuple remains<\/li>\n<\/ul>\n
    Because these values come straight from the table file\u2014not catalog statistics\u2014they provide an exact picture of on-disk reality.<\/p>\n
    Summary at a glance<\/h2>\n
    If you only need high-level counts\u2014useful when scripting checks\u2014call pg_visibility_map_summary()<\/code>:<\/p>\n
    SELECT * FROM pg_visibility_map_summary('schema.table_name');<\/code><\/pre>\n
    The function returns total heap pages, how many are all-visible, and how many are all-frozen. These numbers tell you instantly whether autovacuum is keeping up or whether a large batch operation left many pages dirty.<\/p>\n
    Practical uses<\/h2>\n
    Diagnosing page bloat<\/h3>\n
    If you suspect dead tuples after heavy UPDATE or DELETE activity, run:<\/p>\n
    SELECT blkno\nFROM pg_visibility('schema.table_name')\nWHERE has_dead;<\/code><\/pre>\n
    A sizable result confirms many pages still carry dead rows, suggesting a manual VACUUM<\/code> or even VACUUM FULL<\/code>. Because PostgreSQL stores tuples in fixed-size pages, any dead space that cannot be reused becomes bloat and slows sequential scans.<\/p>\n
    Explaining a missing index-only scan<\/h3>\n
    An index-only scan needs every referenced heap page set all-visible. To see where the visibility map falls short, execute:<\/p>\n
    SELECT blkno\nFROM pg_visibility('schema.table_name')\nWHERE NOT all_visible;<\/code><\/pre>\n
    Any returned block forces the planner to visit the heap, preventing the lighter plan. A normal VACUUM<\/code> (or tighter autovacuum thresholds) will usually clear these gaps.<\/p>\n
    Tuning autovacuum freeze settings<\/h3>\n
    Transactions older than autovacuum_freeze_max_age<\/code> must not see unfrozen tuples, or the entire database pauses for freeze. By tracking all_frozen<\/code> counts before and after a workload spike, you can adjust autovacuum_freeze_min_age<\/code> and related settings so tuples are frozen long before wrap-around risk.<\/p>\n
    Spot-checking a single page<\/h3>\n
    Need to drill into one troublesome block? Pass the block number:<\/p>\n
    SELECT * FROM pg_visibility('schema.table_name', blkno := 1234);<\/code><\/pre>\n
    This is especially helpful when analysing HOT-update chains or verifying whether a suspected corruption is logical (tuple state) rather than physical (checksum failure).<\/p>\n
    Performance considerations<\/h2>\n
    pg_visibility<\/code> scans the table directly, so on large relations it can generate noticeable I\/O. Use it during focused investigations rather than embedding it in recurring jobs. Day-to-day monitoring usually relies on VACUUM VERBOSE<\/code> output, autovacuum logs, and pg_stat_user_tables<\/code>.<\/p>\n
    Closing thoughts<\/h2>\n
    pg_visibility<\/code> offers a direct, page-level view of heap health. By pairing its output with routine vacuum statistics, you can verify whether maintenance keeps pace with workload, explain planner decisions, and schedule targeted cleanup before bloat becomes measurable. For anyone responsible for PostgreSQL performance tuning, it is a reliable microscope when the usual metrics leave questions unanswered.<\/p>\n","protected":false},"excerpt":{"rendered":"
    When most people think about PostgreSQL internals, they picture tables, indexes, and perhaps the VACUUM process. Tucked away in the contrib extensions, however, is a tool that exposes what really sits on disk: the pg_visibility extension. By querying it, you see how PostgreSQL tracks visibility and freezing at the page level\u2014information that directly affects vacuum efficiency and index-only scans.<\/p>\n","protected":false},"author":18,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[543],"tags":[595,403,599,597,596,240,600,598,594,593],"class_list":["post-1135","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-autovacuum","tag-database-performance","tag-heap-pages","tag-index-only-scan","tag-pg_visibility","tag-postgresql","tag-storage-internals","tag-table-bloat","tag-vacuum","tag-visibility-map"],"yoast_head":"\nPostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage - SQL Table Talk<\/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.sqltabletalk.com\/?p=1135\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage - SQL Table Talk\" \/>\n<meta property=\"og:description\" content=\"When most people think about PostgreSQL internals, they picture tables, indexes, and perhaps the VACUUM process. Tucked away in the contrib extensions, however, is a tool that exposes what really sits on disk: the pg_visibility extension. By querying it, you see how PostgreSQL tracks visibility and freezing at the page level\u2014information that directly affects vacuum efficiency and index-only scans.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltabletalk.com\/?p=1135\" \/>\n<meta property=\"og:site_name\" content=\"SQL Table Talk\" \/>\n<meta property=\"article:published_time\" content=\"2025-07-11T14:00:00+00:00\" \/>\n<meta name=\"author\" content=\"Ian Parker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ian Parker\" \/>\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.sqltabletalk.com\/?p=1135#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1135\"},\"author\":{\"name\":\"Ian Parker\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/544aba85507b8637f247298cc1e62212\"},\"headline\":\"PostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage\",\"datePublished\":\"2025-07-11T14:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1135\"},\"wordCount\":517,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"keywords\":[\"autovacuum\",\"Database Performance\",\"heap pages\",\"index-only scan\",\"pg_visibility\",\"PostgreSQL\",\"storage internals\",\"table bloat\",\"vacuum\",\"visibility map\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=1135#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1135\",\"url\":\"https:\/\/www.sqltabletalk.com\/?p=1135\",\"name\":\"PostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage - SQL Table Talk\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\"},\"datePublished\":\"2025-07-11T14:00:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1135#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=1135\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1135#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltabletalk.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\",\"url\":\"https:\/\/www.sqltabletalk.com\/\",\"name\":\"SQL Table Talk\",\"description\":\"Breaking Down SQL Server, One Post at a Time.\",\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltabletalk.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\",\"name\":\"Stephen Planck\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"caption\":\"Stephen Planck\"},\"logo\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/dexterwiki.com\",\"https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI\",\"https:\/\/www.youtube.com\/linuxmate\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/544aba85507b8637f247298cc1e62212\",\"name\":\"Ian Parker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/fbee6e7140969d91bc7fac815743ba8299aca3eefe26a0c2771d886f210e5865?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/fbee6e7140969d91bc7fac815743ba8299aca3eefe26a0c2771d886f210e5865?s=96&d=mm&r=g\",\"caption\":\"Ian Parker\"},\"url\":\"https:\/\/www.sqltabletalk.com\/?author=18\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage - SQL Table Talk","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.sqltabletalk.com\/?p=1135","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage - SQL Table Talk","og_description":"When most people think about PostgreSQL internals, they picture tables, indexes, and perhaps the VACUUM process. Tucked away in the contrib extensions, however, is a tool that exposes what really sits on disk: the pg_visibility extension. By querying it, you see how PostgreSQL tracks visibility and freezing at the page level\u2014information that directly affects vacuum efficiency and index-only scans.","og_url":"https:\/\/www.sqltabletalk.com\/?p=1135","og_site_name":"SQL Table Talk","article_published_time":"2025-07-11T14:00:00+00:00","author":"Ian Parker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Ian Parker","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqltabletalk.com\/?p=1135#article","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/?p=1135"},"author":{"name":"Ian Parker","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/544aba85507b8637f247298cc1e62212"},"headline":"PostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage","datePublished":"2025-07-11T14:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqltabletalk.com\/?p=1135"},"wordCount":517,"commentCount":0,"publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"keywords":["autovacuum","Database Performance","heap pages","index-only scan","pg_visibility","PostgreSQL","storage internals","table bloat","vacuum","visibility map"],"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqltabletalk.com\/?p=1135#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqltabletalk.com\/?p=1135","url":"https:\/\/www.sqltabletalk.com\/?p=1135","name":"PostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage - SQL Table Talk","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/#website"},"datePublished":"2025-07-11T14:00:00+00:00","breadcrumb":{"@id":"https:\/\/www.sqltabletalk.com\/?p=1135#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltabletalk.com\/?p=1135"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltabletalk.com\/?p=1135#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltabletalk.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL\u2019s pg_visibility: Peeking Under the Hood of Table Storage"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltabletalk.com\/#website","url":"https:\/\/www.sqltabletalk.com\/","name":"SQL Table Talk","description":"Breaking Down SQL Server, One Post at a Time.","publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltabletalk.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0","name":"Stephen Planck","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","caption":"Stephen Planck"},"logo":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/dexterwiki.com","https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI","https:\/\/www.youtube.com\/linuxmate"]},{"@type":"Person","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/544aba85507b8637f247298cc1e62212","name":"Ian Parker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/fbee6e7140969d91bc7fac815743ba8299aca3eefe26a0c2771d886f210e5865?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/fbee6e7140969d91bc7fac815743ba8299aca3eefe26a0c2771d886f210e5865?s=96&d=mm&r=g","caption":"Ian Parker"},"url":"https:\/\/www.sqltabletalk.com\/?author=18"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/1135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/users\/18"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1135"}],"version-history":[{"count":2,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/1135\/revisions"}],"predecessor-version":[{"id":1145,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/1135\/revisions\/1145"}],"wp:attachment":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}