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 unlikely to be called when data it references is cached. 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 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
). So a simple SELECT * FROM abigtable
isn't enough.
Fortunately, 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, and
columns won't span pages in PostgreSQL, 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 % |
---|---|---|---|---|---|---|---|
pg_operator | 11 | 88 kB | 112 kB | 768 kB | 11.46% | 0.07% | 78.57% |
pg_depend_reference_index | 9 | 72 kB | 240 kB | 768 kB | 9.38% | 0.05% | 30.00% |
pg_depend | 5 | 40 kB | 384 kB | 768 kB | 5.21% | 0.03% | 10.42% |
pg_depend_depender_index | 5 | 40 kB | 240 kB | 768 kB | 5.21% | 0.03% | 16.67% |
pg_statistic_relid_att_inh_index | 4 | 32 kB | 40 kB | 768 kB | 4.17% | 0.02% | 80.00% |
pg_amop | 4 | 32 kB | 32 kB | 768 kB | 4.17% | 0.02% | 100.00% |
pg_operator_oid_index | 4 | 32 kB | 40 kB | 768 kB | 4.17% | 0.02% | 80.00% |
pg_operator_oprname_l_r_n_index | 4 | 32 kB | 40 kB | 768 kB | 4.17% | 0.02% | 80.00% |
pg_amop_fam_strat_index | 3 | 24 kB | 32 kB | 768 kB | 3.13% | 0.02% | 75.00% |
pg_amop_opr_fam_index | 3 | 24 kB | 32 kB | 768 kB | 3.13% | 0.02% | 75.00% |
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 |
---|---|
0.59% | 16288 |
Clearing the OS Cache
Of course, data in PostgreSQL shared buffers is often cached by the OS as well, and 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.