4 ms·

The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many commo

The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many common types of schema evolution become substantially more difficult when the system forces you to wrap your DDL in a series of view drop/recreation steps. This is merely annoying when dealing with regular views because recreating even a large number of views is fast, but can be catastrophic if you have any matviews in your table dependency tree. A matview can easily turn what should be an instantaneous DDL operation into a partial outage while the matview is being regenerated. (this is all postgres specific, it may be untrue for other systems)

smallnamespace 4y ago
To avoid an outage, have you tried fronting the matview with an additional view to allow hot-swapping?
enepture 4y ago
As an FYI using a tool like DBT solves this problem. As someone who was not a data engineer I was not familiar, there were tools like this
- cpursley 4y ago
  I have this problem. What is DBT?
  - banashark 4y ago
    I believe this: https://docs.getdbt.com/docs/introduction https://docs.getdbt.com/docs/introduction
- teej 4y ago
  dbt is great, but I'm not sure it's appropriate to manage objects in a production transactional database. It's designed for analytical, columnar databases.
- PaulMest 4y ago
  In case you're not familiar with DBT, it helps with the "transform" in ELT https://docs.getdbt.com/docs/introduction https://docs.getdbt.com/docs/introduction.
- WesleyJohnson 4y ago
  Thank you for this. I had no idea this existed either and it may solve some points for us on MySQL where we've really been yearning for materialized views... which I'm now reading my be a foot gun.
slt2021 4y ago
it is very dangerous to have dependency on materialized view - it is a poor architectural decision from DBA to do that. if you want view depending on mat view - materialize it yourself in a table, and refresh it yourself controllably.
- semiquaver 4y ago
  Aren’t you just saying that materialized views should not be used? I’m pretty sure that was my original point.
rodw 4y ago
> The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many common types of schema evolution become substantally more difficult when the system forces you to wrap your DDL in a series of view drop/recreation steps. You're not wrong, especially with the second part. I.e., deeply nested or convoluted dependencies between views can definitely make it awkward or painful to make adjustments near the root of the tree. When I started this reply I was going to say "I hear you, but it's not an issue I run into very often". But that's not true. I've actually been burned by that moderately often, and have sometimes avoided or redesigned the root-level table change to avoid having to propagate all those changes to the rest of the dependency tree. That said, in my experience (also mostly with postgres for this context) I feel like that's usually been more of a developer laziness issue (my own laziness that is), rather than an "ossified schema" issue. It's definitely a PITA when some simple change is going to break a dozen inter-connected views, but that's a coding issue not a deployment issue almost all of the time. To be fair I don't really use matviews very often, but for true basic views I am guessing that the actual execution of the DDL to rebuild changed views is manageable in all but the most extreme cases. Even then there _should_ be a maintenance window of some sort available. Thinking this thru a little bit, I believe the "anti-pattern" you're warning against isn't really views themselves but deeply nested/interconnected views (views that query other views, etc). I use views often (for this logical-delete type idiom for example) and I have rarely regretted it. I have often regretted creating complicated view-base dependency trees however, so I think I'm wholeheartedly in agreement on that point.

smallnamespace 4y ago

To avoid an outage, have you tried fronting the matview with an additional view to allow hot-swapping?

enepture 4y ago

As an FYI using a tool like DBT solves this problem. As someone who was not a data engineer I was not familiar, there were tools like this

cpursley 4y ago
I have this problem. What is DBT?
- banashark 4y ago
  I believe this: https://docs.getdbt.com/docs/introduction https://docs.getdbt.com/docs/introduction
teej 4y ago
dbt is great, but I'm not sure it's appropriate to manage objects in a production transactional database. It's designed for analytical, columnar databases.
PaulMest 4y ago
In case you're not familiar with DBT, it helps with the "transform" in ELT https://docs.getdbt.com/docs/introduction https://docs.getdbt.com/docs/introduction.
WesleyJohnson 4y ago
Thank you for this. I had no idea this existed either and it may solve some points for us on MySQL where we've really been yearning for materialized views... which I'm now reading my be a foot gun.

slt2021 4y ago

it is very dangerous to have dependency on materialized view - it is a poor architectural decision from DBA to do that. if you want view depending on mat view - materialize it yourself in a table, and refresh it yourself controllably.

semiquaver 4y ago
Aren’t you just saying that materialized views should not be used? I’m pretty sure that was my original point.

rodw 4y ago

> The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many common types of schema evolution become substantally more difficult when the system forces you to wrap your DDL in a series of view drop/recreation steps. You're not wrong, especially with the second part. I.e., deeply nested or convoluted dependencies between views can definitely make it awkward or painful to make adjustments near the root of the tree. When I started this reply I was going to say "I hear you, but it's not an issue I run into very often". But that's not true. I've actually been burned by that moderately often, and have sometimes avoided or redesigned the root-level table change to avoid having to propagate all those changes to the rest of the dependency tree. That said, in my experience (also mostly with postgres for this context) I feel like that's usually been more of a developer laziness issue (my own laziness that is), rather than an "ossified schema" issue. It's definitely a PITA when some simple change is going to break a dozen inter-connected views, but that's a coding issue not a deployment issue almost all of the time. To be fair I don't really use matviews very often, but for true basic views I am guessing that the actual execution of the DDL to rebuild changed views is manageable in all but the most extreme cases. Even then there _should_ be a maintenance window of some sort available. Thinking this thru a little bit, I believe the "anti-pattern" you're warning against isn't really views themselves but deeply nested/interconnected views (views that query other views, etc). I use views often (for this logical-delete type idiom for example) and I have rarely regretted it. I have often regretted creating complicated view-base dependency trees however, so I think I'm wholeheartedly in agreement on that point.