Re: named cache

Matthew Peter wrote:
> --- Willy-Bas Loos <willybas(at)gmail(dot)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(at)yahoo(dot)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.

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.

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.

--

Shane Ambler
pgSQL(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz