Neat examples, and I agree that extending SQL like this has real potential. Another project along very similar lines is https://github.com/ryrobes/larsql
I work on https://github.com/xtdb/xtdb which is broadly Postgres-compatible with a few key SQL extensions (SQL:2011 bitemporal tables + immutability, first-class nested data, pipeline syntax, etc). Built on Arrow and the JVM but is otherwise mostly from scratch.
XTDB is perhaps not directly relevant to the topic at hand, but I am a firm believer that ML workflows can benefit from robust temporal modelling.
In my experience, usually along the lines of "what was the state of the world?" (valid-time as-of query) instead of "what was the state of the database?" (system-time as-of query).
> it's kind of frustrating that XTDB has to be its own top-level database instead of a storage engine or plugin for another. XTDB's core competence is its approach to temporal row tagging and querying. What part of this core competence requires a new SQL parser?
Many implementation options were considered before we embarked on v2, including building on Calcite. We opted to maximise flexibility over the long term (we have bigger ambitions beyond the bitemporal angle) and to keep non-Clojure/Kotlin dependencies to a minimum.
I saw the post on the clojure subreddit! I’m stoked to try it out.
Ever since I saw Martin Kleppman’s “Turning the database inside out” talk I’ve wanted an easy way to hook into a transaction log. Apache samza/kafka is very cool but I’m not going to set it up for personal projects. It’d be VERY cool to make materialized views straight from the log!!
> It’d be VERY cool to make materialized views straight from the log!!
This is what Materialize does. You point it at some PG (or MySQL, or... probably lots more by now) sources, and then you can define arbitrary views on top of those sources using SQL (with the full power of relational logic: fully precise joins, etc.) The views are maintained incrementally, so they update almost instantly when the underlying data updates; you don't have to manually refresh them.
Disclaimer: I worked on Materialize for five years, and it is a commercial, proprietary project. But it is source-available (https://github.com/materializeinc/materialize) under a BSL-style license, and it has fairly generous terms for free usage.
XTDB doesn't currently solve the problems of user-defined projections (via stored procedures, triggers, Incremental View Maintenance etc.) or multi-partition scaling.
> which is more efficient than "hacking it" with recursive queries in a relational db
It seems to me that the way recursive CTEs were originally defined is the biggest reason that relational databases haven't been more successful with users who need to run serious graph workloads - in Frank McSherry's words:
> As it turns out, WTIH RECURSIVE has a bevy of limitations and mysterious semantics (four pages of limitations in the version of the standard I have, and I still haven't found the semantics yet). I certainly cannot enumerate, or even understand the full list [...] There are so many things I don't understand here.
reply