5 ms·

> It's a Google/Twitter size problem that most industries wouldn't have, but since it's the new shiny thing, you know how that goes. You nailed it on both poin

> It's a Google/Twitter size problem that most industries wouldn't have, but since it's the new shiny thing, you know how that goes. You nailed it on both points. Very few organizations are going to need that kind of scale, but many want to think that they will! Even when you get to horizontal scaling, you can simply replicate what needs to be replicated. Key-value is really just a subset of relational data, right? If so, I bet we can query and push only that data out across the nodes. It's not to say non-relational models don't have a place, but they're the exception as far as I'm concerned. I prefer to start with relational and only move off of that if I can justify it. As I said upthread, though, I work in insurance and you might imagine relational works well for us.

Clubber 9y ago
Also, the unstructured data is another thing. For example, lets say you need to store a bunch of contracts (as in your industry) in PDF/TIFF or whatever. The filesystem isn't a really good place to store it because you have a loose coupling between the index in the table and the file on the filesystem. That can get messy and unless security is tight, some developer or admin could muck something up on the filesystem. This is particularly dangerous when you need to keep contracts over a period of time (say 7 years). If you have a really large amount of files, you'll either need a huge array, or figure out a way to distribute them across several NAS's or whatever, then that gets complicated. The RDBMS is a little better at storing this type of data, but it has it's own problems. Backups quickly become huge and a massive undertaking. Development databases now have a bunch of missing data (because who wants to replicate that down to lower levels), you still have the same problem when you have a large amount of data that you need to shard across NAS/arrays, etc. The NoSQL seems like a third option that may well be worth looking into. Again this is for unstructured data that you just need to reference and not necessarily query against. (like stored contracts).
- sk5t 9y ago
  > That can get messy and unless security is tight, some developer or admin could muck something up on the filesystem This isn't a great reason not to use a filesystem--it's a reason not to let staff have at the filesystem like cowboys, and not to avoid replicating and backing up the keys and values on the filesystem as if they are really important. That said, there isn't really a solid replacement for a database transaction involving both fields + blob, but most situations are okay with, say, stowing a blob in S3, and later making a half-hearted attempt to delete it if the associated database transaction fails.
- oldandtired 9y ago
  Contractual information can quite often be quite relational in nature. I have worked for organisations that had custom contracts and custom sales documentation (different industries) that were based on templates that could be filled in from a database. In some cases, the companies were successful in storing the relevant information in a database and use that to generate the documents required based on document templates. This is not hard, but it can be tedious to set up. The companies also have to have a will to walk down that path and many do not see a benefit to this kind of process. It takes a very insightful designer to manage and lead this process as well as a client who has an incentive to move in this direction. One of the considerations is that the approved contract must be in some unalterable form that can be tested against the contract generation process from the information within the database. So that there is always a means of ensuring that any information in the database cannot be corrupted (in a legal sense). That is a matter of safeguards not database information storage capabilities and there are many different ways to do this kind of security. The problem of sharing of data and replication is not often thought through properly. Oft times, I have seen replication being performed wholesale instead of piecemeal. Similarly for data sharing and the consequences have been less than efficient. Oft times, the actual requirements for sharing and repliation are very small and it is a matter of understanding what is actually required and what is desired for the business. Finally, very little data is unstructured if you look at it correctly. Many times, I have seen people talk about unstructured data (including documents of all kinds) and when you look at the data, it is highly structured. It becomes a matter of seeing what that structure is and working out what is variable and what is constant data within the structure.
- deleted 9y ago
  [deleted]
- koolba 9y ago
  This is a perfect example of not having to use the same hammer for everything. The "standard" solution to this problem is to store the files themselves in cheap yet durable object storage (S3, NFS, etc) with a reference to the unique ID or URL in your primary database. The record locator is small (which is good for the RDBMS) and can be transactionally updated in the DB with any changes that involve that object (ex: update contract associated with a client). There's no need to store the full details as a BLOB in your DB. That has nothing to do with SQL, NoSQL, distributed, or centralized. It's just a vanilla separation of immutable object data (the raw bytes of the PDFs) and application state (the record locator).
- dhd415 9y ago
  It's proprietary and therefore not an option for everyone, but SQL Server's "Filestream" feature [1] was designed for the "large blob of unstructured data" use case (small blobs of unstructured data generally work fine in regular tables). It stores large blob data efficiently in individual files directly on disk that are still managed by the database engine and written/read in the same transaction as table data. It's well-integrated with their other standard features such as backups, HA clusters, etc. It's a pretty impressive feature. [1] https://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx https://technet.microsoft.com/en-us/library/bb933993(v=sql.1...
  - nwatson 9y ago
    Postgres has the BLOB functionality, but also has the JSONB data type that not only stores structured JSON data efficiently, but also allows indexing on the contents of that data. So you can use BLOBs and explicitly store in the table what you need to search on, or else you can avoid that and take the pseudo-NoSQL route and store indexed JSON.
- SmellTheGlove 9y ago
  > For example, lets say you need to store a bunch of contracts (as in your industry) in PDF/TIFF or whatever. Contracts are not as unstructured as you would think. At least in my part of the insurance world, most of them are produced by a contract writing system of some kind. Think of it as assembling as a series of contract provisions, each having a valid set of choices to select. Use your auto policy as an example - Liability - $100k Comprehensive - $10k / $1000 Deductible Collision - $50k / $1000 Deductible Yes there are other attributes captured, but you see the pattern. It's provisions and boilerplate. The use cases needing the imaged executed contract where the attribute/provision soup is not sufficient is uncommon. The operational terms of the contract exist in some other system (and database) where it's consumable in some structured way.
