Studying hot and cold cache behavior

Database cache plays a vital role in the query performance. In this recipe, we will be discussing the hot and cold cache impact on the submitted SQL.

Getting ready

Cold cache is the behavior when the submitted SQL does not find its required data in memory, which leads to process to read the data from the disk into the memory. Hot cache is vice versa to the cold cache. If the SQL's required data is found in the memory, then it is a hot cache behavior, which will avoid the disk read operations, and also improves the query response time. The query performance is directly proportional to the amount of data we have in the memory. That is, if we can make less I/O operations then performance will improve automatically.

How to do it…

PostgreSQL provides a few extensions such as pg_prewarm, pg_buffercache, which will help us to deal with cache. So, let's create these two extensions as follows:

benchmarksql=# CREATE EXTENSION pg_buffercache ; 
CREATE EXTENSION...