9

I'm looking at using PostgreSQL's jsonb column type for a new backend project that will mainly serve as an REST-ful JSON API. I believe that PostgreSQL's jsonb will be a good fit for this project as it will give me JSON objects without need for conversion on the backend.

However, I have read that the jsonb data type slows down as keys are added, and my schema will have need of using primary keys and foreign key references.

I was wondering if having primary keys/foreign keys in their own columns (in the standard relational database way) and then having a jsonb column for the rest of the data would be beneficial, or would this cause problems (whether now or down the road)?

In short, would:

table car(id int, manufacturer_id int, data jsonb)

perform better or worse than:

table car(data jsonb)

Especially when looking up foreign keys frequently?
Would there be downsides to the first one, from a performance or a schema perspective?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Anthony F.
  • 545
  • 1
  • 5
  • 20
  • Why do you want to use `jsonb` at all? Sounds like you have a more or less fixed schema and converting rows to JSON should be fast enough that you don't need to worry about it. – mu is too short Dec 30 '14 at 21:37
  • Good question: I have a good idea of the relationships that my schema will need, but at this point in time I don't have a concrete understanding of the information that each table will need, and while I could do database migrations each time I figure that out, I think using jsonb would allow me good performance coupled with an easy way to add things quickly. Maybe later down the road, once I have a more concrete understanding of the data needed I can go back to a good relational setup. But that's beside the point of the question, which is: does one perform better/worse than the other? – Anthony F. Dec 30 '14 at 21:41
  • 1
    But you're going to have to do a bunch of migrations anyway to rewrite your JSON, a couple ALTER TABLEs here and there shouldn't be frightening and if they are then rewriting all your data and code to track a constantly changing schema should be more frightening. As far as answering the question goes, first you need to ask the right question. I think you need to figure out what your data looks like before you start slinging data around. If you think you're going to wing it and then go back and redesign the database you're almost certainly wrong, it won't happen. – mu is too short Dec 30 '14 at 22:01
  • Correct me if I'm wrong, but isn't the whole purpose of the jsonb column type that it accepts any JSON string? So if I wanted to add a `car_color_hex_code` or another random attribute, I would just add it to the json string and store it in the jsonb column, right? No migration necessary. As to your second point: how can I better ask this question the right way? I simply want to know if a primary/foreign key would work better in the jsonb column or in its own column, and what ramifications having it outside would cause. Is there a better way to ask that? – Anthony F. Dec 30 '14 at 22:17
  • JSON is (IMO) meant for unstructured or loosely structured data, you can throw whatever JSON you want into a `jsonb` but that doesn't mean you should. I don't think you can even have an FK with a source or target inside `jsonb`, similarly for a PK. Using `jsonb` might make sense for your "bag of random attributes" but you won't have much in the way of integrity constraints to help you keep your data clean and sensible. Performance questions are very difficult to answer with anything more than handwaving unless you have benchmarks with real data. – mu is too short Dec 30 '14 at 22:32
  • Going back to the original question, without trying to argue about SQL vs NoSQL databases (because that's where I see this heading): are there any upsides or downsides to using a key inside the jsonb or in its own column, see as how we have both those options now with PostgreSQL and jsonb? – Anthony F. Dec 30 '14 at 23:03

2 Answers2

16

All values involved in a PRIMARY KEY or FOREIGN KEY constraint must be stored as separate columns (best in normalized form). Constraints and references do not work for values nested inside a json / jsonb column.

As for the rest of the data: it depends. Having them inside a jsonb (or json) value carries the well-known advantages and disadvantages of storing unstructured document-type data.

For attributes that are present for all or most rows, it is typically better (faster, cleaner, smaller storage) to store them as separate columns. Especially simpler and cheaper to update. Easier indexing and other queries, too. The new jsonb has amazing index capabilities, but indexing dedicated columns is still simpler / faster.

For rarely used or dynamically appearing attributes, or if you want to store and retrieve JSON values without much handling inside the DB, look to jsonb.

For basic EAV structures with mainly character data, without nesting and no connection to JSON I would consider hstore. There are also the xml (more complex and verbose) and json data types (mostly superseded by jsonb), which are losing ground.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    Yup ... "it depends". One issue not addressed here is that if you update *any* subfield of a jsonb value, the *whole tuple* must be rewritten and any/all indexes pointing to it must be updated. If you've decomposed your data into entities with pk/fk relationships this is not the case anymore, you can insert/update/delete just parts of it without forcing the whole thing to be rewritten. – Craig Ringer Dec 31 '14 at 03:58
  • @CraigRinger Is this still true in postgres 9.5? I ask after reading this section in the release docs https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#JSONB-modifying_operators_and_functions – t1m0 Mar 17 '16 at 14:45
  • 3
    @t1m0 Yes. It's inherent to the TOAST out-of-line storage and to MVCC. PostgreSQL can now modify a jsonb object without having to completely deconstruct and reconstruct it, but that's in-memory modification. It must still read the whole thing in from disk and it must still write the whole new modified version out again to the new tuple. – Craig Ringer Mar 17 '16 at 14:52
4

Which perform better? Depends on usage. It is same question, when you compare SQL (relational) and NoSQL (KeyValue or Document) databases. For some use cases a NoSQL databases performs very well, for other not.

Relational concept (normalized schema) is optimized for typical OLTP usage - 70% read/30% write, multiuser, lot of updates, report calculating, some ad hoc queries. Relational concept is relatively wide general .. with very wide usability (evidence, accounting, processing support, ...). Usually it is not too bad everywhere.

It is clear, so specialized databases (Document, KeyValue, Graph) can be significantly better (one order faster) on specialized use cases. But their usage is significantly narrower. When you are out of optimized use case, then performance can be bad.

Other question is database size - record numbers. The difference in performance on production databases can be significant in hundred thousand rows. For some smaller databases the impact can be not significant.

Postgres is relational database and my preference is to use a normalized schema for all important data in database. When you use it well, it is terrible fast. Non relation types is perfect for some fuzzy data (HStore, JSON, XML, Jsonb) - it is significantly better than EAV schema (perform worse on bigger data).

If you need do some important decision, prepare prototype, fill it for expected data (3 years) and check a speed of some important queries for your system. Attention: strong impact on these benchmarks has used hw, current load, current sw.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94