2 ms·
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 t
by simonw 3y ago
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?
If you don't want to pay the cost of updating the full JSON value, that's what columns are for.
- brigadier132 3y ago> 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?
- simonw 3y agoThe clue is in how the query is written: 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.
- wild_egg 3y agoI'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
- vlovich123 3y agoThere are obviously ways you could accomplish it. RocksDB’s Merge operator uses this as one of the canonical examples - your update records just the new value through a merge record. When you go to retrieve, you have to keep going through delta updates to reconstruct what the value is now & you only rewrite the value as part of a large compaction process. This way the cost of the write is the cost of the delta, the read cost is impacted although in practice negligible if you use a row cache, and the cost of rewriting the document is amortized into a much larger operation.
- simonw 3y agoThat's a really interesting example, thanks.
- koolba 3y ago> If you don't want to pay the cost of updating the full JSON value, that's what columns are for. You pay the full cost for writing the entire new row in PostgreSQL if only a single column changes. There’s some saving for indexes, but the row data is always written in full for the new row even if just one column changes. Now that’s no reason not to use actual columns. The value of a well defined schema is knowing the types and constraints of your data.