X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 75FA9329D2A for ; Mon, 27 Sep 2004 18:20:57 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59752-05 for ; Mon, 27 Sep 2004 17:20:51 +0000 (GMT) Received: from bayswater1.ymogen.net (host-154-240-27-217.pobox.net.uk [217.27.240.154]) by svr1.postgresql.org (Postfix) with ESMTP id 4D231329F0B for ; Mon, 27 Sep 2004 18:20:50 +0100 (BST) Received: from solent (unknown [213.165.136.10]) by bayswater1.ymogen.net (Postfix) with ESMTP id EB0469EA32; Mon, 27 Sep 2004 18:20:48 +0100 (BST) Reply-To: From: "Matt Clark" To: "'Aaron Werman'" , "'Scott Kirkwood'" , Subject: Re: Caching of Queries Date: Mon, 27 Sep 2004 18:20:48 +0100 Organization: Ymogen Ltd Message-ID: <00e801c4a4b6$5526dd30$8300a8c0@solent> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/285 X-Sequence-Number: 8413 > I think it's very hard to cache results on the client side=20 > without guidance because it is expensive to notify the client=20 > of change events. A changing table couldn't be cached on=20 > client side without a synchronous check to the db - defeating=20 > the purpose. This is very true. Client side caching is an enormous win for apps, but it requires quite a lot of logic, triggers to update last-modified fields on relevant tables, etc etc. Moving some of this logic to the DB would perhaps not usually be quite as efficient as a bespoke client caching solution, but it will above all be a lot easier for the application developer! The other reason why it is god for the DB to support this feature is that in typical web apps there are multiple web/app servers in a farm, but mostly just one live DB instance, so effective client side caching requires a distributed cache, or a SQL proxy, both of which are the kind of middleware that tends to give cautious people cause to fret. As a side effect, this would also satisfy the common gotcha of count(), max() and other aggregates always needing a scan. There are _so_ many occasions where 'select count(*) from bar' really does not need to be that accurate. So yeah, here's another vote for this feature. It doesn't even need to happen automagically to be honest, so long as it's really simple for the client to turn on (preferably per-statement or per-table). Actually, that gives me an implementation idea. How about cacheable views? So you might do: CREATE [ CACHEABLE ] VIEW view=20 [ MAXSTALEDATA seconds ] [ MAXSTALEPLAN seconds ] AS ...=20 That would be tidy I think... M