The next generation of Materialize • Hacker News Board

Re: The next generation of Materialize

#21

Post by mozinator » Mon, Oct 03, 2022, 5:03 PM UTC

benesch wrote: post #19

andrewmutz wrote: post #17

Earlier quoted context omitted.

Kafka Connect can do all this for you if you configure it properly. You would use a postgres "source" connector called Debezium that tracks all changes via postgres replication. All row changes then flow in realtime to Kafka topics. Keeping the data updated in real time in elastic search is also another off-the-shelf Kafka Connector (a "sink" connector)

The biggest problem we've encountered with existing tools in the Kafka ecosystem (and the homegrown solutions that we've seen) is that nearly all of them sacrifice consistency. Debezium and most other Kafka Connect plugins will produce duplicate records upon restart, for example, that are very difficult to correctly deduplicate downstream. Things look right when you first turn on the plugin, but a week later when you…

You can achieve consistency using a transactional outbox and "homegrown" solutions the following way.

Make sure postgresql is configured with `synchronous_commit = remote_apply`

* Create a postgresql logical replication slot which creates a postgresql snapshot in time.

* Start a repeatable read transaction with the snapshot id

* Store all relevant data from the snapshot in sqlite / kv store

* Start listening for WAL changes ( json or protobufs )

* Receive WAL change, mark to postgresql the "write" position of the slot

* Process the data and query all relevant data for materialization from sqlite/kv

* Send data to elasticsearch

* Mark to postgresql the "flush" and "apply" position of the slot

This way you achieve consistency using "homegrown" or Kafka connect possibly too.

Re: The next generation of Materialize

#22

Post by etherealpine » Mon, Oct 03, 2022, 5:04 PM UTC

Does this fix how much of an insane memory hog materialize is? Some queries are just impossible if you can’t use disk. This is why I was forced to stick with Flink. Even though materialize makes things appear stupid simple and easy with SQL, I found that you can only do the most simple streaming views with it. You can’t even do unique counts with this for very long without breaking—and there’s no probabilistic alternatives.

Bad for big data. Great for small and simple data sets. But who is using Kafka with small data?

Also they do not integrate at all with custom data types in Postgres IME. E.g. an enumeration in your table will mean materialize can’t read the table as a source. Lame.

Re: The next generation of Materialize

#23

Post by acjohnson55 » Mon, Oct 03, 2022, 5:09 PM UTC

A lot of folks are reacting to the fact that it seems like this new version won't be able to be self-hosted. And while I get why that's a turn-off to many orgs, there's a massive market for turnkey, managed data products like this. You can get really far without having to staff data or infra engineers. The way I see it, the next phase of "tech" company will deemphasize the amount of in-house engineering outside of their core areas of innovation. There will, of course, be many companies that remain engineering-driven, but I see that as less the assumed norm going forward.

Re: The next generation of Materialize

#24

Post by daricknance » Mon, Oct 03, 2022, 5:26 PM UTC

I've been following the Materialize project and Frank McSherry's work for a long time and seeing them go cloud native should really democratize the use cases for the mainstream. yes there are trade-offs associated with this but this will make it usable by the vast vast majority of the market.

Also I love the tagline "Consistency, Scalability, Low Latency: Pick Three"

Re: The next generation of Materialize

#25

Post by revertts » Mon, Oct 03, 2022, 5:30 PM UTC

andrewmutz wrote: post #17

mozinator wrote: post #15

The poor man's version of Materialize that I implemented is the following: Step 1) Find all "paths" between tables * Use the postgres information schema to get all relations * Use npm library graph-cycles to see if there are any graph cycles. If so.. some relations go on a blacklist. * Use npm library topopsort to sort the graph * Traverse the graph and find all possible paths from and to tables * Generate SQL querie…

Kafka Connect can do all this for you if you configure it properly. You would use a postgres "source" connector called Debezium that tracks all changes via postgres replication. All row changes then flow in realtime to Kafka topics. Keeping the data updated in real time in elastic search is also another off-the-shelf Kafka Connector (a "sink" connector)

It's very hard for Kafka Connect plugins to maintain consistency in all scenarios - both because of the semantics of some upstream databases, and because of the guarantees the connect API itself offers. Hopefully KIP-618 will eliminate more of the edge cases though.

Re: The next generation of Materialize

#26

Post by mytherin » Mon, Oct 03, 2022, 5:35 PM UTC

daricknance wrote: post #24

I've been following the Materialize project and Frank McSherry's work for a long time and seeing them go cloud native should really democratize the use cases for the mainstream. yes there are trade-offs associated with this but this will make it usable by the vast vast majority of the market. Also I love the tagline "Consistency, Scalability, Low Latency: Pick Three"

