Say I have a table t with columns t_id (uuid), xs (jsonb, a json array).
My question is, is the SQL update t set xs = xs || '["x1", "x2"]' :: jsonb where t_id = 'some-uuid' expensive?
It may be expensive for two reasons:
a) the || operator parse the left operand, so if the left operand is large, the parsing will be costly
b) I heard somewhere PG will update the entire row if you update any column, since the xs column is large, this involves reading the original value (the left operand), concatenate it with the new value, and write the result to disk to a new location (on disk)
Am I correct?
Your second point is spot on: PostgreSQL reads the whole row, modifies the value and and writes a completely new row version to the table. The old row version is preserved to serve concurrent reads.
Modifying the large jsonb won't be terribly expensive, but the whole thing has to be snarfed into RAM.
You may be better off normalizing the table structure and storing the JSON attributes that receive regular modifications in a separate table. That would make the operation cheaper.