patternsqlMinor
Does updating a jsonb column trigger updates on all expression indexes on that column?
Viewed 0 times
expressiontriggercolumnallupdatingindexesthatdoesjsonbupdates
Problem
I am using the jsonb type in PostgreSQL. I frequently make updates of the following kind (where
I have a number of expression indexes on the jsonb column:
As you can see, I only change
jdoc is a jsonb column):UPDATE my_table SET jdoc = jdoc || '{a: "Hello World!"}'::jsonb WHERE id = 123I 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?Solution
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.
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.
Context
StackExchange Database Administrators Q#133649, answer score: 7
Revisions (0)
No revisions yet.