Hacker News Reader

Comment by rst | Hacker News Reader

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-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.

View on HN

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...

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)

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

[2] https://help.sap.com/doc/PRODUCTION/e1b391d2a3f3439fbab27ed8...

The projects you've mentioned are attempts to address stream processing needs with a SQL-like language. That is fundamentally different from providing incremental view maintenance of actual SQL using streaming techniques (what Materialize does).

If you want to maintain the results of a SQL query with a correlated subquery, StreamSQL in Aurora did not do that (full query decorrelation is relatively recent, afaiu). I have no idea what TIBCO's current implementation does.

If you want to maintain the results of a SQL query containing a WITH RECURSIVE fragment, you can do this in differential dataflow today (and in time, Materialize). I'm pretty sure you have no chance of doing this in StreamSQL or CCL or CQL or BEAM or ...

The important difference is that lots of people do actually want to maintain their SQL queries, and are not satisfied with "SQL inspired" languages that are insert-only (Aurora), or require windows on joins, or only cover the easy cases.

> The projects you've mentioned are attempts to address stream processing needs with a SQL-like language. That is fundamentally different from providing incremental view maintenance of actual SQL using streaming techniques (what Materialize does).

With all due respect, CREATE SINK and CREATE SOURCE are SQL-like. I would argue that the pipeline created from the set of SINKs and SOURCEs is the key concept to grasp for developers new to your platform. The purity of the PostgreSQL CREATE MATERIALIZED VIEW syntax and other PG/SQL constructs seems like a minor selling point, in my (very narrowly informed) opinion. I hope I'm wrong.

Our difference of opinion involves marketing language and perceived differentiators. There are some important use cases for continuous SQL queries over Kafka-like data streams that remain unaddressed (as far as I know). I hope Materialize gains traction where others have failed to do so. If PG/SQL compatibility was the only thing holding back this style of solution then kudos to you and your team for recognizing it. Good luck (honestly).

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.

About a year ago Vitess merged in support for what we call 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!

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.

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

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...

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#DWHSG914...

*Almost, because the surface area is truly astonishing.

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?

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).

Reply on news.ycombinator.com