Hacker News Reader

Comment by harel | Hacker News Reader

You don't have to write the entire jsonb column in order to update one field. Or you can perform various update operations on it. This is pretty cool

View on HN

This is incorrect. None of these functions modify the value of a column. All of them are purely functional and return a jsonb value with the specified field(s) replaced.

The significance is one of performance and convenience: before, you would have had to construct a new jsonb object with only the field(s) you wanted modified changed. (In fact it is possible to define these new operators in terms of such; I have done so.) Now you can just use these handy built-in functions, which presumably perform better than the manual method as well.

I think you are using different interpretations of "you".

If you do:

  update aTable
  set loginInfo = loginInfo - 'lastLogin'

where 'loginInfo' is a jsonb column, you, the programmer, need not write out the parts of the data to keep. PostgreSQL still sees this as 'read column, evaluate expression, write result', so it will read and write the entire jsonb value.

This is only partially correct. These new features reduce the amount of data that must be transmitted to/from the postgres server and how much json must be parsed, but the full row is still written to the WAL (write ahead log). This is true even for partial updates to JSONB columns and even when only updating other non-JSONB columns in the row.

When you do need to update JSONB columns this is a big improvement. You still should consider the size of your JSONB columns and the number & frequency of updates to those rows.

Reply on news.ycombinator.com