{"id":11290,"date":"2023-10-11T10:31:00","date_gmt":"2023-10-11T15:31:00","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=11290"},"modified":"2024-12-11T10:34:27","modified_gmt":"2024-12-11T15:34:27","slug":"postgresql-vacuum-and-analyze-stats-for-tables","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=11290","title":{"rendered":"PostgreSQL &#8211; Vacuum and Analyze Stats for Tables"},"content":{"rendered":"\n<p>This query retrieves information about the health of PostgreSQL tables<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT\n    schemaname,\n    relname AS table_name,\n    last_vacuum,\n    last_autovacuum,\n    last_analyze,\n    last_autoanalyze,\n    n_live_tup AS live_tuples,\n    n_dead_tup AS dead_tuples,\n    vacuum_count,\n    autovacuum_count\nFROM \n    pg_stat_all_tables\nWHERE \n    schemaname NOT IN (&#039;pg_catalog&#039;, &#039;information_schema&#039;)\n    and n_dead_tup  &gt; 0\nORDER BY \n    n_dead_tup DESC;    \n<\/pre><\/div>\n\n\n<p>Voila:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/12\/pg_vacuum_query.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"301\" src=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/12\/pg_vacuum_query-1024x301.png\" alt=\"\" class=\"wp-image-11291\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/12\/pg_vacuum_query-1024x301.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/12\/pg_vacuum_query-300x88.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/12\/pg_vacuum_query-768x226.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/12\/pg_vacuum_query-1536x451.png 1536w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/12\/pg_vacuum_query-750x220.png 750w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/12\/pg_vacuum_query.png 1565w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>This query retrieves information about the health of PostgreSQL tables Voila:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1522],"tags":[1349],"class_list":["post-11290","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/11290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11290"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/11290\/revisions"}],"predecessor-version":[{"id":11292,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/11290\/revisions\/11292"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11290"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}