Thread: How to implement table caching
To increase performance, I'm thinking about storing copies of less frequently changed tables in a client computer. At startup client application compares last change times and downloads newer tables from server. CREATE TABLE lastchange ( tablename CHAR(8) PRIMARY KEY, lastchange timestamp without time zone ); INSERT INTO lastupdated (tablename) values ('mytable1'); .... INSERT INTO lastupdated (tablename) values ('mytablen'); CREATE OR REPLACE FUNCTION setlastchange() RETURNS "trigger" AS $$BEGIN UPDATE lastchange SET lastchange='now' WHERE tablename=TG_RELNAME; RETURN NULL; END$$ LANGUAGE plpgsql STRICT; CREATE TRIGGER mytable1_trig BEFORE INSERT OR UPDATE OR DELETE ON mytable1 EXECUTE PROCEDURE setlastchange(); .... CREATE TRIGGER mytablen_trig BEFORE INSERT OR UPDATE OR DELETE ON mytablen EXECUTE PROCEDURE setlastchange(); Is table caching good idea? Is this best way to implement table caching ? Andrus.
Andrus, You might consider something like materialized views: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Whether table caching is a good idea depends completely on the demands of your application. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Aug 14, 2005, at 1:12 PM, Andrus Moor wrote: > To increase performance, I'm thinking about storing copies of less > frequently changed tables in a client computer. > At startup client application compares last change times and > downloads newer > tables from server. > > CREATE TABLE lastchange ( > tablename CHAR(8) PRIMARY KEY, > lastchange timestamp without time zone ); > > INSERT INTO lastupdated (tablename) values ('mytable1'); > .... > INSERT INTO lastupdated (tablename) values ('mytablen'); > > CREATE OR REPLACE FUNCTION setlastchange() RETURNS "trigger" > AS $$BEGIN > UPDATE lastchange SET lastchange='now' WHERE tablename=TG_RELNAME; > RETURN NULL; > END$$ LANGUAGE plpgsql STRICT; > > CREATE TRIGGER mytable1_trig BEFORE INSERT OR UPDATE OR DELETE ON > mytable1 > EXECUTE PROCEDURE setlastchange(); > .... > CREATE TRIGGER mytablen_trig BEFORE INSERT OR UPDATE OR DELETE ON > mytablen > EXECUTE PROCEDURE setlastchange(); > > Is table caching good idea? > Is this best way to implement table caching ? > > Andrus.
There is also http://people.freebsd.org/~seanc/pgmemcache/ On Mon, Aug 15, 2005 at 04:54:31PM -0500, Thomas F. O'Connell wrote: > Andrus, > > You might consider something like materialized views: > > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html > > Whether table caching is a good idea depends completely on the > demands of your application. > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > > Strategic Open Source: Open Your i? > > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-469-5150 > 615-469-5151 (fax) > > On Aug 14, 2005, at 1:12 PM, Andrus Moor wrote: > > >To increase performance, I'm thinking about storing copies of less > >frequently changed tables in a client computer. > >At startup client application compares last change times and > >downloads newer > >tables from server. > > > >CREATE TABLE lastchange ( > > tablename CHAR(8) PRIMARY KEY, > > lastchange timestamp without time zone ); > > > >INSERT INTO lastupdated (tablename) values ('mytable1'); > >.... > >INSERT INTO lastupdated (tablename) values ('mytablen'); > > > >CREATE OR REPLACE FUNCTION setlastchange() RETURNS "trigger" > >AS $$BEGIN > >UPDATE lastchange SET lastchange='now' WHERE tablename=TG_RELNAME; > >RETURN NULL; > >END$$ LANGUAGE plpgsql STRICT; > > > >CREATE TRIGGER mytable1_trig BEFORE INSERT OR UPDATE OR DELETE ON > >mytable1 > > EXECUTE PROCEDURE setlastchange(); > >.... > >CREATE TRIGGER mytablen_trig BEFORE INSERT OR UPDATE OR DELETE ON > >mytablen > > EXECUTE PROCEDURE setlastchange(); > > > >Is table caching good idea? > >Is this best way to implement table caching ? > > > >Andrus. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461