Skip to content

Add order_by updated_at to Pipelines API

Dan Jensen requested to merge 33099-add-pipelines-api-order-by-updated-at into master

What does this MR do?

This enhances the List Project Pipelines API endpoint to add an order_by option for updated_at. (This is the first timestamp option - there is no existing order_by option for created_at.) It includes a migration to concurrently add an index on ci_pipelines#updated_at to ensure adequate performance.

This is motivated by a specific customer request, see #33099 (closed), and was preceded by !17911 (merged).

This MR is very similar to !19658 (merged) (which does the same thing for the Deployments API).

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

The primary risk is probably that the migration to index the ci_pipelines#updated_at column will take quite a long time, even if it is concurrent/non-blocking.

Query

Based on customer feedback, we believe a status parameter will usually be given. In that case the query executed by the Pipelines API endpoint looks like:

SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 1 AND "ci_pipelines"."status" = 'success' ORDER BY "ci_pipelines"."updated_at" DESC;

Without the status parameter, there is no status condition in the query. Also, note the default ORDER BY clause (which is desirable).

Query plan BEFORE adding index:

 Sort  (cost=312.39..313.04 rows=261 width=296) (actual time=0.201..0.201 rows=0
 loops=1)
   Sort Key: id DESC
   Sort Method: quicksort  Memory: 25kB
   ->  Index Scan using index_ci_pipelines_on_project_id_and_status_and_config_s
ource on ci_pipelines  (cost=0.57..301.91 rows=261 width=296) (actual time=0.170
..0.170 rows=0 loops=1)
         Index Cond: ((project_id = 1) AND ((status)::text = 'success'::text))
 Planning time: 4.714 ms
 Execution time: 0.264 ms
(7 rows)

Adding index: "Duration: 8.350 min" in Slack#database-lab.

Query plan AFTER adding index: See comment: !19886 (comment 247053130)

2019-11-14 edited to add "Query" section. 2019-11-19 edited to add "after" query.

Edited by Dan Jensen

Merge request reports