{"id":266,"date":"2018-02-05T10:53:45","date_gmt":"2018-02-05T10:53:45","guid":{"rendered":"https:\/\/blinksql.com\/?p=266"},"modified":"2018-02-05T10:53:45","modified_gmt":"2018-02-05T10:53:45","slug":"monitoring-in-postgresql","status":"publish","type":"post","link":"https:\/\/blinksql.com\/?p=266","title":{"rendered":"Monitoring in Postgresql"},"content":{"rendered":"

In normal\u00a0PostgreSQL<\/span>\u00a0operation, tuples that are deleted or obsoleted by an update are not physically removed from their table.<\/p>\n

They remain present until a\u00a0VACUUM<\/tt>\u00a0is done. Therefore it’s necessary to do\u00a0VACUUM<\/tt>\u00a0periodically, especially on frequently-updated tables.<\/p>\n

VACUUM :<\/strong><\/div>\n
-> Garbage-collect and optionally analyze a database.<\/div>\n
-> Reclaims space and makes it available for re-use for that alone not to the operating system.<\/div>\n
-> Its fast compared to Vacuum Full.<\/div>\n
VACUUM ANALYZE FULL:\u00a0<\/tt><\/strong><\/div>\n
-> performs a\u00a0VACUUM<\/tt>\u00a0and then an\u00a0ANALYZE<\/tt>\u00a0for each selected table.<\/div>\n
-> Reclaim more space, but takes much longer and exclusively locks the table.<\/div>\n
VERBOSE:<\/tt><\/strong>-> Prints a detailed vacuum activity report for each table.<\/p>\n
\n

-> When it\u00a0<\/span>is specified,\u00a0VACUUM<\/tt>\u00a0emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.<\/p>\n<\/div>\n

<\/div>\n

ANALYZE:<\/tt><\/strong><\/p>\n

Updates statistics used by the planner to determine the most efficient way to execute a query.<\/p>\n

Eg: Vacuum (verbose,analyze) temp;<\/strong><\/p>\n<\/div>\n

Note:<\/strong><\/p>\n

1. PostgreSQL<\/span>\u00a0includes an\u00a0“autovacuum”<\/span>\u00a0facility which can automate routine vacuum maintenance<\/p>\n

2. If we are not specifying any table name then all the tables in the current database get vacuumed.<\/p>\n

VACUUM Progress Reporting:<\/strong><\/p>\n

Whenever Vacuum\u00a0<\/strong> is running, the pg_stat_progress_vacuum<\/strong> view will contain one row for each backend (including autovacuum worker processes) that is currently vacuuming.<\/p>\n

Wait monitoring:<\/strong><\/p>\n

-> Previously DBA’s use pg_stat_activity to provide information about what’s currently happening on the database.<\/p>\n

-> This returns information about each connection in the cluster, including the queries being run, which users are connected to which database, when the connection was made, whether it’s active or idle, and various other bits of information.<\/p>\n

2 new columns:<\/strong><\/p>\n

wait_event_type:<\/strong>Lock<\/p>\n

wait_event:<\/strong>tuple\/Write-Ahead Log<\/p>\n

-> By using these columns we can determine on which the connection\u00a0 is waiting for.<\/p>\n

 <\/p>\n

\n

 <\/p>\n<\/div>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

In normal\u00a0PostgreSQL\u00a0operation, tuples that are deleted or obsoleted by an update are not physically removed from their table. They remain present until a\u00a0VACUUM\u00a0is done. Therefore it’s necessary to do\u00a0VACUUM\u00a0periodically, especially…<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":""},"categories":[1],"tags":[],"yoast_head":"\nMonitoring in Postgresql - Blink SQL<\/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:\/\/blinksql.com\/?p=266\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Monitoring in Postgresql - Blink SQL\" \/>\n<meta property=\"og:description\" content=\"In normal\u00a0PostgreSQL\u00a0operation, tuples that are deleted or obsoleted by an update are not physically removed from their table. They remain present until a\u00a0VACUUM\u00a0is done. Therefore it’s necessary to do\u00a0VACUUM\u00a0periodically, especially…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blinksql.com\/?p=266\" \/>\n<meta property=\"og:site_name\" content=\"Blink SQL\" \/>\n<meta property=\"article:published_time\" content=\"2018-02-05T10:53:45+00:00\" \/>\n<meta name=\"author\" content=\"Hannie\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@harika_sanaka\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Hannie\" \/>\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:\/\/blinksql.com\/?p=266#article\",\"isPartOf\":{\"@id\":\"https:\/\/blinksql.com\/?p=266\"},\"author\":{\"name\":\"Hannie\",\"@id\":\"https:\/\/blinksql.com\/#\/schema\/person\/f04b99304f59e14956e768e764d2afd4\"},\"headline\":\"Monitoring in Postgresql\",\"datePublished\":\"2018-02-05T10:53:45+00:00\",\"dateModified\":\"2018-02-05T10:53:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blinksql.com\/?p=266\"},\"wordCount\":314,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blinksql.com\/#organization\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blinksql.com\/?p=266#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blinksql.com\/?p=266\",\"url\":\"https:\/\/blinksql.com\/?p=266\",\"name\":\"Monitoring in Postgresql - Blink SQL\",\"isPartOf\":{\"@id\":\"https:\/\/blinksql.com\/#website\"},\"datePublished\":\"2018-02-05T10:53:45+00:00\",\"dateModified\":\"2018-02-05T10:53:45+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blinksql.com\/?p=266#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blinksql.com\/?p=266\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blinksql.com\/?p=266#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/blinksql.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Monitoring in Postgresql\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blinksql.com\/#website\",\"url\":\"https:\/\/blinksql.com\/\",\"name\":\"Blink SQL\",\"description\":\" Thinking SQL\",\"publisher\":{\"@id\":\"https:\/\/blinksql.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blinksql.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blinksql.com\/#organization\",\"name\":\"Blink SQL\",\"url\":\"https:\/\/blinksql.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/blinksql.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blinksql.com\/wp-content\/uploads\/2022\/11\/cropped-BlinkSQL-removebg.png\",\"contentUrl\":\"https:\/\/blinksql.com\/wp-content\/uploads\/2022\/11\/cropped-BlinkSQL-removebg.png\",\"width\":280,\"height\":172,\"caption\":\"Blink SQL\"},\"image\":{\"@id\":\"https:\/\/blinksql.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/blinksql.com\/#\/schema\/person\/f04b99304f59e14956e768e764d2afd4\",\"name\":\"Hannie\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/blinksql.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c5876b3497d2c2eb98256b7c183220c1?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c5876b3497d2c2eb98256b7c183220c1?s=96&d=mm&r=g\",\"caption\":\"Hannie\"},\"sameAs\":[\"http:\/\/blinksql.com\",\"https:\/\/twitter.com\/harika_sanaka\"],\"url\":\"https:\/\/blinksql.com\/?author=2\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Monitoring in Postgresql - Blink SQL","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:\/\/blinksql.com\/?p=266","og_locale":"en_US","og_type":"article","og_title":"Monitoring in Postgresql - Blink SQL","og_description":"In normal\u00a0PostgreSQL\u00a0operation, tuples that are deleted or obsoleted by an update are not physically removed from their table. They remain present until a\u00a0VACUUM\u00a0is done. Therefore it’s necessary to do\u00a0VACUUM\u00a0periodically, especially…","og_url":"https:\/\/blinksql.com\/?p=266","og_site_name":"Blink SQL","article_published_time":"2018-02-05T10:53:45+00:00","author":"Hannie","twitter_card":"summary_large_image","twitter_creator":"@harika_sanaka","twitter_misc":{"Written by":"Hannie","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blinksql.com\/?p=266#article","isPartOf":{"@id":"https:\/\/blinksql.com\/?p=266"},"author":{"name":"Hannie","@id":"https:\/\/blinksql.com\/#\/schema\/person\/f04b99304f59e14956e768e764d2afd4"},"headline":"Monitoring in Postgresql","datePublished":"2018-02-05T10:53:45+00:00","dateModified":"2018-02-05T10:53:45+00:00","mainEntityOfPage":{"@id":"https:\/\/blinksql.com\/?p=266"},"wordCount":314,"commentCount":0,"publisher":{"@id":"https:\/\/blinksql.com\/#organization"},"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blinksql.com\/?p=266#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blinksql.com\/?p=266","url":"https:\/\/blinksql.com\/?p=266","name":"Monitoring in Postgresql - Blink SQL","isPartOf":{"@id":"https:\/\/blinksql.com\/#website"},"datePublished":"2018-02-05T10:53:45+00:00","dateModified":"2018-02-05T10:53:45+00:00","breadcrumb":{"@id":"https:\/\/blinksql.com\/?p=266#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blinksql.com\/?p=266"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blinksql.com\/?p=266#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/blinksql.com\/"},{"@type":"ListItem","position":2,"name":"Monitoring in Postgresql"}]},{"@type":"WebSite","@id":"https:\/\/blinksql.com\/#website","url":"https:\/\/blinksql.com\/","name":"Blink SQL","description":" Thinking SQL","publisher":{"@id":"https:\/\/blinksql.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blinksql.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/blinksql.com\/#organization","name":"Blink SQL","url":"https:\/\/blinksql.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blinksql.com\/#\/schema\/logo\/image\/","url":"https:\/\/blinksql.com\/wp-content\/uploads\/2022\/11\/cropped-BlinkSQL-removebg.png","contentUrl":"https:\/\/blinksql.com\/wp-content\/uploads\/2022\/11\/cropped-BlinkSQL-removebg.png","width":280,"height":172,"caption":"Blink SQL"},"image":{"@id":"https:\/\/blinksql.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/blinksql.com\/#\/schema\/person\/f04b99304f59e14956e768e764d2afd4","name":"Hannie","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blinksql.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c5876b3497d2c2eb98256b7c183220c1?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c5876b3497d2c2eb98256b7c183220c1?s=96&d=mm&r=g","caption":"Hannie"},"sameAs":["http:\/\/blinksql.com","https:\/\/twitter.com\/harika_sanaka"],"url":"https:\/\/blinksql.com\/?author=2"}]}},"_links":{"self":[{"href":"https:\/\/blinksql.com\/index.php?rest_route=\/wp\/v2\/posts\/266"}],"collection":[{"href":"https:\/\/blinksql.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blinksql.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blinksql.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blinksql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=266"}],"version-history":[{"count":0,"href":"https:\/\/blinksql.com\/index.php?rest_route=\/wp\/v2\/posts\/266\/revisions"}],"wp:attachment":[{"href":"https:\/\/blinksql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=266"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blinksql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=266"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blinksql.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=266"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}