106

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
User1
  • 39,458
  • 69
  • 187
  • 265
  • 2
    this is a very helpful feature for debugging queries (speed). I don't know why postgres devs aren't providing it. – Nulik Jan 19 '21 at 15:54

12 Answers12

77

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.

Yustina Ivanova
  • 122
  • 9
Greg Smith
  • 16,965
  • 1
  • 34
  • 27
  • 52
    Is it possible to simply bypass the caching within a single session? We often need to performance test different queries and this caching makes it very difficult to assess whether one method is better than another (except when comparing the cached performance!) – EvilPuppetMaster Dec 09 '10 at 02:02
  • Thanks, but is there a Postgres SQL command that flushes the cache? –  Jan 14 '11 at 23:59
  • 8
    There is no way to bypass or flush the database's cache. All you can do to clear it is restart the server. – Greg Smith Jan 22 '11 at 15:24
  • 4
    Is it conceivable that this could be made to be possible, for example in future development? Or is this just something that with the current systems (PG and Linux) won't be possible if if one was to try? – Kuberchaun Dec 19 '11 at 22:15
  • 14
    When using a managed PostgreSQL installation such as Amazon RDS, one doesn't have access to the OS, and emptying OS caches for testing purposes can be very hard, so this feature would be very beneficial in PostgreSQL. – Samuli Pahaoja Mar 18 '14 at 09:05
  • Your last sentence made me think there was some kind of danger in not running sync first, but that isn't the case at all. It just means more caches will be dropped. I understand that it's important to ensure that the OS clears out what you think it does, but it's not really a "warning." – jpmc26 Jan 04 '17 at 20:52
  • 8
    Can't reproduce a slow query it's a problem, how can I be sure that my query is performing after a tunning? Restarts the server is not an option I am testing the query in prod because just prod has concurrency, locks and records enough to reproduce the issue – deFreitas Mar 23 '18 at 23:55
  • What about MacOs ? – Islacine Mar 05 '20 at 00:29
25

I haven't seen any commands to flush the caches in PostgreSQL. What you see is likely just normal index and data caches being read from disk and held in memory. by both postgresql and the caches in the OS. To get rid of all that, the only way I know of:

What you should do is:

  1. Shutdown the database server (pg_ctl, sudo service postgresql stop, sudo systemctl stop postgresql, etc.)
  2. echo 3 > /proc/sys/vm/drop_caches This will clear out the OS file/block caches - very important though I don't know how to do that on other OSs. (In case of permission denied, try sudo sh -c "echo 3 > /proc/sys/vm/drop_caches" as in that question)
  3. Start the database server (e.g. sudo service postgresql start, sudo systemctl start postgresql)
Jojo
  • 357
  • 2
  • 10
Leeeroy
  • 1,035
  • 2
  • 12
  • 10
  • 2
    Thought it would be helpful to note: if Postgres' data directory is not on the same volume that '/' is mounted on, you may need to umount before/after the operation above (not sure which, really). In addition (maybe a little voodoo) try running 'sync' before and after the those steps. – marqueed Nov 04 '11 at 07:01
21

Greg Smith's answer about drop_caches was very helpful. I did find it necessary to stop and start the postgresql service, in addition to dropping the caches. Here's a shell script that does the trick. (My environment is Ubuntu 14.04 and PostgreSQL 9.3.)

#!/usr/bin/sudo bash

service postgresql stop
sync
echo 3 > /proc/sys/vm/drop_caches
service postgresql start

I tested with a query that took 19 seconds the first time, and less than 2 seconds on subsequent attempts. After running this script, the query once again took 19 seconds.

Steve Saporta
  • 4,581
  • 3
  • 30
  • 32
15

I use this command on my linux box:

sync; /etc/init.d/postgresql-9.0 stop; echo 1 > /proc/sys/vm/drop_caches; /etc/init.d/postgresql-9.0 start

It completely gets rid of the cache.

