7 ms·

Somebody in our team put this on production. I guess this solution has some merits if you need something quick, but in the long run it turned out to be painful.

Somebody in our team put this on production. I guess this solution has some merits if you need something quick, but in the long run it turned out to be painful. It's basically SQL over REST. Additionally, your DB schema becomes your API schema and that either means you force one for the purposes of the other or you build DB views to fix that.

deleted 7y ago
[deleted]
ruslan_talpa 7y ago
what's wrong with views (which should have been used formt he start)? What were the pain points?
- bdcravens 7y ago
  They are great when used well, but non-materialized views can kill performance with large data sets.
  - doh 7y ago
    That's the same as saying "unoptimized selects can kill performance with large data sets". Of course they can. That's what optimization is for. We have quite large amount of data (100TB+ and trillions of rows at this point [0]) and no problem with views. [0] https://www.citusdata.com/customers/pex https://www.citusdata.com/customers/pex
  - ruslan_talpa 7y ago
    a view is nothing but a query, so if the view is "killing" the performace for you, running the same query from the client will not change anything, the porformance will get "killed" in the exact same way.
    - bdcravens 7y ago
      Yes, if you are running the same query. Some of the worst use of views I've seen involve massive joins without filters, and then filtering further down, so you end up working with a recordset in the millions of records rather than a few thousand.
      - philwelch 7y ago
        That kind of reminds me of the classic doctors joke though. If it hurts when you do that, don't do that.
      - aidos 7y ago
        So filtering later outside of the db? If you filter on a view the optimiser should be able to sort it out - as others have mentioned, it’s no different from a regular query.
      - ruslan_talpa 7y ago
        I understand the problem you are describing, i would say that is a wrong type of view to create, if one plans to write filters on top of that view then it should be written so the filters can be inlined in the view (which would basically give you the finished query you would be sending from the layer above)
      - sk5t 7y ago
        It's kind of unclear what problem you are trying to describe. Views shouldn't confound the query planner, and creating views with "filters" sounds like probably a mistake--query the view with the predicates you need then.
cryptonector 7y ago
> or you build DB views to fix that. That's what VIEWs are for! Well, one use-case of VIEWs, anyways. There's nothing wrong with the schema as the API since you can use VIEWs to maintain backwards compatibility as you evolve your product. Put another way: you will have an API, you will need to maintain backwards compatibility. Not exposing a SQL schema as an API does not absolve you or make it easier to be backwards-compatible. You might argue that you could have server-side JSON schema mapping code to help with schema transitions, and, indeed, that would be true, but whatever you write that code in, it's code, and using SQL or something else is just as well.
- mycall 7y ago
  How do you do CRUD with views? I know Reads are what views do.
  - flukus 7y ago
    You can have insert/update trigger on views. You shouldn't but you can. More realistically, stored procs would do the CUD parts.
    - dragonwriter 7y ago
      > You can have insert/update trigger on views. You shouldn't but you can. You can, and there is no reason you shouldn't. > More realistically, stored procs would do the CUD parts. Why are stored procs more realistic?
      - taffer 7y ago
        Triggers are implicit, have side effects and are not deterministic. They are confusing and surprising. A procedure call is explicit, a trigger is implicit. You don't call a trigger, it just happens as a side effect of something else. People tend to forget implicit things. Suddenly you notice that something is acting strangely or slowly in your application. You can look at your functions and procedures and try to find the problem. But if your application has triggers all over the place, how do you know what is going on? A trigger can change a dozen rows, which in turn can change other rows, so changing a single row can fire thousands or millions of triggers. Also, triggers are not fired in a particular order, the database is free to change the query plan according to what it thinks is best at the moment, so triggers are not deterministic. Triggers can sometimes work and sometimes not. Almost everything that can be done with a trigger can be done with a procedure, but explicitly, deterministically and in most cases without side effects.

deleted 7y ago

[deleted]

ruslan_talpa 7y ago

what's wrong with views (which should have been used formt he start)? What were the pain points?

bdcravens 7y ago
They are great when used well, but non-materialized views can kill performance with large data sets.
- doh 7y ago
  That's the same as saying "unoptimized selects can kill performance with large data sets". Of course they can. That's what optimization is for. We have quite large amount of data (100TB+ and trillions of rows at this point [0]) and no problem with views. [0] https://www.citusdata.com/customers/pex https://www.citusdata.com/customers/pex
- ruslan_talpa 7y ago
  a view is nothing but a query, so if the view is "killing" the performace for you, running the same query from the client will not change anything, the porformance will get "killed" in the exact same way.
  - bdcravens 7y ago
    Yes, if you are running the same query. Some of the worst use of views I've seen involve massive joins without filters, and then filtering further down, so you end up working with a recordset in the millions of records rather than a few thousand.
    - philwelch 7y ago
      That kind of reminds me of the classic doctors joke though. If it hurts when you do that, don't do that.
    - aidos 7y ago
      So filtering later outside of the db? If you filter on a view the optimiser should be able to sort it out - as others have mentioned, it’s no different from a regular query.
    - ruslan_talpa 7y ago
      I understand the problem you are describing, i would say that is a wrong type of view to create, if one plans to write filters on top of that view then it should be written so the filters can be inlined in the view (which would basically give you the finished query you would be sending from the layer above)
    - sk5t 7y ago
      It's kind of unclear what problem you are trying to describe. Views shouldn't confound the query planner, and creating views with "filters" sounds like probably a mistake--query the view with the predicates you need then.

cryptonector 7y ago

> or you build DB views to fix that. That's what VIEWs are for! Well, one use-case of VIEWs, anyways. There's nothing wrong with the schema as the API since you can use VIEWs to maintain backwards compatibility as you evolve your product. Put another way: you will have an API, you will need to maintain backwards compatibility. Not exposing a SQL schema as an API does not absolve you or make it easier to be backwards-compatible. You might argue that you could have server-side JSON schema mapping code to help with schema transitions, and, indeed, that would be true, but whatever you write that code in, it's code, and using SQL or something else is just as well.

mycall 7y ago
How do you do CRUD with views? I know Reads are what views do.
- flukus 7y ago
  You can have insert/update trigger on views. You shouldn't but you can. More realistically, stored procs would do the CUD parts.
  - dragonwriter 7y ago
    > You can have insert/update trigger on views. You shouldn't but you can. You can, and there is no reason you shouldn't. > More realistically, stored procs would do the CUD parts. Why are stored procs more realistic?
    - taffer 7y ago
      Triggers are implicit, have side effects and are not deterministic. They are confusing and surprising. A procedure call is explicit, a trigger is implicit. You don't call a trigger, it just happens as a side effect of something else. People tend to forget implicit things. Suddenly you notice that something is acting strangely or slowly in your application. You can look at your functions and procedures and try to find the problem. But if your application has triggers all over the place, how do you know what is going on? A trigger can change a dozen rows, which in turn can change other rows, so changing a single row can fire thousands or millions of triggers. Also, triggers are not fired in a particular order, the database is free to change the query plan according to what it thinks is best at the moment, so triggers are not deterministic. Triggers can sometimes work and sometimes not. Almost everything that can be done with a trigger can be done with a procedure, but explicitly, deterministically and in most cases without side effects.