CODEWITHSUNDEEP
sqljsonpostgresql
Brandon Ros
Brandon Ros

Reputation: 199

What's proper usage for PostgreSQL JSON columns?

I am trying to understand why you would want to use a JSON column. Is it when you are too lazy to normalize the data at hand?

For example, I have an account's billing information. I could store it as a JSON dump, or break it down into columns, correct?

Which is preferred, and why?

Upvotes: 1

Views: 66

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324275

Generally, normalize it if the structure can be reasonably known in advance.

json / jsonb are good for when you don't control the structure fully, it's highly flexible, it changes over time, etc.

It can sometimes also be handy to store data denormalized for performance, if you always fetch entire "objects", you rarely update them, etc.

Upvotes: 2

Robby Cornelissen
Robby Cornelissen

Reputation: 97120

I would argue that it depends on the situation.

If you have complex hierarchical data, splitting it up into tables can get unwieldy, and retrieving the data might require a large number of joins that can negatively impact performance.

On the other hand, if you need to query for specific values in your data, splitting it up into tables/columns and creating the required indexes will most likely increase query performance.

Upvotes: 1

Related Questions