Flushing Caches

Sometimes it's useful to flush the PostgreSQL buffer cache (remove all cached relation and index data), for example when trying to optimize a query that is likely to be executed on uncached data. Unfortunately PostgreSQL doesn't provide a built-in way to do this (short of a restart).

Scanning a large table would seem to be the obvious way to do it. However PostgreSQL won't use all available shared buffers for most sequential scans, as it would reduce the effectiveness of the buffer cache in general. Instead it uses a small ring buffer, currently hardcoded to 256KB (details are in src/backend/storage/buffer/README). This means that SELECT * FROM abigtable isn't enough.

Iterating over the results of a large table in PL/pgSQL defeats this mechanism. We can create a table slightly larger than shared_buffers, scan it from PL/pgSQL, then drop it.

First we'll create a table with a single char(5000) column. This is larger than half a page; columns won't span pages, so we'll end up with one row per page. We'll set STORAGE to PLAIN to prevent values being compressed or stored in a TOAST table. The table is UNLOGGED to speed things up by not writing any WAL; this is safe as we would not need it recovering in case of a crash. Note this does mean that it will not be created on any standbys.

CREATE UNLOGGED TABLE trash_buffers (a char(5000));
ALTER TABLE trash_buffers ALTER COLUMN a SET STORAGE PLAIN;

CREATE TABLE
ALTER TABLE

Next we populate the table. The unit of shared_buffers in pg_settings is pages, so we insert that many rows, plus a few extra to be sure. The char column is fixed length, so the actual value inserted is unimportant:

DO LANGUAGE plpgsql $$ BEGIN
    FOR i IN 1..(SELECT setting::bigint
                 FROM pg_settings
                 WHERE name = 'shared_buffers') + 1000 LOOP
        INSERT INTO trash_buffers VALUES ('x');
    END LOOP;
END; $$ ;

DO

Now we iterate over all rows in the table with PL/pgSQL:

DO LANGUAGE plpgsql $$
DECLARE
    row record;
BEGIN
   FOR row IN SELECT * FROM trash_buffers
   LOOP
   END LOOP;
END
$$

Finally, we drop the table. If you need to do this several times, you could keep the table around and run the scan when necessary.

DROP TABLE IF EXISTS trash_buffers;

DROP TABLE

Now we can confirm that the only tables in the buffer cache are catalog tables (this query uses the pg_buffercache extension, distributed with PostgreSQL):

SELECT c.relname,
      count(*) AS buffers,
      pg_size_pretty(count(*) * 8192) AS size,
      pg_size_pretty(pg_relation_size(c.oid)) "rel size",
      pg_size_pretty(sum(count(*)) over () * 8192) "cache size",
      to_char(count(*) / (sum(count(*)) over ()) * 100, '990.99%') as "cache %",
      to_char(count(*)::double precision / (SELECT setting::bigint
                                            FROM pg_settings
                                            WHERE name = 'shared_buffers') * 100,
              '990.99%') AS "shared buffers %",
      to_char(CASE pg_relation_size(c.oid)
              WHEN 0 THEN 100
              ELSE (count(*) * 8192 * 100) / pg_relation_size(c.oid)::float
              END, '990.99%') AS "rel %"
FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode=c.relfilenode
INNER JOIN pg_database d ON (b.reldatabase=d.oid AND d.datname=current_database())
GROUP BY c.relname, c.oid
ORDER BY 2 DESC LIMIT 10;

A summary of shared buffer usage:

SELECT to_char(count(*)::double precision / (SELECT setting::bigint
                                             FROM pg_settings
                                             WHERE name = 'shared_buffers') * 100,
               '990.99%') AS "shared buffers %",
       (SELECT setting::bigint 
        FROM pg_settings
        WHERE name = 'shared_buffers') - count(*) AS "unused shared buffers"
FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode=c.relfilenode