5 ms·

Materialize is neat, but there are other database systems that refresh at least some materialized views on the fly, while being smart about not rebuilding the e

by rst 6y ago

Materialize is neat, but there are other database systems that refresh at least some materialized views on the fly, while being smart about not rebuilding the entire view every time. See for example, Oracle, where FAST REFRESH ON COMMIT does most of what Materialize is advertised as doing, at least for views which that feature can support (restriction list here: https://stackoverflow.com/questions/49578932/materialized-view-in-oracle-with-fast-refresh-instead-of-complete-dosnt-work https://stackoverflow.com/questions/49578932/materialized-vi... ). Mind you, this comes with Oracle's extremely hefty price tag, so I'm not sure I'd recommend it to anyone who isn't already stuck with Oracle, but it is technical precedent. It would be interesting to compare notes, and see what Materialize does better.

deleted 6y ago
[deleted]
willvarfar 6y ago
Similar to the very cool TimescaleDB’s Continuous Aggregates (and InfluxDB’s not so cool Continous Queries). There’s also ksqlDB that can be used in a similar way?
atwebb 6y ago
Indexed views in SQL server behave similarly (though not as performant as Oracle I believe). They also have some constraints on application (schema bound, data types, nesting).
dkhenry 6y ago
About a year ago Vitess merged in support for what we call VReplication. ( https://vitess.io/docs/reference/features/vreplication/ https://vitess.io/docs/reference/features/vreplication/ ) The initial demo was a custom Materialized view that did calculations and rollup's on data in realish time ( it uses the MySQL binlog ). Right now the limiting factor for us calling is true Materalized View's is that configuring them is done out of band using the Vitess Control plane, but soon we will be merging in the features to allow standard DDL to configure and manage them!
jacques_chester 6y ago
The OP briefly alludes to the big difference: differential dataflow. My understanding is that it means that the database doesn't update the view simply because a datum arrived, but rather only if the datum will change it. That avoids an enormous amount of churn and makes materialized views fantastically cheaper on average. Edit: The original thread here on HN has lots of detailed answers from the Materialize folks, plus some gushing fandom from that jacques_chester dweeb: https://news.ycombinator.com/item?id=22359769 https://news.ycombinator.com/item?id=22359769
- rst 6y ago
  That's what Oracle FAST REFRESH does as well, in the limited cases where it works. Which is why a comparison to Materialize would be intersting...
  - arjunnarayan 6y ago
    I think a 5-second summary of the difference is that Materialize is Oracle FAST REFRESH for almost* all of SQL, as opposed to just those very limited cases where Oracle's FAST REFRESH works. Oracle's docs have entire features in order to help you diagnose whether your views are eligible for fast or synchronous refresh[1]. In Materialize, everything you can express in SQL is fast refreshed. No caveats. [1] https://docs.oracle.com/database/121/DWHSG/sync.htm#DWHSG9146 https://docs.oracle.com/database/121/DWHSG/sync.htm#DWHSG914... *Almost, because the surface area is truly astonishing.
quodlibetor 6y ago
I'm not sure exactly how fast FAST REFRESH ON COMMIT is, but we at materialize are very fast, and getting faster. Once data is in materialized we can process streams to multiple downstream views with millisecond-level latencies. I'm working on improving our benchmarking capabilities so that we can provide less qualified answers to the question of "how fast is materialized?" Much more interesting than our speed, though, in my opinion, is the fact that you can use materialized as the place where you do joins _across_ databases and file formats. It's particularly interesting in a microservices environment, where you may have a postgres db that was set up by one team and a mysql db that was set up by another team and the only thing you care about is doing some join or aggregate across the two: with materialized (and debezium) you can just stream the data into materialized and have continuously up to date views across services. Combine this with a graphql or rest postgres api layer (like hasura) and a large amount of CRUD code -- entire CRUD services that I've worked on in the past -- just disappears.
frankmcsherry 6y ago
Hi folks. Frank from Materialize here. The main differences you should expect to see are generality and performance. Generality, in that there are fewer limitations on what you can express. Oracle (and most RDBMSs) build their Incremental View Maintenance on their existing execution infrastructure, and are limited by the queries whose update rules they can fit in that infrastructure. We don't have that limitation, and are able to build dataflows that update arbitrary SQL92 at the moment. Outer joins with correlated subqueries in the join constraint; fine. Performance, in that we have the ability to specialize computation for incremental maintenance in a way that RDBMSs are less well equipped to do. For example, if you want to maintain a MIN or MAX query, it seems Oracle will do this quickly only for insert-only workloads; on retractions it re-evaluates the whole group. Materialize maintains a per-group aggregation tree, the sort of which previously led to a 10,000x throughput increase for TPCH Query 15 [0]. Generally, we'll build and maintain a few more indexes for you (automatically) burning a bit more memory but ensuring low latencies. As far as I know, Timescale's materialized views are for join-free aggregates. Systems like Druid were join-free and are starting to introduce limited forms. KSQLdb has the same look and feel, but a. is only eventually consistent and b. round-trips everything through Kafka. Again, all AFAIK and could certainly change moment by moment. Obviously we aren't allowed to benchmark against Oracle, but you can evaluate our stuff and let everyone know. So that's one difference. [0]: https://github.com/TimelyDataflow/differential-dataflow/tree/master/tpchlike https://github.com/TimelyDataflow/differential-dataflow/tree...
- cevian 6y ago
  Just as a point of clarification: timescale materialized views don't allow joins during materialization. But you can join materialized views arbitrarily at query time. (TimescaleDB engineer here)
- sradman 6y ago
  Materialize sounds like it has reinvented Michael Stonebraker's StreamSQL [1] and SAP's Continuous Computation Language (CCL) [2] which was created as part of a StreamSQL competitor named Coral8 and lives on in an enterprise product now named SAP HANA Smart Data Streaming. This space has gone by many names, Streaming SQL, Complex Event Processing (CEP), and Event Streaming. I think the Continuous Computation Language (CCL) name captures the essence of these systems: data flows through the computation/query. These systems have always had promise but none have found anything but niche adoption. The two most popular use cases seem to be ETL-like dataflows and OLAP style Window queries incrementally updated with streaming data (e.g. computations over stock tick data joined with multiple data sources). [1] https://en.wikipedia.org/wiki/StreamSQL https://en.wikipedia.org/wiki/StreamSQL [2] https://help.sap.com/doc/PRODUCTION/e1b391d2a3f3439fbab27ed882618873/2.0.00/en-US/streaming_ccl_reference.pdf https://help.sap.com/doc/PRODUCTION/e1b391d2a3f3439fbab27ed8...
albertwang 6y ago
I'm a PM @ Materialize. Regarding the questions from the OP's link: #1 -- Postgres is built in C, while Timely/Differential (which underpin Materialize) are built in Rust. Materialize could be a Postgres extension at some point, but for now we want to control the stack (to more easily do cross-dataflow optimization, etc) #2 -- We are absolutely interested in exposing an API to enable new data sources (and data sinks!). It currently takes a bit of time to understand some basic Timely Dataflow concepts, but we intend on documenting this and opening things up. We're also trying to understand user requirements around things like durability guarantees and UPDATE/DELETEs (Feel free to email me or hit us up in our public Slack as well). If anyone is interested in sending data via HTTP POST, we'd love to hear more: https://github.com/MaterializeInc/materialize/issues/1701 https://github.com/MaterializeInc/materialize/issues/1701.

by rst 6y ago

deleted 6y ago

[deleted]

willvarfar 6y ago

Similar to the very cool TimescaleDB’s Continuous Aggregates (and InfluxDB’s not so cool Continous Queries). There’s also ksqlDB that can be used in a similar way?

atwebb 6y ago

Indexed views in SQL server behave similarly (though not as performant as Oracle I believe). They also have some constraints on application (schema bound, data types, nesting).

dkhenry 6y ago

About a year ago Vitess merged in support for what we call VReplication. ( https://vitess.io/docs/reference/features/vreplication/ https://vitess.io/docs/reference/features/vreplication/ ) The initial demo was a custom Materialized view that did calculations and rollup's on data in realish time ( it uses the MySQL binlog ). Right now the limiting factor for us calling is true Materalized View's is that configuring them is done out of band using the Vitess Control plane, but soon we will be merging in the features to allow standard DDL to configure and manage them!

jacques_chester 6y ago

The OP briefly alludes to the big difference: differential dataflow. My understanding is that it means that the database doesn't update the view simply because a datum arrived, but rather only if the datum will change it. That avoids an enormous amount of churn and makes materialized views fantastically cheaper on average. Edit: The original thread here on HN has lots of detailed answers from the Materialize folks, plus some gushing fandom from that jacques_chester dweeb: https://news.ycombinator.com/item?id=22359769 https://news.ycombinator.com/item?id=22359769

rst 6y ago
That's what Oracle FAST REFRESH does as well, in the limited cases where it works. Which is why a comparison to Materialize would be intersting...
- arjunnarayan 6y ago
  I think a 5-second summary of the difference is that Materialize is Oracle FAST REFRESH for almost* all of SQL, as opposed to just those very limited cases where Oracle's FAST REFRESH works. Oracle's docs have entire features in order to help you diagnose whether your views are eligible for fast or synchronous refresh[1]. In Materialize, everything you can express in SQL is fast refreshed. No caveats. [1] https://docs.oracle.com/database/121/DWHSG/sync.htm#DWHSG9146 https://docs.oracle.com/database/121/DWHSG/sync.htm#DWHSG914... *Almost, because the surface area is truly astonishing.

quodlibetor 6y ago

I'm not sure exactly how fast FAST REFRESH ON COMMIT is, but we at materialize are very fast, and getting faster. Once data is in materialized we can process streams to multiple downstream views with millisecond-level latencies. I'm working on improving our benchmarking capabilities so that we can provide less qualified answers to the question of "how fast is materialized?" Much more interesting than our speed, though, in my opinion, is the fact that you can use materialized as the place where you do joins _across_ databases and file formats. It's particularly interesting in a microservices environment, where you may have a postgres db that was set up by one team and a mysql db that was set up by another team and the only thing you care about is doing some join or aggregate across the two: with materialized (and debezium) you can just stream the data into materialized and have continuously up to date views across services. Combine this with a graphql or rest postgres api layer (like hasura) and a large amount of CRUD code -- entire CRUD services that I've worked on in the past -- just disappears.

frankmcsherry 6y ago

Hi folks. Frank from Materialize here. The main differences you should expect to see are generality and performance. Generality, in that there are fewer limitations on what you can express. Oracle (and most RDBMSs) build their Incremental View Maintenance on their existing execution infrastructure, and are limited by the queries whose update rules they can fit in that infrastructure. We don't have that limitation, and are able to build dataflows that update arbitrary SQL92 at the moment. Outer joins with correlated subqueries in the join constraint; fine. Performance, in that we have the ability to specialize computation for incremental maintenance in a way that RDBMSs are less well equipped to do. For example, if you want to maintain a MIN or MAX query, it seems Oracle will do this quickly only for insert-only workloads; on retractions it re-evaluates the whole group. Materialize maintains a per-group aggregation tree, the sort of which previously led to a 10,000x throughput increase for TPCH Query 15 [0]. Generally, we'll build and maintain a few more indexes for you (automatically) burning a bit more memory but ensuring low latencies. As far as I know, Timescale's materialized views are for join-free aggregates. Systems like Druid were join-free and are starting to introduce limited forms. KSQLdb has the same look and feel, but a. is only eventually consistent and b. round-trips everything through Kafka. Again, all AFAIK and could certainly change moment by moment. Obviously we aren't allowed to benchmark against Oracle, but you can evaluate our stuff and let everyone know. So that's one difference. [0]: https://github.com/TimelyDataflow/differential-dataflow/tree/master/tpchlike https://github.com/TimelyDataflow/differential-dataflow/tree...

cevian 6y ago
Just as a point of clarification: timescale materialized views don't allow joins during materialization. But you can join materialized views arbitrarily at query time. (TimescaleDB engineer here)
sradman 6y ago
Materialize sounds like it has reinvented Michael Stonebraker's StreamSQL [1] and SAP's Continuous Computation Language (CCL) [2] which was created as part of a StreamSQL competitor named Coral8 and lives on in an enterprise product now named SAP HANA Smart Data Streaming. This space has gone by many names, Streaming SQL, Complex Event Processing (CEP), and Event Streaming. I think the Continuous Computation Language (CCL) name captures the essence of these systems: data flows through the computation/query. These systems have always had promise but none have found anything but niche adoption. The two most popular use cases seem to be ETL-like dataflows and OLAP style Window queries incrementally updated with streaming data (e.g. computations over stock tick data joined with multiple data sources). [1] https://en.wikipedia.org/wiki/StreamSQL https://en.wikipedia.org/wiki/StreamSQL [2] https://help.sap.com/doc/PRODUCTION/e1b391d2a3f3439fbab27ed882618873/2.0.00/en-US/streaming_ccl_reference.pdf https://help.sap.com/doc/PRODUCTION/e1b391d2a3f3439fbab27ed8...

albertwang 6y ago

I'm a PM @ Materialize. Regarding the questions from the OP's link: #1 -- Postgres is built in C, while Timely/Differential (which underpin Materialize) are built in Rust. Materialize could be a Postgres extension at some point, but for now we want to control the stack (to more easily do cross-dataflow optimization, etc) #2 -- We are absolutely interested in exposing an API to enable new data sources (and data sinks!). It currently takes a bit of time to understand some basic Timely Dataflow concepts, but we intend on documenting this and opening things up. We're also trying to understand user requirements around things like durability guarantees and UPDATE/DELETEs (Feel free to email me or hit us up in our public Slack as well). If anyone is interested in sending data via HTTP POST, we'd love to hear more: https://github.com/MaterializeInc/materialize/issues/1701 https://github.com/MaterializeInc/materialize/issues/1701.