Re: named cache - Mailing list pgsql-general

--- Shane Ambler <pgsql@007Marketing.com> wrote:

> Matthew Peter wrote:
> > --- Willy-Bas Loos <willybas@gmail.com> wrote:
> >
> >> maybe you would find "materialized views" interesting.
> >> http://www.google.com/search?q=materialized+view+postgresql
> >>
> >>
> >> On 12/1/06, Matthew Peter <survivedsushi@yahoo.com> wrote:
> >>> Is it possible to put an query result into memory? Like SELECT * from
> >>> table WHERE
> >>> [...] CACHE cache_name TIMEOUT '1 hour'::interval; So if "cache_name"
> >>> exists with
> >>> the same SQL statement, the result would be fetched from the cache,
> >>> refreshing and
> >>> updating the cache with fresh results when it expires? Reducing disk
> >>> reads, query
> >>> times, etc.
> >>>
> >>>
> >
> >
> > That is basically the idea but talk about a headache. Too many functions and
> > triggers to handle a single view none the less.
> >
> > Rather, why not write an function to use SELECT INTO and put the new tables in a
> > schema named "cache." Drop and recreate the schema cached tables of the views
> and
> > wallah. Making this process cleanly abstracted into the background with 4
> additional
> > words would be a beautiful thing. eg,
> >
> > SELECT * from table WHERE [...] CACHE cache_name TIMEOUT interval;
> >
> > Since it's a cache, it doesn't need to be updated until the TIMEOUT expires and
> > permissions can be inherited by the VIEW that creates it, etc.
> >
> > Or if that is that an SQL-spec no-no? Maybe...
> >
> > CREATE CACHED VIEW on view_name as view_name_cache TIMEOUT interval;
> >
> > Oh ya. Like CREATE UNIQUE INDEX. Now querying from view_name_cache will not
> effect
> > querying from the original view view_name for fresh data!
> >
> > Internally implemented the cached views could be put in a schema like pg_cache,
> in
> > RAM, etc. Doesn't really matter. Would just be nice to have something seamless,
> > clean, upgrade agnostic, and easy! Thoughts?
> >
>
> There was a discussion on pgsql-hackers about a month and a half ago
> that went along these lines. The talk started with the idea of
> integrating pgmemcached into Postgres.
>
> The main result was that the current postgres cache and system cache
> would give the same results as using forced caching configuration.

Yeah. I read throught some of that but didn't know if that was the official
conclusion.

> The overhead of the client connection and sql parsing/planning would
> negate the benefits of specifying what is cached.
>
> One option that was brought up was to create a ram disk and then create
> a tablespace on that disk with tables to hold what you want to cache. Of
> course maintaining that between restarts becomes a hassle as well.
> And if you have enough ram to do that then you have enough for
> PostgreSQL to cache the data that is used in ram anyway.

Wouldn't it work just like plpgsql functions? Where the first call caches the plan
or whatever?

> Using pgmemcached outside of the pg client connection allows you to
> bypass the sql parsing and planning and get the speed improvements you
> are looking for but that is handled by the client not the server.

Interesting. I really don't have any serious problems with performance actually.
Postgresql runs fantastically. I was just curious about RAM caching, I have the
typical 80/20 issue where I would like to free up disk IO for other stuff.

I haven't read up to much on pgmemcached. Although I did read about memcache from
dinga(?) a while back for livejournal. I'd still like it if pg could put a views
results into RAM out of the box.



____________________________________________________________________________________
Yahoo! Music Unlimited
Access over 1 million songs.
http://music.yahoo.com/unlimited