PostgreSQL: Load table into Buffer Cache, Increase the Speed of Accessing data

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a demonstration on how to load a table data into the Buffer Cache of PostgreSQL?

If your table available in the Buffer Cache, you can reduce the cost of DISK I/O. The similar feature of Memory Engine or Database In-Memory concept.

But this is not always good, because compare to DISK we have always the limited size of Memory and memory is also require of OS. But in some special cases, we can load frequently used table into Buffer Cache of PostgreSQL.

Before loading any table into the Buffer Cache, you must check the size of the table and the available size of Buffer Cache.

Using pg_prewarm module we can do this. It provides a convenient way to load relation data into either the operating system buffer cache or the PostgreSQL buffer cache.

Once you perform the below test, next question would be “How to check the status of Buffer Cache for each table?”
Please visit tomorrow’s article for this solution…

PostgreSQL: Script to check the status of Shared Buffer (Use pg_buffercache)

Please check the below demonstration:

Create two sample tables:

Create pg_prewarm extension:

Now, prefatch the tbl_ItemTransactions_1 table using pg_prewarm:

Check the execution plan of tbl_ItemTransactions_1:
The execution plan of prefatched table is quite faster than normal table.

Check the execution plan of tbl_ItemTransactions_2: