> I would have thought that the fact that the entire jsonb value is rewritten if you add a new field to it would be clear to anyone who gave it a few seconds of thought. How else could that work?
We are talking about postgres, unless someone went into the manual to look up the performance characteristics of jsonb columns why wouldn't they assume that it works in the most optimal way?
UPDATE characters
SET jsonb_column = jsonb_set(
jsonb_column,
'{superpower}',
'"invisibility"'
)
WHERE id = 5;
Or:
UPDATE characters
SET jsonb_column = jsonb_column ||
'{"superpower": "invisibility"}'
WHERE id = 5;
In both cases the SET is a pretty strong clue that the entire value will be overwritten.
I have immense respect for the PostgreSQL development team but I still can't imagine how they would optimize that not to be a rewrite of the stored JSON value.
I'm not sure if it's just sugar but PostgreSQL does allow subscripting on JSONB in UPDATE statements
UPDATE characters
SET jsonb_column['superpower'] = '"invisibility"'
WHERE id = 5;
Can't seem to find any docs around whether that still does a full rewrite under the hood or not but seemed like an important omission from your examples
We are talking about postgres, unless someone went into the manual to look up the performance characteristics of jsonb columns why wouldn't they assume that it works in the most optimal way?