|
|
|
|
|
|
by xwolfi
1360 days ago
|
|
|
It cant be. Json has a huge structural problem: it's an ASCII representation of a schema+value list, where the schema is repeated with each value. It improved on xml because it doesn't repeat the schema twice, at least... It's nonsensical most of the time: do a table, transform values out of the db or in the consumer. The reason postgres does it is because lazy developpers overused the json columns and then got fucked and say postgres is slow (talking from repeated experience here). Yeah searching in random unstructured blob is slow, surprise. I dont dislike the idea to store json and structured data together but... you dont need performance then. Transferring a binary representation of a table and having a binary to object converter in your consumer (even chrome) is several orders of magnitudes faster than parsing strings, especially with json vomit of schema at every value. |
|
|
As usual, it comes down to being sensible about how to use a given tool. You can start with a json column and later when access patterns become clear you split out specific keys that are often accessed / queried on into specific columns.
Another good use case for data that you want to have but don't have to query on often: https://supabase.com/blog/audit
> Yeah searching in random unstructured blob is slow, surprise.
If your use case it to search / query on it often then jsonb column is the wrong choice. You can have an index on a json key, which works reasonably well but I'd probably not put it in the hot path: https://www.postgresql.org/docs/current/datatype-json.html#J...