Large number of pipelines leads to 500 error - PG::QueryCanceled: ERROR: canceling statement due to statement timeout

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

Summary

Customer is encountering 500 errors accessing a project on gitlab.com - 297986 [internal].

In checking Kibana we see the following SQL examples in most occurrences of the 500 error:

PG::QueryCanceled: ERROR: canceling statement due to statement timeout

/*application:web,correlation_id:01G63DTDWVMVK4AE95YCPZ6W10,endpoint_id:GET /api/:version/projects/:id/pipelines,db_config_name:ci_replica*/ SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = $1 AND "ci_pipelines"."source" != $2 ORDER BY "ci_pipelines"."id" DESC LIMIT $4 OFFSET $2349029 
---
/*application:web,correlation_id:01G54PEESG8BAR5S6ZRKN2T16Y,endpoint_id:RootController#index, 
db_config_name:ci_replica*/
SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = $1 
AND 
    ( 
        "ci_pipelines"."source" IN ($2, 
                                    $3, 
                                    $4, 
                                    $5, 
                                    $6, 
                                    $7, 
                                    $8, 
                                    $9, 
                                    $10, 
                                    $11) 
    OR  "ci_pipelines"."source" IS NULL) 
AND "ci_pipelines"."sha" = $12 
ORDER BY 
    "ci_pipelines"."id" DESC 
LIMIT 
    $13

The 500 error is encountered anywhere in the UI where the latest pipeline status is shown which ultimately renders the project inaccessible.

Discussed the issue on Slack [internal] where it was determined the project has > 99K pipelines.

Steps to reproduce

Access any page that shows latest pipeline status on the customer project - the page will not completely render and the error will ensue.

Example Project

See ZD ticket [internal] for project details.

What is the current bug behavior?

The ci_pipelines query times out leading to a 500 error and pages are not rendered completely.

What is the expected correct behavior?

All pages in the project should be accessible and functional.

Relevant logs and/or screenshots

Kibana example

Output of checks

This bug happens on GitLab.com

Results of GitLab environment info

Expand for output related to GitLab environment info 
(For installations with omnibus-gitlab package run and paste the output of:
`sudo gitlab-rake gitlab:env:info`)

(For installations from source run and paste the output of:
`sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)

Results of GitLab application Check

Expand for output related to the GitLab application check 
(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true)


(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true)


(we will only investigate if the tests are passing)

Possible fixes

Edited by 🤖 GitLab Bot 🤖