5 ms·

Versioning: sorry I'm not sure what you mean. Same as if you had queries in an ORM and views using the results, you'd need to branch and make changes, then merg

by sivers 7y ago

Versioning: sorry I'm not sure what you mean. Same as if you had queries in an ORM and views using the results, you'd need to branch and make changes, then merge the branch together at once. I just do the same. If the database structure changes, I run the ALTER TABLE commands, when switching to that branch. Other people : I guess like anyone choosing a language, you're excluding those who don't know it. I'm assuming more know SQL than Rust or Elixir or whatever. And more should. Gain in practice : Two best improvements were: (1) Having all data and data-logic (which might also be business-logic) in the same place. No worrying that some external code somewhere might have old data/business logic in it. One definitive source. Like Rich Hickey's classic "Simple/Complex Hard/Easy" talk - https://www.youtube.com/watch?v=rI8tNMsozo0 https://www.youtube.com/watch?v=rI8tNMsozo0 - I like that this is simple, un-complex. The data and external code don't need to be braided/complected together to work. (2) The freedom do switch my external code from Ruby to Go or Elixir or whatever, and not have to rewrite all that functionality. It's all just simple API calls. Sorry I haven't looked into materialized views yet, so I don't know how this compares.

1996 7y ago
Thanks a lot for your reply! It seems the main gain for your is to move between languages and allow initial iterations/bugfixes without touching the application code. The database has more maintenance issues (like rolling txids) than the application code. I am not sure I want to add more complexity and potential issues to the database. FYI, a materialized view is a potentially long or complex query whose results are cached, so you can say 'select * from complex_query_result' to get them, and refresh the complex_query_result whenever you feel like it. You can also update the query that generates complex_query_result. In practice, MV can give you speed (as you can refresh the MV when you need/want, while keeping the results) and also put the data-logic inside the database (as the MV is defined initially, and can later be updated) if you don't need super fresh results. If you do, use a regular view. In either case, you can use the view approach when parameters are needed, iff you can reduce you query to where parameter=something on the view. Otherwise, you need to use languages like pl sql. As the materialized views queries just return the results to be processed, and I have very little extra to do, your approach seems overkill for my use case.
- Twisell 7y ago
  Unless it changed very recently be aware that you can't update sql definition of a materialized as easily as you would update a view. There is currently no "CREATE OR REPLACE" option so any dependencie build onto a materialized view can quickly become a real pain (been there). Sometime, it might be easier to stick to the old trick of a table updated by a refresher function (possibly called as a trigger).
  - 1996 7y ago
    Personally I have update scripts for 'version control' of the MV and its dependancies, it is not very painful to do: begin; drop ... cascade; create ...; commit;
  - mhd 7y ago
    Dependencies are a general problem, like changing the type of a table that has dependent views. It's a good idea if your database update scripts/migration software can handle something like this. Other databases don't tend to be as strict as Postgres here (I only found out that some rarely-accessed views never quite worked after migrating from Oracle) I've had good luck with these functions: https://gist.github.com/mateuszwenus/11187288 https://gist.github.com/mateuszwenus/11187288 Allows you to save-and-drop dependent views (materialized or regular) and then restore them after your updates.
    - Twisell 7y ago
      Deeply agree about dependencies being a general problem. Thanks for the script that look pretty clever #bookmarking. Like particularly the approach of "drop what you saved, no more no less". DROP CASCADE is simpler but can have undetected side effect, if this script fail to backup all dependency, logically you will get an error when attempting to delete target and that rocks.

by sivers 7y ago

1996 7y ago

Thanks a lot for your reply! It seems the main gain for your is to move between languages and allow initial iterations/bugfixes without touching the application code. The database has more maintenance issues (like rolling txids) than the application code. I am not sure I want to add more complexity and potential issues to the database. FYI, a materialized view is a potentially long or complex query whose results are cached, so you can say 'select * from complex_query_result' to get them, and refresh the complex_query_result whenever you feel like it. You can also update the query that generates complex_query_result. In practice, MV can give you speed (as you can refresh the MV when you need/want, while keeping the results) and also put the data-logic inside the database (as the MV is defined initially, and can later be updated) if you don't need super fresh results. If you do, use a regular view. In either case, you can use the view approach when parameters are needed, iff you can reduce you query to where parameter=something on the view. Otherwise, you need to use languages like pl sql. As the materialized views queries just return the results to be processed, and I have very little extra to do, your approach seems overkill for my use case.

Twisell 7y ago
Unless it changed very recently be aware that you can't update sql definition of a materialized as easily as you would update a view. There is currently no "CREATE OR REPLACE" option so any dependencie build onto a materialized view can quickly become a real pain (been there). Sometime, it might be easier to stick to the old trick of a table updated by a refresher function (possibly called as a trigger).
- 1996 7y ago
  Personally I have update scripts for 'version control' of the MV and its dependancies, it is not very painful to do: begin; drop ... cascade; create ...; commit;
- mhd 7y ago
  Dependencies are a general problem, like changing the type of a table that has dependent views. It's a good idea if your database update scripts/migration software can handle something like this. Other databases don't tend to be as strict as Postgres here (I only found out that some rarely-accessed views never quite worked after migrating from Oracle) I've had good luck with these functions: https://gist.github.com/mateuszwenus/11187288 https://gist.github.com/mateuszwenus/11187288 Allows you to save-and-drop dependent views (materialized or regular) and then restore them after your updates.
  - Twisell 7y ago
    Deeply agree about dependencies being a general problem. Thanks for the script that look pretty clever #bookmarking. Like particularly the approach of "drop what you saved, no more no less". DROP CASCADE is simpler but can have undetected side effect, if this script fail to backup all dependency, logically you will get an error when attempting to delete target and that rocks.