Skip to content

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

Screenshot_2022-12-19_at_1.39.16_PM

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

  1. Send request to: http://127.0.0.1:3000/api/v4/projects/21/jobs?pagination=keyset
  2. This should return jobs sorted by id: desc
  3. Additional filters can be added eg: http://127.0.0.1:3000/api/v4/projects/21/jobs?pagination=keyset&per_page=2&scope[]=canceled
  4. 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.

Related to #362172 (closed)

Edited by Max Fan

Merge request reports