1

i'm curious if there is any way to hint postgresql which data should be more likely cached in memory. in my scenario the server would be handling mostly read traffic, whole data-set is much larger than available memory but some of the tables [ which are not necessarily most frequently accessed ] should be stored in memory, while other do not require high performance and can be accessed from the disk.

i know that memcached or similar can be reasonable workaround, but maybe it all can be done without that.

is this question better suited for stackoverflow?

pQd
  • 29,561
  • 5
  • 64
  • 106

1 Answers1

2

There is no way to hint PostgreSQL about your cache. In most cases you don't need to - the most frequently used pieces of the tables will be cached. It's not quite as naive as just "this table is most used, let's cache this table", it does take other things into accord. (For example, sequential scans on large tables are still not allowed to use up the full buffer cache)

A workaround you could do is to put the hot tables in a different tablespace, mounted on a different partition, and tweak the OS caching parameters for that partition (if that's possible on the OS level, of course). PostgreSQL relies fairly heavily on the OS level filesystem cache, so if you can tune that one to your needs, that could solve your issue.

Magnus Hagander
  • 2,227
  • 15
  • 9
  • thanks! thing is that in this particular case most of queries will thrash the disk and refer to large table - and that's fine by me. while some - less often queries - will access much smaller tables which ideally should be buffered. idea about splitting the table space is very interesting. can you direct me to any up-to-date performance tuning for postgresql under linux? [eg. in case when the server has 32-64GB of ram]. – pQd Jan 10 '10 at 12:07
  • 1
    You'll find the most up-to-date information on wiki.postgresql.org - there are a couple of performance tuning articles on there. Most of the tuning isn't very platform-specific. – Magnus Hagander Jan 10 '10 at 14:31