5 ms·

I read this a few days ago. I was waiting for it to show on HN because I have a few questions. I want to do the same, but with big databases (the TXID wraparou

by 1996 7y ago

I read this a few days ago. I was waiting for it to show on HN because I have a few questions. I want to do the same, but with big databases (the TXID wraparound is a problem every few months). For now, the queries are stored in the code. They rarely evolve. How do you manage versioning? Queries in code mean you can ensure everything match. How easily other people interact with that? Not many developers know SQL well. Poor SQL skills can slow down a server for other users. What did you gain in practice? It is nice, like CI/CD or version control, but I see little gains for something that does not change very often. Currently, most of the queries are materialized views that can be refreshed separately. It seems simpler to me. In practice, how is this different from refreshing materialized views?

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.
deleted 7y ago
[deleted]
segmondy 7y ago
You just talked about TXID wraparound, materialized views and then talking about not many developers know SQL well. You're all over the place with your questions. This is very simple, you wrap all your logic as functions/stored procedures, have the DB do the work. This will work for 99% of businesses out there. How many people really wraparound ID? How many people really need to shard DB? Those 1% will know how to tackle the problem. For most people by moving all these into the DB, your code is much simpler. Something like this should be encouraged more, it's far easier to implement something like this than deal with ORM.

by 1996 7y ago

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.

deleted 7y ago

[deleted]

segmondy 7y ago

You just talked about TXID wraparound, materialized views and then talking about not many developers know SQL well. You're all over the place with your questions. This is very simple, you wrap all your logic as functions/stored procedures, have the DB do the work. This will work for 99% of businesses out there. How many people really wraparound ID? How many people really need to shard DB? Those 1% will know how to tackle the problem. For most people by moving all these into the DB, your code is much simpler. Something like this should be encouraged more, it's far easier to implement something like this than deal with ORM.