Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

5 Answers 5

up vote 10 down vote accepted

You may be interessted in one of the mailing lists topics, it's answerd by Tom Lane (core dev):

[..] But my opinion is that people who think they are smarter than an LRU caching algorithm are typically mistaken. If the table is all that heavily used, it will stay in memory just fine. If it's not sufficiently heavily used to stay in memory according to an LRU algorithm, maybe the memory space really should be spent on something else. [..]

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

share|improve this answer
    
+1 The same idea applies to other RDBMS too. –  gbn Apr 3 '11 at 16:45
3  
Yes and no. We lock some Oracle tables in-memory because we know that they might not be used that often, but in the situation they are used, latency will be a killer. A DB should always give the DBA final say (another example is hinting the query optimizer). –  Gaius Apr 4 '11 at 9:17
add comment

well,i had similar problem. after restarting server service and all cashed data dropped, many queries called first time where really really slow, cause of specific complexity of the queries, until all necessary indexes and data was cashed. that means, for example users must hit once every "item" (1-3 sec exectime) and related data from 50 million rows, so users would not experience any unwanted delays anymore. it takes first 3 hours for users to experience annoying hangs, till most used data is cashed and programs are ruining top notch with production performance, end even then, 2 days a few sudden short delays, when hitting less first time accessed data ..., for statistics data etc.

to solve this, did write a small python script which does perform selects on heaviest used tables with large indexes. which took 15 min to run, and no performance delays ...

share|improve this answer
add comment

I use RamDrive from QSoft, which was benchmarked as the fastest ramdisk for Windows. I just used

initdb -D e:\data

where e:\ is the place of the RamDisk.

share|improve this answer
1  
PG on Windows is a pretty brave choice for a production site since it is way slower on Windows than on *nix (independent from the RAM). –  DrColossos Apr 11 '11 at 18:04
add comment

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:

SELECT * FROM <table>
SELECT <primary key fields> FROM <table> ORDER BY <primary key fields>
SELECT <indexed fields> FROM <table> ORDER BY <indexed fields>

(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 sys.objects/sys.indexes/sys.columns in MSSQL), then you can selectively scan the indexes that are most commonly used rather than scanning everything which will take longer.

share|improve this answer
    
This won't work, at least on PostgreSQL. A small (256KB) ring buffer is allocated from shared buffers for sequential scans to prevent the entire buffer cache from being used. See github.com/postgres/postgres/blob/master/src/backend/storage/… for the details. You can verify this by doing a SELECT * from a large table then looking at the pg_buffercache table (from pg_buffercache extension). –  yangyang Jul 14 at 11:09
add comment

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:

pg_dump -U <user> -t <table> <database> > /dev/null

Other way is to find all table files and run cat <files> > /dev/null.

Here is the example on how to get table filenames:

# SELECT oid, datname FROM pg_database ;
  oid  |  datname  
-------+-----------                                                                                                                                          
<...>
 16384 | test
-- out of database is 16384
# SELECT oid, relname FROM pg_class WHERE relname like 'fn%';
  oid  | relname 
-------+---------
 24576 | fn
(1 row)
-- oid of our table is 24576

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.