Flushing Caches
Flushing the PostgreSQL Buffer Cache
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;
relname | buffers | size | rel size | cache size | cache % | shared buffers % | rel % |
---|---|---|---|---|---|---|---|
testtable | 443 | 3544 kB | 3544 kB | 4760 kB | 74.45% | 2.70% | 100.00% |
pg_statistic | 16 | 128 kB | 184 kB | 4760 kB | 2.69% | 0.10% | 69.57% |
pg_operator | 14 | 112 kB | 112 kB | 4760 kB | 2.35% | 0.09% | 100.00% |
testtable_idx_gin | 12 | 96 kB | 256 kB | 4760 kB | 2.02% | 0.07% | 37.50% |
pg_depend_reference_index | 11 | 88 kB | 272 kB | 4760 kB | 1.85% | 0.07% | 32.35% |
pg_depend | 7 | 56 kB | 432 kB | 4760 kB | 1.18% | 0.04% | 12.96% |
pg_index | 6 | 48 kB | 24 kB | 4760 kB | 1.01% | 0.04% | 200.00% |
pg_statistic_relid_att_inh_index | 5 | 40 kB | 40 kB | 4760 kB | 0.84% | 0.03% | 100.00% |
pg_operator_oprname_l_r_n_index | 5 | 40 kB | 40 kB | 4760 kB | 0.84% | 0.03% | 100.00% |
pg_amop | 5 | 40 kB | 40 kB | 4760 kB | 0.84% | 0.03% | 100.00% |
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
shared buffers % | unused shared buffers |
---|---|
3.70% | 15778 |
Clearing the OS Cache
Of course, data in PostgreSQL shared buffers is often cached by the OS as well. If you want to ensure all data is read from disk, you'll need to clear the OS cache too.
On Linux, do this:
echo 3 | sudo tee /proc/sys/vm/drop_caches
On Windows, you can use ATM or Sysinternals RAMMap to clear standby lists.