Comment by estebarb | Hacker News Reader

Comment by estebarb | Hacker News Reader

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.

I really want to do streaming queries over updates and inserts in a normal SQL database. Imagine writing

    SUBSCRIBE SELECT customers.id, customers.name 
    FROM EVENTS(customer_purchases) AS cpe
    LEFT OUTER JOIN customers ON cpe.row.customer_id = customers.id
    WHERE cpe.row.product_id = '123abc' AND cpe.type IN ('update', 'insert')

in postgres itself and just getting every incoming purchase for a particular product.

I wonder if Materalize gets you close?

Materialize excites me, but probably can't be used for the current problems I'm working on, because of the operational overhead we'd be putting on our clients. Streaming SQL queries inside an existing well-understood database would be the killer feature.

https://ksqldb.io/ is great for this, assuming you're already using Kafka.

Someone with more in depth knowledge can tell me why this isn't a good idea, but I'm pretty sure you can get something very close to this with graphql subscription query against hasura backed with postgres

Reply on news.ycombinator.com