The tagline is likely inspired by the SQLite tagline [1]: "Small. Fast. Reliable. Choose any three".

[1] https://www.sqlite.org/index.html

Re: The next generation of Materialize

#27

Post by revertts » Mon, Oct 03, 2022, 5:36 PM UTC

mozinator wrote: post #21

benesch wrote: post #19

Earlier quoted context omitted.

The biggest problem we've encountered with existing tools in the Kafka ecosystem (and the homegrown solutions that we've seen) is that nearly all of them sacrifice consistency. Debezium and most other Kafka Connect plugins will produce duplicate records upon restart, for example, that are very difficult to correctly deduplicate downstream. Things look right when you first turn on the plugin, but a week later when you…

You can achieve consistency using a transactional outbox and "homegrown" solutions the following way. Make sure postgresql is configured with `synchronous_commit = remote_apply` * Create a postgresql logical replication slot which creates a postgresql snapshot in time. * Start a repeatable read transaction with the snapshot id * Store all relevant data from the snapshot in sqlite / kv store * Start listening for WAL…

Failures while communicating to the external systems (the kv store and elastic in your example) are usually where this falls down. It's easy to build a system that's consistent ~90% of the time, but if you want to build a system where things like failures during snapshot write or failures during export to elastic are handled properly it starts getting complex (you will need to find ways to recover and retract data, or build smarts into the consumer to query around aborts, or find a way to do a 2PC-esque dance with the external system a la Kafka's transaction support, etc.). Getting to full consistency isn't easy.

Re: The next generation of Materialize

#28

Post by mozinator » Mon, Oct 03, 2022, 5:42 PM UTC

mozinator wrote: post #21

benesch wrote: post #19

Earlier quoted context omitted.

The biggest problem we've encountered with existing tools in the Kafka ecosystem (and the homegrown solutions that we've seen) is that nearly all of them sacrifice consistency. Debezium and most other Kafka Connect plugins will produce duplicate records upon restart, for example, that are very difficult to correctly deduplicate downstream. Things look right when you first turn on the plugin, but a week later when you…

You can achieve consistency using a transactional outbox and "homegrown" solutions the following way. Make sure postgresql is configured with `synchronous_commit = remote_apply` * Create a postgresql logical replication slot which creates a postgresql snapshot in time. * Start a repeatable read transaction with the snapshot id * Store all relevant data from the snapshot in sqlite / kv store * Start listening for WAL…

Looked in the source of Materialize and it looks like this is exactly what they are doing.

They are using Debezium + Kafka for receiving the WAL changes. And using send the processed WAL offsets back using a Kafka topic to Debezium + Postges. This way they can achieve consistency

Re: The next generation of Materialize

#29

Post by daricknance » Mon, Oct 03, 2022, 5:54 PM UTC

mytherin wrote: post #26

daricknance wrote: post #24

I've been following the Materialize project and Frank McSherry's work for a long time and seeing them go cloud native should really democratize the use cases for the mainstream. yes there are trade-offs associated with this but this will make it usable by the vast vast majority of the market. Also I love the tagline "Consistency, Scalability, Low Latency: Pick Three"

The tagline is likely inspired by the SQLite tagline [1]: "Small. Fast. Reliable. Choose any three". [1] https://www.sqlite.org/index.html

ah makes sense. something something great artists steal

Re: The next generation of Materialize

#30

Post by mozinator » Mon, Oct 03, 2022, 6:06 PM UTC

revertts wrote: post #27

mozinator wrote: post #21

Earlier quoted context omitted.

You can achieve consistency using a transactional outbox and "homegrown" solutions the following way. Make sure postgresql is configured with `synchronous_commit = remote_apply` * Create a postgresql logical replication slot which creates a postgresql snapshot in time. * Start a repeatable read transaction with the snapshot id * Store all relevant data from the snapshot in sqlite / kv store * Start listening for WAL…

Failures while communicating to the external systems (the kv store and elastic in your example) are usually where this falls down. It's easy to build a system that's consistent ~90% of the time, but if you want to build a system where things like failures during snapshot write or failures during export to elastic are handled properly it starts getting complex (you will need to find ways to recover and retract data, o…

This has been my experience too. Instead of going the logical replication route I tend to leverage the transactional outbox to achieve consistency in the application layer instead.

So when I transact data into tables I immediately fetch the latest outbox id.

And then when query from Elasticsearch I first fetch what the last outbox id of the processed data is.

This way I know if the transaction was already processed into Elasticsearch or not. Repeat. Until outbox id of Elasticsearch is equal or higher than the outbox id of the mutation.

This way I don't have to use logical replication, no k/v store and I can just use a script that fetches and processes the latest outbox changes on a loop.