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
