Background migration to deduplicate LFS object projects
What does this MR do and why?
This MR adds a batched background migration to remove lfs_objects_projects duplicates by lfs_project_id, project_id and repository_type.
We need to add a unique index once the migration finishes, including these columns, to ensure data consistency since the unique validation already exists at the model level.
Related to #199909
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
| Description | Screenshot |
|---|---|
Duplicates lfs_objects_projects - |
 |
After bundle exec rake db:migrate
|
 |
batched_background_migration_jobs table after migration (affected rows 5) |
 |
Migrations
bundle exec rake db:migrate
$ scripts/db_tasks db:migrate
Running: `bundle exec rake db:migrate`
main: == [advisory_lock_connection] object_id: 114680, pg_backend_pid: 128
main: == 20240708180350 QueueDeduplicateLfsObjectsProjects: migrating ===============
main: == 20240708180350 QueueDeduplicateLfsObjectsProjects: migrated (0.0981s) ======
main: == [advisory_lock_connection] object_id: 114680, pg_backend_pid: 128
bundle exec rake db:migrate:down VERSION=20240610021223
$ scripts/db_tasks db:migrate:down VERSION=20240708180350
Running: `bundle exec rake db:migrate:down VERSION=20240708180350`
main: == [advisory_lock_connection] object_id: 114820, pg_backend_pid: 108
main: == 20240708180350 QueueDeduplicateLfsObjectsProjects: reverting ===============
main: == 20240708180350 QueueDeduplicateLfsObjectsProjects: reverted (0.0598s) ======
main: == [advisory_lock_connection] object_id: 114820, pg_backend_pid: 108Database lab
Queries
1. Duplicated value finder
Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29672/commands/92122
WITH "distinct_values" AS MATERIALIZED (
SELECT DISTINCT
"lfs_objects_projects"."project_id",
"lfs_objects_projects"."lfs_object_id"
FROM
"lfs_objects_projects"
WHERE
"lfs_objects_projects"."id" >= 481386
AND "lfs_objects_projects"."id" < 491386
)
SELECT
"project_id",
"lfs_object_id"
FROM
"distinct_values" AS "lfs_objects_projects"
WHERE ((
SELECT
COUNT(*) AS count
FROM (
SELECT
1
FROM
lfs_objects_projects lop
WHERE (lop.project_id = lfs_objects_projects.project_id)
AND (lop.lfs_object_id = lfs_objects_projects.lfs_object_id)
LIMIT 2) cnt) = 2)2. Dedup query
Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29672/commands/92124
SQL query
DELETE FROM "lfs_objects_projects"
WHERE "lfs_objects_projects"."id" IN (
SELECT
"lfs_objects_projects"."id"
FROM
"lfs_objects_projects"
INNER JOIN (
SELECT
project_id,
lfs_object_id,
repository_type,
MAX(id) AS max_id
FROM
"lfs_objects_projects"
WHERE ((project_id, lfs_object_id) IN (
VALUES (1274100, 453498),
(1274100, 449105),
(1274100, 453443),
(1274100, 451187),
(1274100, 453369),
(1274100, 448313),
(1274100, 453183),
(1274100, 452924),
(1274100, 451198),
(1274100, 450582),
(1274100, 449689),
(1274100, 450195),
(1274100, 450356),
(1274100, 451507),
(1274100, 452453),
(1274100, 449330),
(1274100, 452355),
(1274100, 449916),
(1274100, 452423),
(1274100, 452380),
(1274100, 452404),
(1274100, 450576),
(1274100, 453311),
(1274100, 452393),
(1274100, 452381),
(1274100, 453009),
(1274100, 453169),
(1274100, 450557),
(1274100, 450072),
(1274100, 450706),
(1274100, 450466),
(1274100, 452546),
(1274100, 450501),
(1274100, 449830),
(1274100, 449242),
(1274100, 452877),
(1274100, 453170),
(1274100, 450154),
(1274100, 448701),
(1274100, 448874),
(1274100, 448261),
(1274100, 448591),
(1274100, 450027),
(1274100, 453371),
(1274100, 452192),
(1274100, 450170),
(1274100, 448794),
(1274100, 449362),
(1274100, 450363),
(1274100, 450637),
(1274100, 448933),
(1274100, 448816),
(1274100, 448580),
(1274100, 451111),
(1274100, 448698),
(1274100, 453201),
(1274100, 448196),
(1274100, 448398),
(1274100, 449982),
(1274100, 452269),
(1274100, 450360),
(1274100, 452544),
(1274100, 452432),
(1274100, 451668),
(1274100, 448909),
(1274100, 453503),
(1274100, 453417),
(1274100, 451093),
(1274100, 452227),
(1274100, 449905),
(1274100, 453484),
(1274100, 449022),
(1274100, 448702),
(1274100, 449036),
(1274100, 453324),
(1274100, 453149),
(1274100, 451958),
(1274100, 449088),
(1274100, 448307),
(1274100, 449241),
(1274100, 450787),
(1274100, 451495),
(1274100, 450281),
(1274100, 449509),
(1274100, 448640),
(1274100, 449884),
(1274100, 450973),
(1274100, 450717),
(1274100, 450684),
(1274100, 452512),
(1274100, 450817),
(1274100, 450558),
(1274100, 452543),
(1274100, 449054),
(1274100, 448338),
(1274100, 450032),
(1274100, 451177),
(1274100, 451380),
(1274100, 451057),
(1274100, 451140),
(1274100, 449418),
(1274100, 449025),
(1274100, 451564),
(1274100, 452896),
(1274100, 451088),
(1274100, 451085),
(1274100, 453418),
(1274100, 451685),
(1274100, 448732),
(1274100, 449556),
(1274100, 448347),
(1274100, 449877),
(1274100, 453472),
(1274100, 450579),
(1274100, 453198),
(1274100, 452938),
(1274100, 452035),
(1274100, 450854),
(1274100, 449684),
(1274100, 451558),
(1274100, 448511),
(1274100, 452821),
(1274100, 448450),
(1274100, 449829),
(1274100, 453425),
(1274100, 453437),
(1274100, 450169),
(1274100, 448871),
(1274100, 448368),
(1274100, 449781),
(1274100, 449622),
(1274100, 450997),
(1274100, 451968),
(1274100, 452549),
(1274100, 448872),
(1274100, 453156),
(1274100, 452299),
(1274100, 452985),
(1274100, 453148),
(1274100, 449447),
(1274100, 450945),
(1274100, 450291),
(1274100, 453222),
(1274100, 450745),
(1274100, 453181),
(1274100, 449942),
(1274100, 451563),
(1274100, 450025),
(1274100, 453122),
(1274100, 449699),
(1274100, 452329),
(1274100, 451038),
(1274100, 449793),
(1274100, 450651),
(1274100, 451404),
(1274100, 452360),
(1274100, 453067),
(1274100, 448922),
(1274100, 449366),
(1274100, 453292),
(1274100, 448783),
(1274100, 450925),
(1274100, 449422),
(1274100, 452663),
(1274100, 452584),
(1274100, 452721),
(1274100, 449312),
(1274100, 452743),
(1274100, 451442),
(1274100, 450564),
(1274100, 449573),
(1274100, 452428),
(1274100, 451411),
(1274100, 453518),
(1274100, 452708),
(1274100, 452061),
(1274100, 453094),
(1274100, 451555),
(1274100, 451920),
(1274100, 449179),
(1274100, 453172),
(1274100, 451702),
(1274100, 448866),
(1274100, 451096),
(1274100, 450150),
(1274100, 452429),
(1274100, 449669),
(1274100, 449117),
(1274100, 452247),
(1274100, 449091),
(1274100, 448837),
(1274100, 452024),
(1274100, 449948),
(1274100, 450248),
(1274100, 449046),
(1274100, 451614),
(1274100, 450171),
(1274100, 451188),
(1274100, 448597),
(1274100, 449467),
(1274100, 453457),
(1274100, 449051),
(1274100, 452317),
(1274100, 453143),
(1274100, 452733),
(1274100, 451514),
(1274100, 453224),
(1274100, 453165),
(1274100, 451551),
(1274100, 450843),
(1274100, 448333),
(1274100, 451581),
(1274100, 449570),
(1274100, 452452),
(1274100, 449044),
(1274100, 453252),
(1274100, 449104),
(1274100, 449770),
(1274100, 452551),
(1274100, 449407),
(1274100, 452534),
(1274100, 451700),
(1274100, 450858),
(1274100, 450218),
(1274100, 450172),
(1274100, 450824),
(1274100, 450353),
(1274100, 450560),
(1274100, 450462),
(1274100, 448600),
(1274100, 452011),
(1274100, 452225),
(1274100, 448695),
(1274100, 452603),
(1274100, 453445),
(1274100, 449469),
(1274100, 450348),
(1274100, 451566),
(1274100, 451079),
(1274100, 450750),
(1274100, 448321),
(1274100, 452268),
(1274100, 450705),
(1274100, 451467),
(1274100, 452737),
(1274100, 452953),
(1274100, 452872),
(1274100, 452176),
(1274100, 452642),
(1274100, 450333),
(1274100, 451949),
(1274100, 448287),
(1274100, 450026),
(1274100, 451964),
(1274100, 452272),
(1274100, 452626),
(1274100, 452857),
(1274100, 450151),
(1274100, 450267),
(1274100, 449814),
(1274100, 448278),
(1274100, 451405),
(1274100, 450084),
(1274100, 452669),
(1274100, 448466),
(1274100, 448919),
(1274100, 449902),
(1274100, 450531),
(1274100, 452361),
(1274100, 452211),
(1274100, 450192),
(1274100, 453084),
(1274100, 449545),
(1274100, 449675),
(1274100, 449190),
(1274100, 453386),
(1274100, 448275),
(1274100, 453268),
(1274100, 453132),
(1274100, 451934),
(1274100, 449094),
(1274100, 449100),
(1274100, 452826),
(1274100, 450498),
(1274100, 448761),
(1274100, 449702),
(1274100, 449695),
(1274100, 450563),
(1274100, 451199),
(1274100, 450772),
(1274100, 450492),
(1274100, 448339),
(1274100, 453273),
(1274100, 448669),
(1274100, 449210),
(1274100, 449365),
(1274100, 450289),
(1274100, 451138),
(1274100, 450198),
(1274100, 449357),
(1274100, 448668),
(1274100, 448995),
(1274100, 452132),
(1274100, 448829),
(1274100, 449971),
(1274100, 453087),
(1274100, 449138),
(1274100, 448269),
(1274100, 452449),
(1274100, 451455),
(1274100, 450691),
(1274100, 452232),
(1274100, 449263),
(1274100, 450954),
(1274100, 453092),
(1274100, 448575),
(1274100, 452043),
(1274100, 452243),
(1274100, 453365),
(1274100, 448253),
(1274100, 451604),
(1274100, 451094),
(1274100, 451371),
(1274100, 448868),
(1274100, 453068),
(1274100, 450290),
(1274100, 448754),
(1274100, 448343),
(1274100, 452010),
(1274100, 449757),
(1274100, 453334),
(1274100, 448767),
(1274100, 450493),
(1274100, 451608),
(1274100, 448470),
(1274100, 452967),
(1274100, 453343),
(1274100, 452017),
(1274100, 448374),
(1274100, 450402),
(1274100, 449522),
(1274100, 452063),
(1274100, 449219),
(1274100, 451148),
(1274100, 449903),
(1274100, 451012),
(1274100, 448694),
(1274100, 449112),
(1274100, 449690),
(1274100, 451105),
(1274100, 449779),
(1274100, 452062),
(1274100, 450293),
(1274100, 449068),
(1274100, 452819),
(1274100, 451648),
(1274100, 450819),
(1274100, 452822),
(1274100, 453328),
(1274100, 452042),
(1274100, 452143),
(1274100, 451164),
(1274100, 449114),
(1274100, 449650),
(1274100, 450710),
(1274100, 448264),
(1274100, 449574),
(1274100, 452261),
(1274100, 449240),
(1274100, 451169),
(1274100, 449160),
(1274100, 450668),
(1274100, 449963),
(1274100, 452104),
(1274100, 449646),
(1274100, 449973),
(1274100, 448699),
(1274100, 449306),
(1274100, 453341),
(1274100, 448283),
(1274100, 450015),
(1274100, 448924),
(1274100, 450304),
(1274100, 448534),
(1274100, 448664),
(1274100, 449159),
(1274100, 452182),
(1274100, 450419),
(1274100, 452864),
(1274100, 453174),
(1274100, 448955),
(1274100, 452559),
(1274100, 452882),
(1274100, 450541),
(1274100, 453368),
(1274100, 449126),
(1274100, 449508),
(1274100, 450656),
(1274100, 448592),
(1274100, 449216),
(1274100, 453381),
(1274100, 449217),
(1274100, 448645),
(1274100, 453128),
(1274100, 452014),
(1274100, 451036),
(1274100, 453478),
(1274100, 450095),
(1274100, 449505),
(1274100, 451691),
(1274100, 451516),
(1274100, 453494),
(1274100, 453289),
(1274100, 450765),
(1274100, 452246),
(1274100, 450079),
(1274100, 450088),
(1274100, 451432),
(1274100, 450097),
(1274100, 448308),
(1274100, 452779),
(1274100, 452667),
(1274100, 451701),
(1274100, 449676),
(1274100, 452934),
(1274100, 450722),
(1274100, 450076),
(1274100, 450083),
(1274100, 448836),
(1274100, 451384),
(1274100, 449654),
(1274100, 453470),
(1274100, 450214),
(1274100, 450667),
(1274100, 449174),
(1274100, 450258),
(1274100, 449503),
(1274100, 453493),
(1274100, 451676),
(1274100, 448604),
(1274100, 450049),
(1274100, 450424),
(1274100, 451061),
(1274100, 450416),
(1274100, 452265),
(1274100, 452660),
(1274100, 451015),
(1274100, 448925),
(1274100, 450635),
(1274100, 448441),
(1274100, 453340),
(1274100, 450396),
(1274100, 453497),
(1274100, 452873),
(1274100, 450554),
(1274100, 448599),
(1274100, 450476),
(1274100, 448382),
(1274100, 448415),
(1274100, 450324),
(1274100, 452778),
(1274100, 452023),
(1274100, 449698),
(1274100, 452028),
(1274100, 450618),
(1274100, 448391),
(1274100, 451092),
(1274100, 452070),
(1274100, 448665),
(1274100, 449416),
(1274100, 449627),
(1274100, 453038),
(1274100, 449037),
(1274100, 452798),
(1274100, 452318),
(1274100, 449012),
(1274100, 452422),
(1274100, 453031),
(1274100, 449836),
(1274100, 452736),
(1274100, 449567),
(1274100, 448696),
(1274100, 449421),
(1274100, 449786),
(1274100, 452144),
(1274100, 451965),
(1274100, 450524),
(1274100, 449440),
(1274100, 452657),
(1274100, 453182),
(1274100, 448641),
(1274100, 450837),
(1274100, 451719),
(1274100, 448510),
(1274100, 451422),
(1274100, 449047),
(1274100, 451959),
(1274100, 449778),
(1274100, 449041),
(1274100, 449481),
(1274100, 452697),
(1274100, 450272),
(1274100, 452596),
(1274100, 453305),
(1274100, 448191),
(1274100, 449671),
(1274100, 449842),
(1274100, 451184),
(1274100, 452427),
(1274100, 449583),
(1274100, 452560),
(1274100, 452112),
(1274100, 451988),
(1274100, 449298),
(1274100, 451192),
(1274100, 452297),
(1274100, 450729),
(1274100, 451556),
(1274100, 448230),
(1274100, 453440),
(1274100, 448305),
(1274100, 448601),
(1274100, 452605),
(1274100, 448642),
(1274100, 452548),
(1274100, 451530),
(1274100, 449888),
(1274100, 452532),
(1274100, 452022),
(1274100, 451166),
(1274100, 452960),
(1274100, 451654),
(1274100, 450950),
(1274100, 448452),
(1274100, 450055),
(1274100, 451471),
(1274100, 452321),
(1274100, 448993),
(1274100, 452359),
(1274100, 449289),
(1274100, 449962),
(1274100, 451923),
(1274100, 453322),
(1274100, 449143),
(1274100, 452398),
(1274100, 451030),
(1274100, 453179),
(1274100, 450375),
(1274100, 448484),
(1274100, 449191),
(1274100, 452654),
(1274100, 453176),
(1274100, 450813),
(1274100, 451357),
(1274100, 449869),
(1274100, 452658),
(1274100, 451607),
(1274100, 452966),
(1274100, 450305),
(1274100, 448724),
(1274100, 448310),
(1274100, 452950),
(1274100, 450048),
(1274100, 448731),
(1274100, 452650),
(1274100, 453102),
(1274100, 448537),
(1274100, 451649),
(1274100, 450730),
(1274100, 452477),
(1274100, 451505),
(1274100, 448270),
(1274100, 449359),
(1274100, 450415),
(1274100, 449089),
(1274100, 450714),
(1274100, 449178),
(1274100, 451937),
(1274100, 450388),
(1274100, 451569),
(1274100, 453399),
(1274100, 452773),
(1274100, 450764),
(1274100, 451172),
(1274100, 450671),
(1274100, 450672),
(1274100, 449707),
(1274100, 450559),
(1274100, 453461),
(1274100, 453248),
(1274100, 448588),
(1274100, 453460),
(1274100, 450459),
(1274100, 453416),
(1274100, 451098),
(1274100, 451362),
(1274100, 448937),
(1274100, 451579),
(1274100, 449185),
(1274100, 452334),
(1274100, 450537),
(1274100, 449519),
(1274100, 452566),
(1274100, 450373),
(1274100, 453296),
(1274100, 448225),
(1274100, 448603),
(1274100, 450191),
(1274100, 448535),
(1274100, 448883),
(1274100, 450653),
(1274100, 452410),
(1274100, 451139),
(1274100, 448681),
(1274100, 453073),
(1274100, 451645),
(1274100, 448440),
(1274100, 449207),
(1274100, 450019),
(1274100, 452978),
(1274100, 453071),
(1274100, 452700),
(1274100, 448234),
(1274100, 450685),
(1274100, 452634),
(1274100, 449765),
(1274100, 452478),
(1274100, 449428),
(1274100, 451459),
(1274100, 448785),
(1274100, 451553),
(1274100, 453230),
(1274100, 451577),
(1274100, 451089),
(1274100, 453290),
(1274100, 452769),
(1274100, 450067),
(1274100, 453028),
(1274100, 448865),
(1274100, 452111),
(1274100, 453287),
(1274100, 449124),
(1274100, 452973),
(1274100, 450602),
(1274100, 450301),
(1274100, 451464),
(1274100, 452087),
(1274100, 452007),
(1274100, 449304),
(1274100, 451044),
(1274100, 451029),
(1274100, 450490),
(1274100, 448774),
(1274100, 450965),
(1274100, 451565),
(1274100, 448926),
(1274100, 450604),
(1274100, 452013),
(1274100, 450220),
(1274100, 450158),
(1274100, 453337),
(1274100, 451510)))
GROUP BY
project_id,
lfs_object_id,
repository_type
HAVING (COUNT(*) > 1)) AS duplicates ON lfs_objects_projects.project_id = duplicates.project_id
AND lfs_objects_projects.lfs_object_id = duplicates.lfs_object_id
AND lfs_objects_projects.repository_type = duplicates.repository_type
WHERE
NOT (lfs_objects_projects.id = duplicates.max_id))Old approach with JOIN query
1. Join query
Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29416/commands/91438
EXPLAIN SELECT
"lfs_objects_projects".*
FROM
"lfs_objects_projects"
INNER JOIN (
SELECT
lfs_object_id,
project_id,
repository_type,
MAX(id) AS max_id
FROM
"lfs_objects_projects"
WHERE
"lfs_objects_projects"."project_id" IN (16, 20, 72, 1013, 2652, 2867, 4449, 5743, 5818, 7264, 7764, 9360, 13083, 13764, 13767, 16648, 18497, 18498, 18499, 18500, 18501, 18502, 18503, 18504, 18505, 18506, 18507, 18508, 18509, 18510, 18511, 18512, 18513, 18514, 18515, 18516, 18517, 18518, 18519, 18520, 18521, 18522, 18523, 18524, 18525, 18526, 18527, 18528, 18529, 18530, 18532, 18533, 18534, 18535, 18536, 18537, 18538, 18539, 18540, 18541, 18542, 18543, 18544, 18545, 18546, 18547, 18548, 18549, 18550, 18551, 20699, 23403, 29286, 32401, 32806, 33463, 33475, 36568, 36569, 36571, 38069, 39707, 40264, 63904, 78283, 78285, 78286, 95281, 146313, 163275, 163396, 163398, 309823, 353096, 387896, 691639, 695082, 952703, 1065383, 1118812, 1875244, 2206408, 2230440, 2255957, 2337675, 2751917, 2751922, 3333912, 3334373, 3334381, 3344948, 3800434, 4354685, 4354686, 4354688, 4354689, 4354691, 4409640, 4441689, 4542834, 4549417, 5630882, 5630887, 6118128, 6530427, 7434094, 9424406, 10415674, 11026233, 11026418, 11026537, 15059472, 16382505, 17566686, 17631786, 17632366, 17845429, 17930014, 21504640, 21531594, 22646620, 23285169, 29270005, 31279336, 41404100, 56337667, 56337723, 126696, 306410, 306426, 306446, 307034, 307893, 308028, 308089, 308097, 309623, 310621, 339289, 1, 297, 13787, 13802, 20458, 31759, 163475, 478692, 581461, 592104, 881609, 897750, 958540, 976977, 4152332, 4189054, 4206070, 5465839, 8120453, 8514576, 8577489, 8594287, 19244005, 21861275, 24299934, 599597, 730326, 730328, 744732, 745025, 748274, 755576, 757516, 1572597, 1572749, 2303288, 3375587, 20067313, 12, 2222936, 17844770, 17844796, 21251551, 427683, 427691, 464552, 551152, 580687, 662460, 680750, 773595, 994459, 1384129, 2137286, 3517087, 4252049, 4834649, 4977479, 5030944, 5216190, 5356625, 6008489, 7991166, 8728867, 8899630, 9164879, 13098716, 13098717, 16654078, 44835, 1446864, 2207032, 2825429, 2898689, 5803973, 6110635, 3, 4809264, 5028005, 23657708, 32450472, 324, 325, 19, 15, 25, 32, 33, 860995, 1235357, 1617647, 1647970, 2036871, 2368403, 7163302, 7372856, 8196479, 10635432, 10740940, 10775635, 11265703, 11923292, 12609050, 17314228, 17496513, 19350196, 24125594, 30710980, 11, 8941946, 19353091, 19353189, 41924938, 2168985, 7757775, 7758099, 7758104, 7758107, 7758108, 7758109, 7758110, 7925235, 8076263, 8700624, 10573981, 15705061, 14, 44920238, 56044502, 13, 1615389, 2324884, 22, 430, 83744, 1779106, 1807910, 1820646, 1888938, 1888940, 1938817, 5141145, 7110480, 7912628, 10701829, 11046052, 12467909, 19058014, 19992269, 22009461, 23336966, 8, 10832194, 5759482, 10, 1023513, 1023520, 4314342, 4751853, 5092858, 8053697, 8840060, 9480350, 9503413, 20884, 7, 11629851, 1191, 1365, 1844, 1850, 2732, 7667, 17744, 22985, 30776, 36743, 39984, 45169, 99945, 169765, 173142, 174024, 182309, 192795, 198120, 200149, 200980, 201378, 205896, 245362, 270497, 280425, 281857, 282144, 282211, 305463, 332909, 465390, 484997, 505199, 629060, 639908, 645380, 651742, 841782, 899615, 934896, 1265999, 1409446, 1409902, 1612960, 2063685, 2682421, 3128568, 3625877, 3662568, 3684185, 3787208, 3828396, 3991945, 4053197, 4359271, 4467622, 4492386, 4737966, 4899189, 4949400, 5064907, 5109694, 5191294, 5382660, 5899899, 6335481, 6374580, 6374588, 6374596, 7628274, 7686095, 7745145, 8223987, 8259605, 9655255, 10702458, 11060863, 11376680, 11392672, 11569466, 11569554, 13780298, 13831684, 14231319, 14574604, 15113443, 15126989, 16088759, 16152857, 16607739, 17373998, 17771506, 18889967, 18897919, 19047934, 20095954, 21005471, 21878261, 23582114, 24528529, 25651766, 25683483, 26735010, 26770092, 26888837, 26906858, 27996746, 31279611, 34770409, 38256317, 44378497, 44940243, 48336847, 53027300, 24, 246, 298, 29, 31, 3015276, 3119046, 3935880, 36800235, 36827361, 34, 35, 908052, 908053, 908054, 908055, 908056, 908057, 908058, 908059, 908060, 908061, 908062, 908063, 908064, 908065, 908069, 908070, 908072, 908074, 908075, 908076, 908077, 908079, 908080, 908081, 908082, 908083, 908084, 908085, 908086, 38, 37, 58525632, 58569926, 58682239, 39, 7045398, 14550145, 14748140, 14748180, 14748199, 18199014, 833935, 833936, 833943, 833944, 833945, 833946)
GROUP BY
lfs_object_id,
project_id,
repository_type
HAVING (COUNT(*) > 1)) AS duplicates ON lfs_objects_projects.project_id = duplicates.project_id
AND lfs_objects_projects.lfs_object_id = duplicates.lfs_object_id
AND lfs_objects_projects.repository_type = duplicates.repository_type
WHERE
NOT (lfs_objects_projects.id = duplicates.max_id);2. Delete all
Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29416/commands/91439
EXPLAIN DELETE FROM "lfs_objects_projects"
WHERE "lfs_objects_projects"."id" IN (
SELECT
"lfs_objects_projects"."id"
FROM
"lfs_objects_projects"
INNER JOIN (
SELECT
lfs_object_id,
project_id,
repository_type,
MAX(id) AS max_id
FROM
"lfs_objects_projects"
WHERE
"lfs_objects_projects"."project_id" IN (16, 20, 72, 1013, 2652, 2867, 4449, 5743, 5818, 7264, 7764, 9360, 13083, 13764, 13767, 16648, 18497, 18498, 18499, 18500, 18501, 18502, 18503, 18504, 18505, 18506, 18507, 18508, 18509, 18510, 18511, 18512, 18513, 18514, 18515, 18516, 18517, 18518, 18519, 18520, 18521, 18522, 18523, 18524, 18525, 18526, 18527, 18528, 18529, 18530, 18532, 18533, 18534, 18535, 18536, 18537, 18538, 18539, 18540, 18541, 18542, 18543, 18544, 18545, 18546, 18547, 18548, 18549, 18550, 18551, 20699, 23403, 29286, 32401, 32806, 33463, 33475, 36568, 36569, 36571, 38069, 39707, 40264, 63904, 78283, 78285, 78286, 95281, 146313, 163275, 163396, 163398, 309823, 353096, 387896, 691639, 695082, 952703, 1065383, 1118812, 1875244, 2206408, 2230440, 2255957, 2337675, 2751917, 2751922, 3333912, 3334373, 3334381, 3344948, 3800434, 4354685, 4354686, 4354688, 4354689, 4354691, 4409640, 4441689, 4542834, 4549417, 5630882, 5630887, 6118128, 6530427, 7434094, 9424406, 10415674, 11026233, 11026418, 11026537, 15059472, 16382505, 17566686, 17631786, 17632366, 17845429, 17930014, 21504640, 21531594, 22646620, 23285169, 29270005, 31279336, 41404100, 56337667, 56337723, 126696, 306410, 306426, 306446, 307034, 307893, 308028, 308089, 308097, 309623, 310621, 339289, 1, 297, 13787, 13802, 20458, 31759, 163475, 478692, 581461, 592104, 881609, 897750, 958540, 976977, 4152332, 4189054, 4206070, 5465839, 8120453, 8514576, 8577489, 8594287, 19244005, 21861275, 24299934, 599597, 730326, 730328, 744732, 745025, 748274, 755576, 757516, 1572597, 1572749, 2303288, 3375587, 20067313, 12, 2222936, 17844770, 17844796, 21251551, 427683, 427691, 464552, 551152, 580687, 662460, 680750, 773595, 994459, 1384129, 2137286, 3517087, 4252049, 4834649, 4977479, 5030944, 5216190, 5356625, 6008489, 7991166, 8728867, 8899630, 9164879, 13098716, 13098717, 16654078, 44835, 1446864, 2207032, 2825429, 2898689, 5803973, 6110635, 3, 4809264, 5028005, 23657708, 32450472, 324, 325, 19, 15, 25, 32, 33, 860995, 1235357, 1617647, 1647970, 2036871, 2368403, 7163302, 7372856, 8196479, 10635432, 10740940, 10775635, 11265703, 11923292, 12609050, 17314228, 17496513, 19350196, 24125594, 30710980, 11, 8941946, 19353091, 19353189, 41924938, 2168985, 7757775, 7758099, 7758104, 7758107, 7758108, 7758109, 7758110, 7925235, 8076263, 8700624, 10573981, 15705061, 14, 44920238, 56044502, 13, 1615389, 2324884, 22, 430, 83744, 1779106, 1807910, 1820646, 1888938, 1888940, 1938817, 5141145, 7110480, 7912628, 10701829, 11046052, 12467909, 19058014, 19992269, 22009461, 23336966, 8, 10832194, 5759482, 10, 1023513, 1023520, 4314342, 4751853, 5092858, 8053697, 8840060, 9480350, 9503413, 20884, 7, 11629851, 1191, 1365, 1844, 1850, 2732, 7667, 17744, 22985, 30776, 36743, 39984, 45169, 99945, 169765, 173142, 174024, 182309, 192795, 198120, 200149, 200980, 201378, 205896, 245362, 270497, 280425, 281857, 282144, 282211, 305463, 332909, 465390, 484997, 505199, 629060, 639908, 645380, 651742, 841782, 899615, 934896, 1265999, 1409446, 1409902, 1612960, 2063685, 2682421, 3128568, 3625877, 3662568, 3684185, 3787208, 3828396, 3991945, 4053197, 4359271, 4467622, 4492386, 4737966, 4899189, 4949400, 5064907, 5109694, 5191294, 5382660, 5899899, 6335481, 6374580, 6374588, 6374596, 7628274, 7686095, 7745145, 8223987, 8259605, 9655255, 10702458, 11060863, 11376680, 11392672, 11569466, 11569554, 13780298, 13831684, 14231319, 14574604, 15113443, 15126989, 16088759, 16152857, 16607739, 17373998, 17771506, 18889967, 18897919, 19047934, 20095954, 21005471, 21878261, 23582114, 24528529, 25651766, 25683483, 26735010, 26770092, 26888837, 26906858, 27996746, 31279611, 34770409, 38256317, 44378497, 44940243, 48336847, 53027300, 24, 246, 298, 29, 31, 3015276, 3119046, 3935880, 36800235, 36827361, 34, 35, 908052, 908053, 908054, 908055, 908056, 908057, 908058, 908059, 908060, 908061, 908062, 908063, 908064, 908065, 908069, 908070, 908072, 908074, 908075, 908076, 908077, 908079, 908080, 908081, 908082, 908083, 908084, 908085, 908086, 38, 37, 58525632, 58569926, 58682239, 39, 7045398, 14550145, 14748140, 14748180, 14748199, 18199014, 833935, 833936, 833943, 833944, 833945, 833946)
GROUP BY
lfs_object_id,
project_id,
repository_type
HAVING (COUNT(*) > 1)) AS duplicates ON lfs_objects_projects.project_id = duplicates.project_id
AND lfs_objects_projects.lfs_object_id = duplicates.lfs_object_id
AND lfs_objects_projects.repository_type = duplicates.repository_type
WHERE
NOT (lfs_objects_projects.id = duplicates.max_id));Edited by Javiera Tapia