Clearing the cache

In this recipe, we will be discussing how to clean the cache from databases at operating system level.

Getting ready

In PostgreSQL, we do not have any predefined functionality to clear the cache from the memory. To clear the database level cache, we need to shut down the whole instance and to clear the operating system cache, we need to use the operating system utility commands.

Tip

Do not run any of the following operations in any production servers as it will cause production outage.

How to do it…

1. Let's load some sample data into the database cache by using the pg_prewarm function:

   benchmarksql=# SELECT pg_prewarm('bmsql_customer', 'buffer');
    pg_prewarm
   ------------
         25211
   (1 row)
   

2. Let's validate whether we are able to hit the buffers, which are loaded using the pg_prewarm function:

   benchmarksql=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM
             bmsql_customer;
                                                          QUERY PLAN                                   ...