5 ms·

Isn't this what is done by some SQL engines with materialized views? They update view data (counts and group by and sums) as the source table data changes. But

Isn't this what is done by some SQL engines with materialized views? They update view data (counts and group by and sums) as the source table data changes. But not by rerunning the view, but smartly updating its contents based on the change that happened. There are tons of limits in these views, as they have to provide stable results. See ms sql documentation on limits of streamed/indexes sql views https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/v...

adeelk93 3y ago
Those limits seem to be decreasing every year though. Lot of competition in the analytical SQL space. Also worth noting Delta Live Tables, another very popular streaming SQL implementation. https://www.databricks.com/product/delta-live-tables https://www.databricks.com/product/delta-live-tables
wredue 3y ago
Also lots of cubes can do similar things. Seems the author made a lot of assumptions without a lot of research. For one example, I would strongly doubt that count(*) without a where clause isn’t already highly optimized.
- EmilStenstrom 3y ago
  The author did lots of research. Here's a comparison of some common other ways to do this: https://docs.epsio.io/faq/ https://docs.epsio.io/faq/
- phamilton 3y ago
  > I would strongly doubt that count() without a where clause isn’t already highly optimized It's actually not well optimized in PostgreSQL because of MVCC. See https://wiki.postgresql.org/wiki/Slow_Counting https://wiki.postgresql.org/wiki/Slow_Counting TL;DR: The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table. PostgreSQL must walk through all rows to determine visibility.
MuffinFlavored 3y ago
> By default, materialized views in PostgreSQL are not automatically refreshed. If the underlying data changes, the materialized view remains stale until it's refreshed.
- joelhaasnoot 3y ago
  And only the owner can then refresh it - there are some annoying gotchas to them
- gigatexal 3y ago
  Can’t this be somewhat alleviated by running a job native on Postgres or some external one to refresh the views on a schedule? It’s not ideal or realtime but better than having it done as-hoc.
  - jitl 3y ago
    Rewriting a large view based on hundreds of gb of rows sounds like a recipe for a possible outage in an OLTP situation.
    - tucnak 3y ago
      https://docs.timescale.com/use-timescale/latest/continuous-aggregates/about-continuous-aggregates/ https://docs.timescale.com/use-timescale/latest/continuous-a...
  - wayfinder 3y ago
    Yeah but then it’s just lipstick on a pig. I can already create a regular table and update it myself. If my RBDMS adds materialized views, I want it to help me with it lol
infogulch 3y ago
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://github.com/timelydataflow/differential-dataflow https://materialize.com/ https://materialize.com/
- School-Cotton 3y ago
  Note that Materialize uses differential-dataflow under the hood. (I work for Materialize)
  - deleted 3y ago
    [deleted]
maor10 3y ago
Hi, author here :) In general, many databases have attempted something similar- but because "batch query" engines are so monumentally different than incremental engines, it becomes a very tough task and ends up being a hill of patches- which leads to many limitations. The easiest example I can think of is the direction of the flow of data. Postgres, for example, works from "top to bottom", where you start with the result node which runs the node underneath it and so on. Each node continues requesting information from the node underneath it until it returns Null, meaning it finished. This works very well with a snapshot of the data- but if you want to maintain a high throughput with a constant stream of changes, you're going to want to work in the opposite direction- this would mean either overhauling the very way Postgres works, or adding a completely different engine into Postgres.

adeelk93 3y ago

Those limits seem to be decreasing every year though. Lot of competition in the analytical SQL space. Also worth noting Delta Live Tables, another very popular streaming SQL implementation. https://www.databricks.com/product/delta-live-tables https://www.databricks.com/product/delta-live-tables

wredue 3y ago

Also lots of cubes can do similar things. Seems the author made a lot of assumptions without a lot of research. For one example, I would strongly doubt that count(*) without a where clause isn’t already highly optimized.

EmilStenstrom 3y ago
The author did lots of research. Here's a comparison of some common other ways to do this: https://docs.epsio.io/faq/ https://docs.epsio.io/faq/
phamilton 3y ago
> I would strongly doubt that count() without a where clause isn’t already highly optimized It's actually not well optimized in PostgreSQL because of MVCC. See https://wiki.postgresql.org/wiki/Slow_Counting https://wiki.postgresql.org/wiki/Slow_Counting TL;DR: The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table. PostgreSQL must walk through all rows to determine visibility.

MuffinFlavored 3y ago

> By default, materialized views in PostgreSQL are not automatically refreshed. If the underlying data changes, the materialized view remains stale until it's refreshed.

joelhaasnoot 3y ago
And only the owner can then refresh it - there are some annoying gotchas to them
gigatexal 3y ago
Can’t this be somewhat alleviated by running a job native on Postgres or some external one to refresh the views on a schedule? It’s not ideal or realtime but better than having it done as-hoc.
- jitl 3y ago
  Rewriting a large view based on hundreds of gb of rows sounds like a recipe for a possible outage in an OLTP situation.
  - tucnak 3y ago
    https://docs.timescale.com/use-timescale/latest/continuous-aggregates/about-continuous-aggregates/ https://docs.timescale.com/use-timescale/latest/continuous-a...
- wayfinder 3y ago
  Yeah but then it’s just lipstick on a pig. I can already create a regular table and update it myself. If my RBDMS adds materialized views, I want it to help me with it lol

infogulch 3y ago

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://github.com/timelydataflow/differential-dataflow https://materialize.com/ https://materialize.com/

School-Cotton 3y ago
Note that Materialize uses differential-dataflow under the hood. (I work for Materialize)
- deleted 3y ago
  [deleted]

maor10 3y ago

Hi, author here :) In general, many databases have attempted something similar- but because "batch query" engines are so monumentally different than incremental engines, it becomes a very tough task and ends up being a hill of patches- which leads to many limitations. The easiest example I can think of is the direction of the flow of data. Postgres, for example, works from "top to bottom", where you start with the result node which runs the node underneath it and so on. Each node continues requesting information from the node underneath it until it returns Null, meaning it finished. This works very well with a snapshot of the data- but if you want to maintain a high throughput with a constant stream of changes, you're going to want to work in the opposite direction- this would mean either overhauling the very way Postgres works, or adding a completely different engine into Postgres.