5 ms·
We Built a Streaming SQL Engine
- posnet 3y agoIn the streaming SQL space, I feel like at this point it's essential to start off with saying what you bring to the table compared with Materialize. No matter what you think about how the Materialize, the company, has executed, differential dataflow should still be the standard that all streaming SQl hold themselves to. If you can't do recursive CTE's streaming, then you are worse than Materialize and you need to show that you add something above and beyond. Note, I don't work for, or have any affiliation with the company, I just enjoy and wish more people have read, 'Scalability! But at what COST?'.
- ramraj07 3y agoI was hyped about materialize in the beginning but now looks like they’re just cloud focused and it’s unclear who is supposed to be their customer at all. Also I swear I saw their original docs said they can do window functions but not anymore.
- higeorge13 3y agohttps://materialize.com/docs/transform-data/patterns/window-functions/ https://materialize.com/docs/transform-data/patterns/window-... Are you sure they don’t?
- ergl 3y agoIf you click "Get access" on their main page it says: > It seamlessly integrates with your existing database, saving you from complicated setup procedures. So maybe their advantage over Materialize is that this can work on top of whatever database you already have?
- amath 3y agoThat paper is too good not to link - https://www.usenix.org/system/files/conference/hotos15/hotos15-paper-mcsherry.pdf https://www.usenix.org/system/files/conference/hotos15/hotos... I hope more companies can be built around timely-dataflow.
- necubi 3y agoAnd yet today Materialize is distributed ;)
- maor10 3y agoHi, author here :) I think there are two critical differences, one on the tech side and one on the UX side- On the UX side, we're seated behind your database, and our goal is for you to only ever interact with your original database, and never have to query us directly. This has a lot of implications when it comes to migrations on your database (you call epsio.create_view in your original DB which is transaction safe), and not having the backend interact with two databases. On the tech side, we're built from ground zero to be above storage, whereas differential dataflow (the package Materialize is built upon) is strongly built to be above memory (we actually played with the idea of using differential with spillover to disk- was too slow). This means we actually lose when it comes to speed (e.g. we may take ~50 milliseconds more which can be a big deal in realtime applications), but have huge gains in cost, since we're trading compute for storage. Check out our FAQ- we have a nice little table for comparison :)
- ericHosick 3y agoThis looks super cool. Do you support Common Table Expressions? What kind of overhead have you seen? Does this negate the need for indexes in some cases?
- reactordev 3y agoI have so many issues with the underlying context of this article. Its choice of neo-christian metaphor. It’s assumptions of good vs evil. The subtle hints that they are your savior yet can’t muster more than 2 quotes on their tech from customers. The article could have easily gone with another metaphor or needle->haystack rather than invoke subliminal God complexes. I’m also curious as to what more you bring to the table over Materialize. CTE’s, etc. How about a blog post where you leave the holier-than-thou christian symbolism for actual tech features and why one should consider you for their data engine needs. Beyond trying to see if The Lord has admin access.
- j45 3y agoIt might not be for me either, but I found I was able to read it through a lens of humour and see some of it as an attempt at humour. The world is not responsible to not trigger us, as I’m sure references to some things do.
- reactordev 3y agoI don’t find cults humorous. I know what you are saying though I just think there’s dozens of other ways that could have been said. It shows a lack of professionalism in their communications department to me. Be that as it may, I still read the article, I browsed the site, I looked for more tech evidence and reasoning for adoption and found it lacking.
- camgunz 3y agoThere's no reference to or invocation of cults, just a playful use of the cartoonish idea of evil people. I'd find it distasteful if there were like, references to kool-aid or Manson or whatever, but there aren't. Is it your position no one should poke fun at the idea of "evil"? Honestly that just seems like no fun.
- reactordev 3y agoBelief in a mystical Lord is a cult belief. Yes they use it in satire but I don’t think religious tongue in cheek has any place in the business world unless your business is selling snake oil or bigotry.
- adamzochowski 3y agoIsn't this what is done by some SQL engines with materialized views? They update view data (counts and group by and sums) as the source table data changes. But not by rerunning the view, but smartly updating its contents based on the change that happened. There are tons of limits in these views, as they have to provide stable results. See ms sql documentation on limits of streamed/indexes sql views https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/v...
- adeelk93 3y agoThose limits seem to be decreasing every year though. Lot of competition in the analytical SQL space. Also worth noting Delta Live Tables, another very popular streaming SQL implementation. https://www.databricks.com/product/delta-live-tables https://www.databricks.com/product/delta-live-tables
- wredue 3y agoAlso lots of cubes can do similar things. Seems the author made a lot of assumptions without a lot of research. For one example, I would strongly doubt that count(*) without a where clause isn’t already highly optimized.
- EmilStenstrom 3y agoThe author did lots of research. Here's a comparison of some common other ways to do this: https://docs.epsio.io/faq/ https://docs.epsio.io/faq/
- phamilton 3y ago> I would strongly doubt that count() without a where clause isn’t already highly optimized It's actually not well optimized in PostgreSQL because of MVCC. See https://wiki.postgresql.org/wiki/Slow_Counting https://wiki.postgresql.org/wiki/Slow_Counting TL;DR: The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table. PostgreSQL must walk through all rows to determine visibility.
- throwaway295729 3y agoI understand how a streaming SQL engine works, but I’m wondering what the real world use cases for this are. Is this more for large scale enterprises?
- _dark_matter_ 3y agoOne big one I've seen are online ML models. You essentially have a feature store built on SQL ETLs. The ML engineers utilize this to build models, and deploy those to production. However when using the models for prediction, the data hasn't been ETLed (think: credit modeling). One solution here is to use the same SQL for the ETL and for the online transformation to query the model. It can guarantee the biz logic in the features matches between training and online.
- gunnarmorling 3y agoA large variety of use cases really, e.g. fraud detection via pattern matching on a stream of credit card transactions, the creation of denormalized views (seeing this a lot in the context of Debezium change event streams from RDBMS), updating real-time dashboards e.g. based on incoming order data, etc. I don't think it depends at the size of the company, we see users of all kinds of sizes. (Disclaimer: I work for decodable.co, a managed stream processing platform leveraging Flink SQL)
- smlavine 3y agoFor the first count(*) example, I don't understand why that couldn't be solved with a TRIGGER on INSERT and DELETE?
- ddorian43 3y agoYou will get bottlenecked on concurrent updates in a single row. Imagine you have 10K/s of insert/deletes on that table with replication.
- gavinray 3y agoOn the topic of streaming SQL, I found this new startup because one of their team was making a ton of contributions to "sqlparser-rs" and Arrow Datafusion. I thought it looked interesting (even though I personally don't have a use for it) and the technology it's built on is solid: https://www.synnada.ai/ https://www.synnada.ai/
- abrazensunset 3y agoThis has loose overlap with: - Materialize - Flink SQL - Arroyo - Readyset - RisingWave - Timeplus - Pathway - Dozer - ReadySet - Snowflake dynamic tables - Native materialized views in OLTP databases - Just having a stack of views in your db - Poor man's MVs with triggers All subtly different on every spectrum from consistency, UDF support, operator support, latency, scaling/state limits, source/sink integrations, and compatibility with existing protocols. What seems unique is the focus on "writebacks to the source without Kafka/Connect in between", instead of having either a built-in cache, serving as a stream processor, or both. It looks like the built-in cache is still available through the FDW deployment pattern. They note that relative to the source tables they are eventually consistent (of course, unless you want to delay transaction writes) but it's not clear what other consistency aspects they respect (such as preserving transactions end to end). Overall this looks like it's designed to overcome materialized view limitations (which in popular OLTP dbs are pretty severe w.r.t. either what operations are supported, latency, or both) compared to other solutions that basically move the action downstream...curious if it will see much use, or if they'll inevitably introduce sinks and direct access to see if they can compete in the "live ODS" segment with Materialize and RisingWave. edit: to make my comment more clear: this is a new entrant in a crowded space with several sophisticated, established players and the main differentiation is the deployment pattern. I'd be curious to know if anything else sets them apart
- necubi 3y agoI'm with Arroyo [0] — thanks for the mention! I'd be interested to see someone from Epsio chime in with where exactly they're positioning, but you're right that this is (recently) a very crowded space. I think you can somewhat arbitrarily draw a line between systems like Materialize/RisingWave that are focused on materialized view maintenance (often reading change feeds from your OLTP database) and stream processors like Flink/Arroyo that are focused on supporting more operational use cases and work with a lager variety of sources and sinks. Epsio seems like it's working primarily in the former mold, with fast incremental computation of materialized views. Unlike Materialize/RisingWave it seems to be designed to run in front of your database, with all queries going through it. ReadySet is a really cool project in the query caching/materialized view space that I think doesn't get enough attention. Rather than making you define your materialized views ahead of time, it acts as an automated caching layer on top of postgres/mysql that performs incremental computation for components of query graphs. As someone who's been in the streaming space for years, it's really exciting to see so much energy in the space in the past couple of years after a long period of stagnation, with everyone trying to figure out the programming and deployment models that make the most sense. Most folks right now are gravitating towards materialized views as the model, largely because it's easy and familar for users. But ultimately I think this approach will end up too limited for most use cases and will remain valuable but somewhat niche. [0] https://github.com/ArroyoSystems/arroyo https://github.com/ArroyoSystems/arroyo
- robertlagrant 3y agoThis has been posted...a lot, recently. We Built a Streaming SQL Engine (epsio.io) 83 points by croes 10 hours ago | flag | past | 29 comments How We Built a Streaming SQL Engine (epsio.io) 8 points by nightmaremard 3 days ago | flag | past | discuss How To Build a Streaming Engine (epsio.io) 4 points by daymareman 20 days ago | past How We Built a Streaming SQL Engine (epsio.io) 1 point by dkgs1998 23 days ago | past How we built a Streaming SQL Engine (epsio.io) 2 points by giladkkl 37 days ago | past How We Built a Streaming SQL Engine (epsio.io) 2 points by dkgs1998 38 days ago | past
- deleted 3y ago[deleted]
- pvg 3y agoOnly matters if there's been discussion and there hasn't.
- sorenbs 3y agoAlso check out https://github.com/feldera/feldera https://github.com/feldera/feldera in this space. And do read the paper - It's a cool addition to the Materialize approach.
- leontrolski 3y agoI've been playing around with writing an application-orientated Incremental View Maintenance library along these lines: https://stepping.site https://stepping.site It's based off of the DBSP paper, which counts Materialize's Frank McSherry as an author. Definitely not ready for the prime time, but any feeback welcome!
- gz09 3y agoVery nice! Are you using the dbsp implementation from the paper authors for this (e.g., https://github.com/feldera/feldera/tree/main/crates/dbsp https://github.com/feldera/feldera/tree/main/crates/dbsp)?
- leontrolski 3y agoI didn't - I wanted to build something I could understand, and the rust implementation comes with a load of (reasonable) complexity, some resulting from the execution model, some from performance requirements. stepping has 3 Zset implementations - in memory, sqlite and postgres. Currently considering whether to write a small rust one. There's some more details here: https://stepping.site/docs/internals/how-it-works/ https://stepping.site/docs/internals/how-it-works/
- gz09 3y agoneat! I sent you an email
- leontrolski 3y agoAhh, I see you're a cofounder at feldera :) I have a couple of dbsp questions if you're feeling generous and have 10mins free - reach out ojhrussell at gmail
- skadamat 3y agoHow well does this work with the bytewax or quix libraries?
- amath 3y agoI am often confused on how things like imperative stream processing frameworks are supposed to work with the streaming database, if at all. Like what are you supposed to interact with the streaming database with? Is it only for streaming ETL? Do you need another application layer that can natively integrate, orchestrate and function. Maybe using a stream processing framework with a streaming database could facilitate a full application stack.
- skadamat 3y agoYeah if anything it seems like the streaming SQL engines _replace_ libraries like bytewax or quix
- gigatexal 3y agoI didn’t care for all the religious stuff in the blog post and there’s no run-it-myself container or method that I can tell. I have to fill out a form to “get started” which flow is very much “we don’t have a pricing page we have a contact us for more info” corporate selling vibe. I don’t care for it. So I’ll not invest time in playing with it and maybe eventually selling it internally to my team.
- deleted 3y ago[deleted]