4 ms·

> I guess it's relatively easy to do This is a very hard problem to do the right way and probably would need some changes on the RDBMS itself. You would need t

by mtrovo 5y ago

> I guess it's relatively easy to do This is a very hard problem to do the right way and probably would need some changes on the RDBMS itself. You would need to monitor all tables that might affect your query for changes and how these changes affect your query (say you're just reading a value, aggregating with sum, doing average with count of rows, the list goes on). Add more complexity on top of that if you want to support querying from other views that also aggregate the data on your query. As pointed on another comment there's DB Noria [0] but I'm not sure how production ready it's right now. You an idea of the complexity of the task on a interview with one of the project leads [1]. [0] https://github.com/mit-pdos/noria https://github.com/mit-pdos/noria [1] https://corecursive.com/030-rethinking-databases-with-jon-gjengset/ https://corecursive.com/030-rethinking-databases-with-jon-gj...

vaughan 5y ago
> the right way Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring. > how these changes affect your query Yeh, this is where it gets tricky. I think it can be simpler though if the SQL is kept simple with less exotic sub-queries, CTEs, JOINs. Thanks for the links.
- cirego 5y ago
  > Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring. Unfortunately, it turns out that recursively refreshing views still leads to surprising behavior. I think post summarizes the problem quite nicely: https://scattered-thoughts.net/writing/internal-consistency-in-streaming-systems/ https://scattered-thoughts.net/writing/internal-consistency-.... If you cannot refresh all of the views, at a single point in time, then there will be internal inconsistencies in your dataset. When looking at automatic refreshing, simple triggers and `LISTEN/NOTIFY` don't scale, as was mentioned in the comment regarding Hasura's multiplexing. I think, in the absence of incrementally maintained views, their multiplexing strategy is a good compromise for databases like postgres. However, it should be noted that continuous query / subscription of views is the exact scenario under which incremental computation will provide both lower latency and greater resource efficiency.
  - vaughan 5y ago
    > If you cannot refresh all of the views, at a single point in time, then there will be internal inconsistencies in your dataset. In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined. > incremental computation will provide both lower latency and greater resource efficiency. Wish we had some better database primitives to assemble rather than building everything on Postgres - its not ideal for a lot of things.
    - cirego 5y ago
      > In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined. I see that now -- makes sense! > Wish we had some better database primitives to assemble rather than building everything on Postgres - its not ideal for a lot of things. I'm curious to hear more about this! We agree that better primitives are required and that's why Materialize is written in Rust using using TimelyDataflow[1] and DifferentialDataflow[2] (both developed by Materialize co-founder Frank McSherry). The only relationship between Materialize and Postgres is that we are wire-compatible with Postgres and we don't share any code with Postgres nor do we have a dependence on it. [1] https://github.com/TimelyDataflow/timely-dataflow https://github.com/TimelyDataflow/timely-dataflow [2] https://github.com/TimelyDataflow/differential-dataflow https://github.com/TimelyDataflow/differential-dataflow
      - vaughan 5y ago
        > I'm curious to hear more about this I think the [FoundationDB layer concept][1] said it well: "When you choose a database today, you’re not choosing one piece of technology, you’re choosing three: storage technology, data model, and API/query language..." I like the idea of [Apache Calcite][2] that provides an API to access the query planner. I think if you had more convenient access to some of the underlying components you could build a lot of cool stuff. There's too much magic where you punch in an SQL command or a config and hope it eventually does what you need. I haven't look too much into the internals, but I'm keen to do so soon. [1]: https://apple.github.io/foundationdb/layer-concept.html https://apple.github.io/foundationdb/layer-concept.html [2]: https://calcite.apache.org/ https://calcite.apache.org/
- mr_toad 5y ago
  > Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You might find it’s cheaper to do just that. It might cost you in computational resources, but save you millions in engineering time.

by mtrovo 5y ago

vaughan 5y ago

> the right way Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring. > how these changes affect your query Yeh, this is where it gets tricky. I think it can be simpler though if the SQL is kept simple with less exotic sub-queries, CTEs, JOINs. Thanks for the links.

cirego 5y ago
> Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring. Unfortunately, it turns out that recursively refreshing views still leads to surprising behavior. I think post summarizes the problem quite nicely: https://scattered-thoughts.net/writing/internal-consistency-in-streaming-systems/ https://scattered-thoughts.net/writing/internal-consistency-.... If you cannot refresh all of the views, at a single point in time, then there will be internal inconsistencies in your dataset. When looking at automatic refreshing, simple triggers and `LISTEN/NOTIFY` don't scale, as was mentioned in the comment regarding Hasura's multiplexing. I think, in the absence of incrementally maintained views, their multiplexing strategy is a good compromise for databases like postgres. However, it should be noted that continuous query / subscription of views is the exact scenario under which incremental computation will provide both lower latency and greater resource efficiency.
- vaughan 5y ago
  > If you cannot refresh all of the views, at a single point in time, then there will be internal inconsistencies in your dataset. In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined. > incremental computation will provide both lower latency and greater resource efficiency. Wish we had some better database primitives to assemble rather than building everything on Postgres - its not ideal for a lot of things.
  - cirego 5y ago
    > In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined. I see that now -- makes sense! > Wish we had some better database primitives to assemble rather than building everything on Postgres - its not ideal for a lot of things. I'm curious to hear more about this! We agree that better primitives are required and that's why Materialize is written in Rust using using TimelyDataflow[1] and DifferentialDataflow[2] (both developed by Materialize co-founder Frank McSherry). The only relationship between Materialize and Postgres is that we are wire-compatible with Postgres and we don't share any code with Postgres nor do we have a dependence on it. [1] https://github.com/TimelyDataflow/timely-dataflow https://github.com/TimelyDataflow/timely-dataflow [2] https://github.com/TimelyDataflow/differential-dataflow https://github.com/TimelyDataflow/differential-dataflow
    - vaughan 5y ago
      > I'm curious to hear more about this I think the [FoundationDB layer concept][1] said it well: "When you choose a database today, you’re not choosing one piece of technology, you’re choosing three: storage technology, data model, and API/query language..." I like the idea of [Apache Calcite][2] that provides an API to access the query planner. I think if you had more convenient access to some of the underlying components you could build a lot of cool stuff. There's too much magic where you punch in an SQL command or a config and hope it eventually does what you need. I haven't look too much into the internals, but I'm keen to do so soon. [1]: https://apple.github.io/foundationdb/layer-concept.html https://apple.github.io/foundationdb/layer-concept.html [2]: https://calcite.apache.org/ https://calcite.apache.org/
mr_toad 5y ago
> Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You might find it’s cheaper to do just that. It might cost you in computational resources, but save you millions in engineering time.