Materialize: Default to non-materialized views

[frankmcsherry]

At the moment, all of our views are materialized. We probably want to switch this to non-materialized by default, as this matches user expectations. Users should be able to materialize views by creating indexes, or perhaps with the MATERIALIZED keyword.

We have not exercised non-materialized views yet, and there is probably some work to do in terms of dataflow building when Get expressions do not correspond to materialized views (so we have to chase down view definitions to try and find materializations at their roots). There is also the question of whether we fuse all the views and then optimize, or optimize and then re-use dataflow fragments.

The sooner we get started on this the better, as it seems like there are probably several issues that we might need to shake out!

[cuongdo]

Without some user education, I do not think it'd be obvious that creating an index should impact the materialization of a view. The de-facto standard (at least for MS SQL Server, PostgreSQL, and Oracle) is to use CREATE MATERIALIZED VIEW. Are there objections to using CREATE MATERIALIZED VIEW?

[rjnn]

I strongly second using the syntax CREATE MATERIALIZED VIEW to indicate that we wish to create the view, as well as all the indexes required to efficiently support it.

[benesch]

Yeah, the plan as I've understood it is for CREATE MATERIALIZED VIEW to reduce down to CREATE VIEW v; CREATE INDEX v_primary_idx ON v (*). I'd really prefer not to have the distinction between materialized and non-materialized views that existing RDBMSes have, where materialized views can have indices and non-materialized views cannot—it's just a lot less flexible and doesn't fit well into the Materialize ethos. (FWIW I'm very in favor a materialized bool column in SHOW VIEWS that computes the materialization state by checking for the presence of at least one index on the view.)

[frankmcsherry]

Minor debatable caveat: if VIEW has unique keys (e.g. is capped with a Reduce), then we might create an index using those keys instead of *. But, that might be a bit too cute. Dunno.

[frankmcsherry]

where materialized views can have indices and non-materialized views cannot

is it appropriate to co-opt this and instead define materialized views as those that have indexes? Like, we ... uh, "automatically up- and down-convert between materialized and non-materialized views as you create and remove indexes"?

Also, it's more declarative!

[benesch]

is it appropriate to co-opt this and instead define materialized views as those that have indexes? Like, we ... uh, "automatically up- and down-convert between materialized and non-materialized views as you create and remove indexes"?

Er, yeah, that's... exactly what I was trying to suggest! :D

FWIW I'm very in favor a materialized bool column in SHOW VIEWS that computes the materialized state by checking for the presence of at least one index on the view.

[frankmcsherry]

Ah, I read too much into "prefer not to have the distinction". I should read that as the preference to not have the pre-specified distinction, like for views that cannot be materialized/indexed without more typing, right?

[benesch]

Yeah, distinction in the async/non-async function sense; like, this function is red, and that function is blue, even though someone deeper into PL theory would tell you that red and blue are just different shades of the same universal color.

[cuongdo]

I'm happy with the approach @benesch wrote above.

[wangandi]

Would we want SHOW MATERIALIZED VIEWS to only list ones that are materialized?

[rjnn]

Yes. SHOW VIEWS should show both unmaterialized and materialized views (along with a boolean column 'materialized'), but SHOW MATERIALIZED VIEWS should only show the materialized ones.

[wangandi]

To check:
in the event that unmaterialized view a uses raw source b and/or unmaterialized source c, it would not be possible to run a select query on a because we have no timestamps.
On the other hand, in the event that unmaterialized view a uses materialized view d, we are able to run a select query on a, and we do want to be able to that, right?

In the case that we do want to be able to select on a unmaterialized view if it only depends on materialized views, do we want SHOW VIEWS to also have a boolean column selectable?

[frankmcsherry]

Perhaps "queryable"? But a bool that helps folks understand this about views would be a good idea. I don't think anything should be blocked by it, but it sounds like a reasonable quality of life addition!