Thread: Pinning a table into memory

In Oracle you can Pin large objects into memory to prevent frequent
reloads. Is there anyway to do this with Postgres?  It appears that some
of our tables that get hit a lot may get kicked out of memory when we
access some of our huge tables. Then they have to wait for I/O to get
loaded back in.


David Blood
Matraex, Inc

"David Blood" <david@matraex.com> writes:
> In Oracle you can Pin large objects into memory to prevent frequent
> reloads. Is there anyway to do this with Postgres?

I can never understand why people think this would be a good idea.
If you're hitting a table frequently, it will stay in memory anyway
(either in Postgres shared buffers or kernel disk cache).  If you're
not hitting it frequently enough to keep it swapped in, then whatever
is getting swapped in instead is probably a better candidate to be
occupying the space.  ISTM that a manual "pin this table" knob would
mostly have the effect of making performance worse, whenever the
system activity is slightly different from the situation you had in
mind when you installed the pin.

Having said that, I'll freely concede that our cache management
algorithms could use improvement (and there are people looking at
that right now).  But a manual pin doesn't seem like a better answer.

            regards, tom lane

Tom Lane wrote:
>
> "David Blood" <david@matraex.com> writes:
> > In Oracle you can Pin large objects into memory to prevent frequent
> > reloads. Is there anyway to do this with Postgres?
>
> I can never understand why people think this would be a good idea.
> If you're hitting a table frequently, it will stay in memory anyway
> (either in Postgres shared buffers or kernel disk cache).  If you're
> not hitting it frequently enough to keep it swapped in, then whatever
> is getting swapped in instead is probably a better candidate to be
> occupying the space.

As I understand it, he's looking for a mechanism to prevent a single
sequential scan on a table, larger than the buffer cache, to kick out
everything else at once. But I agree with you that pinning other objects
is just mucking with the symptoms instead of curing the desease.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #