5 ms·

So could you use this to implement always-up-to-date materialized views in Postgres? It says it uses relational data for inputs and outputs. It says it operates

So could you use this to implement always-up-to-date materialized views in Postgres? It says it uses relational data for inputs and outputs. It says it operates in-memory but also that it starts up from a set of known facts (which sounds like it could just read the current matview at startup). This would be a really cool extension for someone to build!

namibj 6y ago
Yes. Almost exactly what you're describing exists [0] already. [0]: https://materialize.com/ https://materialize.com/
- tda 6y ago
  What a horrible landing page. Is it a stand alone product? Does it integrate with my SQL database? Is it a server side application or a client side thing? What is the pricing model? So many questions, zero answers Am in the process of exploring kafka to do something similar, and I think it will work but requires quite a complex setup (compared to an incrementally updated materialized view)
  - namibj 6y ago
    It's typically integrated with Kafka sources (and often also Kafka sinks), but you can just use any normal PostgreSQL client driver and normal SQL. It does integrate with your postgres database, but logical replication target mode is still a TODO. Don't worry though, this only means the change data stream has to go through a Kafka broker. There should be a guide on this (if not, plop in the community Slack and ask). The license allows for free single-node deployments; the SaaS variant is currently in Beta, so you'll have to ask. On-premise clusters for production usage are "contact sales", but AFAIK due to the low sales volume (turns out, vertical scaling is easy and often enough), so don't let that scare you off.
- aidos 6y ago
  Looks like it’s Postgres protocol compatible rather than running as a Postgres extension? I’ve been keeping an eye on work that’s been done to support this internally in Postgres. Looks like it’s still moving along. I guess it’s a pretty tricky puzzle (especially once you add aggregates). Depending on the performance implications, it would sit in a pretty sweet spot for some usecases. For example, at the moment I have a form of materialised view that’s simple too slow to build from scratch. My compromise is to run periodically, figure out which subset of the table needs refreshing and update just those rows. https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...
  - namibj 6y ago
    It doesn't run as an extension (at least that's not the typical deployment, and I'd be surprised if it did). You can feed a CDC to Materialize and either feed the output back to Postgres, or just query it directly in Materialize (via a normal postgres client). It's very likely that you can cut the typical maintenance latency down below one second. Currently that likely involves using a Kafka broker between the two databases, but feel free to plop in the community Slack [0]. [0]: http://materialize.com/s/chat http://materialize.com/s/chat
YeGoblynQueenne 6y ago
>> It says it operates in-memory but also that it starts up from a set of known facts (which sounds like it could just read the current matview at startup). That "it starts up from a set of known facts" refers to the "bottom-up" evaluation of datalog programs, in contrast with the top-down evaluation in Prolog. Consider for instance the following database of facts and rules: grandparent(X,Y):- parent(X,Z), parent(Z,Y). % (1) parent(maria, kostas). parent(kostas,yannis). Prolog's top-down evaluation starts with the "head" of rule (1), grandparent(X,Y) and proceeds to its "body", (parent(X,Z), parent(Z,Y)) to instantiate the variables {X,Y,Z} according to the facts in the database and derive the new fact grandparent(maria,yannis). Datalog's bottom-up evaluation instead starts with the facts in the program: parent(maria,kostas), parent(kostas, yannis) and adds those to the set of immediate consequences of the program (TP is called the "immediate consequence operator"): TP₀ = {parent(maria,kostas), parent(kostas,yannis)} Now the "parent" facts in the body of the "grandparent" rule can be instantiated as follows: (parent(maria,kostas), parent(kostas,yannis)). And finally the head of the rule can be instantiated to derive the same new atom as with Prolog's top-down evaluation: TP₁ = TP₀ ∪ {grandparent(maria,yannis)} The advantage of bottom-up evaluation is decidability, as long as a program is datalog, which means no function symbols other than constants (which are function symbols of arity 0, i.e. with 0 arguments) and no negation. For instance, given the following program with a left-recursive clause, datalog's bottom-up evaluation terminates: ancestor(X,Y):- parent(X,Y). % (2) ancestor(X,Y):- ancestor(X,Z), ancestor(Z,Y). % (3) parent(maria,kostas). parent(kostas,yannis). Given this program, datalog's bottom-up evaluation will first add the two facts of "parent" to the immediate consequences of the program; then instantiate the fact parent(X,Y) in the body of rule (2) to the two facts of "parent" in the program; and finally instantiate the head of rule (2), deriving two new facts of "ancestor": TP₁ = {ancestor(maria,kostas), ancestor(kostas,yannis), parent(maria,kostas), parent(kostas,yannis)} Then the two facts of "ancestor" in the body of rule (3) will be instantiated to the facts derived in the previous step and a new fact derived from the instantiation of the variables in the head of the rule : TP₂ = TP₀ ∪ {ancestor(maria,yannis)} And then the program will terminate because there are no more new facts to derive. By contrast, Prolog's top-down evaluation will first correctly derive the two new "ancestor" facts from the head of rule (2) but then will try to evaluate the first ancestor(X,Z) fact in the body of rule (3). This will take it back to the head of rule (3) and evaluation will be stuck in an infinite recursion. The trade-off is that bottom-up evaluation cannot handle negation or functions (including the cons function used to construct lists) and so is, in that sense, incomplete, whereas Prolog can do both. For this reason, datalog is preferred as a database language that offers a limited ability for reasoning (certainly richer than what is possible e.g. with SQL, that essentially only allows facts) whereas Prolog is preferred for general-purpose programming.

