Implement service to sync pipelines to ClickHouse
requested to merge pedropombeiro/470079/2-ingest-finished_pipeline_ch_sync_events-data-in-ch into master
What does this MR do and why?
This MR implements a service that goes through a queue of sync events, and syncs the respective finished pipelines to ClickHouse. It is similar to !132010 (merged), which implemented a similar service for builds. This service will later be used from a Sidekiq worker working periodically to processed any pending sync events.
Part of #470079 (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.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
-
Ensure you have ClickHouse installed.
-
Go to the shell in your GDK
gitlabdirectory and runbundle exec rake "gitlab:seed:runner_fleet". This will seed your GDK with some runners and jobs required for testing this MR. -
Create the sync event records from the existing pipelines in your
gdk rails console:Ci::Pipeline.finished.where.not(finished_at: nil).order(finished_at: :asc).each_batch(of: 40000) { |batch| project_namespace_ids = ::Project.id_in(batch.pluck(:project_id).uniq).pluck(:id, :project_namespace_id).to_h; Ci::FinishedPipelineChSyncEvent.transaction { Ci::FinishedPipelineChSyncEvent.insert_all(batch.map { |pipeline| { pipeline_id: pipeline.id, pipeline_finished_at: pipeline.finished_at, project_namespace_id: project_namespace_ids[pipeline.project_id] } }, unique_by: [:pipeline_id, :partition]) } } -
Enable the FF:
Feature.enable(:ci_pipelines_data_ingestion_to_click_house) -
Use the service to go through the sync events and send the respective pipeline data to ClickHouse in CSV upload batches (can be done in parallel):
loop do Ci::ClickHouse::DataIngestion::FinishedPipelinesSyncService.new.execute; sleep 1; end -
In the ClickHouse console, check that the records are correctly created:
SELECT * FROM ci_finished_pipelines FINAL -
You can check that the traversal IDs in the
pathcolumn are correct with# For a path value of `72/74/75/78/`, use the last segment below ::Namespace.find(78).traversal_ids => [72, 74, 75, 78]
Database query plans
Schema setup
CREATE TABLE p_ci_finished_pipeline_ch_sync_events (
pipeline_id bigint NOT NULL, project_namespace_id bigint NOT NULL, partition bigint DEFAULT 1 NOT NULL,
pipeline_finished_at timestamp without time zone NOT NULL,
processed boolean DEFAULT FALSE NOT NULL
)
PARTITION BY LIST (PARTITION);
ALTER TABLE ONLY p_ci_finished_pipeline_ch_sync_events
ADD CONSTRAINT p_ci_finished_pipeline_ch_sync_events_pkey PRIMARY KEY (pipeline_id, PARTITION);
CREATE INDEX index_ci_finished_pipeline_ch_sync_events_for_partitioned_query ON ONLY
p_ci_finished_pipeline_ch_sync_events USING btree (((pipeline_id % (100)::bigint)), pipeline_id)
WHERE (processed = FALSE);
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);
INSERT INTO p_ci_finished_pipeline_ch_sync_events (pipeline_id, project_namespace_id, pipeline_finished_at) (
SELECT id AS pipeline_id, 1 AS project_namespace_id, finished_at AS pipeline_finished_at
FROM ci_pipelines
WHERE ("ci_pipelines"."status" IN ('success','failed','canceled'))
AND "ci_pipelines"."finished_at" IS NOT NULL
ORDER BY id DESC
LIMIT 100000);finished_pipelines_sync_service.rb:144:in `yield_pipelines'
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/29664/commands/92093
SELECT *
FROM (WITH RECURSIVE "array_cte" AS MATERIALIZED (
SELECT "pipeline_id_partition"
FROM generate_series(0, 99) AS "p_ci_finished_pipeline_ch_sync_events" (pipeline_id_partition)), "recursive_keyset_cte" AS ((
SELECT NULL::bigint AS pipeline_id, NULL::bigint AS project_namespace_id, array_cte_pipeline_id_partition_array,
p_ci_finished_pipeline_ch_sync_events_pipeline_id_array,
p_ci_finished_pipeline_ch_sync_events_project_namespace_id_array, 0::bigint AS count
FROM (
SELECT ARRAY_AGG("array_cte"."pipeline_id_partition") AS array_cte_pipeline_id_partition_array,
ARRAY_AGG("p_ci_finished_pipeline_ch_sync_events"."pipeline_id") AS
p_ci_finished_pipeline_ch_sync_events_pipeline_id_array,
ARRAY_AGG("p_ci_finished_pipeline_ch_sync_events"."project_namespace_id") AS
p_ci_finished_pipeline_ch_sync_events_project_namespace_id_array
FROM (
SELECT "array_cte"."pipeline_id_partition"
FROM array_cte) array_cte
LEFT JOIN LATERAL (
SELECT "p_ci_finished_pipeline_ch_sync_events"."pipeline_id" AS pipeline_id,
"p_ci_finished_pipeline_ch_sync_events"."project_namespace_id" AS project_namespace_id
FROM "p_ci_finished_pipeline_ch_sync_events"
WHERE "p_ci_finished_pipeline_ch_sync_events"."processed" = FALSE
AND (pipeline_id % 100) = "array_cte"."pipeline_id_partition"
AND (("p_ci_finished_pipeline_ch_sync_events"."pipeline_id",
"p_ci_finished_pipeline_ch_sync_events"."project_namespace_id") > (1360603030, 1))
ORDER BY "p_ci_finished_pipeline_ch_sync_events"."pipeline_id" ASC,
"p_ci_finished_pipeline_ch_sync_events"."project_namespace_id" ASC
LIMIT 1) p_ci_finished_pipeline_ch_sync_events ON TRUE
WHERE "p_ci_finished_pipeline_ch_sync_events"."pipeline_id" IS NOT NULL
AND "p_ci_finished_pipeline_ch_sync_events"."project_namespace_id" IS NOT NULL) array_scope_lateral_query
LIMIT 1)
UNION ALL (
SELECT recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_pipeline_id_array[position],
recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_project_namespace_id_array[position],
array_cte_pipeline_id_partition_array,
recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_pipeline_id_array[:position_query.position - 1] ||
next_cursor_values.pipeline_id ||
recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_pipeline_id_array[position_query.position + 1:],
recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_project_namespace_id_array[:position_query.position - 1]
|| next_cursor_values.project_namespace_id ||
recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_project_namespace_id_array[position_query.position +
1:], recursive_keyset_cte.count + 1
FROM recursive_keyset_cte, LATERAL (
SELECT pipeline_id, project_namespace_id, position
FROM UNNEST(p_ci_finished_pipeline_ch_sync_events_pipeline_id_array,
p_ci_finished_pipeline_ch_sync_events_project_namespace_id_array)
WITH ORDINALITY AS u (pipeline_id, project_namespace_id, position)
WHERE pipeline_id IS NOT NULL
AND project_namespace_id IS NOT NULL
ORDER BY 1 ASC, 2 ASC
LIMIT 1) AS position_query, LATERAL (
SELECT "record"."pipeline_id", "record"."project_namespace_id"
FROM (
VALUES (NULL, NULL)) AS nulls
LEFT JOIN (
SELECT "p_ci_finished_pipeline_ch_sync_events"."pipeline_id" AS pipeline_id,
"p_ci_finished_pipeline_ch_sync_events"."project_namespace_id" AS project_namespace_id
FROM "p_ci_finished_pipeline_ch_sync_events"
WHERE "p_ci_finished_pipeline_ch_sync_events"."processed" = FALSE
AND (pipeline_id % 100) = recursive_keyset_cte.array_cte_pipeline_id_partition_array[position]
AND (("p_ci_finished_pipeline_ch_sync_events"."pipeline_id",
"p_ci_finished_pipeline_ch_sync_events"."project_namespace_id") >
(recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_pipeline_id_array[position],
recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_project_namespace_id_array[position]))
ORDER BY "p_ci_finished_pipeline_ch_sync_events"."pipeline_id" ASC,
"p_ci_finished_pipeline_ch_sync_events"."project_namespace_id" ASC
LIMIT 1) record ON TRUE
LIMIT 1) AS next_cursor_values))
SELECT pipeline_id, project_namespace_id
FROM "recursive_keyset_cte" AS "p_ci_finished_pipeline_ch_sync_events"
WHERE (count <> 0)) p_ci_finished_pipeline_ch_sync_events
LIMIT 500 Limit (cost=828.96..832.23 rows=100 width=16) (actual time=1.985..31.064 rows=500 loops=1)
Buffers: shared hit=3238
I/O Timings: read=0.000 write=0.000
-> CTE Scan on recursive_keyset_cte p_ci_finished_pipeline_ch_sync_events (cost=828.96..831.23 rows=100 width=16) (actual time=1.984..30.999 rows=500 loops=1)
Filter: (p_ci_finished_pipeline_ch_sync_events.count <> 0)
Rows Removed by Filter: 1
Buffers: shared hit=3238
I/O Timings: read=0.000 write=0.000
CTE array_cte
-> Function Scan on generate_series p_ci_finished_pipeline_ch_sync_events_1 (cost=0.00..1.00 rows=100 width=4) (actual time=0.015..0.024 rows=100 loops=1)
I/O Timings: read=0.000 write=0.000
CTE recursive_keyset_cte
-> Recursive Union (cost=467.88..827.95 rows=101 width=120) (actual time=1.834..29.366 rows=501 loops=1)
Buffers: shared hit=3238
I/O Timings: read=0.000 write=0.000
-> Limit (cost=467.88..467.90 rows=1 width=120) (actual time=1.832..1.837 rows=1 loops=1)
Buffers: shared hit=475
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on array_scope_lateral_query (cost=467.88..467.90 rows=1 width=120) (actual time=1.832..1.836 rows=1 loops=1)
Buffers: shared hit=475
I/O Timings: read=0.000 write=0.000
-> Aggregate (cost=467.88..467.89 rows=1 width=96) (actual time=1.831..1.834 rows=1 loops=1)
Buffers: shared hit=475
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=3.42..467.13 rows=100 width=20) (actual time=0.104..1.801 rows=100 loops=1)
Buffers: shared hit=475
I/O Timings: read=0.000 write=0.000
-> CTE Scan on array_cte (cost=0.00..2.00 rows=100 width=4) (actual time=0.016..0.040 rows=100 loops=1)
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on p_ci_finished_pipeline_ch_sync_events_2 (cost=3.42..4.64 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=100)
Filter: ((p_ci_finished_pipeline_ch_sync_events_2.pipeline_id IS NOT NULL) AND (p_ci_finished_pipeline_ch_sync_events_2.project_namespace_id IS NOT NULL))
Rows Removed by Filter: 0
Buffers: shared hit=475
I/O Timings: read=0.000 write=0.000
-> Limit (cost=3.42..4.63 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=100)
Buffers: shared hit=475
I/O Timings: read=0.000 write=0.000
-> Incremental Sort (cost=3.42..611.01 rows=500 width=16) (actual time=0.017..0.017 rows=1 loops=100)
Sort Key: p_ci_finished_pipeline_ch_sync_events_3.pipeline_id, p_ci_finished_pipeline_ch_sync_events_3.project_namespace_id
Buffers: shared hit=475
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_3 (cost=0.42..596.38 rows=500 width=16) (actual time=0.015..0.016 rows=2 loops=100)
Index Cond: (((p_ci_finished_pipeline_ch_sync_events_3.pipeline_id % '100'::bigint) = array_cte.pipeline_id_partition) AND (p_ci_finished_pipeline_ch_sync_events_3.pipeline_id >= 1360603030))
Filter: (ROW(p_ci_finished_pipeline_ch_sync_events_3.pipeline_id, p_ci_finished_pipeline_ch_sync_events_3.project_namespace_id) > ROW(1360603030, 1))
Rows Removed by Filter: 0
Buffers: shared hit=469
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=2.00..35.80 rows=10 width=120) (actual time=0.054..0.054 rows=1 loops=500)
Buffers: shared hit=2763
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.16..1.98 rows=10 width=112) (actual time=0.038..0.039 rows=1 loops=500)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on recursive_keyset_cte (cost=0.00..0.20 rows=10 width=104) (actual time=0.000..0.000 rows=1 loops=500)
I/O Timings: read=0.000 write=0.000
-> Limit (cost=0.16..0.16 rows=1 width=24) (actual time=0.038..0.038 rows=1 loops=500)
I/O Timings: read=0.000 write=0.000
-> Sort (cost=0.16..0.18 rows=10 width=24) (actual time=0.038..0.038 rows=1 loops=500)
Sort Key: u.pipeline_id, u.project_namespace_id
Sort Method: top-N heapsort Memory: 25kB
I/O Timings: read=0.000 write=0.000
-> Function Scan on u (cost=0.01..0.11 rows=10 width=24) (actual time=0.012..0.024 rows=100 loops=500)
Filter: ((u.pipeline_id IS NOT NULL) AND (u.project_namespace_id IS NOT NULL))
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Limit (cost=1.85..3.33 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=500)
Buffers: shared hit=2763
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=1.85..3.33 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=500)
Buffers: shared hit=2763
I/O Timings: read=0.000 write=0.000
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=500)
I/O Timings: read=0.000 write=0.000
-> Limit (cost=1.85..3.30 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=500)
Buffers: shared hit=2763
I/O Timings: read=0.000 write=0.000
-> Incremental Sort (cost=1.85..243.93 rows=167 width=16) (actual time=0.009..0.009 rows=1 loops=500)
Sort Key: p_ci_finished_pipeline_ch_sync_events_4.pipeline_id, p_ci_finished_pipeline_ch_sync_events_4.project_namespace_id
Buffers: shared hit=2763
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_4 (cost=0.42..236.42 rows=167 width=16) (actual time=0.008..0.008 rows=2 loops=500)
Index Cond: (((p_ci_finished_pipeline_ch_sync_events_4.pipeline_id % '100'::bigint) = (recursive_keyset_cte.array_cte_pipeline_id_partition_array)[u."position"]) AND (p_ci_finished_pipeline_ch_sync_events_4.pipeline_id >= (recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_pipeline_id_array)[u."position"]))
Filter: (ROW(p_ci_finished_pipeline_ch_sync_events_4.pipeline_id, p_ci_finished_pipeline_ch_sync_events_4.project_namespace_id) > ROW((recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_pipeline_id_array)[u."position"], (recursive_keyset_cte.p_ci_finished_pipeline_ch_sync_events_project_namespace_id_arra)[u."position"]))
Rows Removed by Filter: 1
Buffers: shared hit=2763
I/O Timings: read=0.000 write=0.000finished_pipelines_sync_service.rb:154:in `yield_pipelines'
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/29664/commands/92094
SELECT "ci_pipelines"."finished_at", "ci_pipelines"."id", "ci_pipelines"."duration", "ci_pipelines"."status",
"ci_pipelines"."source", "ci_pipelines"."ref", EXTRACT(epoch FROM ci_pipelines.committed_at) AS casted_committed_at,
EXTRACT(epoch FROM ci_pipelines.created_at) AS casted_created_at, EXTRACT(epoch FROM ci_pipelines.started_at) AS
casted_started_at, EXTRACT(epoch FROM ci_pipelines.finished_at) AS casted_finished_at,
ARRAY_TO_STRING(ci_namespace_mirrors.traversal_ids, '/') || '/' AS path
FROM "ci_pipelines"
LEFT OUTER JOIN "ci_project_mirrors" ON "ci_project_mirrors"."project_id" = "ci_pipelines"."project_id"
LEFT OUTER JOIN "ci_namespace_mirrors" ON "ci_namespace_mirrors"."namespace_id" = "ci_project_mirrors"."namespace_id"
WHERE "ci_pipelines"."id" IN (1360603031,1360603032,1360603033,1360603034,1360603035,1360603036,1360603038,1360603042,1360603043,1360603044,1360603045,1360603046,1360603047,1360603048,1360603049,1360603050,1360603051,1360603052,1360603054,1360603055,1360603056,1360603058,1360603059,1360603060,1360603061,1360603062,1360603063,1360603065,1360603066,1360603067,1360603068,1360603069,1360603070,1360603071,1360603072,1360603073,1360603076,1360603077,1360603078,1360603079,1360603080,1360603081,1360603083,1360603084,1360603085,1360603086,1360603087,1360603088,1360603089,1360603090,1360603091,1360603092,1360603094,1360603096,1360603097,1360603098,1360603099,1360603100,1360603101,1360603102,1360603103,1360603104,1360603105,1360603106,1360603107,1360603109,1360603110,1360603111,1360603112,1360603113,1360603114,1360603116,1360603117,1360603119,1360603120,1360603123,1360603124,1360603125,1360603126,1360603127,1360603128,1360603129,1360603130,1360603131,1360603132,1360603134,1360603136,1360603137,1360603139,1360603140,1360603142,1360603143,1360603144,1360603145,1360603146,1360603147,1360603148,1360603149,1360603150,1360603152,1360603153,1360603154,1360603155,1360603156,1360603157,1360603158,1360603159,1360603160,1360603161,1360603162,1360603163,1360603165,1360603167,1360603168,1360603169,1360603171,1360603172,1360603173,1360603174,1360603175,1360603176,1360603177,1360603178,1360603179,1360603180,1360603182,1360603183,1360603184,1360603186,1360603187,1360603188,1360603189,1360603190,1360603191,1360603192,1360603193,1360603194,1360603195,1360603196,1360603197,1360603198,1360603199,1360603200,1360603201,1360603202,1360603203,1360603204,1360603205,1360603207,1360603208,1360603209,1360603211,1360603212,1360603213,1360603214,1360603215,1360603216,1360603217,1360603218,1360603219,1360603220,1360603221,1360603223,1360603224,1360603225,1360603226,1360603227,1360603228,1360603229,1360603230,1360603231,1360603232,1360603233,1360603234,1360603235,1360603236,1360603237,1360603239,1360603240,1360603241,1360603242,1360603243,1360603244,1360603245,1360603246,1360603247,1360603248,1360603249,1360603250,1360603252,1360603254,1360603255,1360603257,1360603258,1360603259,1360603260,1360603261,1360603262,1360603263,1360603264,1360603265,1360603266,1360603269,1360603270,1360603271,1360603272,1360603273,1360603274,1360603275,1360603276,1360603278,1360603279,1360603280,1360603282,1360603283,1360603284,1360603285,1360603286,1360603287,1360603288,1360603289,1360603290,1360603291,1360603294,1360603295,1360603296,1360603297,1360603298,1360603299,1360603300,1360603301,1360603302,1360603303,1360603304,1360603305,1360603306,1360603307,1360603308,1360603309,1360603310,1360603311,1360603312,1360603313,1360603314,1360603315,1360603316,1360603318,1360603319,1360603320,1360603321,1360603322,1360603323,1360603324,1360603325,1360603326,1360603328,1360603329,1360603330,1360603331,1360603332,1360603333,1360603334,1360603335,1360603336,1360603337,1360603339,1360603341,1360603342,1360603343,1360603344,1360603345,1360603347,1360603348,1360603349,1360603350,1360603351,1360603352,1360603353,1360603354,1360603355,1360603356,1360603357,1360603358,1360603360,1360603361,1360603362,1360603363,1360603364,1360603365,1360603366,1360603367,1360603368,1360603369,1360603370,1360603371,1360603372,1360603373,1360603374,1360603376,1360603378,1360603379,1360603380,1360603381,1360603382,1360603383,1360603384,1360603385,1360603387,1360603388,1360603389,1360603390,1360603391,1360603392,1360603393,1360603394,1360603396,1360603397,1360603398,1360603399,1360603400,1360603401,1360603402,1360603403,1360603404,1360603405,1360603406,1360603407,1360603409,1360603410,1360603411,1360603413,1360603414,1360603415,1360603416,1360603417,1360603418,1360603419,1360603420,1360603421,1360603422,1360603423,1360603424,1360603425,1360603426,1360603427,1360603429,1360603430,1360603431,1360603432,1360603433,1360603434,1360603435,1360603436,1360603437,1360603438,1360603439,1360603440,1360603441,1360603442,1360603443,1360603444,1360603448,1360603449,1360603450,1360603451,1360603452,1360603453,1360603454,1360603455,1360603456,1360603457,1360603458,1360603459,1360603460,1360603461,1360603462,1360603463,1360603464,1360603465,1360603466,1360603467,1360603468,1360603469,1360603470,1360603471,1360603472,1360603473,1360603474,1360603475,1360603476,1360603477,1360603478,1360603479,1360603480,1360603481,1360603482,1360603483,1360603485,1360603487,1360603488,1360603490,1360603491,1360603492,1360603493,1360603494,1360603495,1360603496,1360603497,1360603498,1360603499,1360603500,1360603501,1360603502,1360603503,1360603504,1360603506,1360603507,1360603508,1360603509,1360603510,1360603511,1360603513,1360603514,1360603515,1360603516,1360603517,1360603518,1360603519,1360603520,1360603521,1360603522,1360603523,1360603524,1360603525,1360603526,1360603527,1360603528,1360603529,1360603530,1360603531,1360603532,1360603533,1360603534,1360603535,1360603536,1360603537,1360603538,1360603539,1360603542,1360603543,1360603544,1360603545,1360603546,1360603547,1360603548,1360603549,1360603550,1360603551,1360603553,1360603554,1360603555,1360603556,1360603557,1360603558,1360603559,1360603560,1360603561,1360603562,1360603563,1360603564,1360603566,1360603567,1360603568,1360603571,1360603572,1360603573,1360603576,1360603577,1360603579,1360603581,1360603582,1360603583,1360603584,1360603585,1360603587,1360603589,1360603591,1360603592,1360603593,1360603594,1360603595,1360603596,1360603597,1360603598,1360603599,1360603600,1360603601,1360603602,1360603603,1360603604) Nested Loop Left Join (cost=1.71..3563.23 rows=500 width=205) (actual time=14.214..2465.017 rows=500 loops=1)
Buffers: shared hit=6792 read=2205 dirtied=339
I/O Timings: read=2416.888 write=0.000
-> Nested Loop Left Join (cost=1.14..3249.64 rows=500 width=73) (actual time=10.208..1303.554 rows=500 loops=1)
Buffers: shared hit=5203 read=1294 dirtied=332
I/O Timings: read=1272.663 write=0.000
-> Index Scan using ci_pipelines_pkey on public.ci_pipelines (cost=0.58..1459.89 rows=500 width=73) (actual time=6.023..267.403 rows=500 loops=1)
Index Cond: (ci_pipelines.id = ANY ('{1360603031,1360603032,1360603033,1360603034,1360603035,1360603036,1360603038,1360603042,1360603043,1360603044,1360603045,1360603046,1360603047,1360603048,1360603049,1360603050,1360603051,1360603052,1360603054,1360603055,1360603056,1360603058,1360603059,1360603060,1360603061,1360603062,1360603063,1360603065,1360603066,1360603067,1360603068,1360603069,1360603070,1360603071,1360603072,1360603073,1360603076,1360603077,1360603078,1360603079,1360603080,1360603081,1360603083,1360603084,1360603085,1360603086,1360603087,1360603088,1360603089,1360603090,1360603091,1360603092,1360603094,1360603096,1360603097,1360603098,1360603099,1360603100,1360603101,1360603102,1360603103,1360603104,1360603105,1360603106,1360603107,1360603109,1360603110,1360603111,1360603112,1360603113,1360603114,1360603116,1360603117,1360603119,1360603120,1360603123,1360603124,1360603125,1360603126,1360603127,1360603128,1360603129,1360603130,1360603131,1360603132,1360603134,1360603136,1360603137,1360603139,1360603140,1360603142,1360603143,1360603144,1360603145,1360603146,1360603147,1360603148,1360603149,1360603150,1360603152,1360603153,1360603154,1360603155,1360603156,1360603157,1360603158,1360603159,1360603160,1360603161,1360603162,1360603163,1360603165,1360603167,1360603168,1360603169,1360603171,1360603172,1360603173,1360603174,1360603175,1360603176,1360603177,1360603178,1360603179,1360603180,1360603182,1360603183,1360603184,1360603186,1360603187,1360603188,1360603189,1360603190,1360603191,1360603192,1360603193,1360603194,1360603195,1360603196,1360603197,1360603198,1360603199,1360603200,1360603201,1360603202,1360603203,1360603204,1360603205,1360603207,1360603208,1360603209,1360603211,1360603212,1360603213,1360603214,1360603215,1360603216,1360603217,1360603218,1360603219,1360603220,1360603221,1360603223,1360603224,1360603225,1360603226,1360603227,1360603228,1360603229,1360603230,1360603231,1360603232,1360603233,1360603234,1360603235,1360603236,1360603237,1360603239,1360603240,1360603241,1360603242,1360603243,1360603244,1360603245,1360603246,1360603247,1360603248,1360603249,1360603250,1360603252,1360603254,1360603255,1360603257,1360603258,1360603259,1360603260,1360603261,1360603262,1360603263,1360603264,1360603265,1360603266,1360603269,1360603270,1360603271,1360603272,1360603273,1360603274,1360603275,1360603276,1360603278,1360603279,1360603280,1360603282,1360603283,1360603284,1360603285,1360603286,1360603287,1360603288,1360603289,1360603290,1360603291,1360603294,1360603295,1360603296,1360603297,1360603298,1360603299,1360603300,1360603301,1360603302,1360603303,1360603304,1360603305,1360603306,1360603307,1360603308,1360603309,1360603310,1360603311,1360603312,1360603313,1360603314,1360603315,1360603316,1360603318,1360603319,1360603320,1360603321,1360603322,1360603323,1360603324,1360603325,1360603326,1360603328,1360603329,1360603330,1360603331,1360603332,1360603333,1360603334,1360603335,1360603336,1360603337,1360603339,1360603341,1360603342,1360603343,1360603344,1360603345,1360603347,1360603348,1360603349,1360603350,1360603351,1360603352,1360603353,1360603354,1360603355,1360603356,1360603357,1360603358,1360603360,1360603361,1360603362,1360603363,1360603364,1360603365,1360603366,1360603367,1360603368,1360603369,1360603370,1360603371,1360603372,1360603373,1360603374,1360603376,1360603378,1360603379,1360603380,1360603381,1360603382,1360603383,1360603384,1360603385,1360603387,1360603388,1360603389,1360603390,1360603391,1360603392,1360603393,1360603394,1360603396,1360603397,1360603398,1360603399,1360603400,1360603401,1360603402,1360603403,1360603404,1360603405,1360603406,1360603407,1360603409,1360603410,1360603411,1360603413,1360603414,1360603415,1360603416,1360603417,1360603418,1360603419,1360603420,1360603421,1360603422,1360603423,1360603424,1360603425,1360603426,1360603427,1360603429,1360603430,1360603431,1360603432,1360603433,1360603434,1360603435,1360603436,1360603437,1360603438,1360603439,1360603440,1360603441,1360603442,1360603443,1360603444,1360603448,1360603449,1360603450,1360603451,1360603452,1360603453,1360603454,1360603455,1360603456,1360603457,1360603458,1360603459,1360603460,1360603461,1360603462,1360603463,1360603464,1360603465,1360603466,1360603467,1360603468,1360603469,1360603470,1360603471,1360603472,1360603473,1360603474,1360603475,1360603476,1360603477,1360603478,1360603479,1360603480,1360603481,1360603482,1360603483,1360603485,1360603487,1360603488,1360603490,1360603491,1360603492,1360603493,1360603494,1360603495,1360603496,1360603497,1360603498,1360603499,1360603500,1360603501,1360603502,1360603503,1360603504,1360603506,1360603507,1360603508,1360603509,1360603510,1360603511,1360603513,1360603514,1360603515,1360603516,1360603517,1360603518,1360603519,1360603520,1360603521,1360603522,1360603523,1360603524,1360603525,1360603526,1360603527,1360603528,1360603529,1360603530,1360603531,1360603532,1360603533,1360603534,1360603535,1360603536,1360603537,1360603538,1360603539,1360603542,1360603543,1360603544,1360603545,1360603546,1360603547,1360603548,1360603549,1360603550,1360603551,1360603553,1360603554,1360603555,1360603556,1360603557,1360603558,1360603559,1360603560,1360603561,1360603562,1360603563,1360603564,1360603566,1360603567,1360603568,1360603571,1360603572,1360603573,1360603576,1360603577,1360603579,1360603581,1360603582,1360603583,1360603584,1360603585,1360603587,1360603589,1360603591,1360603592,1360603593,1360603594,1360603595,1360603596,1360603597,1360603598,1360603599,1360603600,1360603601,1360603602,1360603603,1360603604}'::bigint[]))
Buffers: shared hit=3604 read=391 dirtied=310
I/O Timings: read=252.062 write=0.000
-> Index Scan using index_ci_project_mirrors_on_project_id on public.ci_project_mirrors (cost=0.56..3.58 rows=1 width=8) (actual time=2.069..2.069 rows=1 loops=500)
Index Cond: (ci_project_mirrors.project_id = ci_pipelines.project_id)
Buffers: shared hit=1598 read=903 dirtied=21
I/O Timings: read=1020.601 write=0.000
-> Index Scan using index_ci_namespace_mirrors_on_namespace_id on public.ci_namespace_mirrors (cost=0.56..0.61 rows=1 width=32) (actual time=2.312..2.312 rows=1 loops=500)
Index Cond: (ci_namespace_mirrors.namespace_id = ci_project_mirrors.namespace_id)
Buffers: shared hit=1589 read=911 dirtied=7
I/O Timings: read=1144.225 write=0.000Marking events as processed
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/29664/commands/92095
UPDATE
"p_ci_finished_pipeline_ch_sync_events"
SET "processed" = TRUE
WHERE "p_ci_finished_pipeline_ch_sync_events"."pipeline_id" IN (1360603031, 1360603032, 1360603033, 1360603034,
1360603035, 1360603036, 1360603038, 1360603042, 1360603043, 1360603044, 1360603045, 1360603046, 1360603047,
1360603048, 1360603049, 1360603050, 1360603051, 1360603052, 1360603054, 1360603055, 1360603056, 1360603058,
1360603059, 1360603060, 1360603061, 1360603062, 1360603063, 1360603065, 1360603066, 1360603067, 1360603068,
1360603069, 1360603070, 1360603071, 1360603072, 1360603073, 1360603076, 1360603077, 1360603078, 1360603079,
1360603080, 1360603081, 1360603083, 1360603084, 1360603085, 1360603086, 1360603087, 1360603088, 1360603089,
1360603090, 1360603091, 1360603092, 1360603094, 1360603096, 1360603097, 1360603098, 1360603099, 1360603100,
1360603101, 1360603102, 1360603103, 1360603104, 1360603105, 1360603106, 1360603107, 1360603109, 1360603110,
1360603111, 1360603112, 1360603113, 1360603114, 1360603116, 1360603117, 1360603119, 1360603120, 1360603123,
1360603124, 1360603125, 1360603126, 1360603127, 1360603128, 1360603129, 1360603130, 1360603131, 1360603132,
1360603134, 1360603136, 1360603137, 1360603139, 1360603140, 1360603142, 1360603143, 1360603144, 1360603145,
1360603146, 1360603147, 1360603148, 1360603149, 1360603150, 1360603152, 1360603153, 1360603154, 1360603155,
1360603156, 1360603157, 1360603158, 1360603159, 1360603160, 1360603161, 1360603162, 1360603163, 1360603165,
1360603167, 1360603168, 1360603169, 1360603171, 1360603172, 1360603173, 1360603174, 1360603175, 1360603176,
1360603177, 1360603178, 1360603179, 1360603180, 1360603182, 1360603183, 1360603184, 1360603186, 1360603187,
1360603188, 1360603189, 1360603190, 1360603191, 1360603192, 1360603193, 1360603194, 1360603195, 1360603196,
1360603197, 1360603198, 1360603199, 1360603200, 1360603201, 1360603202, 1360603203, 1360603204, 1360603205,
1360603207, 1360603208, 1360603209, 1360603211, 1360603212, 1360603213, 1360603214, 1360603215, 1360603216,
1360603217, 1360603218, 1360603219, 1360603220, 1360603221, 1360603223, 1360603224, 1360603225, 1360603226,
1360603227, 1360603228, 1360603229, 1360603230, 1360603231, 1360603232, 1360603233, 1360603234, 1360603235,
1360603236, 1360603237, 1360603239, 1360603240, 1360603241, 1360603242, 1360603243, 1360603244, 1360603245,
1360603246, 1360603247, 1360603248, 1360603249, 1360603250, 1360603252, 1360603254, 1360603255, 1360603257,
1360603258, 1360603259, 1360603260, 1360603261, 1360603262, 1360603263, 1360603264, 1360603265, 1360603266,
1360603269, 1360603270, 1360603271, 1360603272, 1360603273, 1360603274, 1360603275, 1360603276, 1360603278,
1360603279, 1360603280, 1360603282, 1360603283, 1360603284, 1360603285, 1360603286, 1360603287, 1360603288,
1360603289, 1360603290, 1360603291, 1360603294, 1360603295, 1360603296, 1360603297, 1360603298, 1360603299,
1360603300, 1360603301, 1360603302, 1360603303, 1360603304, 1360603305, 1360603306, 1360603307, 1360603308,
1360603309, 1360603310, 1360603311, 1360603312, 1360603313, 1360603314, 1360603315, 1360603316, 1360603318,
1360603319, 1360603320, 1360603321, 1360603322, 1360603323, 1360603324, 1360603325, 1360603326, 1360603328,
1360603329, 1360603330, 1360603331, 1360603332, 1360603333, 1360603334, 1360603335, 1360603336, 1360603337,
1360603339, 1360603341, 1360603342, 1360603343, 1360603344, 1360603345, 1360603347, 1360603348, 1360603349,
1360603350, 1360603351, 1360603352, 1360603353, 1360603354, 1360603355, 1360603356, 1360603357, 1360603358,
1360603360, 1360603361, 1360603362, 1360603363, 1360603364, 1360603365, 1360603366, 1360603367, 1360603368,
1360603369, 1360603370, 1360603371, 1360603372, 1360603373, 1360603374, 1360603376, 1360603378, 1360603379,
1360603380, 1360603381, 1360603382, 1360603383, 1360603384, 1360603385, 1360603387, 1360603388, 1360603389,
1360603390, 1360603391, 1360603392, 1360603393, 1360603394, 1360603396, 1360603397, 1360603398, 1360603399,
1360603400, 1360603401, 1360603402, 1360603403, 1360603404, 1360603405, 1360603406, 1360603407, 1360603409,
1360603410, 1360603411, 1360603413, 1360603414, 1360603415, 1360603416, 1360603417, 1360603418, 1360603419,
1360603420, 1360603421, 1360603422, 1360603423, 1360603424, 1360603425, 1360603426, 1360603427, 1360603429,
1360603430, 1360603431, 1360603432, 1360603433, 1360603434, 1360603435, 1360603436, 1360603437, 1360603438,
1360603439, 1360603440, 1360603441, 1360603442, 1360603443, 1360603444, 1360603448, 1360603449, 1360603450,
1360603451, 1360603452, 1360603453, 1360603454, 1360603455, 1360603456, 1360603457, 1360603458, 1360603459,
1360603460, 1360603461, 1360603462, 1360603463, 1360603464, 1360603465, 1360603466, 1360603467, 1360603468,
1360603469, 1360603470, 1360603471, 1360603472, 1360603473, 1360603474, 1360603475, 1360603476, 1360603477,
1360603478, 1360603479, 1360603480, 1360603481, 1360603482, 1360603483, 1360603485, 1360603487, 1360603488,
1360603490, 1360603491, 1360603492, 1360603493, 1360603494, 1360603495, 1360603496, 1360603497, 1360603498,
1360603499, 1360603500, 1360603501, 1360603502, 1360603503, 1360603504, 1360603506, 1360603507, 1360603508,
1360603509, 1360603510, 1360603511, 1360603513, 1360603514, 1360603515, 1360603516, 1360603517, 1360603518,
1360603519, 1360603520, 1360603521, 1360603522, 1360603523, 1360603524, 1360603525, 1360603526, 1360603527,
1360603528, 1360603529, 1360603530, 1360603531, 1360603532, 1360603533, 1360603534, 1360603535, 1360603536,
1360603537, 1360603538, 1360603539, 1360603542, 1360603543, 1360603544, 1360603545, 1360603546, 1360603547,
1360603548, 1360603549, 1360603550, 1360603551, 1360603553, 1360603554, 1360603555, 1360603556, 1360603557,
1360603558, 1360603559, 1360603560, 1360603561, 1360603562, 1360603563, 1360603564, 1360603566, 1360603567,
1360603568, 1360603571, 1360603572, 1360603573, 1360603576, 1360603577, 1360603579, 1360603581, 1360603582,
1360603583, 1360603584, 1360603585, 1360603587, 1360603589, 1360603591, 1360603592, 1360603593, 1360603594,
1360603595, 1360603596, 1360603597, 1360603598, 1360603599, 1360603600, 1360603601, 1360603602, 1360603603,
1360603604) ModifyTable on public.p_ci_finished_pipeline_ch_sync_events (cost=0.42..1032.00 rows=0 width=0) (actual time=3.376..3.377 rows=0 loops=1)
Buffers: shared hit=5018 dirtied=5 written=5
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_1 (cost=0.42..1032.00 rows=500 width=11) (actual time=0.055..0.642 rows=500 loops=1)
Index Cond: (p_ci_finished_pipeline_ch_sync_events_1.pipeline_id = ANY ('{1360603031,1360603032,1360603033,1360603034,1360603035,1360603036,1360603038,1360603042,1360603043,1360603044,1360603045,1360603046,1360603047,1360603048,1360603049,1360603050,1360603051,1360603052,1360603054,1360603055,1360603056,1360603058,1360603059,1360603060,1360603061,1360603062,1360603063,1360603065,1360603066,1360603067,1360603068,1360603069,1360603070,1360603071,1360603072,1360603073,1360603076,1360603077,1360603078,1360603079,1360603080,1360603081,1360603083,1360603084,1360603085,1360603086,1360603087,1360603088,1360603089,1360603090,1360603091,1360603092,1360603094,1360603096,1360603097,1360603098,1360603099,1360603100,1360603101,1360603102,1360603103,1360603104,1360603105,1360603106,1360603107,1360603109,1360603110,1360603111,1360603112,1360603113,1360603114,1360603116,1360603117,1360603119,1360603120,1360603123,1360603124,1360603125,1360603126,1360603127,1360603128,1360603129,1360603130,1360603131,1360603132,1360603134,1360603136,1360603137,1360603139,1360603140,1360603142,1360603143,1360603144,1360603145,1360603146,1360603147,1360603148,1360603149,1360603150,1360603152,1360603153,1360603154,1360603155,1360603156,1360603157,1360603158,1360603159,1360603160,1360603161,1360603162,1360603163,1360603165,1360603167,1360603168,1360603169,1360603171,1360603172,1360603173,1360603174,1360603175,1360603176,1360603177,1360603178,1360603179,1360603180,1360603182,1360603183,1360603184,1360603186,1360603187,1360603188,1360603189,1360603190,1360603191,1360603192,1360603193,1360603194,1360603195,1360603196,1360603197,1360603198,1360603199,1360603200,1360603201,1360603202,1360603203,1360603204,1360603205,1360603207,1360603208,1360603209,1360603211,1360603212,1360603213,1360603214,1360603215,1360603216,1360603217,1360603218,1360603219,1360603220,1360603221,1360603223,1360603224,1360603225,1360603226,1360603227,1360603228,1360603229,1360603230,1360603231,1360603232,1360603233,1360603234,1360603235,1360603236,1360603237,1360603239,1360603240,1360603241,1360603242,1360603243,1360603244,1360603245,1360603246,1360603247,1360603248,1360603249,1360603250,1360603252,1360603254,1360603255,1360603257,1360603258,1360603259,1360603260,1360603261,1360603262,1360603263,1360603264,1360603265,1360603266,1360603269,1360603270,1360603271,1360603272,1360603273,1360603274,1360603275,1360603276,1360603278,1360603279,1360603280,1360603282,1360603283,1360603284,1360603285,1360603286,1360603287,1360603288,1360603289,1360603290,1360603291,1360603294,1360603295,1360603296,1360603297,1360603298,1360603299,1360603300,1360603301,1360603302,1360603303,1360603304,1360603305,1360603306,1360603307,1360603308,1360603309,1360603310,1360603311,1360603312,1360603313,1360603314,1360603315,1360603316,1360603318,1360603319,1360603320,1360603321,1360603322,1360603323,1360603324,1360603325,1360603326,1360603328,1360603329,1360603330,1360603331,1360603332,1360603333,1360603334,1360603335,1360603336,1360603337,1360603339,1360603341,1360603342,1360603343,1360603344,1360603345,1360603347,1360603348,1360603349,1360603350,1360603351,1360603352,1360603353,1360603354,1360603355,1360603356,1360603357,1360603358,1360603360,1360603361,1360603362,1360603363,1360603364,1360603365,1360603366,1360603367,1360603368,1360603369,1360603370,1360603371,1360603372,1360603373,1360603374,1360603376,1360603378,1360603379,1360603380,1360603381,1360603382,1360603383,1360603384,1360603385,1360603387,1360603388,1360603389,1360603390,1360603391,1360603392,1360603393,1360603394,1360603396,1360603397,1360603398,1360603399,1360603400,1360603401,1360603402,1360603403,1360603404,1360603405,1360603406,1360603407,1360603409,1360603410,1360603411,1360603413,1360603414,1360603415,1360603416,1360603417,1360603418,1360603419,1360603420,1360603421,1360603422,1360603423,1360603424,1360603425,1360603426,1360603427,1360603429,1360603430,1360603431,1360603432,1360603433,1360603434,1360603435,1360603436,1360603437,1360603438,1360603439,1360603440,1360603441,1360603442,1360603443,1360603444,1360603448,1360603449,1360603450,1360603451,1360603452,1360603453,1360603454,1360603455,1360603456,1360603457,1360603458,1360603459,1360603460,1360603461,1360603462,1360603463,1360603464,1360603465,1360603466,1360603467,1360603468,1360603469,1360603470,1360603471,1360603472,1360603473,1360603474,1360603475,1360603476,1360603477,1360603478,1360603479,1360603480,1360603481,1360603482,1360603483,1360603485,1360603487,1360603488,1360603490,1360603491,1360603492,1360603493,1360603494,1360603495,1360603496,1360603497,1360603498,1360603499,1360603500,1360603501,1360603502,1360603503,1360603504,1360603506,1360603507,1360603508,1360603509,1360603510,1360603511,1360603513,1360603514,1360603515,1360603516,1360603517,1360603518,1360603519,1360603520,1360603521,1360603522,1360603523,1360603524,1360603525,1360603526,1360603527,1360603528,1360603529,1360603530,1360603531,1360603532,1360603533,1360603534,1360603535,1360603536,1360603537,1360603538,1360603539,1360603542,1360603543,1360603544,1360603545,1360603546,1360603547,1360603548,1360603549,1360603550,1360603551,1360603553,1360603554,1360603555,1360603556,1360603557,1360603558,1360603559,1360603560,1360603561,1360603562,1360603563,1360603564,1360603566,1360603567,1360603568,1360603571,1360603572,1360603573,1360603576,1360603577,1360603579,1360603581,1360603582,1360603583,1360603584,1360603585,1360603587,1360603589,1360603591,1360603592,1360603593,1360603594,1360603595,1360603596,1360603597,1360603598,1360603599,1360603600,1360603601,1360603602,1360603603,1360603604}'::bigint[]))
Buffers: shared hit=1505
I/O Timings: read=0.000 write=0.000