Sample Header Ad - 728x90

Performance issues on json field update

0 votes
1 answer
800 views
I have a postgreSQL database on which I am experiencing huge latency issues when trying to update a json field using the following query:
UPDATE collections
SET "layout" = jsonb_set(layout, '{actions}', '[{"actionId":45,"actionName":"my_action"}]')
WHERE id = 1
It takes about 30sec to complete even though it does not seems like a big query. Furthermore the query seems to block all other incoming queries even the simplest SELECT (on the same collection). Good to know too, it happens only on our production database. On local databases it's pretty fast (so I'm kind of assuming it has to do with the traffic and locks on that table). It's also instantaneous on a replica of our production database. Which makes me think it might be related to some things happening under the hood that lock this query. If anybody has any hints on what could cause those troubles, it would be much appreciated. Regards, PostgreSQL: v9.5.20, 8GB RAM, 74/256GB disk used
Asked by Larcin (1 rep)
Feb 26, 2020, 02:59 PM
Last activity: May 30, 2025, 03:03 PM