Re: Enough RAM for entire Database.. cost aside, is this

On Thu, 08 Jul 2004 14:27:29 +0530, Shridhar Daithankar
<shridhar@frodo.hserus.net> wrote:

> - Postgresql or no postgresql, OS has to manage buffer cache. Why duplicate the
> efforts which somebody is already better at? If OS improves, everybody benefits.
> I can recall quite a few posts benefitting from moving to 2.6.x kernel from 2.4.x.

One big reason why a large memory space to cache things in your
database server can be beneficial is that it allows for some sometimes
very effective on the fly optimizations to use data structures
designed around in-memory access instead of on-disk access.

Anything in the OS's buffer cache must be represented in the on-disk format.

There are a variety of highly specialized databases that are designed
around being able to build certain structures such as indexes in
memory, and their claim to fame is being much faster for certain types
of queries on in-memory databases.  There are many commodity databases
such as innodb that can do things such as build, on the fly, an in
memory hash of a btree index (or a range from a btree index) if
queries are frequently doing lookups that could benefit from it.

As the cost of having large quantities of data present in main memory
drops, postgresql's approach makes it hard to adopt significant
optimizations that minimize the difference between algorithms designed
for in-memory database access and disk based access.

How postgresql approaches these things isn't a bad way, and works
quite well for many people and does allow more effort to be focused in
other areas, but it still is a limitation in certain use cases.