Postgres using a JSONB column instead of defined columns

Question

Postgres has a lot of support for JSON (even indexing). I want to just put 1 jsonb field called everything instead of defining of bunch of typed columns (mongodb style).

But I assume this is a bad idea. My question is: Why shouldn't I do this? Is the only con just the fact that it'll probably take extra storage space?

(My table has a lot of optional fields which is why I want to do this)

That will depend on how well you want your data structured. A json field will make you lose normalization (and that is debatable) but for an application that it have a defined object (like a simple pojo) it could be a good option. I'm an old school I always prefer to have a good and well defined and normalized database model. Nowadays people tend to use json data. Anyways if you are using a json data just to get rid of columns i disagree but if it is to represent an object it is fine. — Jorge Campos
– Jorge Campos, Commented Jun 17, 2016 at 19:19
first concern - FK. jsonb indexing is great, but having constraints would be difficult — Vao Tsun
– Vao Tsun, Commented Jun 17, 2016 at 19:32

Paul A Jungwirth · Accepted Answer · 2016-06-17 19:37:36Z

4

I can think of a few reasons:

If the JSON blob has foreign keys to other columns, I don't think you can add a REFERENCES constraint.
With columns you can enforce NOT NULL constraints very easily, but with JSON you'll have to write CHECK constraints for each required value.
JSON has fewer types than Postgres. For instance, no date/time/timestamp, no intervals, no ranges, just one kind of numeric type, no IP addresses, no binary data.
Over time if you add/move/delete fields in the JSON structure, you'll find that older rows become obsolete and have JSON your app doesn't understand. Using regular columns forces you to keep the whole table up-to-date.

answered Jun 17, 2016 at 19:37

Paul A Jungwirth

24.9k16 gold badges79 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres using a JSONB column instead of defined columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related