Batch gitaly delete refs in buffered queue

What does this MR do and why?

GitLab keeps track of internal "refs" in Gitaly that are used to track the SHA for a specific pipeline running or SHA for a merge request. At various parts of the lifecycle of pipelines and merge requests we can delete these refs from Gitaly. These deletes happen at very rapid volume (up to 4k/minute on GitLab.com including users deleting refs). There are internal race conditions in Gitaly/git which make it unsafe to delete 2 refs from the same project concurrently from 2 requests. As such we recently introduced !125109 (merged) and !123976 (merged) which serialize deletes on a per project basis using Sidekiq and Redis locks. While neither of these are deployed on GitLab.com they both have helped self-managed customers to reduce deadlock race conditions. The downside to these is that it slows down the rate of deletes we can send to Gitaly because we always wait for 1 to finish before starting the next delete.

Since it's safe to delete multiple refs in Gitaly in a single request, this MR reimplements the serialized approach to use a queue in Postgres which we can process to delete refs in batches per project. The implementation is:

1. When a delete needs to happen we insert into the partitioned table p_batched_git_ref_updates_deletions
2. Every minute the CleanupSchedulerWorker cron worker runs. It loops over all projects in p_batched_git_ref_updates_deletions and schedules a ProjectCleanupWorker for the project
3. The ProjectCleanupWorker finds all pending deletions in p_batched_git_ref_updates_deletions for the given project and loops over those in batches and sends deletes to Gitaly in batches

This change is behind 2 feature flags. One for pipeline refs and the other for merge request refs. Since these have different lifecycles and possibly different resilience to asynchronous deletion we figured it would be best to manage them separately. These feature flags can be rolled out per project. We aren't yet certain if this change should be enabled on GitLab.com but we believe it will help self-managed customers and would replace the other serialized deletes feature flags that some self-managed customers are running.

Benchmarking

Since we don't have these tables in production yet we can't use postgres.ai to get query plans. I've done local benchmarking to hopefully demonstrate the performance.

ProjectCleanupService

I used the following patch to benchmark performance.

spec/services/batched_git_ref_updates/project_cleanup_service_spec.rb

diff --git a/spec/services/batched_git_ref_updates/project_cleanup_service_spec.rb b/spec/services/batched_git_ref_updates/project_cleanup_service_spec.rb
index dcdfdfade3cf..e215ecdfad88 100644
--- a/spec/services/batched_git_ref_updates/project_cleanup_service_spec.rb
+++ b/spec/services/batched_git_ref_updates/project_cleanup_service_spec.rb
@@ -32,6 +32,54 @@
       project2.repository.create_ref('HEAD', project2_ref1.ref)
     end
 
+    it 'benchmark', :delete do
+      self.class.use_transactional_tests = false
+      p "#{Time.now}: Setup 1"
+      100.times do |i|
+        p "#{Time.now}: Setup 1-#{i}"
+        project = create(:project, :repository)
+        deletions = []
+        1000.times do |j|
+          ref = "refs/test/#{i}-#{j}"
+          deletions << BatchedGitRefUpdates::Deletion.new(project_id: project.id, ref: ref, created_at: Time.now, updated_at: Time.now)
+        end
+        BatchedGitRefUpdates::Deletion.bulk_insert!(deletions)
+      end
+
+      p "#{Time.now}: Setup 2"
+      100.times do |i|
+        p "#{Time.now}: Setup 2-#{i}"
+        deletions = []
+        1000.times do |j|
+          ref = "refs/test/to-be-deleted-#{i}-#{j}"
+
+          # Don't bother creating tonnes of refs in Gitaly, only create a few. It doesn't seem to impact runtime if the
+          # delete is deleting a non-existing ref.
+          project1.repository.create_ref('HEAD', ref) if j % 100 == 0
+
+          deletions << BatchedGitRefUpdates::Deletion.new(project_id: project1.id, ref: ref, created_at: Time.now, updated_at: Time.now)
+        end
+
+        BatchedGitRefUpdates::Deletion.bulk_insert!(deletions)
+      end
+
+      # Make sure the DB contains a bunch of processed records so that it sees a reason to use the pending index
+      p "#{Time.now} Priming"
+      service.execute
+      service.execute
+      service.execute
+      service.execute
+      service.execute
+
+      p "#{Time.now}: Running"
+      result = Benchmark.measure do
+        service.execute
+      end
+      puts result
+      p "#{Time.now}: Finished"
+    end
+
     it 'deletes the named refs in batches for the given project only' do
       expect(test_refs(project1)).to include(
         'refs/test/ref-to-not-be-deleted',

I found with the chosen batch sizes and total 10_000 limit the ProjectCleanupService took 7s to run if it cleaned up 10k rows with a total of 100k rows in the DB for this project to be cleaned up.

I used similar data and auto_explain to extract the below query plans but it's messy in RSpec due to transactions.

Select start ID

Query Text: SELECT "p_batched_git_ref_updates_deletions"."id" FROM "p_batched_git_ref_updates_deletions" WHERE "p_batched_git_ref_updates_deletions"."status" = 1 AND "p_batched_git_ref_updates_deletions"."project_id" = 8 ORDER BY "p_batched_git_ref_updates_deletions"."id" ASC LIMIT 1 /*application:test,correlation_id:473469f637887d710d54cf5c0b4beebf,db_config_name:main,line:/app/models/concerns/each_batch.rb:62:in `each_batch'*/
Limit  (cost=0.42..0.44 rows=1 width=8) (actual time=1.058..1.059 rows=1 loops=1)
  Buffers: shared hit=246
  ->  Index Only Scan using p_batched_git_ref_updates_deletions_1_project_id_id_idx on p_batched_git_ref_updates_deletions_1 p_batched_git_ref_updates_deletions  (cost=0.42..1745.05 rows=67218 width=8) (actual time=1.058..1.058 rows=1 loops=1)
        Index Cond: (project_id = 8)
        Heap Fetches: 14001
        Buffers: shared hit=246

Select a batch

Query Text: SELECT "p_batched_git_ref_updates_deletions"."ref", "p_batched_git_ref_updates_deletions"."id", "p_batched_git_ref_updates_deletions"."partition_id" FROM "p_batched_git_ref_updates_deletions" WHERE "p_batched_git_ref_updates_deletions"."status" = 1 AND "p_batched_git_ref_updates_deletions"."project_id" = 8 AND "p_batched_git_ref_updates_deletions"."id" >= 64007 AND "p_batched_git_ref_updates_deletions"."id" < 65007 /*application:test,correlation_id:473469f637887d710d54cf5c0b4beebf,db_config_name:main,line:(pry):3:in `block (2 levels) in execute'*/
Index Scan using p_batched_git_ref_updates_deletions_1_project_id_id_idx on p_batched_git_ref_updates_deletions_1 p_batched_git_ref_updates_deletions  (cost=0.41..392.41 rows=493 width=45) (actual time=0.020..0.296 rows=1000 loops=1)
  Index Cond: ((project_id = 8) AND (id >= 64007) AND (id < 65007))
  Buffers: shared hit=21

Mark records processed

Query Text: UPDATE "p_batched_git_ref_updates_deletions" SET "status" = 2 WHERE "p_batched_git_ref_updates_deletions"."status" = 1 AND "p_batched_git_ref_updates_deletions"."partition_id" = 1 AND "p_batched_git_ref_updates_deletions"."id" IN (69007, 69008, 69009, 69010, 69011, 69012, 69013, 69014, 69015, 69016, 69017, 69018, 69019, 69020, 69021, 69022, 69023, 69024, 69025, 69026, 69027, 69028, 69029, 69030, 69031, 69032, 69033, 69034, 69035, 69036, 69037, 69038, 69039, 69040, 69041, 69042, 69043, 69044, 69045, 69046, 69047, 69048, 69049, 69050, 69051, 69052, 69053, 69054, 69055, 69056, 69057, 69058, 69059, 69060, 69061, 69062, 69063, 69064, 69065, 69066, 69067, 69068, 69069, 69070, 69071, 69072, 69073, 69074, 69075, 69076, 69077, 69078, 69079, 69080, 69081, 69082, 69083, 69084, 69085, 69086, 69087, 69088, 69089, 69090, 69091, 69092, 69093, 69094, 69095, 69096, 69097, 69098, 69099, 69100, 69101, 69102, 69103, 69104, 69105, 69106, 69107, 69108, 69109, 69110, 69111, 69112, 69113, 69114, 69115, 69116, 69117, 69118, 69119, 69120, 69121, 69122, 69123, 69124, 69125, 69126, 69127, 69128, 69129, 69130, 69131, 69132, 69133, 69134, 69135, 69136, 69137, 69138, 69139, 69140, 69141, 69142, 69143, 69144, 69145, 69146, 69147, 69148, 69149, 69150, 69151, 69152, 69153, 69154, 69155, 69156, 69157, 69158, 69159, 69160, 69161, 69162, 69163, 69164, 69165, 69166, 69167, 69168, 69169, 69170, 69171, 69172, 69173, 69174, 69175, 69176, 69177, 69178, 69179, 69180, 69181, 69182, 69183, 69184, 69185, 69186, 69187, 69188, 69189, 69190, 69191, 69192, 69193, 69194, 69195, 69196, 69197, 69198, 69199, 69200, 69201, 69202, 69203, 69204, 69205, 69206, 69207, 69208, 69209, 69210, 69211, 69212, 69213, 69214, 69215, 69216, 69217, 69218, 69219, 69220, 69221, 69222, 69223, 69224, 69225, 69226, 69227, 69228, 69229, 69230, 69231, 69232, 69233, 69234, 69235, 69236, 69237, 69238, 69239, 69240, 69241, 69242, 69243, 69244, 69245, 69246, 69247, 69248, 69249, 69250, 69251, 69252, 69253, 69254, 69255, 69256, 69257, 69258, 69259, 69260, 69261, 69262, 69263, 69264, 69265, 69266, 69267, 69268, 69269, 69270, 69271, 69272, 69273, 69274, 69275, 69276, 69277, 69278, 69279, 69280, 69281, 69282, 69283, 69284, 69285, 69286, 69287, 69288, 69289, 69290, 69291, 69292, 69293, 69294, 69295, 69296, 69297, 69298, 69299, 69300, 69301, 69302, 69303, 69304, 69305, 69306, 69307, 69308, 69309, 69310, 69311, 69312, 69313, 69314, 69315, 69316, 69317, 69318, 69319, 69320, 69321, 69322, 69323, 69324, 69325, 69326, 69327, 69328, 69329, 69330, 69331, 69332, 69333, 69334, 69335, 69336, 69337, 69338, 69339, 69340, 69341, 69342, 69343, 69344, 69345, 69346, 69347, 69348, 69349, 69350, 69351, 69352, 69353, 69354, 69355, 69356, 69357, 69358, 69359, 69360, 69361, 69362, 69363, 69364, 69365, 69366, 69367, 69368, 69369, 69370, 69371, 69372, 69373, 69374, 69375, 69376, 69377, 69378, 69379, 69380, 69381, 69382, 69383, 69384, 69385, 69386, 69387, 69388, 69389, 69390, 69391, 69392, 69393, 69394, 69395, 69396, 69397, 69398, 69399, 69400, 69401, 69402, 69403, 69404, 69405, 69406, 69407, 69408, 69409, 69410, 69411, 69412, 69413, 69414, 69415, 69416, 69417, 69418, 69419, 69420, 69421, 69422, 69423, 69424, 69425, 69426, 69427, 69428, 69429, 69430, 69431, 69432, 69433, 69434, 69435, 69436, 69437, 69438, 69439, 69440, 69441, 69442, 69443, 69444, 69445, 69446, 69447, 69448, 69449, 69450, 69451, 69452, 69453, 69454, 69455, 69456, 69457, 69458, 69459, 69460, 69461, 69462, 69463, 69464, 69465, 69466, 69467, 69468, 69469, 69470, 69471, 69472, 69473, 69474, 69475, 69476, 69477, 69478, 69479, 69480, 69481, 69482, 69483, 69484, 69485, 69486, 69487, 69488, 69489, 69490, 69491, 69492, 69493, 69494, 69495, 69496, 69497, 69498, 69499, 69500, 69501, 69502, 69503, 69504, 69505, 69506, 69507, 69508, 69509, 69510, 69511, 69512, 69513, 69514, 69515, 69516, 69517, 69518, 69519, 69520, 69521, 69522, 69523, 69524, 69525, 69526, 69527, 69528, 69529, 69530, 69531, 69532, 69533, 69534, 69535, 69536, 69537, 69538, 69539, 69540, 69541, 69542, 69543, 69544, 69545, 69546, 69547, 69548, 69549, 69550, 69551, 69552, 69553, 69554, 69555, 69556, 69557, 69558, 69559, 69560, 69561, 69562, 69563, 69564, 69565, 69566, 69567, 69568, 69569, 69570, 69571, 69572, 69573, 69574, 69575, 69576, 69577, 69578, 69579, 69580, 69581, 69582, 69583, 69584, 69585, 69586, 69587, 69588, 69589, 69590, 69591, 69592, 69593, 69594, 69595, 69596, 69597, 69598, 69599, 69600, 69601, 69602, 69603, 69604, 69605, 69606, 69607, 69608, 69609, 69610, 69611, 69612, 69613, 69614, 69615, 69616, 69617, 69618, 69619, 69620, 69621, 69622, 69623, 69624, 69625, 69626, 69627, 69628, 69629, 69630, 69631, 69632, 69633, 69634, 69635, 69636, 69637, 69638, 69639, 69640, 69641, 69642, 69643, 69644, 69645, 69646, 69647, 69648, 69649, 69650, 69651, 69652, 69653, 69654, 69655, 69656, 69657, 69658, 69659, 69660, 69661, 69662, 69663, 69664, 69665, 69666, 69667, 69668, 69669, 69670, 69671, 69672, 69673, 69674, 69675, 69676, 69677, 69678, 69679, 69680, 69681, 69682, 69683, 69684, 69685, 69686, 69687, 69688, 69689, 69690, 69691, 69692, 69693, 69694, 69695, 69696, 69697, 69698, 69699, 69700, 69701, 69702, 69703, 69704, 69705, 69706, 69707, 69708, 69709, 69710, 69711, 69712, 69713, 69714, 69715, 69716, 69717, 69718, 69719, 69720, 69721, 69722, 69723, 69724, 69725, 69726, 69727, 69728, 69729, 69730, 69731, 69732, 69733, 69734, 69735, 69736, 69737, 69738, 69739, 69740, 69741, 69742, 69743, 69744, 69745, 69746, 69747, 69748, 69749, 69750, 69751, 69752, 69753, 69754, 69755, 69756, 69757, 69758, 69759, 69760, 69761, 69762, 69763, 69764, 69765, 69766, 69767, 69768, 69769, 69770, 69771, 69772, 69773, 69774, 69775, 69776, 69777, 69778, 69779, 69780, 69781, 69782, 69783, 69784, 69785, 69786, 69787, 69788, 69789, 69790, 69791, 69792, 69793, 69794, 69795, 69796, 69797, 69798, 69799, 69800, 69801, 69802, 69803, 69804, 69805, 69806, 69807, 69808, 69809, 69810, 69811, 69812, 69813, 69814, 69815, 69816, 69817, 69818, 69819, 69820, 69821, 69822, 69823, 69824, 69825, 69826, 69827, 69828, 69829, 69830, 69831, 69832, 69833, 69834, 69835, 69836, 69837, 69838, 69839, 69840, 69841, 69842, 69843, 69844, 69845, 69846, 69847, 69848, 69849, 69850, 69851, 69852, 69853, 69854, 69855, 69856, 69857, 69858, 69859, 69860, 69861, 69862, 69863, 69864, 69865, 69866, 69867, 69868, 69869, 69870, 69871, 69872, 69873, 69874, 69875, 69876, 69877, 69878, 69879, 69880, 69881, 69882, 69883, 69884, 69885, 69886, 69887, 69888, 69889, 69890, 69891, 69892, 69893, 69894, 69895, 69896, 69897, 69898, 69899, 69900, 69901, 69902, 69903, 69904, 69905, 69906, 69907, 69908, 69909, 69910, 69911, 69912, 69913, 69914, 69915, 69916, 69917, 69918, 69919, 69920, 69921, 69922, 69923, 69924, 69925, 69926, 69927, 69928, 69929, 69930, 69931, 69932, 69933, 69934, 69935, 69936, 69937, 69938, 69939, 69940, 69941, 69942, 69943, 69944, 69945, 69946, 69947, 69948, 69949, 69950, 69951, 69952, 69953, 69954, 69955, 69956, 69957, 69958, 69959, 69960, 69961, 69962, 69963, 69964, 69965, 69966, 69967, 69968, 69969, 69970, 69971, 69972, 69973, 69974, 69975, 69976, 69977, 69978, 69979, 69980, 69981, 69982, 69983, 69984, 69985, 69986, 69987, 69988, 69989, 69990, 69991, 69992, 69993, 69994, 69995, 69996, 69997, 69998, 69999, 70000, 70001, 70002, 70003, 70004, 70005, 70006) /*application:test,correlation_id:473469f637887d710d54cf5c0b4beebf,db_config_name:main,line:/app/models/batched_git_ref_updates/deletion.rb:47:in `block in mark_records_processed'*/
Update on p_batched_git_ref_updates_deletions  (cost=0.42..1795.33 rows=543 width=77) (actual time=3.719..3.719 rows=0 loops=1)
  Update on p_batched_git_ref_updates_deletions_1
  Buffers: shared hit=15092 dirtied=39 written=8
  ->  Index Scan using p_batched_git_ref_updates_deletions_1_pkey on p_batched_git_ref_updates_deletions_1  (cost=0.42..1795.33 rows=543 width=77) (actual time=0.021..0.615 rows=1000 loops=1)
        Index Cond: ((id = ANY ('{69007,69008,69009,69010,69011,69012,69013,69014,69015,69016,69017,69018,69019,69020,69021,69022,69023,69024,69025,69026,69027,69028,69029,69030,69031,69032,69033,69034,69035,69036,69037,69038,69039,69040,69041,69042,69043,69044,69045,69046,69047,69048,69049,69050,69051,69052,69053,69054,69055,69056,69057,69058,69059,69060,69061,69062,69063,69064,69065,69066,69067,69068,69069,69070,69071,69072,69073,69074,69075,69076,69077,69078,69079,69080,69081,69082,69083,69084,69085,69086,69087,69088,69089,69090,69091,69092,69093,69094,69095,69096,69097,69098,69099,69100,69101,69102,69103,69104,69105,69106,69107,69108,69109,69110,69111,69112,69113,69114,69115,69116,69117,69118,69119,69120,69121,69122,69123,69124,69125,69126,69127,69128,69129,69130,69131,69132,69133,69134,69135,69136,69137,69138,69139,69140,69141,69142,69143,69144,69145,69146,69147,69148,69149,69150,69151,69152,69153,69154,69155,69156,69157,69158,69159,69160,69161,69162,69163,69164,69165,69166,69167,69168,69169,69170,69171,69172,69173,69174,69175,69176,69177,69178,69179,69180,69181,69182,69183,69184,69185,69186,69187,69188,69189,69190,69191,69192,69193,69194,69195,69196,69197,69198,69199,69200,69201,69202,69203,69204,69205,69206,69207,69208,69209,69210,69211,69212,69213,69214,69215,69216,69217,69218,69219,69220,69221,69222,69223,69224,69225,69226,69227,69228,69229,69230,69231,69232,69233,69234,69235,69236,69237,69238,69239,69240,69241,69242,69243,69244,69245,69246,69247,69248,69249,69250,69251,69252,69253,69254,69255,69256,69257,69258,69259,69260,69261,69262,69263,69264,69265,69266,69267,69268,69269,69270,69271,69272,69273,69274,69275,69276,69277,69278,69279,69280,69281,69282,69283,69284,69285,69286,69287,69288,69289,69290,69291,69292,69293,69294,69295,69296,69297,69298,69299,69300,69301,69302,69303,69304,69305,69306,69307,69308,69309,69310,69311,69312,69313,69314,69315,69316,69317,69318,69319,69320,69321,69322,69323,69324,69325,69326,69327,69328,69329,69330,69331,69332,69333,69334,69335,69336,69337,69338,69339,69340,69341,69342,69343,69344,69345,69346,69347,69348,69349,69350,69351,69352,69353,69354,69355,69356,69357,69358,69359,69360,69361,69362,69363,69364,69365,69366,69367,69368,69369,69370,69371,69372,69373,69374,69375,69376,69377,69378,69379,69380,69381,69382,69383,69384,69385,69386,69387,69388,69389,69390,69391,69392,69393,69394,69395,69396,69397,69398,69399,69400,69401,69402,69403,69404,69405,69406,69407,69408,69409,69410,69411,69412,69413,69414,69415,69416,69417,69418,69419,69420,69421,69422,69423,69424,69425,69426,69427,69428,69429,69430,69431,69432,69433,69434,69435,69436,69437,69438,69439,69440,69441,69442,69443,69444,69445,69446,69447,69448,69449,69450,69451,69452,69453,69454,69455,69456,69457,69458,69459,69460,69461,69462,69463,69464,69465,69466,69467,69468,69469,69470,69471,69472,69473,69474,69475,69476,69477,69478,69479,69480,69481,69482,69483,69484,69485,69486,69487,69488,69489,69490,69491,69492,69493,69494,69495,69496,69497,69498,69499,69500,69501,69502,69503,69504,69505,69506,69507,69508,69509,69510,69511,69512,69513,69514,69515,69516,69517,69518,69519,69520,69521,69522,69523,69524,69525,69526,69527,69528,69529,69530,69531,69532,69533,69534,69535,69536,69537,69538,69539,69540,69541,69542,69543,69544,69545,69546,69547,69548,69549,69550,69551,69552,69553,69554,69555,69556,69557,69558,69559,69560,69561,69562,69563,69564,69565,69566,69567,69568,69569,69570,69571,69572,69573,69574,69575,69576,69577,69578,69579,69580,69581,69582,69583,69584,69585,69586,69587,69588,69589,69590,69591,69592,69593,69594,69595,69596,69597,69598,69599,69600,69601,69602,69603,69604,69605,69606,69607,69608,69609,69610,69611,69612,69613,69614,69615,69616,69617,69618,69619,69620,69621,69622,69623,69624,69625,69626,69627,69628,69629,69630,69631,69632,69633,69634,69635,69636,69637,69638,69639,69640,69641,69642,69643,69644,69645,69646,69647,69648,69649,69650,69651,69652,69653,69654,69655,69656,69657,69658,69659,69660,69661,69662,69663,69664,69665,69666,69667,69668,69669,69670,69671,69672,69673,69674,69675,69676,69677,69678,69679,69680,69681,69682,69683,69684,69685,69686,69687,69688,69689,69690,69691,69692,69693,69694,69695,69696,69697,69698,69699,69700,69701,69702,69703,69704,69705,69706,69707,69708,69709,69710,69711,69712,69713,69714,69715,69716,69717,69718,69719,69720,69721,69722,69723,69724,69725,69726,69727,69728,69729,69730,69731,69732,69733,69734,69735,69736,69737,69738,69739,69740,69741,69742,69743,69744,69745,69746,69747,69748,69749,69750,69751,69752,69753,69754,69755,69756,69757,69758,69759,69760,69761,69762,69763,69764,69765,69766,69767,69768,69769,69770,69771,69772,69773,69774,69775,69776,69777,69778,69779,69780,69781,69782,69783,69784,69785,69786,69787,69788,69789,69790,69791,69792,69793,69794,69795,69796,69797,69798,69799,69800,69801,69802,69803,69804,69805,69806,69807,69808,69809,69810,69811,69812,69813,69814,69815,69816,69817,69818,69819,69820,69821,69822,69823,69824,69825,69826,69827,69828,69829,69830,69831,69832,69833,69834,69835,69836,69837,69838,69839,69840,69841,69842,69843,69844,69845,69846,69847,69848,69849,69850,69851,69852,69853,69854,69855,69856,69857,69858,69859,69860,69861,69862,69863,69864,69865,69866,69867,69868,69869,69870,69871,69872,69873,69874,69875,69876,69877,69878,69879,69880,69881,69882,69883,69884,69885,69886,69887,69888,69889,69890,69891,69892,69893,69894,69895,69896,69897,69898,69899,69900,69901,69902,69903,69904,69905,69906,69907,69908,69909,69910,69911,69912,69913,69914,69915,69916,69917,69918,69919,69920,69921,69922,69923,69924,69925,69926,69927,69928,69929,69930,69931,69932,69933,69934,69935,69936,69937,69938,69939,69940,69941,69942,69943,69944,69945,69946,69947,69948,69949,69950,69951,69952,69953,69954,69955,69956,69957,69958,69959,69960,69961,69962,69963,69964,69965,69966,69967,69968,69969,69970,69971,69972,69973,69974,69975,69976,69977,69978,69979,69980,69981,69982,69983,69984,69985,69986,69987,69988,69989,69990,69991,69992,69993,69994,69995,69996,69997,69998,69999,70000,70001,70002,70003,70004,70005,70006}'::bigint[])) AND (partition_id = 1))
        Filter: (status = 1)
        Buffers: shared hit=3015

CleanupSchedulerService

This one I benchmarked using similar data above and found it could process 10k records in under 2s.

This executes the following queries:

Loose index scan offset 100

 	Query Text: WITH RECURSIVE "loose_index_scan_cte" AS ((SELECT "p_batched_git_ref_updates_deletions"."project_id" FROM "p_batched_git_ref_updates_deletions" WHERE "p_batched_git_ref_updates_deletions"."status" = 1 AND "p_batched_git_ref_updates_deletions"."project_id" >= 0 ORDER BY "p_batched_git_ref_updates_deletions"."project_id" ASC LIMIT 1)
 	UNION
 	(SELECT (SELECT "p_batched_git_ref_updates_deletions"."project_id" FROM "p_batched_git_ref_updates_deletions" WHERE "p_batched_git_ref_updates_deletions"."status" = 1 AND "p_batched_git_ref_updates_deletions"."project_id" > "loose_index_scan_cte"."project_id" ORDER BY "p_batched_git_ref_updates_deletions"."project_id" ASC LIMIT 1) AS project_id FROM "loose_index_scan_cte")) SELECT "project_id" FROM "loose_index_scan_cte" AS "p_batched_git_ref_updates_deletions" WHERE "p_batched_git_ref_updates_deletions"."project_id" IS NOT NULL LIMIT 1 OFFSET 100 /*application:console,db_config_name:main,console_hostname:DylanGitLabMBP,console_username:dylangriffith,line:/app/models/concerns/each_batch.rb:136:in `block in distinct_each_batch'*/
 	Limit  (cost=51.05..51.07 rows=1 width=8) (actual time=0.946..0.947 rows=1 loops=1)
 	  Buffers: shared hit=317
 	  CTE loose_index_scan_cte
 	    ->  Recursive Union  (cost=0.42..49.03 rows=101 width=8) (actual time=0.021..0.916 rows=101 loops=1)
 	          Buffers: shared hit=317
 	          ->  Limit  (cost=0.42..0.44 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)
 	                Buffers: shared hit=4
 	                ->  Index Only Scan using p_batched_git_ref_updates_deletions_1_project_id_id_idx on p_batched_git_ref_updates_deletions_1 p_batched_git_ref_updates_deletions_2  (cost=0.42..2406.28 rows=101107 width=8) (actual time=0.017..0.018 rows=1 loops=1)
 	                      Index Cond: (project_id >= 0)
 	                      Heap Fetches: 0
 	                      Buffers: shared hit=4
 	          ->  WorkTable Scan on loose_index_scan_cte  (cost=0.00..4.66 rows=10 width=8) (actual time=0.008..0.009 rows=1 loops=100)
 	                Buffers: shared hit=313
 	                SubPlan 1
 	                  ->  Limit  (cost=0.42..0.45 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=100)
 	                        Buffers: shared hit=313
 	                        ->  Index Only Scan using p_batched_git_ref_updates_deletions_1_project_id_id_idx on p_batched_git_ref_updates_deletions_1  (cost=0.42..950.17 rows=33705 width=8) (actual time=0.008..0.008 rows=1 loops=100)
 	                              Index Cond: (project_id > loose_index_scan_cte.project_id)
 	                              Heap Fetches: 0
 	                              Buffers: shared hit=313
 	  ->  CTE Scan on loose_index_scan_cte p_batched_git_ref_updates_deletions  (cost=0.00..2.02 rows=100 width=8) (actual time=0.024..0.941 rows=101 loops=1)
 	        Filter: (project_id IS NOT NULL)
 	        Buffers: shared hit=317

Loose index scan finding another 100 rows

 	Query Text: WITH RECURSIVE "loose_index_scan_cte" AS ((SELECT "p_batched_git_ref_updates_deletions"."project_id" FROM "p_batched_git_ref_updates_deletions" WHERE "p_batched_git_ref_updates_deletions"."status" = 1 AND "p_batched_git_ref_updates_deletions"."project_id" >= 0 ORDER BY "p_batched_git_ref_updates_deletions"."project_id" ASC LIMIT 1)
 	UNION
 	(SELECT (SELECT "p_batched_git_ref_updates_deletions"."project_id" FROM "p_batched_git_ref_updates_deletions" WHERE "p_batched_git_ref_updates_deletions"."status" = 1 AND "p_batched_git_ref_updates_deletions"."project_id" < 100 AND "p_batched_git_ref_updates_deletions"."project_id" > "loose_index_scan_cte"."project_id" ORDER BY "p_batched_git_ref_updates_deletions"."project_id" ASC LIMIT 1) AS project_id FROM "loose_index_scan_cte")) SELECT "project_id" FROM "loose_index_scan_cte" AS "p_batched_git_ref_updates_deletions" WHERE "p_batched_git_ref_updates_deletions"."project_id" IS NOT NULL /*application:console,db_config_name:main,console_hostname:DylanGitLabMBP,console_username:dylangriffith,line:/lib/gitlab/batch_worker_context.rb:24:in `each_with_object'*/
 	CTE Scan on loose_index_scan_cte p_batched_git_ref_updates_deletions  (cost=56.58..58.60 rows=100 width=8) (actual time=0.015..0.658 rows=100 loops=1)
 	  Filter: (project_id IS NOT NULL)
 	  Rows Removed by Filter: 1
 	  Buffers: shared hit=317
 	  CTE loose_index_scan_cte
 	    ->  Recursive Union  (cost=0.42..56.58 rows=101 width=8) (actual time=0.014..0.638 rows=101 loops=1)
 	          Buffers: shared hit=317
 	          ->  Limit  (cost=0.42..0.44 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1)
 	                Buffers: shared hit=4
 	                ->  Index Only Scan using p_batched_git_ref_updates_deletions_1_project_id_id_idx on p_batched_git_ref_updates_deletions_1 p_batched_git_ref_updates_deletions_2  (cost=0.42..2406.28 rows=101107 width=8) (actual time=0.013..0.013 rows=1 loops=1)
 	                      Index Cond: (project_id >= 0)
 	                      Heap Fetches: 0
 	                      Buffers: shared hit=4
 	          ->  WorkTable Scan on loose_index_scan_cte  (cost=0.00..5.41 rows=10 width=8) (actual time=0.006..0.006 rows=1 loops=101)
 	                Buffers: shared hit=313
 	                SubPlan 1
 	                  ->  Limit  (cost=0.42..0.52 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=101)
 	                        Buffers: shared hit=313
 	                        ->  Index Only Scan using p_batched_git_ref_updates_deletions_1_project_id_id_idx on p_batched_git_ref_updates_deletions_1  (cost=0.42..52.89 rows=506 width=8) (actual time=0.005..0.005 rows=1 loops=101)
 	                              Index Cond: ((project_id < 100) AND (project_id > loose_index_scan_cte.project_id))
 	                              Heap Fetches: 0
 	                              Buffers: shared hit=313

How to set up and validate locally

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

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 #416967 (closed)