Hi,
I’ve got a web app where I want to store user’s session data. The schema in this data changes a lot so it may be useful here to store the session properties in either a jsonb column, or in multiple rows. Something like:
session_id | data
100 { a: 1, bar: 2 ...
101 { a: 3, baz: 123 …
or
session_id | name | value
100 a 1
100 bar 2
101 baz 123
101 a 3
...
The app currently does something like option 1, but on an older pre-jsonb version of postgres, so the field is just text. I’m hoping to upgrade Postgres soon so jsonb is an option.
I'm wondering about the tradeoffs, specifically: is it possible to update one piece of a jsonb value without having to rewrite the entire field? There are cases where that data field was getting pretty big (500kb). Would you expect any obvious performance differences between these two options?
Yes, I’ll need to build performance tests myself, but that’s a lot of work to get two realistic situations with millions of rows, so I’m wondering about guesses or common knowledge on this.
thanks,
Rob