5 ms·

I tried a similar approach in a previous startup - treat data as documents and store in a JSONB field. Postgres was awesome and handled this brilliantly, but t

I tried a similar approach in a previous startup - treat data as documents and store in a JSONB field. Postgres was awesome and handled this brilliantly, but the lack of schema and typing killed it. We just ended up fighting data quality the whole time. We couldn't assume that any document had all the required fields, or that they were in a format that made sense e.g. the Price column sometimes had currency symbols, and sometimes commas-and-periods in UK/US format and sometimes in Euro format - sorting by Price involved some complicated parsing of all the records first. We moved back to relational tables. I won't say I'd never do this again, but I would definitely not just throw JSON documents to a database and expect good things to happen.

jimmyl02 2y ago
the view I now have is that for a relational table, yes you have to suffer through migrations but at least they are in sql. for document based stores, you still have to have migrations, but they are just implemented in code json documents sound great, especially initially, but end up being a maintenance nightmare
- hans_castorp 2y ago
  > for document based stores, you still have to have migrations, but they are just implemented in code The problem with that - in my experience - is that migrating the structure for thousands (if not millions) of documents is way slower than running a DDL command (as it means reading each document, parsing it, modifying it and writing it back). Many DDL commands are just metadata update to the system catalogs so they are quite fast (e.g. adding a new column with a default value). With documents you wind up with millions of single row updates. This can be mitigated by doing a "lazy" migration when a document with the old structure is first read. But that makes the code much more complicated.
- globular-toast 2y ago
  Or, to put it another way, yes you have to write and maintain an upfront schema, but a document-based system has a schema too, it's just distributed amongst 50 codebase and 10 people's heads.
goosejuice 2y ago
Sounds like most/all of these issues would be solved by validation at the app layer.
- ivandenysov 2y ago
  True, but for example with db-level validations I don’t have to worry that a new developer ships a batch job that runs raw inserts for performance reasons and bypasses app layer validations.
  - zepolen 2y ago
    What? You should be exposing your data access layer as a microservice and all your db level validations should be done there... You can even write a small DSL syntax language to make it easier to use for developers, and perhaps an Obviously Reduntant Middleware that sits between them to convert their programming language objects to the DSL. Add some batch support, perhaps transactional locks (using mongo, we want to be webscale after all) and perhaps a small terminal based client and voila, no one should ever need to deal with petty integrity in the db again.
    - deleted 2y ago
      [deleted]
    - eropple 2y ago
      This post is a fantastic example of Poe's Law. You had me for a second.
  - goosejuice 2y ago
    I'm not advocating for no database level validations. One can and should have both in my opinion. Postgres supports constraints on jsonb as well. I would generally advocate for no write access outside of the app as well. Certainly for new developers. Get some tests on that batch job.
    - corytheboyd 2y ago
jamil7 2y ago
I don’t do much backend work at the moment but I found using JSONB fields for prototyping endpoints to be quite handy. Once I had a feature figured out and working, I’d create a proper schema.
- marcus_holmes 2y ago
  I've found Postgres' Hstore type useful for this, too. I often create a "metadata" hstore field on a table and use it for random bits of data I don't want to create an actual field for yet. When I find that the application needs that bit of data and in a certain format, I'll move it into an actual field.
  - cryptonector 2y ago
    hstore is basically the flat precursor to JSONB, and IMO it's as good as obsolete. I wouldn't use hstore in a new project, just JSONB.
    - marcus_holmes 2y ago
      I get that, but I think the temptation to add too much structure to the JSONB would be too great; this is just a temporary or prototype field, so it just needs a name and a value. Hstore works great for this.
      - cryptonector 2y ago
        And I get that, but also hstore is ugly. Just be disciplined :)
winternewt 2y ago
If you have schema requirements, why _not_ do it with tables and columns instead? The point of the jsonb column would be precisely to store arbitrary stuff that has no schema.
- bruce343434 2y ago
  I usually see this (dynamic scripting langs, schemaless databases) play out as a very good environment to prototype in, very few barriers to change things or make stuff up as you go. Then the prototype is not discarded but "iterated" to form the actual production code. The (now needlessly so) flexible JSON store is grand-fathered in. I have yet to come across an actual use case for JSON stores in a production app with an established design. What the hell do you mean you have no idea what the data might hold?? Why are you storing unknown, undefined, undefinable things??? Or perhaps, there actually is a schema i.e. fields we rely on being present, but we were too lazy to define it formally?
  - okr 2y ago
    Customers sent custom things.
  - SSLy 2y ago
    We're evaluating solutions to ingest business app events and infrastructure logs. Document-ish JSON-ish "DB"/search engine seems like best solution, because no one does structured logs properly. Now we have to pick the stack. ELK, Loki + Graphana, Graylog or maybe just dump into MongoDB?
  - mexicocitinluez 2y ago
    > I have yet to come across an actual use case for JSON stores in a production app with an established design Healthcare. Clinical data is unstructured and constantly changing. Would you build out a table with 2000 columns that changed yearly? What about 5000?
  - eropple 2y ago
    Well, there are a decent number of those use cases, but the one I find most compelling is storing an exact record of what another service sent me in case I need it later. I pull out and store in my typed schema the relevant bits of, say, an OpenID Connect response, but I'll often store the entire response as well in case I want to add functionality in the future based on additional fields, etc. in that response and roll forward without having to have an intermediate state.
