Re: counting algorithm for incremental matview maintenance

Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> This sounds like a fairly good approach.  It would require a
> couple of things though:
>
> 1) admins would need to be able to enable and disable incremental
> updating of matviews, so that if the creation of delta tables is
> bogging down writes, they can disable them temporarily as a
> performance workaround.

Yes.  This is the sort of thing I plan to put on ALTER MATERIALIZED
VIEW and perhaps an ALTER TABLE option which allows the table to
generate deltas.  Turning off the table option should probably have
a CASCADE option with the sort of user feedback DROP options have.

Some other products have AMV options that do things like allow an
attempt to scan a matview automatically cause a REFRESH if
referenced tables have changed since the last REFRESH.  That seems
like something to defer until we have a more sophisticated notion
of freshness (or staleness -- if we prefer to view the glass as
half-empty).

> 2) if an admin enables incremental updating of an existing
> matview, it would need to be REFRESHed immediately before we
> could start incrementally updating it.  So an ENABLE should call
> a REFRESH.

Right.  We would need to coordinate the start of the incremental
maintenance with the snapshot used for the REFRESH.  As far as I
can tell, it could be either type of REFRESH (truncate and create a
new heap or create the new data contents in a temporary relation
and use a differential DELETE and INSERT technique), as long as the
snapshot used for determining the new contents is used to determine
which transactions can provide deltas.

> Yeah, given what you've explained, my first inclination would be
> just to let the counting do its thing and see how slow/expensive
> it is before we try further optimizations.

Agreed.  The counting algorithm itself has some optional
optimizations that I'm not sure we'll get to in 9.4.  Anything this
specialized should be evaluated only after we have all the more
"generic" optimizations in place.

Thanks for the feedback!

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company