6 ms·

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 wi

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.
    - burpsnard 7y ago
      One i saw, was a banking backend; stored blobs retrieved by accountnumber from the mainframe were passed to mid-tier servers that mounted the blobs as r/w db instances. Sort of like piles of sqlite db's on S3
      - deleted 7y ago
        [deleted]
  - Aeolun 7y ago
    I mean, you do you, but you probably see the pattern in the responses to your comment. It is theoretically possible that there’s a good use case for storing all fields in a blob. It’s just that none of the ones you mentioned are ones I agree with. The only thing that makes sense to store in a blob is something that is otherwise incomprehensible (e.g. a jpeg file, doesn’t fit in any other column type), and even that is a bad idea.
    - excerionsforte 7y ago
      Dissenting/disbelief responses do not appear to be from people who've worked with RDBMS at the high scale. I'm content with being able to keep the system simple, sleep at night, not getting pings for database and feeling good about the choice. Big enough reward for me and any others who have dealt with this.
orf 7y ago
I’m sorry, but no matter what you think storing all unindexed columns as binary is very, very weird. You can write all the documentation you want as to why this is superior to what literally everyone else is doing and has been doing since the dawn of time, but it won’t stop people joining your project thinking that you are mad. This kind of thing is the stuff of horror stories. Now I have no doubt that you’ve convinced yourself that this is a great approach, but it’s not, and it will be replaced as soon as you leave (assuming you’re not a one man band).
- excerionsforte 7y ago
  I saw what you posted before: "You are mad". Before you press the send button you should make sure to follow (https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html) or your comment can be submitted for moderation. Leave the emotions out if you want to discuss, thanks. I can personally attack you too, but not interested and not worth the time. As I commented below. Unsubstantiated generalizations are what I distrust. From your comment here, you say "horror stories" where is the substantiation? Notice how my comment said specifically" If I need the speed", "database interpretation" of values. The use of caching for exploded unindexed values. Not sure what it is exactly you don't like other than binary data (512-64kb) in database that is protected by cache. There is literally no change over existing patterns of how to store data in a db other than telling the db not to interpret the values. Are you smearing the idea of lookaside caching in front of a DB (SQL none the less)? Plenty of companies use it. I prefer to use SQL data types unless I need the speed. What's so unreasonable? I never said that I use it everywhere like you purport it.
- evanelias 7y ago
  > I’m sorry, but no matter what you think storing all unindexed columns as binary is very, very weird You may be surprised to learn that several very large and well-known social networks use this technique -- serializing unindexed columns into a blob (typically compressed at some level) -- for their core product tables. It's not really that "weird", if you consider that literally tens of thousands of engineers work at companies doing this. Conceptually it's exactly equivalent the same technique as putting all unindexed columns in a single JSON or HSTORE column. Newer companies use those; older companies tend to have something more homegrown, but ultimately equivalent, typically wrapped by a data access service which handles the deserialization automatically. This technique is especially advantageous if you have a huge number of flexible fields per row, and many of those fields are often missing / default / zero value, and the storage scheme permits just omitting such fields/values entirely. Multiplied by trillions of rows, that results in serious cost savings.

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.
  - burpsnard 7y ago
    One i saw, was a banking backend; stored blobs retrieved by accountnumber from the mainframe were passed to mid-tier servers that mounted the blobs as r/w db instances. Sort of like piles of sqlite db's on S3
    - deleted 7y ago
      [deleted]
- Aeolun 7y ago
  I mean, you do you, but you probably see the pattern in the responses to your comment. It is theoretically possible that there’s a good use case for storing all fields in a blob. It’s just that none of the ones you mentioned are ones I agree with. The only thing that makes sense to store in a blob is something that is otherwise incomprehensible (e.g. a jpeg file, doesn’t fit in any other column type), and even that is a bad idea.
  - excerionsforte 7y ago
    Dissenting/disbelief responses do not appear to be from people who've worked with RDBMS at the high scale. I'm content with being able to keep the system simple, sleep at night, not getting pings for database and feeling good about the choice. Big enough reward for me and any others who have dealt with this.

orf 7y ago

I’m sorry, but no matter what you think storing all unindexed columns as binary is very, very weird. You can write all the documentation you want as to why this is superior to what literally everyone else is doing and has been doing since the dawn of time, but it won’t stop people joining your project thinking that you are mad. This kind of thing is the stuff of horror stories. Now I have no doubt that you’ve convinced yourself that this is a great approach, but it’s not, and it will be replaced as soon as you leave (assuming you’re not a one man band).

excerionsforte 7y ago
I saw what you posted before: "You are mad". Before you press the send button you should make sure to follow (https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html) or your comment can be submitted for moderation. Leave the emotions out if you want to discuss, thanks. I can personally attack you too, but not interested and not worth the time. As I commented below. Unsubstantiated generalizations are what I distrust. From your comment here, you say "horror stories" where is the substantiation? Notice how my comment said specifically" If I need the speed", "database interpretation" of values. The use of caching for exploded unindexed values. Not sure what it is exactly you don't like other than binary data (512-64kb) in database that is protected by cache. There is literally no change over existing patterns of how to store data in a db other than telling the db not to interpret the values. Are you smearing the idea of lookaside caching in front of a DB (SQL none the less)? Plenty of companies use it. I prefer to use SQL data types unless I need the speed. What's so unreasonable? I never said that I use it everywhere like you purport it.
evanelias 7y ago
> I’m sorry, but no matter what you think storing all unindexed columns as binary is very, very weird You may be surprised to learn that several very large and well-known social networks use this technique -- serializing unindexed columns into a blob (typically compressed at some level) -- for their core product tables. It's not really that "weird", if you consider that literally tens of thousands of engineers work at companies doing this. Conceptually it's exactly equivalent the same technique as putting all unindexed columns in a single JSON or HSTORE column. Newer companies use those; older companies tend to have something more homegrown, but ultimately equivalent, typically wrapped by a data access service which handles the deserialization automatically. This technique is especially advantageous if you have a huge number of flexible fields per row, and many of those fields are often missing / default / zero value, and the storage scheme permits just omitting such fields/values entirely. Multiplied by trillions of rows, that results in serious cost savings.