raverbashing 2y ago
> the Price column sometimes had currency symbols, and sometimes commas-and-periods in UK/US format and sometimes in Euro format Well this is not a relational issue is it? It is a data normalization issue
- marcus_holmes 2y ago
  Yes, true. But that was a single example off the top of my head. There were lots of others. Not saying the approach isn't suitable for some use cases. Just that I'd be really careful that this is one of those use cases next time.
aden1ne 2y ago
Had a similar experience. What killed it for me, is that no statistics can be gathered for JSONB columns. This in turn really messes with the query planner once you do something like `select a.* from a join b on a.id = b.a_id where b.my_jsonb_column ->> 'foo' = 'bar';`. Given the lack of statistics, the query planner loves going for a nested loop rather than hash or merge join where those would appropriate, leading to abysmal performance. There is an thread[0] on the PostgreSQL mailing list to add at least some statistics on JSONB column, but this has gone nowhere since 2022. [0]: https://www.postgresql.org/message-id/flat/c9c4bd20-996c-100e-25e7-27e27bb1da7c%40enterprisedb.com https://www.postgresql.org/message-id/flat/c9c4bd20-996c-100...
- jeffffff 2y ago
  it's kind of dumb that postgres uses a nested loop join instead of a hash join there. hash join almost always has the best worst-case behavior, and without stats it should be the default choice.
tracker1 2y ago
This is an issue regardless of your type of Document storage. There are different options for verifying document details and versioning when approaching Document oriented databases. A typical usage is to have a per-record version number and pass documents through a verifier either as part of insert, update or query to ensure all migrations to the current version have occurred.
cruffle_duffle 2y ago
You know I’ve found that LLM’s are awfully good at taking random JSON crap and “converting it” to a consistent format. You don’t even need a large LLM… something like ChatGPT 3.5 or an equivalent class of LLM can do the work just fine. It’s not perfect and requires a fair amount of effort to nail the prompt but when it works it works.

jimmyl02 2y ago

the view I now have is that for a relational table, yes you have to suffer through migrations but at least they are in sql. for document based stores, you still have to have migrations, but they are just implemented in code json documents sound great, especially initially, but end up being a maintenance nightmare

hans_castorp 2y ago
> for document based stores, you still have to have migrations, but they are just implemented in code The problem with that - in my experience - is that migrating the structure for thousands (if not millions) of documents is way slower than running a DDL command (as it means reading each document, parsing it, modifying it and writing it back). Many DDL commands are just metadata update to the system catalogs so they are quite fast (e.g. adding a new column with a default value). With documents you wind up with millions of single row updates. This can be mitigated by doing a "lazy" migration when a document with the old structure is first read. But that makes the code much more complicated.
globular-toast 2y ago
Or, to put it another way, yes you have to write and maintain an upfront schema, but a document-based system has a schema too, it's just distributed amongst 50 codebase and 10 people's heads.

goosejuice 2y ago

Sounds like most/all of these issues would be solved by validation at the app layer.

ivandenysov 2y ago
True, but for example with db-level validations I don’t have to worry that a new developer ships a batch job that runs raw inserts for performance reasons and bypasses app layer validations.
- zepolen 2y ago
  What? You should be exposing your data access layer as a microservice and all your db level validations should be done there... You can even write a small DSL syntax language to make it easier to use for developers, and perhaps an Obviously Reduntant Middleware that sits between them to convert their programming language objects to the DSL. Add some batch support, perhaps transactional locks (using mongo, we want to be webscale after all) and perhaps a small terminal based client and voila, no one should ever need to deal with petty integrity in the db again.
  - deleted 2y ago
    [deleted]
  - eropple 2y ago
    This post is a fantastic example of Poe's Law. You had me for a second.
- goosejuice 2y ago
  I'm not advocating for no database level validations. One can and should have both in my opinion. Postgres supports constraints on jsonb as well. I would generally advocate for no write access outside of the app as well. Certainly for new developers. Get some tests on that batch job.
  - corytheboyd 2y ago

