Re: Query caching - Mailing list pgsql-general

> I've looked for references as to
> Postgresql's ability to do something like this, but I've never been
> certain if it's possible. Can postgresql do this, please? And, if not,
> does it have to hit the disk for every SQL instruction (I would assume
> so)?

 Doing so,  as you  might guess  is quite dangerous.  Eg, RAM  failures are
extremely  rare, with  probability very  close  to 0,  but there's  nothing
absolutely reliable.
 From my, quite  limited, experience, I can tell that  PGSQL relies more on
file caching (or whatever is the term),  provided by the OS, rather than on
slurping relations into RAM. See  the recent discussion of [f]sync(), maybe
it sheds more light.

> I would imagine that the actual query cache would be slightly orthogonal
> to this in-RAM database cache

 Actually, there are  several ways to keep the data  in memory, each having
its advantages drawbacks and reasons. To name just a few: caching pages and
files, mapping  files, storing  `internal' structures  (like the  tuples in
your example) in shared memory areas.
 Apologets and enemies of each method come in all shapes, but the real life
is  even  worse.  Often  these  methods  interfere  with  each  other,  and
inaccurate combination (you  cache the pages, but  overlooked file caching,
performed by the OS) may easily become a bottleneck.

> I'd appreciate any pointers to more information on specific performance
> tuning in this area (IMHO, it would probably be a boon to the postgresql
> database and its community, if there existed some reference like
> O'Reilly's _Oracle Performance Tuning_ that was focused on Postgresql.)

 As I see  it, performance tuning with PGSQL should  be concentrated around
quality design of your DB and queries. I may be wrong, but there's not much
to play with where PGSQL server touches the system.
 Maybe it's  bad, but I like  it. General suggestions about  fs performance
apply to PGSQL  and you don't have  to re-invent the wheel.  There are just
files. Play  with sync, install  a RAID of SCSI  drives, keep your  swap on
separate controller.  Nothing really special  that would impact  or, what's
more important, interfere with other services running on the same box.
 Change must come from inside :) Here, inside is DB design.

 Ed


--

 contaminated fish and microchips
  huge supertankers on Arabian trips
 oily propaganda from the leaders' lips
  all about the future
 there's people over here, people over there
  everybody's looking for a little more air
 crossing all the borders just to take their share
  planning for the future

 Rainbow, Difficult to Cure