{"id":1662,"date":"2018-08-14T09:11:42","date_gmt":"2018-08-14T09:11:42","guid":{"rendered":"http:\/\/dbtut.com\/?p=1662"},"modified":"2018-11-09T23:00:34","modified_gmt":"2018-11-09T23:00:34","slug":"pg-db-optimization","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/","title":{"rendered":"PG DB Optimization"},"content":{"rendered":"<p><span style=\"text-decoration: underline;\"><strong>Overview<\/strong><\/span><\/p>\n<p>This article briefs us on how to tune a database or make it work faster<\/p>\n<ul>\n<li><strong>Max connections:<\/strong> We can configure the maximum number of client connections and this is very important because every connection requires memory and other resources. PostgreSQL can handle some hundred of connections, but if we are planning for thousands of connections, we should use some connection pooling mechanism to reduce overhead of connections.<\/li>\n<li><strong>Shared buffers:\u00a0<\/strong>Instead of big shared memory pool,\u00a0PostgreSQL has a separate process for\u00a0each database connection. PostgreSQL\u00a0uses this dedicated memory area for\u00a0caching purpose, so its provide lots of\u00a0performance gain in your system.\u00a0If we have more loads, we can set large values for shared_buffers.<br \/>\nGenerally, we should set shared_buffers\u00a0values up to 1\/4 part of the main\u00a0memory.\u00a0You can calculate using, below formula\u00a0(0.25 * Main Memory)<\/li>\n<li><strong>Wal buffers:\u00a0<\/strong>This parameter defines, how much space\u00a0is required for caching to write-ahead log\u00a0entries. This is really very small size value\u00a0but it is required to change in heavily\u00a0loaded servers. Recommended value is: 8MB.<\/li>\n<li><strong>Work mem<\/strong>:\u00a0This is also one of the most important parameter which is\u00a0used to set a private work space for each and every new\u00a0connection.<br \/>\nWe have shared_buffers which we are using as the\u00a0dedicated buffer for all connections, but using\u00a0work_mem we can set memory for each query.If we have configured wrongly, it really creates a big\u00a0problem for us because if we have a big, complex query\u00a0and which is running for multiple times then it will create\u00a0a big problem for us.We should configure this parameter base on the number\u00a0of connections and what type of queries we are running\u00a0on the server.We can calculate using this method,\u00a0Memory(MB) \/ Max_connections.\u00a0If the result is very near to Max_connections, perform the\u00a0divide by 2 again.<\/p>\n<p>The result should be at least 16MB otherwise we should\u00a0buy more RAM.<\/li>\n<li><strong>maintenance_work_mem:\u00a0<\/strong>This is also same as like work_mem\u00a0parameter, but use only for maintenance\u00a0related queries like, VACUUM, CLUSTER.<br \/>\nGenerally, we are doing maintenance\u00a0when query load is not too high.\u00a0I suggest you set a big value for\u00a0parameter so that we can quickly<br \/>\ncomplete our maintenance task.\u00a0Default is 16MB and Recommended is\u00a0(Memory \/ 8).<\/li>\n<li><strong>effective_cache_size:\u00a0<\/strong>This parameter is mostly dedicated to\u00a0PostgreSQL query planner because query\u00a0planner is responsible to execute a query<br \/>\nby choosing a better optimized execution\u00a0path.\u00a0The query planner also requires some\u00a0space to perform their work so this\u00a0parameter helps the query<br \/>\nplanner.Default is 65536 and\u00a0Recommended is (Memory (MB) * 0.75)<\/li>\n<li><strong>Autovacuum:\u00a0<\/strong>This is also one of the important\u00a0parameter because PostgreSQL support\u00a0MVCC nature. This is really very required\u00a0and it solves lots of performance related\u00a0issues by removing dead tuples.Whenever\u00a0table is free, Autovacuum performs\u00a0vacuuming on that table so another\u00a0individual Vacuum executes fast because\u00a0it has very less to remove.<\/li>\n<li><strong>synchronous_commit:\u00a0<\/strong>You can set number of transactions to commit\u00a0per second. e.g. set 100 transaction means per\u00a0second, 100 transactions is going to commit.\u00a0This parameter should be ON or OFF is up to\u00a0our choice because when we put OFF, our bulk\u00a0insertion is faster, but there is the chance of<br \/>\nfailure and when we put ON, bulk insertion\u00a0may slow down but the chance of data failure\u00a0is also very less.This is also one of the\u00a0important parameter because PostgreSQL\u00a0support MVCC nature. This is really very\u00a0required and it solves lots of performance\u00a0related issues by removing dead\u00a0tuples.Whenever table is\u00a0free, Autovacuum performs vacuuming on that\u00a0table so another individual Vacuum executes\u00a0fast because it has very less to remove.<\/li>\n<\/ul>\n\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_1662\" class=\"pvc_stats all  \" data-element-id=\"1662\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Overview This article briefs us on how to tune a database or make it work faster Max connections: We can configure the maximum number of client connections and this is very important because every connection requires memory and other resources. PostgreSQL can handle some hundred of connections, but if we are planning for thousands of &hellip;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_1662\" class=\"pvc_stats all  \" data-element-id=\"1662\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":109,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-1662","post","type-post","status-publish","format-standard","","category-postgres"],"aioseo_notices":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>PG DB Optimization - Database Tutorials<\/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:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PG DB Optimization - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Overview This article briefs us on how to tune a database or make it work faster Max connections: We can configure the maximum number of client connections and this is very important because every connection requires memory and other resources. PostgreSQL can handle some hundred of connections, but if we are planning for thousands of &hellip;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-14T09:11:42+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-11-09T23:00:34+00:00\" \/>\n<meta name=\"author\" content=\"Vaibhav Krishna\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vaibhav Krishna\" \/>\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:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/\"},\"author\":{\"name\":\"Vaibhav Krishna\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/6c10f95f66ae2b4026552b02018b18b7\"},\"headline\":\"PG DB Optimization\",\"datePublished\":\"2018-08-14T09:11:42+00:00\",\"dateModified\":\"2018-11-09T23:00:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/\"},\"wordCount\":622,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/\",\"name\":\"PG DB Optimization - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2018-08-14T09:11:42+00:00\",\"dateModified\":\"2018-11-09T23:00:34+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PG DB Optimization\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/6c10f95f66ae2b4026552b02018b18b7\",\"name\":\"Vaibhav Krishna\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/482ae666004473e37e849efeedbf7111152f716f6e9fc6852074d49536796697?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/482ae666004473e37e849efeedbf7111152f716f6e9fc6852074d49536796697?s=96&d=mm&r=g\",\"caption\":\"Vaibhav Krishna\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/vaibhavkrishna\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PG DB Optimization - Database Tutorials","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:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/","og_locale":"en_US","og_type":"article","og_title":"PG DB Optimization - Database Tutorials","og_description":"Overview This article briefs us on how to tune a database or make it work faster Max connections: We can configure the maximum number of client connections and this is very important because every connection requires memory and other resources. PostgreSQL can handle some hundred of connections, but if we are planning for thousands of &hellip;","og_url":"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/","og_site_name":"Database Tutorials","article_published_time":"2018-08-14T09:11:42+00:00","article_modified_time":"2018-11-09T23:00:34+00:00","author":"Vaibhav Krishna","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Vaibhav Krishna","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/"},"author":{"name":"Vaibhav Krishna","@id":"https:\/\/dbtut.com\/#\/schema\/person\/6c10f95f66ae2b4026552b02018b18b7"},"headline":"PG DB Optimization","datePublished":"2018-08-14T09:11:42+00:00","dateModified":"2018-11-09T23:00:34+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/"},"wordCount":622,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/","url":"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/","name":"PG DB Optimization - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2018-08-14T09:11:42+00:00","dateModified":"2018-11-09T23:00:34+00:00","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-db-optimization\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"PG DB Optimization"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/6c10f95f66ae2b4026552b02018b18b7","name":"Vaibhav Krishna","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/482ae666004473e37e849efeedbf7111152f716f6e9fc6852074d49536796697?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/482ae666004473e37e849efeedbf7111152f716f6e9fc6852074d49536796697?s=96&d=mm&r=g","caption":"Vaibhav Krishna"},"url":"https:\/\/dbtut.com\/index.php\/author\/vaibhavkrishna\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/1662","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/109"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=1662"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/1662\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=1662"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=1662"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=1662"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}