Using PostgreSQL’s JSONB for NoSQL (2019) [video]
youtube.com
youtube.com
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.
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.
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.
That can make things a lot easier on the application side, where the code just needs to insert the JSON blob
One trick I learned along the way is to apply gzip to your JSON columns. For us, we got over 70% reduction in column size and query performance actually increased. I presume because it is faster to read fewer IO blocks than more, esp when doing table scans (which you shouldnt but they happen).
The only caveat with this is if you want to query against something in the JSON, but I think this is where the balancing act comes into play regarding what is in (compressed) JSON and what is in a column.
You can signal query able properties who are then duplicated as normal db columns. It works quite nice actually.
However later I found that there two kinds of JSON: one “bad” that we hd to patch up with lots of validation to some spec that emerged, but another one that was absolutely right to be free form, because it was never accessed by us. The user simply inputs it freely, and reads it out somewhere else, with freedom to evolve it as desired.
In our case the "user" that just threw JSON data in and read it out was the plugins. You just have to strike the right balance between core parts of the schema, expressed as tables and relations, and auxiliary data that can use JSON.
Marten DB is really good!
I hadn't seen that ElasticSearch ORM either! The more popular one of course is: https://github.com/elastic/elasticsearch-rails
It can be hard to know what open source is out there!
We deal with largish amounts of JSON event data, and unpack specific fields to computed columns. This allows to use clustered indices, standard analytical tools, etc., while leaving the raw data as it is.
Performance is mostly fine for analytics (time series aggregation and such). It is also a very flexible approach.
Then on rereading the Cosmos records, my model registry automatically imports the required Pydantic model and reconstitutes the live Python object.
"Hey cool, mongodb just accepts whatever you give it! We don't need to worry about writing database migrations!"
...Luckily we convinced them this was a bad idea and ended up using postgres with a handful of json columns instead. Up until that point their only experience had been with mysql so they had no idea json columns existed.