Thread: query result set caching

Dear All:

Im looking for solutions (Free Software is better) to do query result
caching.
Thanks to the people from #postgresql I know that postgres doesn't do
that by himself and the solution should be some kind of middle ware.

This is our problem: we are executing the same set of SELECT SP/queries
hundreds of times, without updating the DB. We would like to cache the
result of this SP/queries so the next time somebody runs one of them,
the results come from the cache instead of the db.

We would like to use an already developed and tested solution but if we
can't find anything that fits our needs, we can develop our own solution
(playing w/ PHP and PG's LISTEN/NOTIFY)

Thanks in advance

Martin Sarsale <martin@emepe3.net> wrote:

> Dear All:
>
> Im looking for solutions (Free Software is better) to do query result
> caching.
> Thanks to the people from #postgresql I know that postgres doesn't do
> that by himself and the solution should be some kind of middle ware.
>
> This is our problem: we are executing the same set of SELECT SP/queries
> hundreds of times, without updating the DB. We would like to cache the
> result of this SP/queries so the next time somebody runs one of them,
> the results come from the cache instead of the db.
>
> We would like to use an already developed and tested solution but if we
> can't find anything that fits our needs, we can develop our own solution
> (playing w/ PHP and PG's LISTEN/NOTIFY)

There was a lot of talk about "materialized views" not too many months ago
on one of the PostgreSQL lists.  I believe these techniques can accomplish
what you're looking for.  An archive or google search should turn up
lots of info.

HTH.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

On Wednesday 04 August 2004 1:06 pm, Martin Sarsale wrote:
> Dear All:
>
> Im looking for solutions (Free Software is better) to do query
> result caching.
> Thanks to the people from #postgresql I know that postgres doesn't
> do that by himself and the solution should be some kind of middle
> ware.
>
> This is our problem: we are executing the same set of SELECT
> SP/queries hundreds of times, without updating the DB. We would
> like to cache the result of this SP/queries so the next time
> somebody runs one of them, the results come from the cache instead
> of the db.

Possible kludge that may or may not help in your situation:

Can you create a table that is the result of your query and have the
front-end select from that? You could even create a trigger to update
the result table whenever the source table(s) are altered.

Cheers,
Steve