Skip to content

Create p_ci_finished_pipeline_ch_sync_events table

Pedro Pombeiro (OOO from July 16th till Aug 7th) requested to merge pedropombeiro/470078/implement-p_ci_finished_pipeline_ch_sync_events into master

What does this MR do and why?

This MR implements the Ci::FinishedPipelineChSyncEvent model. It is very similar to the existing Ci::FinishedBuildChSyncEvent model, only for finished pipelines. The goal is to build a queue of finished pipelines that can be synced to clickhouse in an upcoming issue.

  • Changelog: added
gitlabhq_development_ci> \d p_ci_finished_pipeline_ch_sync_events
+----------------------+-----------------------------+-------------------------+
| Column               | Type                        | Modifiers               |
|----------------------+-----------------------------+-------------------------|
| pipeline_id          | bigint                      |  not null               |
| project_namespace_id | bigint                      |  not null               |
| partition            | bigint                      |  not null default 1     |
| pipeline_finished_at | timestamp without time zone |  not null               |
| processed            | boolean                     |  not null default false |
+----------------------+-----------------------------+-------------------------+
Indexes:
    "p_ci_finished_pipeline_ch_sync_events_pkey" PRIMARY KEY, btree (pipeline_id, partition)
    "index_ci_finished_pipeline_ch_sync_events_for_partitioned_query" btree ((pipeline_id % 100::bigint), pipeline_id) WHERE processed = false
Partition key: LIST (partition)

Part of #470078 (closed)

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

n/a

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

n/a

Database query plans

exec CREATE TABLE IF NOT EXISTS "gitlab_partitions_dynamic"."ci_finished_pipeline_ch_sync_events_1" PARTITION OF "p_ci_finished_pipeline_ch_sync_events" FOR VALUES IN (1);

exec INSERT INTO p_ci_finished_pipeline_ch_sync_events (pipeline_id, pipeline_finished_at) (SELECT id AS pipeline_id, finished_at AS pipeline_finished_at FROM ci_pipelines WHERE status IN ('success', 'failed', 'skipped', 'canceled') AND finished_at IS NOT NULL ORDER BY id DESC LIMIT 10000);
next_partition_if query

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/29587/commands/91905

SELECT "p_ci_finished_pipeline_ch_sync_events"."pipeline_id", "p_ci_finished_pipeline_ch_sync_events"."pipeline_finished_at"
FROM "p_ci_finished_pipeline_ch_sync_events"
WHERE "p_ci_finished_pipeline_ch_sync_events"."partition" = 1
ORDER BY "p_ci_finished_pipeline_ch_sync_events"."pipeline_id" ASC
LIMIT 1
 Limit  (cost=0.29..4.77 rows=1 width=16) (actual time=0.048..0.048 rows=1 loops=1)
   Buffers: shared hit=3
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using ci_finished_pipeline_ch_sync_events_1_pkey on gitlab_partitions_dynamic.ci_finished_pipeline_ch_sync_events_1 p_ci_finished_pipeline_ch_sync_events  (cost=0.29..255.82 rows=57 width=16) (actual time=0.046..0.046 rows=1 loops=1)
         Index Cond: (p_ci_finished_pipeline_ch_sync_events.partition = 1)
         Buffers: shared hit=3
         I/O Timings: read=0.000 write=0.000
detach_partition_if query

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/29587/commands/91906

SELECT 1 AS one
FROM "p_ci_finished_pipeline_ch_sync_events"
WHERE "p_ci_finished_pipeline_ch_sync_events"."processed" = FALSE
  AND "p_ci_finished_pipeline_ch_sync_events"."partition" = 1
LIMIT 1
 Limit  (cost=0.29..0.32 rows=1 width=4) (actual time=0.029..0.030 rows=1 loops=1)
   Buffers: shared hit=3
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using ci_finished_pipeline_ch_sync_events_1_expr_pipeline_id_idx on gitlab_partitions_dynamic.ci_finished_pipeline_ch_sync_events_1 p_ci_finished_pipeline_ch_sync_events  (cost=0.29..368.82 rows=10000 width=4) (actual time=0.028..0.028 rows=1 loops=1)
         Filter: (p_ci_finished_pipeline_ch_sync_events.partition = 1)
         Rows Removed by Filter: 0
         Buffers: shared hit=3
         I/O Timings: read=0.000 write=0.000
Edited by Pedro Pombeiro (OOO from July 16th till Aug 7th)

Merge request reports