[sgarland]

> Even with indexes, joins are costly, especially under load at scale with millions of simultaneous users. You can avoid a lot of this by simply having that sub-table information inside a JSON field with the row in question.

They’re really not that bad. Even on large-ish tables (hundreds of millions of rows), the typical query time I see for a query with 1-2 inner joins is 1-2 msec. That can of course vary with result set size, but in general it’s going to be dwarfed by network RTT.

If you’ve tested your schema with normalized and denormalized versions and found a significant difference that justifies it, by all means, but IME query speed for any non-trivial query is generally dominated by query shape, index design, and schema design (specifically for clustering indices, not taking advantage of it to have physical and logical tuple correlation).

[tracker1]

yeah, 1-2 across an index is fine... but I've seen live websites with very normalized structures where it took no less than 30 joins to get a flat view of a single resource. I could do the same with about 4 secondary queries (each with several joins), but I was trying to map a mental model of where all the data lived as a single view, so I could extract flat records for a search database.

There was absolutely zero advantage in how the data was structured in that case (auto classifieds website).

That's not to say that database normalization and modest level of joins is always bad... it's just something that can go too far, and holds things back even more in certain environments. FWIW, I started bundling child data in XML before JSON was a thing in highly controlled environments where any schema change was very difficult and required approvals through several groups in a large banking org.

That JSON is fairly well supported today only makes this an easier and better option IMO. Again, sometimes, but enough times to make note of.