Mike Starov
  • 7,000
  • 7
  • 36
  • 37
  • 2
    If Postgresql version is not 9.0: sync; sudo service postgresql stop; echo 1 > /proc/sys/vm/drop_caches; sudo service postgresql start – rusllonrails Sep 09 '15 at 06:29
  • @rusllonrails That will only work if the service is named `postgresql`, which may not be the case. – jpmc26 Jan 20 '17 at 22:19
  • I think, `sync` should be done after stopping the server, immediately before `drop_caches`, as Postgres can write something during stop process again. – greatvovan Sep 04 '18 at 23:05
12

I had this error.

psql:/cygdrive/e/test_insertion.sql:9: ERROR: type of parameter 53 (t_stat_gardien) does not match that when preparing the plan (t_stat_avant)

I was looking for flushing the current plan and a found this:

DISCARD PLANS

I had this between my inserts and it solves my problem.

Luc M
  • 16,630
  • 26
  • 74
  • 89
  • 4
    Discard plan didn't fix for me, query looks cached yet – deFreitas Mar 23 '18 at 23:56
  • 2
    The right syntax is `DISCARD PLANS;`. And, as documentation states: "DISCARD releases internal resources associated with a **database session**". – EAmez Aug 26 '19 at 07:50
10

Yes, it is possible to clear both the shared buffers postgres cache AND the OS cache. Solution bellow is for Windows... others have already given the linux solution.

As many people already said, to clear the shared buffers you can just restart Postgres (no need to restart the server). But just doing this won't clear the OS cache.

To clear the OS cache used by Postgres, after stopping the service, use the excelent RamMap (https://technet.microsoft.com/en-us/sysinternals/rammap), from the excelent Sysinternals Suite. Once you execute RamMap, just click "Empty"->"Empty Standby List" in the main menu.

Restart Postgres and you'll see now your next query will be damm slow due to no cache at all.

You can also execute the RamMap without closing Postgres, and probably will have the "no cache" results you want, since as people already said, shared buffers usually gives little impact compared to the OS cache. But for a reliable test, I would rather stop postgres as all before clearing the OS cache to make sure.

Note: AFAIK, I don't recommend clearing the other things besides "Standby list" when using RamMap, because the other data is somehow being used, and you can potentially cause problems/loose data if you do that. Remember that you are clearing memory not only used by postgres files, but any other app and OS as well.

Regards, Thiago L.

Thiago Linhares de Oliveira
  • 141
  • 1
  • 6
9

Yes, postgresql certainly has caching. The size is controlled by the setting shared_buffers. Other than that, there is as the previous answer mentions, the OS file cache which is also used.

If you want to look at what's in the cache, there is a contrib module called pg_buffercache available (in contrib/ in the source tree, in the contrib RPM, or wherever is appropriate for how you installed it). How to use it is listed in the standard PostgreSQL documentation.

There are no ways to clear out the buffer cache, other than to restart the server. You can drop the OS cache with the command mentioned in the other answer - provided your OS is Linux.

Magnus Hagander
  • 23,890
  • 5
  • 56
  • 43
7

There is pg_buffercache module to look into shared_buffers cache. And at some point I needed to drop cache to make some performance tests on 'cold' cache so I wrote an pg_dropcache extension that does exactly this. Please check it out.

Ildar Musin
  • 1,290
  • 1
  • 11
  • 11
6

this is my shortcut

echo 1 > /proc/sys/vm/drop_caches; echo 2 > /proc/sys/vm/drop_caches; echo 3 > /proc/sys/vm/drop_caches; rcpostgresql stop; rcpostgresql start;
wutzebaer
  • 14,365
  • 19
  • 99
  • 170
2

If you have a dedicated test database, you can set the parameter: shared buffers to 16. That should disable the cache for all queries.

Charlie Chen
  • 224
  • 3
  • 12
  • I tried doing this but got a load of "no unpinned buffers available" errors, is there something else you'd need to do in the test database? – Will Munn Apr 07 '21 at 10:30
1

The original heading was "See and Clear" buffers.

Postgres 13 with pg_buffercache extension provides a way to see doc page

Sumit S
  • 516
  • 5
  • 17
0

On OSX there is a purge command for that:

sync && sudo purge

sync - force completion of pending disk writes (flush cache)

purge - force disk cache to be purged (flushed and emptied)

Credit goes to kenorb answering echo 3 > /proc/sys/vm/drop_caches on Mac OSX

wnm
  • 1,349
  • 13
  • 12