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 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.
Adding parameter in postgresql.conf file
# 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.”
Creating extension in postgres using below command:
| 1 2 | dvdrental=# create extension pg_prewarm; CREATE EXTENSION |
Adding table “address” in OS cache using prefetch option
| 1 2 3 4 5 | dvdrental=# SELECT pg_prewarm('address', 'prefetch'); pg_prewarm ------------ 8 (1 row) |
| 1 2 3 4 5 | dvdrental=# SELECT relfilenode FROM pg_class WHERE relname = 'address'; relfilenode ------------- 16544 (1 row) |
Copy the table to both OS cache and buffer cache
| 1 2 3 4 5 | dvdrental=# SELECT pg_prewarm('address', 'read'); pg_prewarm ------------ 8 (1 row) |
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.
| 1 2 3 4 5 6 7 8 9 10 11 12 | 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.
