#Postgresql json query performance movie#

However, there are use cases where it makes a lot of sense to incorporate a JSON document into your model.

#Postgresql json query performance movie#

A user makes purchases and leaves reviews, a movie has actors which act in various movies, etc. The reason for this is because website data tends to be relational. I still believe that most data is modelled very well using a relational database. Why would I even want JSON data in my DB? But the truth is that Postgres now handles JSON pretty well. MongoDB was, afterall, specifically made as a JSON document store and has some pretty great features like the aggregation pipeline. I'm not going to argue that Postgres handles JSON as well as MongoDB. With the release of version 9.4, JSON support tried to make the question 'Do I use a document or relational database?' unnecessary. 9.4 added the ability to store JSON as 'Binary JSON' ( or JSONB), which strips out insignificant whitespace (not a big deal), adds a tiny bit of overhead when inserting data, but provides a huge benefit when querying it: indexes. In version 9.3, Postgres improved on that by adding additional constructor and extractor methods. You could finally use Postgres as a 'NoSQL' database. With the release of version 9.2, Postgres added native JSON support. Postgres has had JSON support for a while, but to be honest it wasn't that great due to a lack of indexing and key extractor methods. However, you’d be increasing the complexity of your app and also of your development and server environments. Up until fairly recently, you had to make that difficult decision up-front when modelling your data: document or relational database? Yes, you could use two separate databases, using each tool for what they’re best at. My experience making websites has been in line with this sentiment: Unless your data objects live in complete silos from one another (and you're sure they will be that way for the foreseeable future), you'll probably be better off using a relational database like Postgres.

Another example is when you store a user’s name in various places for easy access, but when the user updates their name you’re forced to find all of those places to make sure their information is up to date. An example of this is when data that was thought to be in a silo needs to cross boundaries (what relational DBs are great at). An article by Sarah Mei titled ' Why you should never use MongoDB' discusses the issues you’ll run into if you try to use a NoSQL database when a relational database would be far superior.