{"id":432,"date":"2024-08-01T15:44:17","date_gmt":"2024-08-01T15:44:17","guid":{"rendered":"https:\/\/humtechno.com\/intro\/?p=432"},"modified":"2024-08-01T15:51:38","modified_gmt":"2024-08-01T15:51:38","slug":"postgresql-table-maintenance","status":"publish","type":"post","link":"https:\/\/humtechno.com\/intro\/2024\/08\/01\/postgresql-table-maintenance\/","title":{"rendered":"PostgreSQL Table Maintenance"},"content":{"rendered":"\n\n\n\n\n<p>A PostgreSQL table needs to be maintained in order for the database to continue being reliable, secure, and consistent.<\/p>\n\n\n\n<p><strong>Video About PostgreSQL Maintenance<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe title=\"Lecture 11 |PostgreSQL Maintenance Part 4 - Wal Archive Files Maintenance PG-001 |PG-001 | DBA\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/upkIVP2Q79A?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<p><strong>Check Size of Table and Index<\/strong><\/p>\n\n\n\n<p>SELECT pg_size_pretty(pg_total_relation_size(&#8216;my_table&#8217;)) AS total_size, pg_size_pretty(pg_relation_size(&#8216;my_table&#8217;)) AS data_size;<br>SELECT pg_size_pretty(pg_relation_size(&#8216;idx_name&#8217;)) AS index_size;<\/p>\n\n\n\n<p><strong>Vacuum all Tables of Database <\/strong><\/p>\n\n\n\n<p>VACUUM;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img fetchpriority=\"high\" decoding=\"async\" width=\"661\" height=\"153\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image.png\" alt=\"\" class=\"wp-image-433\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image.png 661w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-300x69.png 300w\" sizes=\"(max-width: 661px) 100vw, 661px\" \/><\/figure>\n\n\n\n<p><strong>Vacuum all Tables of Database and Show progress on Screen<\/strong><\/p>\n\n\n\n<p>VACUUM verbose;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"660\" height=\"184\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-1.png\" alt=\"\" class=\"wp-image-434\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-1.png 660w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-1-300x84.png 300w\" sizes=\"(max-width: 660px) 100vw, 660px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"660\" height=\"269\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-2.png\" alt=\"\" class=\"wp-image-435\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-2.png 660w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-2-300x122.png 300w\" sizes=\"(max-width: 660px) 100vw, 660px\" \/><\/figure>\n\n\n\n<p><strong>Vacuum all Tables of Database and its lock the objects till processing<\/strong><\/p>\n\n\n\n<p>VACUUM FULL;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"660\" height=\"156\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-4.png\" alt=\"\" class=\"wp-image-437\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-4.png 660w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-4-300x71.png 300w\" sizes=\"(max-width: 660px) 100vw, 660px\" \/><\/figure>\n\n\n\n<p><strong>Vacuum all Tables of Database which Show progress on screen but its lock the objects till process<\/strong><\/p>\n\n\n\n<p>VACUUM FULL verbose; <\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"657\" height=\"137\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-5.png\" alt=\"\" class=\"wp-image-438\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-5.png 657w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-5-300x63.png 300w\" sizes=\"(max-width: 657px) 100vw, 657px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"665\" height=\"336\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-6.png\" alt=\"\" class=\"wp-image-439\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-6.png 665w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-6-300x152.png 300w\" sizes=\"(max-width: 665px) 100vw, 665px\" \/><\/figure>\n\n\n\n<p><strong>Vacuum Table ( For Release Dead Tuples)<\/strong><\/p>\n\n\n\n<p>vacuum employee;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"661\" height=\"314\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-3.png\" alt=\"\" class=\"wp-image-436\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-3.png 661w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-3-300x143.png 300w\" sizes=\"(max-width: 661px) 100vw, 661px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"654\" height=\"402\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-7.png\" alt=\"\" class=\"wp-image-440\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-7.png 654w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-7-300x184.png 300w\" sizes=\"(max-width: 654px) 100vw, 654px\" \/><\/figure>\n\n\n\n<p><strong>Vacuum Specific Table of Database and Show progress on Screen<\/strong><\/p>\n\n\n\n<p>vacuum verbose employee;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"660\" height=\"417\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-8.png\" alt=\"\" class=\"wp-image-441\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-8.png 660w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-8-300x190.png 300w\" sizes=\"(max-width: 660px) 100vw, 660px\" \/><\/figure>\n\n\n\n<p><strong>Vacuum Specific Table of Database which show progress on Screen and locks the table<\/strong><\/p>\n\n\n\n<p>vacuum full verbose employee;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"665\" height=\"417\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-10.png\" alt=\"\" class=\"wp-image-443\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-10.png 665w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-10-300x188.png 300w\" sizes=\"(max-width: 665px) 100vw, 665px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"663\" height=\"310\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-11.png\" alt=\"\" class=\"wp-image-444\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-11.png 663w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-11-300x140.png 300w\" sizes=\"(max-width: 663px) 100vw, 663px\" \/><\/figure>\n\n\n\n<p><strong>Vacuum and Analyse Table which also show progress on Screen<\/strong><\/p>\n\n\n\n<p>vacuum analyze verbose employee;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"664\" height=\"565\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-12.png\" alt=\"\" class=\"wp-image-446\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-12.png 664w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-12-300x255.png 300w\" sizes=\"(max-width: 664px) 100vw, 664px\" \/><\/figure>\n\n\n\n<p><strong>Analyze All Objects of Database<\/strong><\/p>\n\n\n\n<p>Analyze;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"667\" height=\"172\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-13.png\" alt=\"\" class=\"wp-image-447\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-13.png 667w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-13-300x77.png 300w\" sizes=\"(max-width: 667px) 100vw, 667px\" \/><\/figure>\n\n\n\n<p><strong>Analyze All Objects of Database which progress on screen<\/strong><\/p>\n\n\n\n<p>analyze verbose;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"657\" height=\"564\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-14.png\" alt=\"\" class=\"wp-image-448\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-14.png 657w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-14-300x258.png 300w\" sizes=\"(max-width: 657px) 100vw, 657px\" \/><\/figure>\n\n\n\n<p><strong>Analyze Table<\/strong><\/p>\n\n\n\n<p>ANALYZE my_table;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"655\" height=\"158\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-15.png\" alt=\"\" class=\"wp-image-449\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-15.png 655w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-15-300x72.png 300w\" sizes=\"(max-width: 655px) 100vw, 655px\" \/><\/figure>\n\n\n\n<p><strong>Analyse Table which also show progress on Screen<\/strong><\/p>\n\n\n\n<p>ANALYZE verbose my_table;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"643\" height=\"96\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-16.png\" alt=\"\" class=\"wp-image-450\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-16.png 643w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-16-300x45.png 300w\" sizes=\"(max-width: 643px) 100vw, 643px\" \/><\/figure>\n\n\n\n<p><strong>Check Index of Tables<\/strong><\/p>\n\n\n\n<p>SELECT tablename,indexname, indexdef FROM pg_indexes WHERE schemaname = &#8216;public&#8217; AND tablename = &#8217;employee&#8217;;<\/p>\n\n\n\n<p><strong>Re Index All index in Tables<\/strong><\/p>\n\n\n\n<p>REINDEX table employee;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"661\" height=\"183\" src=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-17.png\" alt=\"\" class=\"wp-image-451\" srcset=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-17.png 661w, https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/08\/image-17-300x83.png 300w\" sizes=\"(max-width: 661px) 100vw, 661px\" \/><\/figure>\n\n\n\n<p><strong> Re Index Table Index without Locking<\/strong><\/p>\n\n\n\n<p>REINDEX Table CONCURRENTLY employee;<\/p>\n\n\n\n<p><strong> Re Index Specific Index without Locking<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">REINDEX INDEX CONCURRENTLY my_index;<\/pre>\n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":2,"featured_media":427,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"pagelayer_contact_templates":[],"_pagelayer_content":"","footnotes":""},"categories":[38],"tags":[53,54],"class_list":["post-432","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-postgresql-maintenance","tag-table-maintenance"],"_links":{"self":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/432","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/comments?post=432"}],"version-history":[{"count":7,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/432\/revisions"}],"predecessor-version":[{"id":460,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/432\/revisions\/460"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/media\/427"}],"wp:attachment":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/media?parent=432"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/categories?post=432"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/tags?post=432"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}