0

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

0

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.

2
  • 1
    I don't think his query updates all rows in the table. He has WHERE id = 1. – jjanes Feb 26 '20 at 16:55
  • 1
    He should also consider setting log_lock_waits = on. That way you don't have to catch the locks "in the act" with pg_locks. – jjanes Feb 26 '20 at 16:58

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.