Re:Re: how to clean the cache from databases and operating system

I use windows server 2012 R2.
How to drop postgresql's data in the system cache.
In windows server 2012 R2, I restart postgresql by restarting postgresql service, wether it can drop postgres' cache?

At 2018-09-26 22:52:08, "Maxence Ahlouche" <maxence(dot)ahlouche(at)gmail(dot)com> wrote:

Hi,

On 26 September 2018 at 08:25, jimmy <mpokky(at)126(dot)com> wrote:

1、When I execute the firse sql query, like below:
select * from tablename;
there are some datas that will be loaded into the database cache.
How to clean the data from cache.
2、When I execute second sql query like below:
SELECT pg_prewarm('tablename', 'buffer') . Data will be loaded into the database cache.
How to clean the data from cache.
3、When I execute the third sql query like below:
SELECT pg_prewarm('tablename', 'main') . Data will be loaded into the os cache.
How to clean the data from cache.

To drop the system cache, as per [0] : echo 3 > /proc/sys/vm/drop_caches
To drop postgres' cache, afaik the easiest is to restart postgres.

If you're like me and too lazy to do all that (and don't care about potentially losing data), you can also install an extension rjuju and I wrote that allows you to do that simply with `SELECT pg_drop_caches`.
See [1] for the extension and [2] for how to use it.

[0] https://www.kernel.org/doc/Documentation/sysctl/vm.txt search for "drop_caches" in that page

[1] https://github.com/rjuju/pg_dropbuffers

[2] https://maahl.net/pg_dropbuffers