3 ms·

In this world you either evolve to Elastic/Cassandra/etc or return to Postgres :crab: You really gotta search your soul and ask yourself: your cool idea really

by paulmd 3y ago

In this world you either evolve to Elastic/Cassandra/etc or return to Postgres :crab: You really gotta search your soul and ask yourself: your cool idea really a database, or just a plugin for Postgres? And the answer is often the latter.

winrid 3y ago
The problem is that postgres is not a replacement. The JSON operations are not atomic which is terrible. If you wanna use PG that's cool, but I'd suggest just avoiding JSON.
- mulmboy 3y ago
  How are JSON operations not atomic? Genuinely curious
  - bandrami 3y ago
    I think the sense is not "atomic by field" or whatever you'd call that. If you're going to ignore the fact that PG is an actual ORDB and just store gigantic blobs of JSON in it, it will write the entire object whenever you update part of it, because the whole point is you're supposed to store it as a multi-field record.
    - SonOfLilit 3y ago
      Isn't that true for Mongo as well?
      - rb12345 3y ago
        Yes, but as MongoDB is a document database, storing and updating giant blobs of JSON as a single operation as opposed to breaking the JSON down into individual fields is intended behaviour. This works in Postgres too, of course, but then you lose the relational database advantages on top of the large-single-field issues. All this really comes down to is picking the right database type for the problem you're trying to solve.
  - winrid 3y ago
    I answered here: https://news.ycombinator.com/item?id=39865835 https://news.ycombinator.com/item?id=39865835
- radiospiel 3y ago
  > The JSON operations are not atomic I hear this today the first time. What exactly os not atomic, and is there a resource with more details?
  - adastral 3y ago
    Not sure if this is what the above comment means by "atomic", but a shortcoming of Postgres' JSON support is that it will have to rewrite an entire JSON object every time a part of it gets updated, no matter how many keys the update really affected. E.g. if I update an integer in a 100MB JSON object, Postgres will write ~100MB (plus WAL, TOAST overhead, etc.), not just a few bytes. I imagine this can be a no-go for certain use cases.
    - bandrami 3y ago
      It drives me batty to see people store 100MB JSON objects with a predictable internal structure as single records in an RDB rather than destructuring it and storing the fields as a single record. Like, yes, you can design it the worst possible way like that, but why? But I see it all the time.
      - Temporary_31337 3y ago
        Because schemas. The whole point of nosql is that you can alter your data model without having to reload the whole database
        bandrami 3y ago
        Which is great in the early stages of development, but people actually deploy like this
        aerhardt 3y ago
        The benefits of going schemaless in the early stages of development are highly suspect in my experience. The time that one might save in data modeling and migrations comes out from the other end with shittier code that’s harder to reason about.

by paulmd 3y ago

winrid 3y ago

The problem is that postgres is not a replacement. The JSON operations are not atomic which is terrible. If you wanna use PG that's cool, but I'd suggest just avoiding JSON.

mulmboy 3y ago
How are JSON operations not atomic? Genuinely curious
- bandrami 3y ago
  I think the sense is not "atomic by field" or whatever you'd call that. If you're going to ignore the fact that PG is an actual ORDB and just store gigantic blobs of JSON in it, it will write the entire object whenever you update part of it, because the whole point is you're supposed to store it as a multi-field record.
  - SonOfLilit 3y ago
    Isn't that true for Mongo as well?
    - rb12345 3y ago
      Yes, but as MongoDB is a document database, storing and updating giant blobs of JSON as a single operation as opposed to breaking the JSON down into individual fields is intended behaviour. This works in Postgres too, of course, but then you lose the relational database advantages on top of the large-single-field issues. All this really comes down to is picking the right database type for the problem you're trying to solve.
- winrid 3y ago
  I answered here: https://news.ycombinator.com/item?id=39865835 https://news.ycombinator.com/item?id=39865835
radiospiel 3y ago
> The JSON operations are not atomic I hear this today the first time. What exactly os not atomic, and is there a resource with more details?
- adastral 3y ago
  Not sure if this is what the above comment means by "atomic", but a shortcoming of Postgres' JSON support is that it will have to rewrite an entire JSON object every time a part of it gets updated, no matter how many keys the update really affected. E.g. if I update an integer in a 100MB JSON object, Postgres will write ~100MB (plus WAL, TOAST overhead, etc.), not just a few bytes. I imagine this can be a no-go for certain use cases.
  - bandrami 3y ago
    It drives me batty to see people store 100MB JSON objects with a predictable internal structure as single records in an RDB rather than destructuring it and storing the fields as a single record. Like, yes, you can design it the worst possible way like that, but why? But I see it all the time.
    - Temporary_31337 3y ago
      Because schemas. The whole point of nosql is that you can alter your data model without having to reload the whole database
      - bandrami 3y ago
        Which is great in the early stages of development, but people actually deploy like this
        aerhardt 3y ago
        The benefits of going schemaless in the early stages of development are highly suspect in my experience. The time that one might save in data modeling and migrations comes out from the other end with shittier code that’s harder to reason about.