Sean,
> I've spent some time in the past thinking about this, and here's the
> best idea that I can come up with:
>
> Part one: setup an ALTER TABLE directive that allows for the
> addition/removal of cached aggregates. Ex:
Actually, Joe Conway and I may be working on something like this for a client.
Joe's idea is to use a hacked version of the statistics collector to cache
selected aggregate values in memory. These aggregates would be
non-persistent, but the main concern for us is having aggregate values that
are instantly accessable, and that don't increase the cost of INSERTS and
UPDATES more than 10%.
This is to satisfy the needs of a particular client, though, so it may never
make it into the general PostgreSQL source. We'll post it somewhere if it
works, though.
We already implemented caching aggregates to tables, with is trivially easy to
do with triggers. The problem with this approach is the
UPDATE/INSERT/DELETE overhead; even with an SPI-optimized C trigger, it's
costing us up to 40% additional time when under heavy write activity ...
which is exactly when we can't afford delays.
For a database which has a low level of UPDATE activity, though, you can
already implement cached aggregates as tables without inventing any new
Postgres extensions.
--
Josh Berkus
Aglio Database Solutions
San Francisco
