4 ms·

In my current case I have highly unstructured data stored as a jsonb in postgres with some basic fields for indexing. So the database doesn't have a schema and

In my current case I have highly unstructured data stored as a jsonb in postgres with some basic fields for indexing. So the database doesn't have a schema and making many tables for each variation of the data would take an enormous amount of time.

jfrn 8y ago
If its truly unstructured why would you not use a Text or bytea field? Storing as json sounds pretty structured unless it’s all in one k-v pair, and then what’s the point of that?
borplk 8y ago
> So the database doesn't have a schema What you described IS the schema of the database. Schema doesn't imply rigidness. A schema can precisely define a wild lack of structure. The reason it's important to have it is because it keeps you in the control seat.
- gerbilly 8y ago
  Often the data in a database can outlive the application it was initially designed for. Say you have a legacy application you want to rewrite.[1] If the data is stored in an unstructured way, then the schema is in the code base of the legacy application, and will have to be reimplemented in the new application.[2] However, if the schema is implemented by the database, then the application can be upgraded relatively easily. [1] Or an example more suited to modern ears, say you just wrote an MVP and want to do a rewrite now that you have 50 customers on it. [2] Often in a a bug for bug compatible manner.
  - wvenable 8y ago
    I have an application written almost 20 years ago using unstructured storage (imagine a JSON blob but not JSON) and it still has code to handle the various schema changes that have happened over the years. The data itself is what would be considered unstructured; basically many dozens of different document types. This is why I chose to store it this way. However, in retrospect, it would have been much better for maintenance, performance, quality, and code-size to have created every document type as a separate strongly-typed table.
- ben509 8y ago
  Another way of looking at it is that you have a schema whether you like it or not. If I write some code to sum up account balances, it's going to expect a series of account objects, and they need to have some `.balance` field. And those fields need to be decimals, because addition will choke if they're not. Your code implicitly defines a schema, and it's utterly rigid because it will crash if the data structure is incorrect. Or worse. Imagine if some of the "balances" are actually some irrelevant field that happens to be a number, or some are missing because some of the JSON misspelled the field name and you skipped those records. Now the result you're processing is silently wrong, and who knows how far the corrupt data gets before someone catches it? All schemas are enforced rigidly because that's how computers work. The difference is whether you want the DBMS to nag you while you're coding, or whether you'd rather be woken in the middle of the night by an angry customer or boss.
  - threeseed 8y ago
    For many use cases we don't know the schema up front. So it's irrelevant whether it exists or not because we don't know it.
    - ben509 8y ago
      That's a great point, but I think the real issue there is that typical SQL DBMS's make it very hard to adjust the schema iteratively as you code. After all, if you're writing in a language like Java, you don't know what your classes look like to start with, but coders manage that by adjusting and refactoring them as they work.
ineedasername 8y ago
It sounds like you have an implicit schema, which the article addresses. Your "each variation of the data" is the gestalt of those implicit schemas. In the article's terms, the schema as built in the database itself is very minimal, but the schema represented by any code is much heavier. I'm not sure all of this is really useful distinctions to make, but it's what the article seems to be saying.

jfrn 8y ago

If its truly unstructured why would you not use a Text or bytea field? Storing as json sounds pretty structured unless it’s all in one k-v pair, and then what’s the point of that?

borplk 8y ago

> So the database doesn't have a schema What you described IS the schema of the database. Schema doesn't imply rigidness. A schema can precisely define a wild lack of structure. The reason it's important to have it is because it keeps you in the control seat.

gerbilly 8y ago
Often the data in a database can outlive the application it was initially designed for. Say you have a legacy application you want to rewrite.[1] If the data is stored in an unstructured way, then the schema is in the code base of the legacy application, and will have to be reimplemented in the new application.[2] However, if the schema is implemented by the database, then the application can be upgraded relatively easily. [1] Or an example more suited to modern ears, say you just wrote an MVP and want to do a rewrite now that you have 50 customers on it. [2] Often in a a bug for bug compatible manner.
- wvenable 8y ago
  I have an application written almost 20 years ago using unstructured storage (imagine a JSON blob but not JSON) and it still has code to handle the various schema changes that have happened over the years. The data itself is what would be considered unstructured; basically many dozens of different document types. This is why I chose to store it this way. However, in retrospect, it would have been much better for maintenance, performance, quality, and code-size to have created every document type as a separate strongly-typed table.
ben509 8y ago
Another way of looking at it is that you have a schema whether you like it or not. If I write some code to sum up account balances, it's going to expect a series of account objects, and they need to have some `.balance` field. And those fields need to be decimals, because addition will choke if they're not. Your code implicitly defines a schema, and it's utterly rigid because it will crash if the data structure is incorrect. Or worse. Imagine if some of the "balances" are actually some irrelevant field that happens to be a number, or some are missing because some of the JSON misspelled the field name and you skipped those records. Now the result you're processing is silently wrong, and who knows how far the corrupt data gets before someone catches it? All schemas are enforced rigidly because that's how computers work. The difference is whether you want the DBMS to nag you while you're coding, or whether you'd rather be woken in the middle of the night by an angry customer or boss.
- threeseed 8y ago
  For many use cases we don't know the schema up front. So it's irrelevant whether it exists or not because we don't know it.
  - ben509 8y ago
    That's a great point, but I think the real issue there is that typical SQL DBMS's make it very hard to adjust the schema iteratively as you code. After all, if you're writing in a language like Java, you don't know what your classes look like to start with, but coders manage that by adjusting and refactoring them as they work.

ineedasername 8y ago

It sounds like you have an implicit schema, which the article addresses. Your "each variation of the data" is the gestalt of those implicit schemas. In the article's terms, the schema as built in the database itself is very minimal, but the schema represented by any code is much heavier. I'm not sure all of this is really useful distinctions to make, but it's what the article seems to be saying.