Using PostgreSQL’s JSONB for NoSQL (2019) [video]

https://www.youtube.com/watch?v=4g4zp-XrZ2s

by zdw • 5 years ago

97 42 5 years ago

sgt• 5 years ago

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.

mateuszf• 5 years ago

Same here. Have only good experiences with this setup.

hodgesrm• 5 years ago

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.

fabian2k3• 5 years ago

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• 5 years 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• 5 years 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.

fabian2k3• 5 years 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.

jrochkind15• 5 years ago

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

bob10292• 5 years 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.

megous• 5 years 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.

stevesimmons2• 5 years ago

Does anyone know of performance (and usability) comparisons between say Postgres JSONB and Azure CosmosDB?

jf222• 5 years ago

Those are two completely different tools with different use cases.

exceptione• 5 years ago

Well, tell that to the MS-shops... I think it is a very good question, would love to see some benchmarks.

fisf• 5 years ago

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.

stevesimmons2• 5 years ago

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.

darkhorse13• 5 years ago

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.

eyan• 5 years ago

Great data point! Thank you!

jf222• 5 years ago

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/

NicoJuicy2• 5 years ago

Came here to say that too.

Marten DB is really good!

nomilk• 5 years ago

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).

jrochkind15• 5 years ago

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).

bullfightonmars2• 5 years ago

We wrote a similar library that hooks into ActiveRecord. https://github.com/data-axle/superstore

paulryanrogers2• 5 years ago

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.

the_gipsy2• 5 years ago

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.

bonzini• 5 years ago

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.

mlk• 5 years ago

Clob

the_gipsy2• 5 years ago

Aha, I only knew them as blobs or JSON

bob10292• 5 years ago

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.

fabian2k3• 5 years ago

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.

tpetry• 5 years ago

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.

NicoJuicy2• 5 years ago

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.

paulryanrogers2• 5 years ago

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.)

simonw• 5 years ago

PostgreSQL 14 is currently in beta and adds JSON subscripting: https://www.postgresql.org/docs/14/datatype-json.html#JSONB-...

mdaniel• 5 years ago

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

lysecret• 5 years ago

I don't know why anyone would use say mongodb when postgresjson exists.

Izkata• 5 years ago

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.

debug-desperado• 5 years ago

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.

Using PostgreSQL’s JSONB for NoSQL (2019) [video]

Related Stories