From: Matt Clark <matt@ymogen.net>
To: 'Aaron Werman' <awerman2@hotmail.com>
To: 'Scott Kirkwood' <scottakirkwood@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Caching of Queries
Date: Mon, 27 Sep 2004 18:20:48 +0100
Message-ID: <00e801c4a4b6$5526dd30$8300a8c0@solent> (raw)
In-Reply-To: <BAY18-DAV6m5UxP9Fek0002fd87@hotmail.com>
> I think it's very hard to cache results on the client side
> without guidance because it is expensive to notify the client
> of change events. A changing table couldn't be cached on
> client side without a synchronous check to the db - defeating
> 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
[ MAXSTALEDATA seconds ]
[ MAXSTALEPLAN seconds ]
AS ...
That would be tidy I think...
M
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: matt@ymogen.net, awerman2@hotmail.com, scottakirkwood@gmail.com
Subject: Re: Caching of Queries
In-Reply-To: <00e801c4a4b6$5526dd30$8300a8c0@solent>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox