Postgresql – Performance issues on json field update

jsonpostgresqlpostgresql-performance

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

Best Answer

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.

Related Solutions

Postgresql – Update a json element in json datatype

Since it's just a string, you might be able to accomplish a simple change/deletion of a node with the regex_replace function.

For example, this is how I recently deleted a certain JSON node in a table (all rows):

UPDATE my_db.my_table
SET my_column = (regexp_replace(my_column::text, ',"some_json_node":(.*),', ','))::json
WHERE NOT my_column IS NULL

Note, for all my JSON data, I keep a "version":"(n).(n)" (i.e. schema version) node in the object. That way I can update objects that comply with a specific version. Your requirements may not be that complex, but if they are, it certainly helps.

PostgreSQL – JSON Field Performance with Large Documents

In PostgreSQL 9.3, performance will be fairly poor for large json documents. The whole document must be parsed in order to access a field, index an array, etc. It's stored on disk as a simple text representation, the json text.

PostgreSQL 9.4 is likely to change this, with support for jsonb storage on disk. It'll still have to read the whole json object from disk to access any part of it, but it won't have the same kinds of parsing overheads you'll see in 9.3. It'll also gain many of the indexing features of hstore.

In 9.3, I strongly suspect that hstore, which is stored in a compact binary representation on disk and supports a variety of indexing features, is much more appropriate for your needs.

Best Answer

Related Solutions

Postgresql – Update a json element in json datatype

PostgreSQL – JSON Field Performance with Large Documents

Related Question