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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
You may be interessted in one of the mailing lists topics, it's answerd by Tom Lane (core dev):
You might also be interessted in an SO question: http://stackoverflow.com/questions/486154/postgresql-temporary-tables and maybe more suiteable http://stackoverflow.com/questions/407006/need-to-load-the-whole-postgresql-database-into-the-ram
Postgres 9.4 finally added an extension to preload data from relations into the OS or database buffer cache (at your choice):
Run once in your database (detailed instructions here):
Then it's simple to preload any given relation. Basic example:
Finds the first table named
The default is
I had similar problem:
To solve this, did write a small python script which does perform selects on heaviest used tables with large indexes. It took 15 min to run, and no performance delays.
where e:\ is the place of the RamDisk.
In the general case if you have enough RAM you can generally trust the database service to do a good job of keeping the things you regularly use in RAM. Some systems allow you to hint that the table should always be held in RAM (which is useful for smallish tables that are not used often but when they are used it is important that they respond as quickly as possible) but if pgsql has such table hints you need to be very careful about using them as you are reducing the amount of memory available for caching anything else so you might slow down your application overall.
If you are looking to prime the database's page cache on startup (for instance after a reboot or other maintainence operation that causes the DB to forget everything that is cached) then write a script that does the following:
(that last step repeated for each index, or course, and be careful to have the fields in the ORDER BY clause in the right order)
After running the above every data and index page should have been read and so will be in the RAM page cache (for the time being at least). We have scripts like this for our application databases, which are run after reboot so that the first users logging into the system afterwards don't experience slower responsiveness. You are better off hand-writing any such script, instead of scanning the db definition tables (like
Hmmm, may be COPY command would help. Just execute COPY to stdout and read from it. It is possible to do it using pg_dump:
Other way is to find all table files and run
Here is the example on how to get table filenames:
so, table's file(s) is /path/to/pgsql/data/base/16384/24576*
You migth want to read indexes and toast tables as well, get their oids in the same way.
BTW, why do you need it? I believe postgresql and OS is smart enough to cache hottest data and maintain good. cache efficiency.