5 ms·

I find this approach incredibly useful if any parts of your data don't have a uniform schema. The one thing you need to look out for is developers putting stuf

I find this approach incredibly useful if any parts of your data don't have a uniform schema. The one thing you need to look out for is developers putting stuff into JSON that belongs into proper columns. The Postgres JSON support is very powerful, but plain old relational queries are faster and often much easier to write.

tda 5y ago
I have created an app with such a hybrid approach as the part if the model was very dynamic. The idea was that I would slowly migrate the stable bits to proper columns, but in practice that never happened. Partly also because it is not really broken, and the performance is actually quite good. You can add partial indexes etc to improve where needed. But writing analytycal queries is definitely more complicated so the cost of not having a "normal" data model adds up over time. To allow business analysts to work with the data I endend up creating a a bunch of views om the data without JSONB fields as a quick fix. So though the approach worked, I am not sure I would do it again.
- kroolik 5y ago
  What you did, in fact, was creating read models specific to use case. This is completely normal and allows one to optimize for different users: app's schema can be different than the one for analytics.
- fabian2k 5y ago
  I'm using the JSON mostly for parts that really have no fixed schema, e.g. fields that vary by customer. The alternative would be something like EAV, and JSON columns are far superior here. Performance is perfectly reasonable with JSON, but there are many more ways to make it slow if you're not careful. Postgres has to read the entire JSON content even if you only access a small part of it, that alone can kill performance if your JSON blobs are large. The lack of true statistics can also be an issue with JSON columns. They're still easily fast enough for many purposes, they just have a few more caveats than plain columns you should be aware of. There's some other aspects that are not that intuitive, but also not a big deal once you know them. For example you can use a jsonb_path_ops GIN index to speed up queries for arbitrary keys and values inside your JSONB column, but you need to use the @> operator so that the index can be used. If you write queries like WHERE myjson->>'foo' = 'bar' it can't use the index.
jrochkind1 5y ago
How would you describe what "belongs" in a proper column? Not just anything that doesn't have a uniform schema?
- bob1029 5y ago
  For us, a "proper" column is usually something we want to index against. Things like timestamps, user ids, etc. Anything that would be useful for top-level management of whatever has the json tacked onto it.
  - conradev 5y ago
    You could pull those out and index them using generated columns, though. That can make things a lot easier on the application side, where the code just needs to insert the JSON blob
    - jrochkind1 5y ago
      You don't even have to "pull those out and index them using generated columns" to index with postgres jsonb, although it's a bit more confusing to figure out the proper index and make sure your queries hit it, jsonb data is indexable on it's own.
megous 5y ago
Also for leftovers when scraping website data. Trendy SPAs usually have JSON endpoints, so I usually use some ID column, maybe timestamp column (to ease incremental syncing later on), some primary content columns, and the whole scraped JSON object is put into a `data JSONB` column in case some of it is needed in the future. :) Works amazingly.

tda 5y ago

I have created an app with such a hybrid approach as the part if the model was very dynamic. The idea was that I would slowly migrate the stable bits to proper columns, but in practice that never happened. Partly also because it is not really broken, and the performance is actually quite good. You can add partial indexes etc to improve where needed. But writing analytycal queries is definitely more complicated so the cost of not having a "normal" data model adds up over time. To allow business analysts to work with the data I endend up creating a a bunch of views om the data without JSONB fields as a quick fix. So though the approach worked, I am not sure I would do it again.

kroolik 5y ago
What you did, in fact, was creating read models specific to use case. This is completely normal and allows one to optimize for different users: app's schema can be different than the one for analytics.
fabian2k 5y ago
I'm using the JSON mostly for parts that really have no fixed schema, e.g. fields that vary by customer. The alternative would be something like EAV, and JSON columns are far superior here. Performance is perfectly reasonable with JSON, but there are many more ways to make it slow if you're not careful. Postgres has to read the entire JSON content even if you only access a small part of it, that alone can kill performance if your JSON blobs are large. The lack of true statistics can also be an issue with JSON columns. They're still easily fast enough for many purposes, they just have a few more caveats than plain columns you should be aware of. There's some other aspects that are not that intuitive, but also not a big deal once you know them. For example you can use a jsonb_path_ops GIN index to speed up queries for arbitrary keys and values inside your JSONB column, but you need to use the @> operator so that the index can be used. If you write queries like WHERE myjson->>'foo' = 'bar' it can't use the index.

jrochkind1 5y ago

How would you describe what "belongs" in a proper column? Not just anything that doesn't have a uniform schema?

bob1029 5y ago
For us, a "proper" column is usually something we want to index against. Things like timestamps, user ids, etc. Anything that would be useful for top-level management of whatever has the json tacked onto it.
- conradev 5y ago
  You could pull those out and index them using generated columns, though. That can make things a lot easier on the application side, where the code just needs to insert the JSON blob
  - jrochkind1 5y ago
    You don't even have to "pull those out and index them using generated columns" to index with postgres jsonb, although it's a bit more confusing to figure out the proper index and make sure your queries hit it, jsonb data is indexable on it's own.

megous 5y ago

Also for leftovers when scraping website data. Trendy SPAs usually have JSON endpoints, so I usually use some ID column, maybe timestamp column (to ease incremental syncing later on), some primary content columns, and the whole scraped JSON object is put into a `data JSONB` column in case some of it is needed in the future. :) Works amazingly.