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).
Alex
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
match
