Thread: named cache

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.



____________________________________________________________________________________
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com

Matthew Peter 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.

No. That's not something PG does internally.

Sounds like you might want to look at memcached/pgmemcached.

--
   Richard Huxton
   Archonet Ltd

--- 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?



____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com

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.

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@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

--- 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

Matthew Peter wrote:

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

A stored procedure can cache the query plan but that is separate from
caching data.

When sending sql select statements to the server the query plan isn't
cached (if it was you would still need to compare the two selects to see
if they could use the same plan negating the benefit)
Your client program may be sending the same select command but how is
the server going to know it is the same? - a straight string comparison?
when you change the where clause that goes out.

If you want to bypass the parser/planner then use stored procedures.

With a small database you can have enough ram to have the whole dataset
(and indexes) in memory and only use the disk to save updates. With
large datasets you want enough ram to keep the most used data in memory
to reduce disk reads as much as possible.

Back to your cached view - if you have enough ram to cache your whole
database then the first select will bring that data into ram cache and
from then on will not need to read it from disk, effectively achieving
what you want - postgres is doing that without you implicitly telling it
to. If the data you want cached is getting replaced by more recent data
then either it isn't used as much as you think or you don't have enough
ram to cache what you use most. Forcing postgres to cache data you think
is more often used only makes it read the disk more for the other data
that it can no longer fit into cache.

Either way adding more ram is the only way to reduce disk access.


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz