5 ms·

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 c

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.
    - excerionsforte 7y ago
      Unsubstantiated generalizations are the sort I disagree with strongly. Why? because it never looks at the use case. Dismissing all ideas just on the basis of one thing is a documented Anti-Pattern. Let's do google search 'storing blobs' to find what do people have to say with their experiences. First link: https://dba.stackexchange.com/questions/2445/should-binary-files-be-stored-in-the-database https://dba.stackexchange.com/questions/2445/should-binary-f... >Most RDBMS have optimizations for storing BLOBs (eg SQL Server filestream) anyway Second to last link: https://www.quora.com/Is-it-a-bad-design-to-store-images-as-blobs-in-a-database https://www.quora.com/Is-it-a-bad-design-to-store-images-as-... > Bill Karwin is correct that you can't make unequivocal statement that storing images in a database is always bad. I like a lot of his points. Most people who are against images in the DB usually are promoting the only thing they know how to do. and I'm not talking about images. Object data that I would serialize is can very between 512b to 64kb (Always interesting to see the scale of memory nowadays, kb is so small).
      - AmericanChopper 7y ago
        Those answers are both dubious and very outdated. At the time they were written, the most mature and widely available solution for blob storage was the filesystem, which has its own set of shortcomings for that use case. This is no longer true, and that alone frankly makes those answer obsolete. Then you have the fact that those answers are ignoring a lot of facts. The answer you referenced from Bill Karwin talks about mutating blobs. This isn’t a reasonable design pattern at all, you create a new blob and update the reference. The downsides to storing blobs in an rdbms are so numerous that you’d really have to have a very strong justification for doing so, and I’m really struggling to think of any that are actually technical.
AmericanChopper 7y ago
Storing blobs in a DB is nearly always a bad idea. The only reason I can ever think to do it would be if you had some insane business constraints which for some reason made it the least bad option. Even though most RDBMS support the blob type, they are absolutely not optimised for handling it. Blob access is always going to be slower through a DB, blob I/O will take resources away from the rest of your app, due to concurrency control your DB is always going to be a bottleneck and is always going to be difficult to horizontally scale, it will also slow down your backups, and running backups eats into your DB performance again, so you want to make them as efficient as possible. Due to the sophistication of object storage solutions these days (which infinitely scale horizontally), you don’t even need to deal with the pain of the filesystem to avoid using blobs.
- abraae 7y ago
  All good points, but there are other reasons to store images in the DB. One is making it much easier to copy graphs of data, e.g copying an account with a bunch of attached users, widgets and their images. When everything's in the DB this is just "insert into ... Select from". When images are held in e.g s3 this gets an order of magnitude harder.
deleted 7y ago
[deleted]

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.
  - excerionsforte 7y ago
    Unsubstantiated generalizations are the sort I disagree with strongly. Why? because it never looks at the use case. Dismissing all ideas just on the basis of one thing is a documented Anti-Pattern. Let's do google search 'storing blobs' to find what do people have to say with their experiences. First link: https://dba.stackexchange.com/questions/2445/should-binary-files-be-stored-in-the-database https://dba.stackexchange.com/questions/2445/should-binary-f... >Most RDBMS have optimizations for storing BLOBs (eg SQL Server filestream) anyway Second to last link: https://www.quora.com/Is-it-a-bad-design-to-store-images-as-blobs-in-a-database https://www.quora.com/Is-it-a-bad-design-to-store-images-as-... > Bill Karwin is correct that you can't make unequivocal statement that storing images in a database is always bad. I like a lot of his points. Most people who are against images in the DB usually are promoting the only thing they know how to do. and I'm not talking about images. Object data that I would serialize is can very between 512b to 64kb (Always interesting to see the scale of memory nowadays, kb is so small).
    - AmericanChopper 7y ago
      Those answers are both dubious and very outdated. At the time they were written, the most mature and widely available solution for blob storage was the filesystem, which has its own set of shortcomings for that use case. This is no longer true, and that alone frankly makes those answer obsolete. Then you have the fact that those answers are ignoring a lot of facts. The answer you referenced from Bill Karwin talks about mutating blobs. This isn’t a reasonable design pattern at all, you create a new blob and update the reference. The downsides to storing blobs in an rdbms are so numerous that you’d really have to have a very strong justification for doing so, and I’m really struggling to think of any that are actually technical.

AmericanChopper 7y ago

Storing blobs in a DB is nearly always a bad idea. The only reason I can ever think to do it would be if you had some insane business constraints which for some reason made it the least bad option. Even though most RDBMS support the blob type, they are absolutely not optimised for handling it. Blob access is always going to be slower through a DB, blob I/O will take resources away from the rest of your app, due to concurrency control your DB is always going to be a bottleneck and is always going to be difficult to horizontally scale, it will also slow down your backups, and running backups eats into your DB performance again, so you want to make them as efficient as possible. Due to the sophistication of object storage solutions these days (which infinitely scale horizontally), you don’t even need to deal with the pain of the filesystem to avoid using blobs.

abraae 7y ago
All good points, but there are other reasons to store images in the DB. One is making it much easier to copy graphs of data, e.g copying an account with a bunch of attached users, widgets and their images. When everything's in the DB this is just "insert into ... Select from". When images are held in e.g s3 this gets an order of magnitude harder.

deleted 7y ago

[deleted]