jamil7 2y ago

I don’t do much backend work at the moment but I found using JSONB fields for prototyping endpoints to be quite handy. Once I had a feature figured out and working, I’d create a proper schema.

marcus_holmes 2y ago
I've found Postgres' Hstore type useful for this, too. I often create a "metadata" hstore field on a table and use it for random bits of data I don't want to create an actual field for yet. When I find that the application needs that bit of data and in a certain format, I'll move it into an actual field.
- cryptonector 2y ago
  hstore is basically the flat precursor to JSONB, and IMO it's as good as obsolete. I wouldn't use hstore in a new project, just JSONB.
  - marcus_holmes 2y ago
    I get that, but I think the temptation to add too much structure to the JSONB would be too great; this is just a temporary or prototype field, so it just needs a name and a value. Hstore works great for this.
    - cryptonector 2y ago
      And I get that, but also hstore is ugly. Just be disciplined :)

winternewt 2y ago

If you have schema requirements, why _not_ do it with tables and columns instead? The point of the jsonb column would be precisely to store arbitrary stuff that has no schema.

bruce343434 2y ago
I usually see this (dynamic scripting langs, schemaless databases) play out as a very good environment to prototype in, very few barriers to change things or make stuff up as you go. Then the prototype is not discarded but "iterated" to form the actual production code. The (now needlessly so) flexible JSON store is grand-fathered in. I have yet to come across an actual use case for JSON stores in a production app with an established design. What the hell do you mean you have no idea what the data might hold?? Why are you storing unknown, undefined, undefinable things??? Or perhaps, there actually is a schema i.e. fields we rely on being present, but we were too lazy to define it formally?
- okr 2y ago
  Customers sent custom things.
- SSLy 2y ago
  We're evaluating solutions to ingest business app events and infrastructure logs. Document-ish JSON-ish "DB"/search engine seems like best solution, because no one does structured logs properly. Now we have to pick the stack. ELK, Loki + Graphana, Graylog or maybe just dump into MongoDB?
- mexicocitinluez 2y ago
  > I have yet to come across an actual use case for JSON stores in a production app with an established design Healthcare. Clinical data is unstructured and constantly changing. Would you build out a table with 2000 columns that changed yearly? What about 5000?
- eropple 2y ago
  Well, there are a decent number of those use cases, but the one I find most compelling is storing an exact record of what another service sent me in case I need it later. I pull out and store in my typed schema the relevant bits of, say, an OpenID Connect response, but I'll often store the entire response as well in case I want to add functionality in the future based on additional fields, etc. in that response and roll forward without having to have an intermediate state.

raverbashing 2y ago

> the Price column sometimes had currency symbols, and sometimes commas-and-periods in UK/US format and sometimes in Euro format Well this is not a relational issue is it? It is a data normalization issue

marcus_holmes 2y ago
Yes, true. But that was a single example off the top of my head. There were lots of others. Not saying the approach isn't suitable for some use cases. Just that I'd be really careful that this is one of those use cases next time.

aden1ne 2y ago

Had a similar experience. What killed it for me, is that no statistics can be gathered for JSONB columns. This in turn really messes with the query planner once you do something like `select a.* from a join b on a.id = b.a_id where b.my_jsonb_column ->> 'foo' = 'bar';`. Given the lack of statistics, the query planner loves going for a nested loop rather than hash or merge join where those would appropriate, leading to abysmal performance. There is an thread[0] on the PostgreSQL mailing list to add at least some statistics on JSONB column, but this has gone nowhere since 2022. [0]: https://www.postgresql.org/message-id/flat/c9c4bd20-996c-100e-25e7-27e27bb1da7c%40enterprisedb.com https://www.postgresql.org/message-id/flat/c9c4bd20-996c-100...

jeffffff 2y ago
it's kind of dumb that postgres uses a nested loop join instead of a hash join there. hash join almost always has the best worst-case behavior, and without stats it should be the default choice.

tracker1 2y ago

This is an issue regardless of your type of Document storage. There are different options for verifying document details and versioning when approaching Document oriented databases. A typical usage is to have a per-record version number and pass documents through a verifier either as part of insert, update or query to ensure all migrations to the current version have occurred.

cruffle_duffle 2y ago

You know I’ve found that LLM’s are awfully good at taking random JSON crap and “converting it” to a consistent format. You don’t even need a large LLM… something like ChatGPT 3.5 or an equivalent class of LLM can do the work just fine. It’s not perfect and requires a fair amount of effort to nail the prompt but when it works it works.