Keyset pagination for `Jobs` API Index endpoint
What does this MR do and why?
Introducing functionality for keyset pagination on projects/:id/jobs
endpoint.
This is because current queries some customers are using take too long with a high offset.
With the ability to use keyset pagination, these customers can query next pages without overloading the database and timing out requests.
Screenshots or screen recordings
Here we see the params are pagination=keyset
and response headers has link
filled in with next cursor
Database Queries
Keyset Pagination will use the following
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."project_id" = 13083 AND ("ci_builds"."id" < 3566739863) ORDER BY "ci_builds"."id" DESC LIMIT 51
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/14343/commands/50359
While Previously Offset Pagination uses the following
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."project_id" = 13083 ORDER BY "ci_builds"."id" DESC LIMIT 51 OFFSET 800
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/14343/commands/50358
How to set up and validate locally
After checking out the branch
In your browser or api extension
- Send request to: http://127.0.0.1:3000/api/v4/projects/21/jobs?pagination=keyset
- This should return jobs sorted by id: desc
- Additional filters can be added eg: http://127.0.0.1:3000/api/v4/projects/21/jobs?pagination=keyset&per_page=2&scope[]=canceled
- Where scope is one or many of: https://docs.gitlab.com/ee/api/jobs.html
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #362172 (closed)