postgresql - See and clear Postgres caches/buffers?

Question

Sometimes I run a Postgres query and 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? Can I force all caches to be cleared for tuning purposes?

I'm basically looking for a Postgres version of the following SQL Server command:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

But I would also like to know how to see what is actually contained in that buffer.

Answer 1

You can see what's in the PostgreSQL buffer cache using the pg_buffercache module. I've done a presentation called "Inside the PostgreSQL Buffer Cache" that explains what you're seeing, and I show some more complicated queries to help interpret that information that go along with that.

It's also possible to look at the operating system cache too on some systems, see [pg_osmem.py] for one somewhat rough example.

There's no way to clear the caches easily. On Linux you can stop the database server and use the drop_caches facility to clear the OS cache; be sure to heed the warning there to run sync first.