{"id":2790,"date":"2025-01-22T16:47:23","date_gmt":"2025-01-22T16:47:23","guid":{"rendered":"https:\/\/bynatree.com\/?p=2790"},"modified":"2025-01-22T16:47:23","modified_gmt":"2025-01-22T16:47:23","slug":"optimizing-postgresql-performance-with-pg_prewarm","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/01\/22\/optimizing-postgresql-performance-with-pg_prewarm\/","title":{"rendered":"Optimizing PostgreSQL Performance with pg_prewarm"},"content":{"rendered":"<blockquote>\n<h2>In this blog post, I will showcase a demonstration of PostgreSQL performance with pg_prewarm in PostgreSQL 16.1<\/h2>\n<\/blockquote>\n<p>PostgreSQL is a powerful open-source database that comes with several extensions to enhance its functionality. One such extension is pg_prewarm, which is particularly useful for optimizing PostgreSQL performance with pg_prewarm after a restart by preloading relation data into shared buffers.<\/p>\n<p><span style=\"font-weight: 400;\">When a PostgreSQL database service restarts, the shared buffers are cleared, forcing queries to read data from disk into memory, which can significantly slow down query execution. This performance drop is more pronounced in systems with large shared buffer configurations, such as those spanning hundreds of gigabytes. By using <\/span><span style=\"font-weight: 400;\">pg_prewarm<\/span><span style=\"font-weight: 400;\">, you can preload the data into shared buffers, ensuring consistent query performance even immediately after a restart.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The pg_prewarm module allows you to load related data into either the operating system buffer cache or the PostgreSQL buffer cache. Prewarming may be done manually with the pg_prewarm function, or automatically by adding it in shared_preload_libraries. In the latter instance, the system will run a background worker that regularly records the contents of shared buffers in a file named autoprewarm.blocks and then reloads those blocks after a restart using two background workers.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">In the preceding example, &#8220;prefetch&#8221; is the prewarm type. You may also select &#8220;read&#8221; or &#8220;buffer&#8221; according on your needs:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">&#8220;Prefetch&#8221;: Simply load the blocks into the OS cache.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">&#8220;read&#8221; loads the blocks into both the OS cache and PostgreSQL&#8217;s buffer cache.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">&#8220;buffer&#8221; loads the blocks solely into PostgreSQL&#8217;s buffer cache.<\/span><\/li>\n<\/ul>\n<h2><span style=\"font-weight: 400;\">Adding parameter in postgresql.conf file<\/span><\/h2>\n<p><span style=\"font-weight: 400;\"># postgresql.conf<\/span><\/p>\n<p><span style=\"font-weight: 400;\">shared_preload_libraries = &#8216;pg_prewarm&#8217;<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Changing parameters required restart.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">\u201cIn this blog post, I will showcase a demonstration of PostgreSQL Performance with pg_prewarm\u00a0 in PostgreSQL 16.1.\u201d<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Creating extension in postgres using below command:<\/span><\/p>\n<pre class=\"theme:solarized-dark lang:pgsql decode:true\">dvdrental=# create extension pg_prewarm;\nCREATE EXTENSION<\/pre>\n<p><span style=\"font-weight: 400;\">Adding table \u201caddress\u201d in OS cache using prefetch option<\/span><\/p>\n<pre class=\"theme:solarized-dark lang:pgsql decode:true \">dvdrental=# SELECT pg_prewarm('address', 'prefetch');\n pg_prewarm\n------------\n          8\n(1 row)<\/pre>\n<pre class=\"theme:solarized-dark lang:pgsql decode:true \">dvdrental=# SELECT relfilenode FROM pg_class WHERE relname = 'address';\n relfilenode\n-------------\n       16544\n(1 row)<\/pre>\n<p><span style=\"font-weight: 400;\">Copy the table to both OS cache and buffer cache<\/span><\/p>\n<pre class=\"theme:solarized-dark lang:pgsql decode:true \">dvdrental=# SELECT pg_prewarm('address', 'read');\n pg_prewarm\n------------\n          8\n(1 row)<\/pre>\n<p><span style=\"font-weight: 400;\">Verify if the table pages are loaded into the buffer cache using the pg_buffercache extension. pg_buffercache extension is used to read the contents of the shared buffers.<\/span><\/p>\n<pre class=\"theme:solarized-dark lang:pgsql decode:true \">dvdrental=# SELECT * FROM pg_buffercache WHERE relfilenode = 16544;\n bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usagecount | pinning_backends\n----------+-------------+---------------+-------------+---------------+----------------+---------+------------+------------------\n      697 |       16544 |          1663 |       16473 |             0 |              0 | f       |          1 |                0\n      698 |       16544 |          1663 |       16473 |             0 |              1 | f       |          1 |                0\n      699 |       16544 |          1663 |       16473 |             0 |              2 | f       |          1 |                0\n      700 |       16544 |          1663 |       16473 |             0 |              3 | f       |          1 |                0\n      701 |       16544 |          1663 |       16473 |             0 |              4 | f       |          1 |                0\n      702 |       16544 |          1663 |       16473 |             0 |              5 | f       |          1 |                0\n      703 |       16544 |          1663 |       16473 |             0 |              6 | f       |          1 |                0\n      704 |       16544 |          1663 |       16473 |             0 |              7 | f       |          1 |                0\n(8 rows)<\/pre>\n<p>In conclusion, pg_prewarm is an invaluable extension for PostgreSQL, enhancing database performance by preloading relation data into shared buffers after a restart. By incorporating this tool, you can ensure your database operates efficiently and maintains optimal performance levels. Explore the benefits of pg_prewarm and take your PostgreSQL performance to the next level.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog post, I will showcase a demonstration of PostgreSQL performance with pg_prewarm in PostgreSQL 16.1 PostgreSQL is a powerful open-source database that comes with several extensions to enhance its functionality. One such extension is pg_prewarm, which is particularly useful for optimizing PostgreSQL performance with pg_prewarm after a restart by preloading relation data into&hellip;<\/p>\n","protected":false},"author":1,"featured_media":2781,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[112,116,231,239,247,266,274,286,318,320],"class_list":["post-2790","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-database-management","tag-database-performance","tag-open-source-database","tag-performance-optimization","tag-pg_prewarm","tag-postgresql","tag-postgresql-extensions","tag-postgresql-tools","tag-shared-buffers","tag-sql-performance","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2790","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2790"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2790\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2781"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2790"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2790"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2790"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}