Is there a way of asking PostgreSQL to read the files of a table directly off the disk, asking the OS not to use the file cache? I am running PostgreSQL 9.1 on Ubuntu Server 64-bit. The server in question has the maximum amount of RAM it supports, but the database has grown much larger. Most of the time it doesn't matter, because only specific tables or parts of indexed tables are queried, and all of that fits in the file cache. But we have a new requirement of queries to a table several times larger than the total RAM, and the database has slowed down considerably for the other queries.
I am assuming that with every query to the large table, the OS caches the files containing the table's data, and since the table is larger than total RAM, all the old caches are cleared. The caches that were useful for other smaller tables are lost, and the new caches of the large table are useless because on the next query caching will start again from the first files of the table. Please point out if there is a problem with this assumption. Note that I am refering to OS file caching and not PostgreSQL caching.
Is there a way around this? I have read that there is a way of asking the OS not to cache a file when the file is opened. Is there a way of telling PostgreSQL to use this option when reading files that belong a specific table?
What about putting the table on a tablespace that is on a different device partition with the sync mount option? Would that help?
All suggestions will be appreciated.
Can you please check the following extension, it may be useful to you.