See and clear Postgres caches/buffers?

optimization postgresql
User1 picture User1 · Aug 1, 2009 · Viewed 94.9k times · Source

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? Can I force all caches to be cleared for tuning purposes?

Note: 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.

Thanks for any help.

Answer

Greg Smith picture Greg Smith · Aug 3, 2009

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.

Related questions

PostgreSQL temporary tables

I need to perform a query 2.5 million times. This query generates some rows which I need to AVG(column) and then use this AVG to filter the table from all values below average. I then need to INSERT these filtered …

Read More
What is the difference between Seq Scan and Bitmap heap scan in postgres?

In output of explain command I found two terms 'Seq Scan' and 'Bitmap heap Scan'. Can somebody tell me what is the difference between these two types of scan? (I am using PostgreSql)

Read More
PostgreSQL performance monitoring tool

I'm setting up a web application with a FreeBSD PostgreSQL back-end. I'm looking for some database performance optimization tool/technique.

Read More