namibj 6y ago

Yes. Almost exactly what you're describing exists [0] already. [0]: https://materialize.com/ https://materialize.com/

tda 6y ago
What a horrible landing page. Is it a stand alone product? Does it integrate with my SQL database? Is it a server side application or a client side thing? What is the pricing model? So many questions, zero answers Am in the process of exploring kafka to do something similar, and I think it will work but requires quite a complex setup (compared to an incrementally updated materialized view)
- namibj 6y ago
  It's typically integrated with Kafka sources (and often also Kafka sinks), but you can just use any normal PostgreSQL client driver and normal SQL. It does integrate with your postgres database, but logical replication target mode is still a TODO. Don't worry though, this only means the change data stream has to go through a Kafka broker. There should be a guide on this (if not, plop in the community Slack and ask). The license allows for free single-node deployments; the SaaS variant is currently in Beta, so you'll have to ask. On-premise clusters for production usage are "contact sales", but AFAIK due to the low sales volume (turns out, vertical scaling is easy and often enough), so don't let that scare you off.
aidos 6y ago
Looks like it’s Postgres protocol compatible rather than running as a Postgres extension? I’ve been keeping an eye on work that’s been done to support this internally in Postgres. Looks like it’s still moving along. I guess it’s a pretty tricky puzzle (especially once you add aggregates). Depending on the performance implications, it would sit in a pretty sweet spot for some usecases. For example, at the moment I have a form of materialised view that’s simple too slow to build from scratch. My compromise is to run periodically, figure out which subset of the table needs refreshing and update just those rows. https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...
- namibj 6y ago
  It doesn't run as an extension (at least that's not the typical deployment, and I'd be surprised if it did). You can feed a CDC to Materialize and either feed the output back to Postgres, or just query it directly in Materialize (via a normal postgres client). It's very likely that you can cut the typical maintenance latency down below one second. Currently that likely involves using a Kafka broker between the two databases, but feel free to plop in the community Slack [0]. [0]: http://materialize.com/s/chat http://materialize.com/s/chat

YeGoblynQueenne 6y ago

>> It says it operates in-memory but also that it starts up from a set of known facts (which sounds like it could just read the current matview at startup). That "it starts up from a set of known facts" refers to the "bottom-up" evaluation of datalog programs, in contrast with the top-down evaluation in Prolog. Consider for instance the following database of facts and rules: grandparent(X,Y):- parent(X,Z), parent(Z,Y). % (1) parent(maria, kostas). parent(kostas,yannis). Prolog's top-down evaluation starts with the "head" of rule (1), grandparent(X,Y) and proceeds to its "body", (parent(X,Z), parent(Z,Y)) to instantiate the variables {X,Y,Z} according to the facts in the database and derive the new fact grandparent(maria,yannis). Datalog's bottom-up evaluation instead starts with the facts in the program: parent(maria,kostas), parent(kostas, yannis) and adds those to the set of immediate consequences of the program (TP is called the "immediate consequence operator"): TP₀ = {parent(maria,kostas), parent(kostas,yannis)} Now the "parent" facts in the body of the "grandparent" rule can be instantiated as follows: (parent(maria,kostas), parent(kostas,yannis)). And finally the head of the rule can be instantiated to derive the same new atom as with Prolog's top-down evaluation: TP₁ = TP₀ ∪ {grandparent(maria,yannis)} The advantage of bottom-up evaluation is decidability, as long as a program is datalog, which means no function symbols other than constants (which are function symbols of arity 0, i.e. with 0 arguments) and no negation. For instance, given the following program with a left-recursive clause, datalog's bottom-up evaluation terminates: ancestor(X,Y):- parent(X,Y). % (2) ancestor(X,Y):- ancestor(X,Z), ancestor(Z,Y). % (3) parent(maria,kostas). parent(kostas,yannis). Given this program, datalog's bottom-up evaluation will first add the two facts of "parent" to the immediate consequences of the program; then instantiate the fact parent(X,Y) in the body of rule (2) to the two facts of "parent" in the program; and finally instantiate the head of rule (2), deriving two new facts of "ancestor": TP₁ = {ancestor(maria,kostas), ancestor(kostas,yannis), parent(maria,kostas), parent(kostas,yannis)} Then the two facts of "ancestor" in the body of rule (3) will be instantiated to the facts derived in the previous step and a new fact derived from the instantiation of the variables in the head of the rule : TP₂ = TP₀ ∪ {ancestor(maria,yannis)} And then the program will terminate because there are no more new facts to derive. By contrast, Prolog's top-down evaluation will first correctly derive the two new "ancestor" facts from the head of rule (2) but then will try to evaluate the first ancestor(X,Z) fact in the body of rule (3). This will take it back to the head of rule (3) and evaluation will be stuck in an infinite recursion. The trade-off is that bottom-up evaluation cannot handle negation or functions (including the cons function used to construct lists) and so is, in that sense, incomplete, whereas Prolog can do both. For this reason, datalog is preferred as a database language that offers a limited ability for reasoning (certainly richer than what is possible e.g. with SQL, that essentially only allows facts) whereas Prolog is preferred for general-purpose programming.