PostgreSQL jsonb?

Question

Which is better, to store config data into single jsonb field or dedicate a field for each property key?

For example, this json data:

{
  notifications: {
    check: {
      enabled: true,
      In_delimiters: true,
      Out_delimiters : false
    },
    battery_low: {
      enabled: true,
      battery_power: 0.70
    },
    gps_on: {
      enabled: true
    },
    gps_off: {
      enabled: true
    },
    speed_exceeded: {
      enabled: true,
      speed: 100
    }
  }
}

If I have thousands of users, each property key will be duplicated in each row, making the DB size grow bigger than if I dedicate a field for each property and store value only.

What's the advice in this case? Shall I go with jsonb and use compression? or dedicate a filed for each property?

json is good for unstructured data. Otherwise do not use it. — Clodoaldo Neto
– Clodoaldo Neto, Commented Jun 21, 2016 at 14:44

Laurenz Albe · Accepted Answer · 2016-06-22 07:30:52Z

2

Don't think of database size first; storage is comparatively cheap these days.

Use json or jsonb if

the structure varies from entry to entry, making it hard to come up with a relational data model
there is not much processing of the values inside the database

Otherwise use a relational data model, that's what a relational database is best at.

If you don't process the data inside the database at all and saving storage is important to you, use json – if your row size exceeds 2KB, it will be compressed automatically. json is also the way to go if you have an object key order, white space or duplicate object keys that need to be preserved.

If you have to use the data in queries, use jsonb by all means. The operators are faster, and you can use indexes.

answered Jun 22, 2016 at 7:30

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

dbf Over a year ago

What do you mean exactly when you write processing of the values inside the database?

Laurenz Albe Over a year ago

Use JSON functions in SQL queries. Use indexes on JSON columns. Basically, do anything more than just storing and retrieving the JSON values.

Collectives™ on Stack Overflow

PostgreSQL jsonb?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related