Jsonb in postgres is fine, I’ve been using it for years. Much better than letting mongodb anywhere near the stack.
But then postgres is basically an OS at this point, enough to compete with emacs for meme potential. And I say that as a happy postgres user.
Wait until people learn about the possibility of putting a web server INSIDE of postgres :)
https://betterprogramming.pub/what-happens-if-you-put-http-server-inside-postgres-a1b259c2ce56
Postgres handles NoSQL better than many dedicated NoSQL database management systems. I kept telling another team to at least evaluate it for that purpose - but they knew better and now they are stuck with managing the MongoDB stack because they are the only ones that use it. Postgres is able to do everything they use out of the box. It just doesn’t sound as fancy and hip.
It’s normal to denormalize data in a relational database. Having a lot of joins can be expensive and non-performant. So it makes sense to use a common structure like JSON for storing the demoralized data. It’s concise, and still human readable and human writable.
Why should I spin up a NoSQL solution when 99% of my data is relational?
Having a lot of joins can be expensive and non-performant.
Only if you don’t know how to do indexing properly. Normalized data is more performant (less duplication of data, less memory and bandwidth is being used) if you know how to index.
It may have been true decades ago that denormalized tables were more performant, I don’t know. But today it’s far more common that the phrase “denormalized tables are more performant” is something that’s said by someone that sucks at indexing and/or is just being lazy.
But I do put JSON into tables sometimes when the data is going to be very inconsistent between different items and there’s no need to index any of the values in there. Like if different vendors provide different kinds of information about their products, I need to store it somewhere, so just serialize it and put it in there to be read by a program that has abstraction layers to deal with it. It’s never going to perform well if I do a query on it, but if all that’s needed is to display details on one item at a time, it’s fine.
SQL blows for hierarchical data though.
Want to fetch a page of posts AND their tags in normalized SQL? Either do a left join and repeat all the post values for every tag or do two round-trip queries and manually join them in code.
If you have the tags in a JSON blob on the post object, you just fetch and decide that.
I’m no expert in JSON, but don’t you lose the ability to filter it before your application receives it all? If you had a reasonable amount of data then in SQL you can add WHERE clause and cut down what you get back so you could end up processing a lot less data than in your JSON example, even with the duplicated top table data. Plus if you’re sensible you can ensure you’re not bringing back more fields than you need.
In a traditional SQL database, yeah. In various document-oriented (NoSQL) databases, though, you can do that.
Modern relational databases have support for it too including indexes etc. For example postgres.
If there’s commonly used data that would be good for indexing or filtering, you can take a few key values and keep them stored in their own fields.
There are also often functions that can parse structured text like XML or JSON so you can store data in blobs but not actually need to query all the blobs out to a client to use them on the database side and retrieve specific values. Another nice thing about blobs is the data can be somewhat flexible in structure. If i need to add a field to something that is a key/value pair inside a blob, i dont necessarily have to change a bunch of table schemas to get the functionality on the front end that I’m after. Just add a few keys inside the blob.
If you only join on indexed columns and filter it down to a reasonable number of results it’s easily fast enough.
For true hierarchical structures there’s tricks. Like using an extra Path table, which consists of AncestorId, DescendentId and NumLevel.
If you have this structure:
A -> B -> C
Then you have:
A, A, 0
A, B, 1
A, C, 2
B, B, 0
B, C, 1
C, C, 0
That way you can easily find out all children below a node without any joins in simple queries.
The fact that you’d need to keep this structure in SQL and make sure it’s consistent and updated kinda proves my point.
It’s also not really relevant to my example, which involves a single level parent-child relationship of completely different models (posts and tags).
I mean in my case it’s for an international company where customers use this structure and the depth can basically be limitless. So trying to find the topmost parent of a child or getting all children and their children anywhere inside this structure becomes a performance bottleneck.
If you have a single level I really don’t understand the problem. SQL joins aren’t slow at all (as long as you don’t do anything stupid, or you start joining a table with a billion entries with another table with a billion entries without filtering it down to a smaller data subset).
Either do a left join and repeat all the post values for every tag or do two round-trip queries and manually join them in code.
JSON_ARRAYAGG. You’ll get the object all tidied up by database in one trip with no need to manipulate on the receiving client.
I recently tried MariaDB for a project and it was kinda neat, having only really messed with DynamoDB and 2012 era MsSQL. All the modern SQL languages support it, though MariaDB and MySQL don’t exactly follow the spec.
The alternative is not super exciting though. My experience with NoSQL has been pretty shit so far. Might change this year as the company I’m at has a perfect case for migrating to NoSQL but I’ve been waiting for over a year for things to move forward…
Also, I had a few cases where storing JSON was super appropriate : we had a form and we wanted to store the answers. It made no sense to create tables and shit, since the form itself could change over time! Having JSON was an elegant way to store the answers. Being able to actually query the JSON via Oracle SQL was like dark magic, and my instincts were all screaming at the obvious trap, but I was rather impressed by the ability.
Both Oracle and Postgres have pretty good support for json in SQL.
and the plane in the picture is perfectly capable of transporting a plane. What’s your point?
JSON in the DB isn’t an antipattern. It is frequently used in absolutely terrible designs but it is not itself a bad thing.
I’m a data architect and I approve this message.
Why not use nosql if your important data is stored in JSON? You can still do all your fancy little joins and whatnot.
Carrying the body of a smaller plane in a larger plane isn’t an antipattern either. Airbus does this between body assembly and attaching the wings.
