Follow

/ :
Is there any db where you can make a materialized view which is automatically updated when the underlying table is but WITHOUT completely redoing the query from scratch ?

· · Web · 2 · 1 · 0

@cjd I know a materialized view for each it doesn't make sense, and I know a materialized view in which it does make sense.

In the latter case it'd even be possible to write triggers in postgresql that'd do what you want for that particular view.

The tricky question is, can these triggers be generated automatically in the general case.

@Wolf480pl Consider the case of bitcoin, you might have a table of transactions which is going to be huge -- then you want a table of utxos so that you know what's unspent, you can do a query over the entire history of transactions in order to get the utxo set but you really don't want to do that very often.
A declarative language like SQL seems ideal for avoiding bugs, but it need to be able to transform updates from the table to the view...

@cjd @Wolf480pl there used to be a thing called mysql flex views which would listen to server's binary log and transform updates to underlying tables to updates for materialized view. I don't know if it's still used by anyone. And there should be a better alternative outside mysql.

@cjd if it's remote, use replication, if it's local, take another stab at optimizing the query and go with a standard view.

Not a whole lot of middle ground on that one, much to my personal annoyance. Materialized views are annoyingly limited.

Sign in to participate in the conversation
Mastodon

Server run by the main developers of the project 🐘 It is not focused on any particular niche interest - everyone is welcome as long as you follow our code of conduct!