This is possible with Oracle utilizing the keep pool

alter table t_name storage ( buffer_pool keep);

If Postgres were to implement it's own caching system, this seems like
it would be easily to implement (beyond the initial caching effort).


On 10/24/05, Craig A. James <[EMAIL PROTECTED]> wrote:
> Jim C. Nasby" <jnasby ( at ) pervasive ( dot ) com> wrote:
> > > Stefan Weiss wrote:
> > > ... IMO it would be useful to have a way to tell
> > > PG that some tables were needed frequently, and should be cached if
> > > possible. This would allow application developers to consider joins with
> > > these tables as "cheap", even when querying on columns that are
> > > not indexed.
> >
> > Why do you think you'll know better than the database how frequently
> > something is used? At best, your guess will be correct and PostgreSQL
> > (or the kernel) will keep the table in memory. Or, your guess is wrong
> > and you end up wasting memory that could have been used for something
> > else.
> >
> > It would probably be better if you describe why you want to force this
> > table (or tables) into memory, so we can point you at more appropriate
> > solutions.
> Or perhaps we could explain why we NEED to force these tables into memory, so 
> we can point you at a more appropriate implementation.  ;-)
> Ok, wittiness aside, here's a concrete example.  I have an application with 
> one critical index that MUST remain in memory at all times.  The index's 
> tablespace is about 2 GB.  As long as it's in memory, performance is 
> excellent - a user's query takes a fraction of a second.  But if it gets 
> swapped out, the user's query might take up to five minutes as the index is 
> re-read from memory.
> Now here's the rub.  The only performance I care about is response to queries 
> from the web application.  Everything else is low priority.  But there is 
> other activity going on.  Suppose, for example, that I'm updating tables, 
> performing queries, doing administration, etc., etc., for a period of an 
> hour, during which no customer visits the site.  The another customer comes 
> along and performs a query.
> At this point, no heuristic in the world could have guessed that I DON'T CARE 
> ABOUT PERFORMANCE for anything except my web application.  The performance of 
> all the other stuff, the administration, the updates, etc., is utterly 
> irrelevant compared to the performance of the customer's query.
> What actually happens is that the other activities have swapped out the 
> critical index, and my customer waits, and waits, and waits... and goes away 
> after a minute or two.  To solve this, we've been forced to purchase two 
> computers, and mirror the database on both.  All administration and 
> modification happens on the "offline" database, and the web application only 
> uses the "online" database.  At some point, we swap the two servers, sync the 
> two databases, and carry on.  It's a very unsatisfactory solution.
> There is ONLY one way to convey this sort of information to Postgres, which 
> is to provide the application developer a mechanism to explicitely name the 
> tables that should be locked in memory.
> Look at tsearchd that Oleg is working on.  It's a direct response to this 
> problem.
> It's been recognized for decades that, as kernel developers (whether a Linux 
> kernel or a database kernel), our ability to predict the behavior of an 
> application is woefully inadequate compared with the application developer's 
> knowledge of the application.  Computer Science simply isn't a match for the 
> human brain yet, not even close.
> To give you perspective, since I posted a question about this problem 
> (regarding tsearch2/GIST indexes), half of the responses I received told me 
> that they encountered this problem, and their solution was to use an external 
> full-text engine.  They all confirmed that Postgres can't deal with this 
> problem yet, primarily for the reasons outlined above.
> Craig
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not

Reply via email to