3 ms·

I still don't understand the advantage of NoSQL over just defining a JSON column and additionally defining columns for the fields you want to query. Postgres le

I still don't understand the advantage of NoSQL over just defining a JSON column and additionally defining columns for the fields you want to query. Postgres lets you do queries over json documents too iirc.

Thaxll 2y ago
JSON in PG is vastly inferior to MongoDB, that being performance and query friendliness from code. Something that most people don't know, any time you add a new field in a jsonb doc, PG has to rewrite the entire document.
- wild_egg 2y ago
  Does MongoDB still throw safety out the window to win benchmarks? I'll give you that the postgres query operators for JSON can be cumbersome, depending on your library support, but JSONB performance has been rock solid for a long time now and I've never had reason to fear my data suddenly vanishing Maybe mongo reliability has improved in the last few years but they did enough reputational damage in the decade prior that I'll never have trust in systems built on top of it
- mbreese 2y ago
  In my experience, you’re going to pay for that complexity at some point. You either pay it at the write end or the read/query end. I don’t see a way around that, so if PG explicitly makes it so that you pay it once on the write, I’m good with that. The alternative is a more complicated data storage layout that makes queries more complicated. That’s okay too, but it just depends on your use case. I prefer the stability and “correctness” of PG, but if more flexible queries are important to you, have at it. It’s nice to have more options for JSON storage.
- simonw 2y ago
  I would have thought that the fact that the entire jsonb value is rewritten if you add a new field to it would be clear to anyone who gave it a few seconds of thought. How else could that work? If you don't want to pay the cost of updating the full JSON value, that's what columns are for.
  - brigadier132 2y ago
    > I would have thought that the fact that the entire jsonb value is rewritten if you add a new field to it would be clear to anyone who gave it a few seconds of thought. How else could that work? We are talking about postgres, unless someone went into the manual to look up the performance characteristics of jsonb columns why wouldn't they assume that it works in the most optimal way?
    - simonw 2y ago
      The clue is in how the query is written: UPDATE characters SET jsonb_column = jsonb_set( jsonb_column, '{superpower}', '"invisibility"' ) WHERE id = 5; Or: UPDATE characters SET jsonb_column = jsonb_column || '{"superpower": "invisibility"}' WHERE id = 5; In both cases the SET is a pretty strong clue that the entire value will be overwritten. I have immense respect for the PostgreSQL development team but I still can't imagine how they would optimize that not to be a rewrite of the stored JSON value.
      - wild_egg 2y ago
        I'm not sure if it's just sugar but PostgreSQL does allow subscripting on JSONB in UPDATE statements UPDATE characters SET jsonb_column['superpower'] = '"invisibility"' WHERE id = 5; Can't seem to find any docs around whether that still does a full rewrite under the hood or not but seemed like an important omission from your examples
  - vlovich123 2y ago
    There are obviously ways you could accomplish it. RocksDB’s Merge operator uses this as one of the canonical examples - your update records just the new value through a merge record. When you go to retrieve, you have to keep going through delta updates to reconstruct what the value is now & you only rewrite the value as part of a large compaction process. This way the cost of the write is the cost of the delta, the read cost is impacted although in practice negligible if you use a row cache, and the cost of rewriting the document is amortized into a much larger operation.
- srcreigh 2y ago
  Wdym rewrite the entire document? AFAICT MongoDB’s storage layer uses 4kb pages, so every modification has to write at least 4kb.
