CODEWITHSUNDEEP
djangopostgresql
user11141180
user11141180

Reputation:

Efficiency of using JSONFields vs. Pure SQL approach Django Postgres

I am wondering what is the difference in efficiency using JSONFields vs. a pure SQL approach in my Postgres DB.

I now know that I can query JSONFields like this:

MyModel.objects.filter(json__title="My title")

In pure SQL, it would look like this:

MyModel.objects.filter(title="My title")

Are these equal in efficiency?

Upvotes: 2

Views: 1005

Answers (1)

tim-mccurrach
tim-mccurrach

Reputation: 6815

Having separate columns for each thing is definitely more efficient.

The advantage of a JSONField is flexibility. You can store anything you want in there, and you don't have to change your database schema. But this comes at a cost. If you have a column that is a CharField with max 255 characters for example, then lots of time and effort will have gone into making a database that can optimise for that particular type (likewise for other types). With a JSONField however, it can be literally anything and it becomes very difficult to optimise a query (at the actual database level) for this.

Unless you have a good reason to use a JSON field (namely you need that level of flexibility) it is much much much better to go with separate columns for each of your fields. There are other advantages besides performance as well. You can define defaults, you can know for certain what types different variables are, which will make programming with them a whole heap easier and avoid a load of errors.

Upvotes: 4

Related Questions