When to Use JSONB

[00:00:00]
>> So I just gave you a pretty rudimentary overview of what JSONB is. We're not gonna go much further than that, but I think you kind of get the point, right? You can kind of treat it as a document on all of your rows. You can have multiple JSONB columns per row, but why?

[00:00:17]
[LAUGH] You can put them all into one JSONB that's what I tend to do, but that's totally up to you, how you want to model your data. So I use it a lot when I'm doing Postgres personally. I showed you everything to this point so you can write Postgres, just how PostgresSQL historically have been written.

[00:00:38]
And everything that I've shown you besides the JSONB stuff works across all databases basically. A lot of the queries that we've written will work, just copy and paste directly into MySQL into IBM db 2, all that kind of stuff, because they're all SQL spec compliant. The JSONB stuff is actually specific to Postgres, so this doesn't exist necessarily in other databases.

[00:01:03]
Part of the reason why I chose to teach you Postgres as opposed to SQLite or something like that is because I think this is one of the most compelling things about Postgres.
>> Why would you use JSONB in Postgres instead of Malcolm?
>> If you still wanted to have all the power and the joining and the unions, and all that kind of stuff that comes from Postgres, the other thing is Postgres is like a little, this is an opinion at this point.

[00:01:33]
Postgres is way more reliable than Mongo, like I think it's just a better product. It's been around longer, there's more eyes on it. It's a very war-hardened database. All of RedHat for years and years and years has run on Postgres, and they have billions and billions of rows.

[00:01:52]
Other companies are doing big things on Mongo, Stripe is one of them, right? Just problems all the time. Yeah, I think between the reliability and you have both the power of a relational database mesh together with the power of a document database, I just think it's better. If I was gonna build a company today, I definitely would choose Postgres as just like a general purpose database.

[00:02:20]
So I just wanted to throw out there, when I think you should use JSONB, I use JSONB a lot. I have throughout, since it's been out and available, when I have one too many relationships I tend to use it quite a bit specifically I tend to not use the second table.

[00:02:36]
I tend to just throw it in JSONB, makes it really easy. If you ever have heterogeneous kind of information, it's a really good place to say it, to use it. Like, let's say, our recipes like we had, like nutritional information, right? And we had a bunch of different types of nutritional information, and some is gonna have some parts of nutritional information.

[00:03:02]
Other parts, like these things I'll have iron and, I don't know, potassium, whereas these other things only have vitamin C. And rather than trying to store every single permutation of nutrition on every single row, that would be a really good use case for a JSONB kind of a situation.

[00:03:20]
I probably would have done our photos in JSONB, we did tags, that's a pretty good use case for it. So the thing that you kind of wanna think about when you're choosing, should I put this in JSONB or should I put this just as a normal column? If every single row has it, make it a column, right?

[00:03:42]
Cuz then you can do things like joins and other things like that, you can do joins on JSONB, it's weird though. If you have any sort of constraints that you need to enforce, like not NULL, or if you need to enforce that it's unique or things like that, that's a really good use case for making a column, not necessarily JSONB.

[00:04:00]
If you have unbounded data like tags, we can have 100 tags or five tags, that's a good case for JSONB. And if everything you have is like a heterogeneous piece of information and you're never doing any joins, you should just use Mongo and not use Postgres at all.

[00:04:19]
If you're doing joins at all, never use Mongo, just from the depth of my soul I implore you to never use Mongo if you're gonna try and do joins. I will not work at your company, if I go in there and I see that it has Mongo and there are joins, that's actually real, I would not.

[00:04:37]
I'm probably making someone feel real bad about something right now, but I have just scars, which is why I'm saying that.