How to clear Postgresql database cache

14 November,2019 by Jack Vamvas

Question: In SQL Server there are two commands which clear down the data and query caches -  DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS.  Are there similar commands in PostgreSQL hosted on Linux?  I want to clear down datasets from memory cache to allow query  performance tuning.

Answer: If you'd like to view what is in the buffer cache -  pg_buffercache will present to you what is in the cache. Of course , pg_buffercache needs to be installed. It's a complicated data set and requires some knowledge to interpret.  Unfortunately - there isn't a straightforward way of clearing down the database cache and query plans - similar to SQL Server. 

I use a fairly basic method of Stopping postgresql , use the drop_caches module , start Postgresql.   The drop_caches Linux module only frees up objects which are completely unused. This is the reason I stop Postgresql , as it will free up the dirty objects and make them freeable. 

An example on a RHEL server is :

service postgresql stop;

/proc/sys/vm/drop_caches;

service postgresql start;

 


Author: Jack Vamvas (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to clear Postgresql database cache


dba-ninja.com