Re: clear cache in postgresql

On Wed, Jun 16, 2021 at 12:02:52PM +0530, Atul Kumar wrote:
>
> Sometimes I run a Postgres query it takes 30 seconds. Then, I
> immediately run the same query and it takes 2 seconds. It appears that
> Postgres has some sort of caching. Can I somehow see what that cache
> is holding?

You can use pgbuffercache for that:
https://www.postgresql.org/docs/current/pgbuffercache.html

> Can I force all caches to be cleared for tuning purposes?
> So I need to clear the cache without restarting/rebooting the postgres
> server to check the correct execution plan of my query.

No, cleaning postgres cache can only be done with a service restart. That
being said, tuning shouldn't be done assuming that there's no cache. On the
opposite maybe what you should do is use pg_prewarm
(https://www.postgresql.org/docs/current/pgprewarm.html) to make sure that your
cache isn't empty after a restart.

Note that you also have the operating system cache. It can be cleared without
restarting the OS, but I have no idea if this is possible with RDS.