PostgreSQL: Force data into memory

Is there a systematic way to force PostgreSQL to load a specific table into memory, or at least read it from disk so that it will be cached by the system?

Postgres 9.4 finally added an extension to preload data from relations into the OS or database buffer cache (at your choice):

pg_prewarm


This allows reaching full operating performance more quickly.

Run once in your database (detailed instructions here):

CREATE EXTENSION pg_prewarm;

Then it's simple to preload any given relation. Basic example:

SELECT pg_prewarm('my_tbl');

Finds the first table named my_tbl in the search path and loads it to the Postgres buffer cache.

Or:

SELECT pg_prewarm('my_schema.my_tbl', 'prefetch');

prefetch issues asynchronous prefetch requests to the operating
system, if this is supported, or throws an error otherwise. read
reads the requested range of blocks; unlike prefetch, this is
synchronous and supported on all platforms and builds, but may be
slower. buffer reads the requested range of blocks into the database
buffer cache.

The default is buffer, which has the greatest impact (higher cost, best effect).

Read the manual for more details.

Depesz blogged about it, too.

StackExchange Database Administrators Q#2041, answer score: 47