8 ms·

Ha, Spouter a middle layer for talking to DB instead of just talking to DB (proxy to do connection pooling, etc could be between). This kind of project came up

Ha, Spouter a middle layer for talking to DB instead of just talking to DB (proxy to do connection pooling, etc could be between). This kind of project came up at my previous job and while I didn't know about Spouter at the time, I knew I didn't like the idea one bit. Managing a DB + middle layer for a small and an already stressed DB team? Can't imagine that going well. The problem was that they spent too much time doing reviews for simple SQL patterns for CRUD + operational issues like debugging database performance issues or watching devs perform migrations. Of course they have other things to do in their job. My opinion was the root of the problem is database choice and practices. If all I want to do is simple CRUD, then give me good scaled Redis cluster (AOF enabled), dynamo or something that constrains the query model. DB team can worry about about the cluster management leaving me to worry about structure. I could consult DB team if I needed opinions for multiple cases. Give me a way to watch db performance as well, so DB team does not need to watch over a migration at 12am or some off period. Sometimes I may need higher performance or more dynamic queries, so just creating a table or elastic search with only indexable values to get the ids works. Use those IDs to fetch from original store.

AmericanChopper 7y ago
> My opinion was the root of the problem is database choice and practices. If all I want to do is simple CRUD, then give me good scaled Redis cluster... Throughout my entire career I have come across very few simple crud applications that will actually work properly with denormalized data structures. It’s not even about scaling users, it’s about scaling your schema. The very first instance you encounter where a document has a nested array will start to cause problems, and the only way to solve it is to push more complexity and state management onto the client. Which is bad interface design, and quickly gets out of control. If you really do have a simple crud app, and don’t want to put too much effort into running your RDBMS, just use MySQL imo.
- excerionsforte 7y ago
  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.

AmericanChopper 7y ago

> My opinion was the root of the problem is database choice and practices. If all I want to do is simple CRUD, then give me good scaled Redis cluster... Throughout my entire career I have come across very few simple crud applications that will actually work properly with denormalized data structures. It’s not even about scaling users, it’s about scaling your schema. The very first instance you encounter where a document has a nested array will start to cause problems, and the only way to solve it is to push more complexity and state management onto the client. Which is bad interface design, and quickly gets out of control. If you really do have a simple crud app, and don’t want to put too much effort into running your RDBMS, just use MySQL imo.

excerionsforte 7y ago
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.