Re: avoiding file system caching of a table

On Mon, Feb 17, 2014 at 2:33 PM, Gabriel Sánchez Martínez <
gabrielesanchez(at)gmail(dot)com> wrote:

> 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.
https://github.com/klando/pgfincore

Regards,
Hari Babu
Fujitsu Australia