Using PostgreSQL’s JSONB for NoSQL (2019) [video](youtube.com)
youtube.com
Using PostgreSQL’s JSONB for NoSQL (2019) [video]
https://www.youtube.com/watch?v=4g4zp-XrZ2s
42 comments
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.
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.
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.
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.
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.
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.
How would you describe what "belongs" in a proper column? Not just anything that doesn't have a uniform schema?
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.
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
That can make things a lot easier on the application side, where the code just needs to insert the JSON blob
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.
I noticed a lot of people noting that they enjoy use some hybrid of JSON w/ their SQL. I do the same thing and I think this is an incredibly productive way to manage fast-changing things.
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.
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.
In Postgres you would generally use JSONB columns, those are stored as TOASTs and are automatically compressed. That compression isn't very good, but there is some work on adding other compression algorithms there.
PostgreSQL 14 will add lz4 compression for toast data. But if i remember correctly the first ~700 bytes are always stored inline and only the rest is compressed. But i could be wrong on this.
In. Net you can use Marten DB.
You can signal query able properties who are then duplicated as normal db columns. It works quite nice actually.
You can signal query able properties who are then duplicated as normal db columns. It works quite nice actually.
The performance of JSONB is also surprisingly great. I played around with around 1M rows of data for a project using JSONB, and compared to traditional SQL, the complex queries were 500-800ms slower, which was totally reasonable for the use case.
Great data point! Thank you!
Been doing this for years now and it has proven itself to he a great solution. We use a hybrid approach: traditional columns in addition plus an entity/content type JSONB column.
JSON blob + columns is the recommended approach for handling semi-structured data in ClickHouse. It's easy to add columns on the fly, since you just give the expression to haul out the data using a DEFAULT clause. ClickHouse applies it automatically on older blocks without rewriting them. For new blocks it materializes the data as rows are inserted.
Same here. Have only good experiences with this setup.
A collegue put some data into a JSON columns and I absolutely loathed it. Can’t query it well and he neither did type it in the models in the code.
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.
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.
Yeah, I have a similar experience from a project that had moved from MongoDB to Postgres. In the conversion they left a free form JSON field with information filled in by plugins, which was messy because it was impossible to query efficiently. Plugins also had some code to format the free form data in HTML, which was suffering from code duplication but provided some ideas to build a common schema for a new table and for the corresponding REST API endpoints. That worked great but we still left a free form JSON field in the new table, and also kept the JSON field in the original table to store the configuration for the plugin.
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.
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.
There is a .NET project that uses this feature to create a NoSQL db experience. I've been using it for a few years and it's been great.
https://martendb.io/
https://martendb.io/
Came here to say that too.
Marten DB is really good!
Marten DB is really good!
As someone who does indie ruby stuff, often under time constraints, I find jsonb great for storing messy data that looks unnecessary, but which might be useful down the track. jsonb is just another type as far as postgres/rails is concerned, so it's fast implement (just like any other rails migration), fast to query, and easy to access using key/value (similar to the ruby hash).
For speculative storage like that my preference is for files, ideally on something like S3. It's harder to analyze but IME the need is infrequent. Storage is usually significantly cheaper than a DB.
If you are using Rails and jsonb, you may be interested in https://github.com/jrochkind/attr_json (by myself), or https://github.com/madeintandem/jsonb_accessor (an alternative).
We wrote a similar library that hooks into ActiveRecord. https://github.com/data-axle/superstore
Interesting, thanks, I hadn't seen this one before! So many of us inventing the same wheel! The docs on yours are kind of limited, I don't really understand how it maps to your postgres tables, what schema it's expecting. But I plan to find time to check it out just to compare and contrast with mine! Thanks!
Yeah. We built this for our own use and haven’t really documented it extensively. We use it in conjunction with an elastic search ORM.
https://github.com/data-axle/elastic_record
https://github.com/data-axle/elastic_record
Again, interesting, thanks!
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!
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!
Does anyone know of performance (and usability) comparisons between say Postgres JSONB and Azure CosmosDB?
To expand on my particular use case for Azure CosmosDB, I have a Python web app built with FastAPI. I find it super convenient to dump Pydantic data models of our primary domain objects to JSON and save them into Cosmos, tagged with the model name.
Then on rereading the Cosmos records, my model registry automatically imports the required Pydantic model and reconstitutes the live Python object.
Then on rereading the Cosmos records, my model registry automatically imports the required Pydantic model and reconstitutes the live Python object.
We use azure SQL (basically mssql) to perform something similar -- this might be a more apt comparison.
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.
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.
Those are two completely different tools with different use cases.
Well, tell that to the MS-shops... I think it is a very good question, would love to see some benchmarks.
Had a great win this last week with JSONB. Basically I needed an aggregated materialized view for performance but still have access to the non-aggregated row data. Think aggregate by account ID but the query may access any given month of data for that account. It’s cheating a bit to use a non-relational structure, but the correlated subqueries I had to use before were 20x slower.
I don't know why anyone would use say mongodb when postgresjson exists.
A terrifying example from work:
"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.
"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.
Does PostgreSQL have standard operators yet? Last I checked they only had the Pg specific ones, which predate the SQL standard. (Same for SQL/PSM sadly.)
PostgreSQL 14 is currently in beta and adds JSON subscripting: https://www.postgresql.org/docs/14/datatype-json.html#JSONB-...
Do you have a link to the standard operators you mean? I know they support [SQL/JSON path function](https://www.postgresql.org/docs/12/functions-json.html#FUNCT...) but as for the operators, I only know of their own which predate the standard
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.