In this recipe, we will be discussing how to clean the cache from databases at operating system level.
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.
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)
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 ...