jchanimal 2y ago
In the same way SQL encourages you to normalize your data for DRY representation, NoSQL encourages you to denormalize for fast retreival. I wrote about this more than a decade ago, and the basics haven't changed: https://web.archive.org/web/20160312130429/http://blog.couchbase.com/performance-oriented-architecture https://web.archive.org/web/20160312130429/http://blog.couch... [Note] I'm a cofounder of Couchbase, my new project is a realtime embedded JavaScript database with local-first sync: https://fireproof.storage https://fireproof.storage
- SJC_Hacker 2y ago
  I guess materialized views are the middle ground here. I had a DB with like 30 joins and it was dog slow. Granted it was SQLite. While SQLite didn't have MATERIALIZE VIEW, what I did was CREATE TABLE AS, in effect denormalizing my heavily normalized DB.
  - zepolen 2y ago
    30 joins, immediately I'm thinking your schema was garbage. But assuming it wasn't, and you actually needed to join across thirty(!) tables (did I mention that's insane btw), it still shouldn't be a problem assuming you had the correct indexes and are querying in a sane manner.
    - maxbond 2y ago
      Maybe instead of jumping to the conclusion they were incompetent, you could ask questions about the things you find surprising. "Why did you need so many tables? 30 joins should still be doable, did you have some kind of resource constraints?" There's probably an interesting story there. I for one am curious.
      - SJC_Hacker 2y ago
        Actual count was 54. This was a baseball database. View is a "game situation" view. https://github.com/praphael/baseball/blob/main/retrosheet_init_db.py#L1274 https://github.com/praphael/baseball/blob/main/retrosheet_in... The real killer wasn't actually with this view. It was the fact it was joining with game_info_view, which itself had about 15 joins. game_info_view is what I ended up materializing. Speed was then reasonable (under 1 s) with just this step. I was trying to do everything in totally in memory (which was possible when it was just the gamelogs) so was really trying to minimize space, which required denormalization. Altogether the DB prior to the " denormalization" was about 3 GB for the entire history of Major League Baseball :-). The Retrosheet files however were < 1 GB uncompressed (about 250 MB compressed) and that. "Materializing" the game_info_view added another 1 GB, so 4 GB total. So the DB even with its supposedly "efficient" storage was expanding the plain-text storage by a factor of 3-4.

Thaxll 2y ago

JSON in PG is vastly inferior to MongoDB, that being performance and query friendliness from code. Something that most people don't know, any time you add a new field in a jsonb doc, PG has to rewrite the entire document.

wild_egg 2y ago
Does MongoDB still throw safety out the window to win benchmarks? I'll give you that the postgres query operators for JSON can be cumbersome, depending on your library support, but JSONB performance has been rock solid for a long time now and I've never had reason to fear my data suddenly vanishing Maybe mongo reliability has improved in the last few years but they did enough reputational damage in the decade prior that I'll never have trust in systems built on top of it
mbreese 2y ago
In my experience, you’re going to pay for that complexity at some point. You either pay it at the write end or the read/query end. I don’t see a way around that, so if PG explicitly makes it so that you pay it once on the write, I’m good with that. The alternative is a more complicated data storage layout that makes queries more complicated. That’s okay too, but it just depends on your use case. I prefer the stability and “correctness” of PG, but if more flexible queries are important to you, have at it. It’s nice to have more options for JSON storage.
simonw 2y ago
I would have thought that the fact that the entire jsonb value is rewritten if you add a new field to it would be clear to anyone who gave it a few seconds of thought. How else could that work? If you don't want to pay the cost of updating the full JSON value, that's what columns are for.
- brigadier132 2y ago
  > I would have thought that the fact that the entire jsonb value is rewritten if you add a new field to it would be clear to anyone who gave it a few seconds of thought. How else could that work? We are talking about postgres, unless someone went into the manual to look up the performance characteristics of jsonb columns why wouldn't they assume that it works in the most optimal way?
  - simonw 2y ago
    The clue is in how the query is written: UPDATE characters SET jsonb_column = jsonb_set( jsonb_column, '{superpower}', '"invisibility"' ) WHERE id = 5; Or: UPDATE characters SET jsonb_column = jsonb_column || '{"superpower": "invisibility"}' WHERE id = 5; In both cases the SET is a pretty strong clue that the entire value will be overwritten. I have immense respect for the PostgreSQL development team but I still can't imagine how they would optimize that not to be a rewrite of the stored JSON value.
    - wild_egg 2y ago
      I'm not sure if it's just sugar but PostgreSQL does allow subscripting on JSONB in UPDATE statements UPDATE characters SET jsonb_column['superpower'] = '"invisibility"' WHERE id = 5; Can't seem to find any docs around whether that still does a full rewrite under the hood or not but seemed like an important omission from your examples
- vlovich123 2y ago
  There are obviously ways you could accomplish it. RocksDB’s Merge operator uses this as one of the canonical examples - your update records just the new value through a merge record. When you go to retrieve, you have to keep going through delta updates to reconstruct what the value is now & you only rewrite the value as part of a large compaction process. This way the cost of the write is the cost of the delta, the read cost is impacted although in practice negligible if you use a row cache, and the cost of rewriting the document is amortized into a much larger operation.
srcreigh 2y ago
Wdym rewrite the entire document? AFAICT MongoDB’s storage layer uses 4kb pages, so every modification has to write at least 4kb.

jchanimal 2y ago

In the same way SQL encourages you to normalize your data for DRY representation, NoSQL encourages you to denormalize for fast retreival. I wrote about this more than a decade ago, and the basics haven't changed: https://web.archive.org/web/20160312130429/http://blog.couchbase.com/performance-oriented-architecture https://web.archive.org/web/20160312130429/http://blog.couch... [Note] I'm a cofounder of Couchbase, my new project is a realtime embedded JavaScript database with local-first sync: https://fireproof.storage https://fireproof.storage

SJC_Hacker 2y ago
I guess materialized views are the middle ground here. I had a DB with like 30 joins and it was dog slow. Granted it was SQLite. While SQLite didn't have MATERIALIZE VIEW, what I did was CREATE TABLE AS, in effect denormalizing my heavily normalized DB.
- zepolen 2y ago
  30 joins, immediately I'm thinking your schema was garbage. But assuming it wasn't, and you actually needed to join across thirty(!) tables (did I mention that's insane btw), it still shouldn't be a problem assuming you had the correct indexes and are querying in a sane manner.
  - maxbond 2y ago
    Maybe instead of jumping to the conclusion they were incompetent, you could ask questions about the things you find surprising. "Why did you need so many tables? 30 joins should still be doable, did you have some kind of resource constraints?" There's probably an interesting story there. I for one am curious.
    - SJC_Hacker 2y ago
      Actual count was 54. This was a baseball database. View is a "game situation" view. https://github.com/praphael/baseball/blob/main/retrosheet_init_db.py#L1274 https://github.com/praphael/baseball/blob/main/retrosheet_in... The real killer wasn't actually with this view. It was the fact it was joining with game_info_view, which itself had about 15 joins. game_info_view is what I ended up materializing. Speed was then reasonable (under 1 s) with just this step. I was trying to do everything in totally in memory (which was possible when it was just the gamelogs) so was really trying to minimize space, which required denormalization. Altogether the DB prior to the " denormalization" was about 3 GB for the entire history of Major League Baseball :-). The Retrosheet files however were < 1 GB uncompressed (about 250 MB compressed) and that. "Materializing" the game_info_view added another 1 GB, so 4 GB total. So the DB even with its supposedly "efficient" storage was expanding the plain-text storage by a factor of 3-4.