Materialize Blog

We've had success using DBT incremental models for this. Deletes aren't supported though, but for our use case that's fine and do a nighly refresh on the whole. Otherwise the best alternative would be something like https://materialize.com

I think these are great suggestions. It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL. 2 & 3 are my biggest pain points. I can't just extract functions like I can with a regular programming language. Instead, SQL queries get increasingly complex with no great tools to manage that. For 3, products like https://materialize.com/ look interesting for being able to create derived materialized views that can efficiently be kept up to date.

This is a really interesting area, would love it if Postgres provided a strong option here. In the meantime, Materialize has very good Postgres integration if it might work for you: https://materialize.com/

Materialize (https://materialize.com/) is capable of performing SQL operations over a stream, using incremental calculation based on differential dataflow. CockroachDB is a distributed SQL database, with strong consistency. I think that Yugabyte is similar. Support for realtime changes, including queries over those changes, is better in other databases, like RethinkDB, OVSDB or Firebase. Relational is not always the best way to store your data. Sometimes a graph model is better. Or for full text search something specialized is better. In that sense dgraph or elastic search may be better OLTP databases in some cases. Columnar storage, like used in Vertical or BigQuery have several advantages in processing and data compression. But implementing it in PG or MySQL I think that would require almost a full rewrite.

Fantastic to see a logic programming/datalog/prolog-like from a big actor like Google. Perhaps this can make more such tools become mainstream. Only I wish we had such a language for a more generic streaming / data processing framework, such as materialize [1]. I was very optimistic about that for some time, as the guy behind the technology, Frank McSherry, wrote some datalog tooling as well [2]. [1] https://materialize.com/ [2] https://github.com/vmware/differential-datalog

Thanks! I'm also looking forward to finishing the Materialize[1] integration I've been working on (currently blocked while I wait for them to release some features). Although Crux has datalog, you can't subscribe to datalog queries. But with Materialize, you can basically subscribe to arbitrary SQL queries. I have a branch that lets you define the SQL queries on the backend and then subscribe to the results via Biff's existing subscription system. [1] https://materialize.io

It's hard to write something that is both accessible and well-motivated. The best uses of category theory is when the morphisms are far more exotic than "regular functions". E.g. it would be nice to describe a circuit of live queries (like https://materialize.com/ stuff) with proper caching, joins, etc. Figuring this out is a bit of an open problem. Haskell's standard library's Monad and stuff are watered down to the point that they are barely category theory, but they are also quite useful and would not have been readily invented without category theory. See even if you have no taste for "abstract nonsense", maybe you can still accept the fact that its left a trail of more accessible "semi-abstract semi-nonsense" in its wake. This stuff takes time. If it's not your cup of tea, no need to make yourself an early adopter. See also things like Lean where fancy type systems are finally reaching "regular non-CS" mathematicians with great success.

Good callout. Sometimes, I joke that warehouse ingestion latency is the bane of my existence, but it's improving... Our average customer runs Hightouch syncs roughly every hour, but we can actually run syncs up to every minute! HT has a lot of optimizations like only sending changes to destinations instead of all data every run. On the warehouse side, we're seeing a lot of improvements. BigQuery has streaming insert APIs [0] implemented with a parallel database on the backend that's joined at read time. Combined with timestamp partitioned tables (sortable) and our in-warehouse diff'ing, you can actually create a streaming pipeline in Hightouch. Some companies like JetBlue are doing cool stuff with lambda views on top of Snowflake [1]. Our power users at Hightouch are running syncs as fast as every minute. For wider context, we find 90%+ of business use cases to be just fine in batch. It's amazing to see how many people are still replacing... manual CSV workflows... with Hightouch :) That said, there are some use cases for truly real-time workflows (e.g. a post-checkout email), and for that, customers either implement outside of Hightouch or lately, we've been fiddling around with letting customers plug directly into streams like Kafka, Kinesis, PubSub - though they lose the power of SQL aggregations _for now_. Streaming SQL databases like Materialize [2] will fix this fundamentally, and Hightouch can connect to them. Email [email protected] if you want to try any of…

Snowflake's `Snowpark` product that they recently announced, which is to bring Spark-like APIs to Snowflake. Having a DS background, I love what SQL-orchestration tool dbt (and peers) have enabled: data consumers to rapidly create our own safe data pipelines. There's easily a 10x productivity improvement for most of my transformation pipelines vs. when I write them in Python or PySpark. But batch ML and SQL are not that friendly (even BigQuery ML is too limiting). I end up butchering dbt's value (simplicity and iteration speed), splitting the DAG into pieces and orchestrating them with Airflow so that I can wedge in other non-dbt parts (like feature engineering, inference, logging, detecting stale models, ...). This isn't what the future looks like. I've tried switching to Databricks, but do not see this as the path forward for unioning the warehouse + batch ML. Hopefully Snowpark is a step forward :) ------------------- Separately, https://materialize.com/ is something I'm paying attention to! Being able to implement all of my SQL-based pipelines as materialized views would be immensely valuable. They recently raised capital and they could become huge.

Materialize https://materialize.io/ Incremental update/materialization of database views with joins and aggregates is super interesting. It enables listening to data changes, not just on a row level, but on a view level. It's an approach that may completely solve the problem of cache invalidation of relational data. Imagine a memcache server, except it now also guaranties consistency. In addition, being able to listen to changes could make live-data applications trivial to make, even with filters, joins, whatever. Similarly, someone is developing a patch for postgres that implements incrementally updating/materializing views[1]. I haven't tried it so I can't speak of its performance or the state of the project, but according to the postgres wiki page on the subject [2] it seems to support some joins and aggregates, but probably not something that would be recommended for production use. [1] https://www.postgresql-archive.org/Implementing-Incremental-View-Maint… [2] https://wiki.postgresql.org/wiki/Incremental_View_Maintenance

This video is really cool. I’ve been following dataflow approaches for a while, including some of Frank McSherry’s (usually enjoyable) articles. None of the comments mention https://materialize.io so I may as well (an open source commercial offering based off these concepts). Watching this explanation I’m slightly curious whether things like materialize and noria are a bit limited in that this could be a paradigm for an actual functional reactive programming language rather than specifically a “data” thing. It appears to have the structure of nested contexts (loops, scopes, etc) advocated by structured programming (ie “goto considered harmful”). It can reliably calculate an answer at each point in time for each state of input, concurrently and with parallelism. Even if there are multiple inputs with their own notion of time (not covered in the video). That’s, like, the holy grail of PLT these days, isn’t it? Or am I missing something?

Overall, I love this approach, but the big pain for me is the lack of incremental view refreshes. I end up needing to recreate giant tables each refresh even though the underlying changes are small. Sure I could implement that myself, but that sacrifices the correctness guarantees that materialized views provide! Two things that would help: 1. Getting Incremental View Maintenance (IVM) [1] into Postgres. It looks like work is beginning on this but it's been 7+ years coming. If there are any Postgres devs looking at this, I'm cheering for you! 2. There's a commercial group doing an implementation of this, Materialize [2], but they don't have any publicly released product yet. I mention it because their interview on Data Engineering Podcast is really good [3] and I really like their focus on correctness by working from replication logs. [1] https://wiki.postgresql.org/wiki/Incremental_View_Maintenance [2] https://materialize.io/ [3] https://www.dataengineeringpodcast.com/materialize-streaming-analytics…

Did I miss in the article where OP reveals the magic database that actually does this? 3rd party solutions like https://readyset.io/ and https://materialize.com/ exist specifically because databases don’t actually have what we all want materialized views to be.

https://materialize.com/ provides another approach, based on "timely dataflow" (https://timelydataflow.github.io/timely-dataflow/) - originated at MS.

I agree. So many disparate solutions. The streaming sql primitives are by themselves good enough (e.g. `tumble`, `hop` or `session` windows), but the infrastructural components are always rough in real life use cases. crossing fingers for solutions like `https://github.com/feldera/feldera` to be wrapped in a nice database, `https://materialize.com/` to solve their memory issues, or `https://clickhouse.com/docs/en/materialized-view` to solve reliable streaming consumption. Various streaming processing frameworks often have domain specific languages with a lot of limitations of how to express aggregations and transformations.

> [...] `https://materialize.com/` to solve their memory issues [...] Disclaimer: I work at Materialize Recently there have been major improvements in Materialize's memory usage as well as using disk to swap out some data. I find it pretty easy to hook up to Postgres/MySQL/Kafka instances: https://materialize.com/blog/materialize-emulator/

A couple of years ago Materialize had all the buzz, not sure what is the difference. https://materialize.com/

Materialize | https://materialize.com/ | Staff Security Engineer | $200- 230k NYC (HQ) or United States We are looking for a Staff Security Engineer on our Cloud team who will own the security of our infrastructure and product. (5+) years of experience as a security-focused engineer. https://boards.greenhouse.io/materialize/jobs/5220351004

We haven't benchmarked TimescaleDB, so I can't say. Results tend to vary heavily by workload, too. What I can say is that the research at the heart of Materialize (https://dl.acm.org/doi/10.1145/2517349.2522738) allows us to efficiently maintain computations that are more complex than what a lot of other IVM systems can handle. Your best bet is to run your own benchmark of both systems using data that's representative of your workload. We offer a free seven day playground if you'd like to run such a benchmark: https://console.materialize.com/account/sign-up We also have a community Slack where a number of Materialize employees hang out and answer questions: http://materialize.com/s/chat

You already mention Materialize -- have you looked at the SUBSCRIBE feature? https://materialize.com/docs/sql/subscribe/ It's fairly straightforward to write a websocket endpoint in whatever backend you have that issues a SUBSCRIBE query on connect and then continuously forwards the updates to the client. The complicated parts you'd need to build are on the frontend -- handling updates and merging them into whatever state you render from. Disclaimer -- I work at Materialize -- but it does seem like the perfect fit for what you need (efficiently updated real-time materialized views).

I recommend having a pre-built integration that dumps all changes to the database in a Debezium CDC compatible format. Not sure how you'd normalize Triplit changes into Debezium updates, but something to think about. Debezium CDC format lets you pipe changes from one DB into a stream system like Kafka, and then out of Kafka into another DB on the other end. It's handy. https://debezium.io/documentation/reference/2.5/transformations/event-… For example, the original method for connecting Postgres to Materialize.com was using a Debezium stream: https://materialize.com/docs/ingest-data/cdc-postgres-kafka-debezium/

I work at Materialize, and we’ve used this to power the syntax diagrams in our docs since the very beginning. Example: https://materialize.com/docs/sql/create-source/kafka/ I think it’s a great tool!

Materialize | New York, New York (HQ) | https://materialize.com Materialize is an Operational Data Warehouse: A cloud data warehouse with streaming internals, built for work that needs action on what’s happening right now. Keep the familiar SQL, keep the proven architecture of cloud warehouses but swap the decades-old batch computation model for an efficient incremental engine to get complex queries that are always up-to-date. Materialize is the operational data warehouse built from the ground up to meet the needs of modern data products: Fresh, Correct, Scalable — all in a familiar SQL UI. Staff Product Manager - https://grnh.se/69754ebf4us Engineering Manager, Storage - https://grnh.se/3771f7734us

Some recent solutions to this problem include Differential Dataflow and Materialize. It would be neat if postgres adopted something similar for live-updating materialized views. https://github.com/timelydataflow/differential-dataflow https://materialize.com/

You can do all of this with https://materialize.com, and you don’t need to write it in Java. Just connect it to a Postgres instance and start creating materialised views using SQL. These views then auto update. So much so, that you can create a view for the top 10 of something, and let it sit there as the list updates. Otherwise just use normal select statements from your views using any Postgres client.

https://materialize.com/ are doing interesting things with this. Although not inside Postgres (they use a few bit of Postgres as components).

> Application can tolerate stale data... Projects like https://readyset.io/, https://materialize.com/, https://github.com/mit-pdos/noria can keep your materialized views up to date as the underlying base tables change.

See also https://readyset.io/ and https://materialize.com/ There's also the exotic https://dynimize.com/ (unsure of their current state).

Even more context: SQL interface on Differential/Timely Dataflow = https://materialize.com/ Python interface on Differential/Timely Dataflow = https://bytewax.io/ Grossly oversimplified, but directionally correct.

Materialize | Senior Software Engineer - Cloud Infra, QA, Security Engineer, Senior SWE's | Full time | On-site NYC office | Remote friendly for some roles Materialize is deployed on top of streaming message queues, like Apache Kafka, and allows users to specify arbitrary SQL views on top of their queues and query those views instantly. Stream processing software like Kafka has greatly improved the end-to-end latency of moving messages around in real time, but today there’s no good solution for making use of that data easily. Materialize brings the user-facing simplicity of SQL views – for arbitrary SQL queries, not just a SQL-inspired minimal language – to the streaming data ecosystem. https://materialize.com/careers/