4 ms·

One big drawback of postgres when updating data in a jsonb column is that it needs to rewrite everything even when you update the value of a single key, this ge

One big drawback of postgres when updating data in a jsonb column is that it needs to rewrite everything even when you update the value of a single key, this gets very expensive if you need to update many rows. As far as I can tell, Mongodb handles this better. Other than this I also prefer postgres despite this limitation.

jabberwcky 6y ago
Mongo stores objects pretty much the same as PG here, a single key update requires a rewrite of the object. Maybe Mongo has some fancy algorithms to avoid full decode/reencode in some cases, but in principle the physical data formats are almost identical
- isbvhodnvemrwvn 6y ago
  In a tiny bit more detail - mongo uses BSON, which is effectively TLV encoded json. I'm not sure how storage of entire collections is done though. https://en.wikipedia.org/wiki/BSON https://en.wikipedia.org/wiki/BSON
  - rualca 6y ago
    > (...) BSON, which is effectively TLV encoded json. For those who, like me, never heard of TLV prior to this post, it's type-length-value encoding. https://en.wikipedia.org/wiki/Type-length-value https://en.wikipedia.org/wiki/Type-length-value
    - swyx 6y ago
      thanks for the link, you weren't alone
karsinkk 6y ago
Disclaimer : I'm a Software Engineer at Oracle In Oracle 21c and the Autonomous JSON Database, we introduced a new Native JSON Datatype with it's own Binary Storage format called OSON[1]. With OSON, you can perform partial updates on a json document. The added benefit is that this results in significantly lesser redo log size. [1] https://blogs.oracle.com/jsondb/osonformat https://blogs.oracle.com/jsondb/osonformat
- dan-robertson 6y ago
  Are there any (independent) benchmarks for how the performance of this compares to other database management systems?
  - j16sdiz 6y ago
    I think standard Oracle license prohibite benchmarking.
    - samb1729 6y ago
      This sounds like a fantastic reason among many to actively resist ever using their software.
  - twic 6y ago
    The benchmark should really compare Oracle to PostgreSQL with however much extra RAM the cost of an Oracle license would buy.

jabberwcky 6y ago

Mongo stores objects pretty much the same as PG here, a single key update requires a rewrite of the object. Maybe Mongo has some fancy algorithms to avoid full decode/reencode in some cases, but in principle the physical data formats are almost identical

isbvhodnvemrwvn 6y ago
In a tiny bit more detail - mongo uses BSON, which is effectively TLV encoded json. I'm not sure how storage of entire collections is done though. https://en.wikipedia.org/wiki/BSON https://en.wikipedia.org/wiki/BSON
- rualca 6y ago
  > (...) BSON, which is effectively TLV encoded json. For those who, like me, never heard of TLV prior to this post, it's type-length-value encoding. https://en.wikipedia.org/wiki/Type-length-value https://en.wikipedia.org/wiki/Type-length-value
  - swyx 6y ago
    thanks for the link, you weren't alone

karsinkk 6y ago

Disclaimer : I'm a Software Engineer at Oracle In Oracle 21c and the Autonomous JSON Database, we introduced a new Native JSON Datatype with it's own Binary Storage format called OSON[1]. With OSON, you can perform partial updates on a json document. The added benefit is that this results in significantly lesser redo log size. [1] https://blogs.oracle.com/jsondb/osonformat https://blogs.oracle.com/jsondb/osonformat

dan-robertson 6y ago
Are there any (independent) benchmarks for how the performance of this compares to other database management systems?
- j16sdiz 6y ago
  I think standard Oracle license prohibite benchmarking.
  - samb1729 6y ago
    This sounds like a fantastic reason among many to actively resist ever using their software.
- twic 6y ago
  The benchmark should really compare Oracle to PostgreSQL with however much extra RAM the cost of an Oracle license would buy.