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
|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|
|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.