PostgreSQL Performance with pg_prewarm helps reduce query latency after database restarts by preloading frequently accessed tables into the operating system cache and PostgreSQL shared buffers. The pg_prewarm extension improves database performance by minimizing disk I/O and ensuring critical data is readily available in memory. This guide demonstrates how to configure and use pg_prewarm in PostgreSQL 16.1 to achieve faster query execution and consistent database performance.
In this blog post, I will showcase a demonstration of PostgreSQL performance with pg_prewarm in PostgreSQL 16.1
What is PostgreSQL Performance with pg_prewarm?
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.
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 pg_prewarm, you can preload the data into shared buffers, ensuring consistent query performance even immediately after a restart.
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.
In the preceding example, “prefetch” is the prewarm type. You may also select “read” or “buffer” according on your needs:
- “Prefetch“: Simply load the blocks into the OS cache.
- “read” loads the blocks into both the OS cache and PostgreSQL’s buffer cache.
- “buffer” loads the blocks solely into PostgreSQL’s buffer cache.
Configure pg_prewarm in PostgreSQL
# postgresql.conf
shared_preload_libraries = 'pg_prewarm'
- Changing parameters required restart.
“In this blog post, I will showcase a demonstration of PostgreSQL Performance with pg_prewarm in PostgreSQL 16.1.”
Install the pg_prewarm Extension
dvdrental=# create extension pg_prewarm;
CREATE EXTENSIONPreload Tables Using pg_prewarm
dvdrental=#
SELECT pg_prewarm('address', 'prefetch');
pg_prewarm
------------
8
(1 row)
dvdrental=# SELECT relfilenode FROM pg_class WHERE relname = 'address';
relfilenode
-------------
16544
(1 row)Load Data into PostgreSQL Buffer Cache
dvdrental=# SELECT pg_prewarm('address', 'read');
pg_prewarm
------------
8
(1 row)Verify pg_prewarm Using pg_buffercache
Verify whether the specific table pages are successfully loaded and actively residing within the PostgreSQL buffer cache by utilizing the pg_buffercache extension. This extension provides a detailed mechanism to inspect, read, and analyze the current contents of the shared buffers, thereby allowing administrators to confirm caching behavior, monitor performance, and troubleshoot potential inefficiencies in memory usage.
dvdrental=# SELECT * FROM pg_buffercache WHERE relfilenode = 16544;
bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usagecount | pinning_backends
----------+-------------+---------------+-------------+---------------+----------------+---------+------------+------------------
697 | 16544 | 1663 | 16473 | 0 | 0 | f | 1 | 0
698 | 16544 | 1663 | 16473 | 0 | 1 | f | 1 | 0
699 | 16544 | 1663 | 16473 | 0 | 2 | f | 1 | 0
700 | 16544 | 1663 | 16473 | 0 | 3 | f | 1 | 0
701 | 16544 | 1663 | 16473 | 0 | 4 | f | 1 | 0
702 | 16544 | 1663 | 16473 | 0 | 5 | f | 1 | 0
703 | 16544 | 1663 | 16473 | 0 | 6 | f | 1 | 0
704 | 16544 | 1663 | 16473 | 0 | 7 | f | 1 | 0
(8 rows)
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.
Contact us: https://bynatree.com/contact/
