Performance issues on json field update

Question

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

Answer 1

If that happens only on one database, and the databases are comparable in size, then the problem is probably locking.

There must be other (long running) transactions that modify rows in the table. If your statement tries to modify the same row, it has to wait until these concurrent transactions are finished.

Examine the pg_locks view for entries that are not granted: these correspond to blocked database sessions.