6 ms·

I was working at a company of like 400 employees using MSSQL, so simple CRUD is not limited to simple crud app. Simple CRUD operations are insert, select, updat

I was working at a company of like 400 employees using MSSQL, so simple CRUD is not limited to simple crud app. Simple CRUD operations are insert, select, update, delete. Given that we auto generated the code and sql (stored procedures) and pasted it into the codebase made me think, why bother with this if I could develop new features without SQL at all. Why should my queries be a stored prod or prepared stmt if all I need is get, insert, update, delete an object. Yes, the auto generated code wrote to the database as an object as in all fields included in the query. Why? There's no transactions, foreign keys, etc. since some tables where stored in different databases. Using a relational db as object store included people inefficiently using it as an object store. In my own projects, if I need the speed, columns are indexed while unindexed data is binary. I can explode out (unserialize) the binary data into cache. Point of my post was centralizing database access tends to be a bottleneck for team efficiency. If not the devs then the db owners. Augment my abilities to execute don't try to completely change it where it brings a whole set of unwanted problems that were not there before.

AmericanChopper 7y ago
I’m not saying you made an inefficient choice. If you only need to interact with objects that have no relations or nested lists, then denormalized data is likely not going to be a problem. But this is a remarkably niche use case. In practice, nearly all cases I’ve seen where people have come to this conclusion, it’s because they didn’t properly analyse their schema to begin with. That’s not to say all use cases for such technology are niche. A lot of CMS applications can fit into that paradigm very well for instance. I’d also say that storing binary data in an RDBMS is a seperate anti-pattern all together.
- excerionsforte 7y ago
  Every column you write to a db is binary at some point. When you choose a type, all you asked the DB to do is interpret the stored binary data in that way. By choosing binary type to store data, you've declared to the db not to interpret the data. You don't hit issues with charset collation/encoding, database interpretation (lack of 128bit ints), and etc. Think about it, what is an integer? It is 4 bytes of data. Anti pattern depends on use case. Yes, I absolutely do not agree with your generalization. Storing binary data in an RDBMS is not an anti-pattern. Binary data can be of any size. The bigger the binary data the less rows you should expect to store. At some point (maybe the binary data is images/watermarks, etc), you have to choose a replicated file system to use that as the part of the datastore operations. Furthermore, I've only worked on high qps applications, so maybe I'm a bit biased on how to use the database efficiently. :)
  - Aeolun 7y ago
    Congratulations, you’ve offloaded to your app what your database is designed to deal with. Storing all data as binary is an anti-pattern too, regardless of your qps. I wouldn’t store anything but metadata in the database, the blob can be somewhere like S3.
    - AmericanChopper 7y ago
      > you’ve offloaded to your app what your database is designed to deal with. A lot of NoSQL design patterns lead you into this trap tbh. If you’re trying to make NoSQL work with relational data, you end up in a lot of situations where you have to choose between complex, over-engineered interfaces, or pushing more work into the client. For example, requiring the client to manage intermediate states because you can’t make efficient use of transactions.
    - excerionsforte 7y ago
      It depends on use case. If you cannot take that then agree to disagree and discussion is no longer warranted. I've been on both sides and take my experiences with me. You can assert anti pattern, but knowing how to structure your tables matter. SQL has BLOB, BINARY, VARBINARY choose the proper type depending on trade off. Models that include blobs can be structured in DB to avoid IO issues (indexed data includes id). Go to S3 with the id. How does what you are saying differ? My first post literally says this. Not only can be structured to avoid IO issues, but are protected via a cache where the unindexed data is exploded. Of course with high QPS I want to offload CPU cycles away from the DB. Scaling the app is easier than a DB. Scratching my head here. Are you arguing for just use only SQL? Why would I do that.
      - Aeolun 7y ago
        > knowing how to structure your tables matters I think we can agree on that. What I don’t think we agree on is that storing all non-indexed columns as binary is a good idea. There are a lot of non-obvious issues with storing blobs in your relational database. I’m sure you can mitigate them, but ultimately I think it’s better to just not give yourself that headache.

AmericanChopper 7y ago

I’m not saying you made an inefficient choice. If you only need to interact with objects that have no relations or nested lists, then denormalized data is likely not going to be a problem. But this is a remarkably niche use case. In practice, nearly all cases I’ve seen where people have come to this conclusion, it’s because they didn’t properly analyse their schema to begin with. That’s not to say all use cases for such technology are niche. A lot of CMS applications can fit into that paradigm very well for instance. I’d also say that storing binary data in an RDBMS is a seperate anti-pattern all together.

excerionsforte 7y ago
Every column you write to a db is binary at some point. When you choose a type, all you asked the DB to do is interpret the stored binary data in that way. By choosing binary type to store data, you've declared to the db not to interpret the data. You don't hit issues with charset collation/encoding, database interpretation (lack of 128bit ints), and etc. Think about it, what is an integer? It is 4 bytes of data. Anti pattern depends on use case. Yes, I absolutely do not agree with your generalization. Storing binary data in an RDBMS is not an anti-pattern. Binary data can be of any size. The bigger the binary data the less rows you should expect to store. At some point (maybe the binary data is images/watermarks, etc), you have to choose a replicated file system to use that as the part of the datastore operations. Furthermore, I've only worked on high qps applications, so maybe I'm a bit biased on how to use the database efficiently. :)
- Aeolun 7y ago
  Congratulations, you’ve offloaded to your app what your database is designed to deal with. Storing all data as binary is an anti-pattern too, regardless of your qps. I wouldn’t store anything but metadata in the database, the blob can be somewhere like S3.
  - AmericanChopper 7y ago
    > you’ve offloaded to your app what your database is designed to deal with. A lot of NoSQL design patterns lead you into this trap tbh. If you’re trying to make NoSQL work with relational data, you end up in a lot of situations where you have to choose between complex, over-engineered interfaces, or pushing more work into the client. For example, requiring the client to manage intermediate states because you can’t make efficient use of transactions.
  - excerionsforte 7y ago
    It depends on use case. If you cannot take that then agree to disagree and discussion is no longer warranted. I've been on both sides and take my experiences with me. You can assert anti pattern, but knowing how to structure your tables matter. SQL has BLOB, BINARY, VARBINARY choose the proper type depending on trade off. Models that include blobs can be structured in DB to avoid IO issues (indexed data includes id). Go to S3 with the id. How does what you are saying differ? My first post literally says this. Not only can be structured to avoid IO issues, but are protected via a cache where the unindexed data is exploded. Of course with high QPS I want to offload CPU cycles away from the DB. Scaling the app is easier than a DB. Scratching my head here. Are you arguing for just use only SQL? Why would I do that.
    - Aeolun 7y ago
      > knowing how to structure your tables matters I think we can agree on that. What I don’t think we agree on is that storing all non-indexed columns as binary is a good idea. There are a lot of non-obvious issues with storing blobs in your relational database. I’m sure you can mitigate them, but ultimately I think it’s better to just not give yourself that headache.