marrs 9y ago
Very few organizations are going to need that kind of scale, but many want to think that they will! Further to that point, many software engineers know that NoSQL is a bad fit for their organisation but use it anyway because they wish to gain the experience required to work with Big Data in the future.
- flamtap 9y ago
  Nothing helps you learn new tech quite like fighting to the death with it!

Clubber 9y ago

Also, the unstructured data is another thing. For example, lets say you need to store a bunch of contracts (as in your industry) in PDF/TIFF or whatever. The filesystem isn't a really good place to store it because you have a loose coupling between the index in the table and the file on the filesystem. That can get messy and unless security is tight, some developer or admin could muck something up on the filesystem. This is particularly dangerous when you need to keep contracts over a period of time (say 7 years). If you have a really large amount of files, you'll either need a huge array, or figure out a way to distribute them across several NAS's or whatever, then that gets complicated. The RDBMS is a little better at storing this type of data, but it has it's own problems. Backups quickly become huge and a massive undertaking. Development databases now have a bunch of missing data (because who wants to replicate that down to lower levels), you still have the same problem when you have a large amount of data that you need to shard across NAS/arrays, etc. The NoSQL seems like a third option that may well be worth looking into. Again this is for unstructured data that you just need to reference and not necessarily query against. (like stored contracts).

sk5t 9y ago
> That can get messy and unless security is tight, some developer or admin could muck something up on the filesystem This isn't a great reason not to use a filesystem--it's a reason not to let staff have at the filesystem like cowboys, and not to avoid replicating and backing up the keys and values on the filesystem as if they are really important. That said, there isn't really a solid replacement for a database transaction involving both fields + blob, but most situations are okay with, say, stowing a blob in S3, and later making a half-hearted attempt to delete it if the associated database transaction fails.
oldandtired 9y ago
Contractual information can quite often be quite relational in nature. I have worked for organisations that had custom contracts and custom sales documentation (different industries) that were based on templates that could be filled in from a database. In some cases, the companies were successful in storing the relevant information in a database and use that to generate the documents required based on document templates. This is not hard, but it can be tedious to set up. The companies also have to have a will to walk down that path and many do not see a benefit to this kind of process. It takes a very insightful designer to manage and lead this process as well as a client who has an incentive to move in this direction. One of the considerations is that the approved contract must be in some unalterable form that can be tested against the contract generation process from the information within the database. So that there is always a means of ensuring that any information in the database cannot be corrupted (in a legal sense). That is a matter of safeguards not database information storage capabilities and there are many different ways to do this kind of security. The problem of sharing of data and replication is not often thought through properly. Oft times, I have seen replication being performed wholesale instead of piecemeal. Similarly for data sharing and the consequences have been less than efficient. Oft times, the actual requirements for sharing and repliation are very small and it is a matter of understanding what is actually required and what is desired for the business. Finally, very little data is unstructured if you look at it correctly. Many times, I have seen people talk about unstructured data (including documents of all kinds) and when you look at the data, it is highly structured. It becomes a matter of seeing what that structure is and working out what is variable and what is constant data within the structure.
deleted 9y ago
[deleted]
koolba 9y ago
This is a perfect example of not having to use the same hammer for everything. The "standard" solution to this problem is to store the files themselves in cheap yet durable object storage (S3, NFS, etc) with a reference to the unique ID or URL in your primary database. The record locator is small (which is good for the RDBMS) and can be transactionally updated in the DB with any changes that involve that object (ex: update contract associated with a client). There's no need to store the full details as a BLOB in your DB. That has nothing to do with SQL, NoSQL, distributed, or centralized. It's just a vanilla separation of immutable object data (the raw bytes of the PDFs) and application state (the record locator).
dhd415 9y ago
It's proprietary and therefore not an option for everyone, but SQL Server's "Filestream" feature [1] was designed for the "large blob of unstructured data" use case (small blobs of unstructured data generally work fine in regular tables). It stores large blob data efficiently in individual files directly on disk that are still managed by the database engine and written/read in the same transaction as table data. It's well-integrated with their other standard features such as backups, HA clusters, etc. It's a pretty impressive feature. [1] https://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx https://technet.microsoft.com/en-us/library/bb933993(v=sql.1...
- nwatson 9y ago
  Postgres has the BLOB functionality, but also has the JSONB data type that not only stores structured JSON data efficiently, but also allows indexing on the contents of that data. So you can use BLOBs and explicitly store in the table what you need to search on, or else you can avoid that and take the pseudo-NoSQL route and store indexed JSON.
SmellTheGlove 9y ago
> For example, lets say you need to store a bunch of contracts (as in your industry) in PDF/TIFF or whatever. Contracts are not as unstructured as you would think. At least in my part of the insurance world, most of them are produced by a contract writing system of some kind. Think of it as assembling as a series of contract provisions, each having a valid set of choices to select. Use your auto policy as an example - Liability - $100k Comprehensive - $10k / $1000 Deductible Collision - $50k / $1000 Deductible Yes there are other attributes captured, but you see the pattern. It's provisions and boilerplate. The use cases needing the imaged executed contract where the attribute/provision soup is not sufficient is uncommon. The operational terms of the contract exist in some other system (and database) where it's consumable in some structured way.

marrs 9y ago

Very few organizations are going to need that kind of scale, but many want to think that they will! Further to that point, many software engineers know that NoSQL is a bad fit for their organisation but use it anyway because they wish to gain the experience required to work with Big Data in the future.

flamtap 9y ago
Nothing helps you learn new tech quite like fighting to the death with it!