2

I am using the jsonb type in PostgreSQL. I frequently make updates of the following kind (where jdoc is a jsonb column):

UPDATE my_table SET jdoc = jdoc || '{a: "Hello World!"}'::jsonb WHERE id = 123

I have a number of expression indexes on the jsonb column:

CREATE INDEX ix_b ON my_table some_function(jdoc->>b)
CREATE INDEX ix_c ON my_table some_other_function(jdoc->>c)

As you can see, I only change jdoc->a, but I have to set the whole jdoc column. Will I therefore suffer the full performance penalties of updating the indexes on jdoc->b and jdoc->c? Or is PostgreSQL smart enough to notice that those values did not change, and that the indexes therefor does not need to be updated? If I suffer the full performance penalty, is there any way to avoid it?

5

Will I therefore suffer the full performance penalties of updating the indexes on jdoc->b and jdoc->c?

Yes.

Or is PostgreSQL smart enough to notice that those values did not change, and that the indexes therefore does not need to be updated?

Nope, because the index probably did change, you just don't see that.

When you update the json document PostgreSQL writes a new version of the row that contains it to the table. There's a new row in the table and the old row gets marked as effectively deleted. (It's not that simple, but this is good enough.) The new row has a new address, and the indexes need to be updated to point to the new row address, even though the value in the index might be exactly the same.

PostgreSQL has some optimisations that let it avoid such index updates in a few cases (read about HOT if you want to know the gory details) but they're no help if you are updating an indexed column.

If I suffer the full performance penalty, is there any way to avoid it?

No, there isn't. You'd have to split the json document into two columns.

This will not change with 9.6's new support for updating json documents. It'll still write the whole updated document to a new TOASTed field as a new tuple, and that'll require index updates. It's more efficient to create the new json document but no faster (or smaller) to update write it to disk and the indexes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.