3 ms·

Re: JSON https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.org/docs/current/datatype-json.html Re: RDBMS was not designed to st

by ttfkam 3y ago

Re: JSON https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.org/docs/current/datatype-json.html Re: RDBMS was not designed to store JSON https://youtube.com/watch?v=rpw_x8TtqTo https://youtube.com/watch?v=rpw_x8TtqTo

sgarland 3y ago
Just because RDBMS have added JSON support (MySQL has had it since 5.6 as well) doesn’t mean it’s a good fit. The language and its implementations are designed for relational data that can be normalized; JSON is neither. Have you ever tested JSON vs. normalized data at scale? Millions+ of rows? I have, and I assure you, JSON loses. Last I checked, Postgres does a terrible job at collecting stats on JSONB - the default type - so query plans aren’t great. Indexing JSON columns is also notoriously hard to do well (even moreso in MySQL), as is using the correct operators to ensure the indices are actually used.
- ttfkam 3y ago
  Millions of rows with a JSON column? Yes, indeed I have. Recently in fact. When all normalized fields are populated, you're absolutely right. However that wasn't our dataset. We had entries with sparse keys. This was due to the nature of the data we were ingesting. We ended with multiple partial expression indexes on the JSON column due to the flexibility it provided. Each index ended up relatively small (again, sparse keys), didn't require a boatload of null values in our tables, was more flexible as new data came in from a client with "loose" data, didn't require us to make schema migrations every time the "loose" data popped in, and we got the job done. In another case, a single GIN index made jsonpath queries trivially easy, again with loose data. I would have loved to have normalized, strict data to work with. In the real world, things can get loose without fault to my team or even the client. The real world is messy. We try to assert order upon it, but sometimes that just isn't possible with a deadline. JSON makes "loose" data possible without losing excessive amounts of development time.

by ttfkam 3y ago

sgarland 3y ago

Just because RDBMS have added JSON support (MySQL has had it since 5.6 as well) doesn’t mean it’s a good fit. The language and its implementations are designed for relational data that can be normalized; JSON is neither. Have you ever tested JSON vs. normalized data at scale? Millions+ of rows? I have, and I assure you, JSON loses. Last I checked, Postgres does a terrible job at collecting stats on JSONB - the default type - so query plans aren’t great. Indexing JSON columns is also notoriously hard to do well (even moreso in MySQL), as is using the correct operators to ensure the indices are actually used.

ttfkam 3y ago
Millions of rows with a JSON column? Yes, indeed I have. Recently in fact. When all normalized fields are populated, you're absolutely right. However that wasn't our dataset. We had entries with sparse keys. This was due to the nature of the data we were ingesting. We ended with multiple partial expression indexes on the JSON column due to the flexibility it provided. Each index ended up relatively small (again, sparse keys), didn't require a boatload of null values in our tables, was more flexible as new data came in from a client with "loose" data, didn't require us to make schema migrations every time the "loose" data popped in, and we got the job done. In another case, a single GIN index made jsonpath queries trivially easy, again with loose data. I would have loved to have normalized, strict data to work with. In the real world, things can get loose without fault to my team or even the client. The real world is messy. We try to assert order upon it, but sometimes that just isn't possible with a deadline. JSON makes "loose" data possible without losing excessive amounts of development time.