Long running transactions in postgresql database
We get long running transactions in gitlab.com from 5/23. gitlab-com/gl-infra/production#854 (closed)
The transaction may be executed for over 30mins. It contains many queries where each query are relative fast. Below is a snapshot of such transaction and the query
| attr | value |
|---|---|
| txn_age | 00:05:38.057067 |
| pid | 28168 |
| query | UPDATE "project_statistics" SET build_artifacts_size = COALESCE(build_artifacts_size, 0) + (-91450), storage_size = COALESCE(storage_size, 0) + (-91450) WHERE "project_statistics"."project_id" = 10339957 |
| client_addr | 127.0.0.1 |
| application_name | sidekiq 5.2.7 queues: post_rec...ndary_scheduler [5 of 27 busy] |
we noticed this update query are executed multiple times in the transaction with different incremental values.
- is it necessary to combine all the queries in to a giant transaction instead of multiple small transactions?
- can we just update project_statistics once at the end of the transaction instead of multiple incremental updates? it has also produced large number of dead tuples which caused auto-vacuum to be executed extensively.