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.
[Note] I'm a cofounder of Couchbase, my new project is a realtime embedded JavaScript database with local-first sync: https://fireproof.storage
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.
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.
Common in a situation where you're writing for a presentation layer and you need human readable data.
30 sounds a lot, but I'm pretty sure I've written SQL with almost 20 while building an ERP application so I don't see why 30 implies garbage schema, just a complex one.
And as you say, it shouldn't be a problem anyway, there no magic number of joined tables where things stay breaking down.
Yup, this was it. For compactness (theoretical) alot of the fields were stored as small integers, then a lookup. Here is the relevant offender if interested.
https://github.com/praphael/baseball/blob/main/retrosheet_in...
Alot of lookup for player names and more descriptive fields (field conditions, play descriptions, etc.)
In retrospect, this all could have been done as part of a post-process step on the backend. I already was doing that for certain fields such as the month. Which does fail the "anything that can be reasonably be done on the DB end should be done by the DB" test. But it might be a better idea.
There's probably an interesting story there. I for one am curious.
This was a baseball database. View is a "game situation" view.
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.
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.
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
If you don't want to pay the cost of updating the full JSON value, that's what columns are for.
You pay the full cost for writing the entire new row in PostgreSQL if only a single column changes. There’s some saving for indexes, but the row data is always written in full for the new row even if just one column changes.
Now that’s no reason not to use actual columns. The value of a well defined schema is knowing the types and constraints of your data.
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?
UPDATE characters
SET jsonb_column = jsonb_set(
jsonb_column,
'{superpower}',
'"invisibility"'
)
WHERE id = 5;
UPDATE characters
SET jsonb_column = jsonb_column ||
'{"superpower": "invisibility"}'
WHERE id = 5;
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.
UPDATE characters
SET jsonb_column['superpower'] = '"invisibility"'
WHERE id = 5;
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.
AFAICT MongoDB’s storage layer uses 4kb pages, so every modification has to write at least 4kb.