Re: Postgresql Caching

Merlin Moncure wrote:
> On 10/15/06, Anon Mous <soundami(at)yahoo(dot)com> wrote:
>> Would it be possible to combine a special memcache implementation of
>> memcache with a Postgresql interface wrapper?
>
> have you seen
> http://people.freebsd.org/~seanc/pgmemcache/
>
> merlin
>
Now you got me thinkin ;-P

Just throwing some ideas around -

What if we could do something like

CREATE TABLESPACE myramcache LOCATION MEMORY(2GB);

CREATE TABLE mycache (
id as integer, data as varchar(50))
USING TABLESPACE myramcache;

INSERT INTO mycache SELECT id,data FROM myrealtable;

You could setup a table in memory to contain known popular data, you
could also use this to create a temporary table in memory to speed up
multiple intermediate calculations without touching disks.

Or maybe just a view for caching -

CREATE MEMORY VIEW mycacheview
USING MAX(2GB) FOR LIFE(10)
AS SELECT * FROM myrealtable;

which would cache SELECTed rows in ram for LIFE seconds before purging
and inserts/updates to myrealtable would trigger or can contain a
trigger that would purge all or some of the view cache.

Or better yet maybe the memory tablespace idea could also be extended to
allow CREATE VIEW ... USING TABLESPACE myramcache LIFE(10);

TABLESPACE LOCATION MEMORY would seem to give an opportunity for later
expansion.

The memory tablespace idea could be expanded to work with something like
memcached (pg_ramcache_slave ?) - allowing multiple machines to work as
a ram cache for the server.

Something like -
CREATE MEMCACHE group1 SLAVE 192.168.0.5;
CREATE MEMCACHE group1 SLAVE 192.168.0.6 PORT 5436;
CREATE MEMCACHE group2 SLAVE 192.168.0.7;
CREATE TABLESPACE myramcache LOCATION MEMORY WITH group1 SLAVES;
CREATE TABLESPACE myramcache2 LOCATION MEMORY WITH group2 SLAVES;

Probably want to put in some limits such as only temporary tables and
'caching' views are allowed in memory tablespace.

Apart from temp tables these could all be saved into system tables so
they are re-created upon server startup.