Bound database transactions of repository cleanup
What does this MR do and why?
Repository cleanup applies a BFG object map. Projects::CleanupService deletes the merge request diffs and note diff files that reference rewritten commits. It did this inside one database transaction per Gitaly batch. For a large cleanup that transaction can stay open for a long time, and long-running transactions are bad for database performance.
This MR, behind the bound_repository_cleanup_transactions feature flag (default off, per project actor), bounds the size of each transaction:
- Processes the rewritten commit SHAs of each Gitaly response in slices of 100 (
OLD_COMMIT_SHA_SLICE_SIZE). - For each slice, plucks the matching merge request diff IDs and calls
destroy_allon them in batches of 25 diffs (MERGE_REQUEST_DIFF_BATCH_SIZE). Each batch runs in oneMergeRequestDiff.transaction.destroy_allis needed so the model callbacks run. - Then plucks the matching note diff file IDs (
NoteDiffFile.referencing_sha, which looks the notes up bycommit_idfirst in a materialized CTE, see query 4) and usesdelete_allin batches of 1000 (NOTE_DIFF_FILE_BATCH_SIZE). Each batch is its own statement, and the Redis highlight cache is cleared per batch outside any long transaction.
While the flag is disabled, cleanup keeps its previous behavior: one transaction per Gitaly batch, with the previous note lookup (NoteDiffFile.legacy_referencing_sha).
Cleanup is safe to repeat. A job that is interrupted restarts from the start of the object map, and rows that were already deleted are simply not found again.
The concurrency limit and database health deferral of the worker were split out into Draft: Limit concurrency of repository cleanup ... (!266661), so this fix can ship while the right values are still being evaluated. Logging how many records a cleanup removes was split out into Log how many records repository cleanup removes (!269578 - merged).
Related to https://gitlab.com/gitlab-org/gitlab/-/work_items/634218+
How the batch sizes were chosen
The goal is that no transaction stays open for more than a short time, and that the lookup queries stay on their fast plans. The numbers below come from read-only experiments on a Database Lab thin clone, using public projects and real public commit SHAs. Only timings and plans were recorded. Thanks to @lma-git for reproducing the note lookup and the diff deletion on other projects, which changed the note lookup and the diff batch size.
| Constant | Value | Reason |
|---|---|---|
OLD_COMMIT_SHA_SLICE_SIZE |
100 | Bounds the IN (...) lists of the lookups. The diff lookup scales linearly with them. |
MERGE_REQUEST_DIFF_BATCH_SIZE |
25 | destroy_all deletes one diff per statement, and each delete cascades to the diff's files, so the cost of a batch follows its file rows. A diff has at most diff_max_files files (1000 by default), so a batch removes about 25,000 file rows at most. See query 3b for a batch of 25 diffs with many files. |
NOTE_DIFF_FILE_BATCH_SIZE |
1000 | Well under 1 s per statement, even cold. |
Finding merge request diffs for a slice of SHAs
This is the MergeRequestDiff.by_commit_sha query. It goes through the partitioned merge_request_diff_commits path with project_id partition pruning. The plan is an index scan on merge_request_commits_metadata (project_id, sha), then an index on merge_request_diff_commits, then an index-only scan on merge_request_diffs. Times are warm.
| SHAs | Diffs found | Time |
|---|---|---|
| 100 | 210 | about 7 ms |
| 500 | 944 | about 20 ms |
| 1000 | 1853 | about 42 ms |
The time is linear and the plan is stable. The tooling could not send lists above about 1000 SHAs.
Finding note diff files for a slice of SHAs
This is NoteDiffFile.referencing_sha. It used to join notes on commit_id IN (...) and project_id. The planner then chooses between an index scan on index_notes_on_commit_id and a BitmapAnd with index_notes_on_project_id_and_noteable_type. The BitmapAnd reads the project's whole range of that index, so its cost follows the number of notes of the project, not the number of SHAs. On gitlab-org/gitlab (tens of millions of notes) the planner used the BitmapAnd from about 130 SHAs. On smaller projects it uses it even at 100 SHAs, and slicing runs the lookup about 110 times per Gitaly response.
The lookup now finds the notes by commit_id in a materialized CTE and filters on project and type afterwards, so its cost follows the notes on those SHAs. First (cold) runs at 100 SHAs:
| Project | Notes | Before | After |
|---|---|---|---|
gitlab-org/gitlab-foss |
1.7M | 6.0 s, 6,844 buffers | 0.55 s, 508 buffers |
gitlab-org/gitlab |
tens of millions | 0.30 s, 551 buffers | 0.45 s, 551 buffers |
The two gitlab-org/gitlab runs read the same pages and differ only in how cold the clone was.
Deleting merge request diffs
destroy_all runs one DELETE ... WHERE id = ? per diff in the batch's transaction. Each one cascades to merge_request_diff_files and merge_request_diff_details, sets merge_requests.latest_merge_request_diff_id to NULL where it points to the diff, and fires the loose foreign key trigger. So the cost of a batch follows the number of file rows of its diffs. @lma-git measured that statement sequence (rolled back) for batches of 100 diffs:
| Diffs | merge_request_diff_files rows |
Cold | Warm |
|---|---|---|---|
| Typical | 123 to 189 | 0.6 to 1.2 s | about 52 ms |
| 100+ files each | 45,637 | 3.9 s | about 124 ms |
A diff has at most diff_max_files files, 1,000 by default, so a batch of 25 diffs removes about 25,000 file rows at most. That is about half of the 45,637-row measurement, and query 3b measures batches of 25 diffs with many files. Most batches are far smaller. A smaller batch only costs more commits, because the per-diff statements and triggers run the same number of times.
Deleting note diff files
DELETE ... WHERE id IN (batch). There are no child rows.
| Rows | Warm | Cold |
|---|---|---|
| 1000 | about 51 ms | about 0.54 s |
| 5000 | about 88 ms | about 1.3 s |
1000 per batch keeps each statement far below a second.
Database review: queries and plans
These are the statements the flag-on path runs. Queries 1 and 4 run once per slice of 100 SHAs, query 2 once per batch of 25 diffs, query 3 once per diff in the batch, and query 5 once per batch of 1000 note diff files. With the flag off the old statements run unchanged, including the previous form of query 4.
The values are real: project 278964 (gitlab-org/gitlab), public commit SHAs, and merge request diff IDs from the public merge request versions API. The plans are the first runs on the Database Lab clone, so the I/O numbers are cold. Where warm numbers are quoted they come from repeated runs on the same clone. Query 5 gets its IDs from a subselect, because the API doesn't expose note diff file IDs.
1. Find the merge request diff IDs for a slice of 100 SHAs
Raw SQL
SELECT "merge_request_diffs"."id" FROM ((SELECT "merge_request_diffs".* FROM "merge_request_diffs" INNER JOIN merge_request_diff_commits ON merge_request_diffs.id = merge_request_diff_commits.merge_request_diff_id INNER JOIN merge_request_commits_metadata ON merge_request_commits_metadata.id = merge_request_diff_commits.merge_request_commits_metadata_id AND merge_request_commits_metadata.project_id IN (278964) WHERE "merge_request_commits_metadata"."sha" IN ('\x57be57423c67cb0c376e8d32bc5b840bfbb752b8', '\x8efd8b59f207394bbe4bc484bd9d8d01b5470846', '\xba51af758db8d85bfb4fdf82b70691bc1ebffee7', '\xd84eaad3e6ccb756275e234e2b8c5acea74ac206', '\xe33010610a4811e5c1f73276294a5d343e2b8402', '\xd78e1e52993f9bd7bd86a0bf30ab008909cbe2cc', '\x8a6605d39d30d9a9a548479badf12cf16c225227', '\x366163d0ac27db52b243e806fe204fc2c0563f90', '\x3f45ce3e1bf0541e0714f187d4bfc976b3334bd5', '\x2d5fa4b4bd2f7772dcba99c099bea9c2ff28bcbb', '\x9e86308287963b27b26a0588ab33f0cf292ad797', '\x567601176dfd5b798e3f9dad41331e8aa76c5a1e', '\xde15e640f5aba976293b4e49bc93bc8c6f4bb811', '\x3e08dee967847ee5ce70c2129cfeff4e409a6209', '\xe61c4e6918ac9a8017a719e65f542bb7c905726a', '\x4eaf915d8b75ee35a9aca0fcfd412d991f9b8bc9', '\x78a5fb80b09260f21ed6b628f5d59f79f15c3e6c', '\x740f887e6c9411659ef9ee81e8c0755f789835c6', '\x6a70e653e1bf2873b23d0cc084f544c1c7ee0c6e', '\x90551fac956c16c1e8b23c0a547813eee5de720d', '\xfb92743cab3d29e1faf3f70b56219785e7305a19', '\x309ebc7cad4b7f436e023d31876d2ccb973d253a', '\xe5d29c48dd380ab8ae02d39f28933c959e15a937', '\x73b11879e42789249047acf10c3b32985f7d32c7', '\x5deca60e612a59323d5c35c0c5c77a9cfb3590df', '\x56ab2e7dbb7aba15a03b7a3ccf30db3f66cad9d4', '\x0c2a6c1d8fbcf5d9b9c2973a1d9f2f5ec4ef5029', '\x46e278531950ec47213bfd66c42e1a9c4b0e19bc', '\x311fea1fc538c493e1f1fc92232024455d7c4388', '\x70373c82b747ce4ebf3f673c49aba0ff01ceeb16', '\x5ea25fc06846b6626db9478849ae3bb9b97a098e', '\xdd4ddf4b1f3319d71adc439222900d462dfb40e8', '\xc712074b53950a7e885173a75cd5790225530bde', '\x4fb3f184500d79cd3e7e791cd0c7e803bf054880', '\xb89b5b2a730f7f3ca969fd0371ec9cc00224934a', '\xa46815e1aeb39cfc367c1249db7c7c216b86e7f2', '\x1f697afd16acdf4aff93ae77a858f6fc6fa46856', '\xc155e7ceaa990444b63070463ac81fbab5767849', '\xb5c5412a5822fd9b76c1e501aaec3771bc1ca1cd', '\xc1c39432eb92d51b4822f85254436cb0dbd8eae4', '\x8683782a6e21be3db3d55b7bc5a7d159146e7af7', '\x4e0fa9397a5341429580a0f6cca1e7d03debc7e8', '\x6536479ddc31d65455411aac775fecf65ca1d658', '\xf99d7bdac1d0b8953e988bdffedbd0935f3683ff', '\xeb2ce61d91427adfaa06a2f38c182c17c3bf8ce0', '\xae636f5e2a8bd349f5c034e9dcb212efc924a667', '\x8ea0fa3451c68622a7bea7620245cf02aa941e0f', '\x76d172652df3c5115c516548369f7b88f3f0fe2c', '\x15a24d7030c8afb2b9ff504cc228c291ed524f6b', '\xb7da0b8fc04351fab4f12e2494411cb1acfa8865', '\x5bfd21ef6e2ae6cf94ec683c46e46bf323e7a446', '\xeb72b466b38cfe22412441364d2830db3d7a096f', '\x814756272de7c0b78cad78182dbca88d94b9efc9', '\x5e03b86ccb07eb9d4db699888b2997a681731347', '\x3c3b07d28e49c087d7a00922c8e8a83aef5a63e5', '\xbc439919e90e2b0a9852e6825e3ffd3de5c5f4ec', '\xb988504047d7704e54d05ed4093213ae2d8e1e6e', '\xbca128ead24fbcf0ce4c9948b937cfd525638c01', '\xd89153f6e5223dd1ab3dce2c38e9cb49e5615a1c', '\xf5a963c67a60bed1fa2094c211fa72162642eeee', '\xff7f05dbd4bb69e998b7812ccfeb96bc7f77ecc2', '\x89f89ed05b911d5975fde1847377cc60e89900cf', '\xa1ad01f429f689ed8b5849b4c0f2e7823eb7eea4', '\x55896a2f3ea510453bbbb689aeda80a3ef9e5206', '\x317c0b6aca2f47270bcccecda83529015713e46c', '\x5135cacfa9b0efe43e8b1ad9428fb42047259c6c', '\xb27cf9badf60a19fc81e0522f5fa82c23e1a0dc9', '\x22cef05b5df544be316c1efd29d05e71264a90c7', '\x3460df0b31878cf4ec60751019462561a8d9942c', '\xe177142b9b81667110a1d5d092033129fd836578', '\xeae3467bbce1ca63a788daa2fa9a3659d2c080fd', '\x0c7eac3011d2b4f075cac34433822fdf57785634', '\xec7e0d8d39146bbdd4385ba133f1c11dbb6e2c0f', '\xbcafd0ba9ccaa641c3b10c280b58ffc0cfb60862', '\x8c23430be675a303242c3711ebce818782171ae2', '\x283fa124a13d9675de4d10dbfdcfad7c8d0fdc0b', '\xe6f596760d1c999976fd930cc2eb191a0fd0dc2f', '\xabeccf0939d43785be539e62fb07ae633e6824ea', '\xa7c25c5c036ce5ce4f12d30e4291ed8ab9e4e220', '\x782f5699ba9b79a9c11173bdf31dbe297aa2078a', '\x201cde9681a0a7cf0f907910529896bbdf1dcb37', '\xe3625f425c5e8ed0ae395ee51733620cf8f52d3d', '\x6016fd683902a43e0dcdf6f6100901288be22b2c', '\x9248d15b05cb49a2c5808a8b5fc977d84eee9948', '\x6f3946defb918eb41760d14ee1d3ae137349c9cb', '\x5fefe721bcc47879978950484c987e0a15b6e8f5', '\x88a53dff9c1cdd4dbc9328838ba2056b89c3fc6f', '\xa017a39fdbaca6848482b1c32340e1d2bfcb6b48', '\x8e19e0732930e020ccf2dae1473511cb0f18a136', '\x6e9d6a6e153b3940a74ee4c0158d8c59b1be88bf', '\x06acc2d55d4f764f10a856257d3e4ced9ec8f377', '\x93fa0a5d8676077b0d18231e14fa7c52d43d6fb2', '\xeea60098544fd9156efbd57e12bf3cf8ca6ca638', '\x614d5901b6882d272f7d2bd638ac44ef898bace6', '\x863a320704eb6850e5d476d457dda4cf4db1af3a', '\x4fb95fa4aba2a53bb9110af22bcc2688ed72e8a4', '\xdbccf483b0a265e146e2d6f5e8f5345b37f19989', '\x9deed13d272bc45b7595fcda8310ef4314f7bbe9', '\x95e28d8cd522ed8008a3fdd09257591069849bf4', '\x335994661c1641ff200e2deb6792ba0e28c4cd12') AND "merge_request_diff_commits"."project_id" = 278964)) merge_request_diffs WHERE "merge_request_diffs"."project_id" = 278964Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/80865/commands/188717
Plan (long lists shortened)
Gather (cost=1001.72..4410.82 rows=1 width=8) (actual time=15.143..435.309 rows=292 loops=1)
Workers Planned: 1, Workers Launched: 1
Buffers: shared hit=2848 read=1056 dirtied=22
-> Nested Loop (actual time=12.434..428.632 rows=146 loops=2)
-> Nested Loop (actual time=7.786..378.338 rows=146 loops=2)
-> Parallel Index Scan using merge_request_commits_metadata_1_project_id_sha_idx on merge_request_commits_metadata_1 (actual time=3.050..96.963 rows=50 loops=2)
Index Cond: ((project_id = 278964) AND (sha = ANY ('{"\x57be57...","\x8efd8b...", ... 100 SHAs}'::bytea[])))
Buffers: shared hit=250 read=226
-> Index Scan using merge_request_diff_commits_b5_merge_request_commits_metadat_idx on merge_request_diff_commits_1 (actual time=0.559..5.625 rows=3 loops=100)
Index Cond: (merge_request_commits_metadata_id = merge_request_commits_metadata.id)
Filter: (project_id = 278964)
-> Index Only Scan using index_merge_request_diffs_on_project_id_and_id on merge_request_diffs (actual time=0.343..0.343 rows=1 loops=292)
Index Cond: ((project_id = 278964) AND (id = merge_request_diff_commits.merge_request_diff_id))
Heap Fetches: 440
Time: 442.246 ms (planning: 6.834 ms, execution: 435.412 ms, I/O read: 842.172 ms)
Buffers: hits 2848, reads 1056, dirtied 222. Load a batch of 25 diffs for destroy_all
Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84377/commands/191576
Raw SQL
SELECT "merge_request_diffs".* FROM "merge_request_diffs" WHERE "merge_request_diffs"."id" IN (1879614270, 1879522389, 1879558811, 1888939374, 1888936248, 1879565622, 1879569170, 1896162860, 1896049312, 1886562902, 1879610107, 1879604884, 1879601592, 1879592622, 1900831915, 1898000415, 1897994709, 1893320299, 1893306931, 1893297648, 1892857010, 1888871718, 1888822105, 1886605498, 1885479979)Plan (long lists shortened)
Index Scan using merge_request_diffs_pkey on public.merge_request_diffs (cost=0.58..76.13 rows=25 width=286) (actual time=10.154..75.642 rows=25 loops=1)
Index Cond: (merge_request_diffs.id = ANY ('{... 100 SHAs}'::bigint[]))
Buffers: shared hit=62 read=63
I/O Timings: read=75.414 write=0.000
Time: 79.792 ms (planning: 4.096 ms, I/O read: 75.414 ms)A primary key index scan returning 25 rows. This is the first, cold run. Buffers: 62 hit, 63 read.
3. Delete one diff (one statement per diff, in the transaction of its batch)
Raw SQL
DELETE FROM "merge_request_diffs" WHERE "merge_request_diffs"."id" = 1880023914Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/80865/commands/188722
Plan (long lists shortened)
Delete on merge_request_diffs (cost=0.58..3.59 rows=0 width=0) (actual time=6.507..6.507 rows=0 loops=1)
Buffers: shared hit=119 read=8 dirtied=2
-> Index Scan using merge_request_diffs_pkey on merge_request_diffs (actual time=1.466..1.468 rows=1 loops=1)
Index Cond: (id = 1880023914)
Trigger RI_ConstraintTrigger_a_35893 for constraint fk_06067f5644: time=16.932 calls=1
Trigger RI_ConstraintTrigger_a_45208 for constraint fk_rails_86f4d24ecd: time=9.105 calls=1
Trigger RI_ConstraintTrigger_a_808079492 for constraint fk_rails_6fff895059: time=12.859 calls=1
Trigger merge_request_diffs_loose_fk_trigger: time=11.866 calls=1
Time: 61.024 ms (planning: 3.650 ms, execution: 57.374 ms, I/O read: 4.672 ms)3b. Delete a batch of 25 diffs with many files (worst case of query 3)
Query 3 runs once per diff, and its cost follows the number of merge_request_diff_files rows the delete cascades to. This measures a whole batch of 25 non-latest diffs of gitlab-org/gitlab. It is one DELETE ... WHERE id IN (...) statement, not 25, so the loose foreign key trigger fires once instead of once per diff (about 12 ms each in query 3). The time of the lookup of the 25 IDs in the picked CTE is not part of the cost of the delete, so the trigger times are the numbers to read. The runs were rolled back.
| Diffs | File rows | Cascade to files (fk_rails_6fff895059), 25 calls |
Other triggers | Plan |
|---|---|---|---|---|
| 100 to 1,000 files each | 3,760 | 326 ms (mostly warm) | about 55 ms | https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84389/commands/191652 |
| 700 to 1,000 files each | 19,734 | 2,604 ms (cold) | about 30 ms | https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84389/commands/191655 |
The file rows were counted with a count(*) over the same 25 diffs: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84389/commands/191651 and https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84389/commands/191654. A batch of 25 diffs at the default diff_max_files of 1,000 cannot go much past about 25,000 file rows.
Raw SQL (700 to 1,000 files each)
WITH picked AS MATERIALIZED (SELECT merge_request_diffs.id FROM merge_request_diffs WHERE merge_request_diffs.project_id = 278964 AND merge_request_diffs.id BETWEEN 1500000000 AND 1895000000 AND merge_request_diffs.files_count BETWEEN 700 AND 1000 AND NOT EXISTS (SELECT 1 FROM merge_requests WHERE merge_requests.latest_merge_request_diff_id = merge_request_diffs.id) ORDER BY merge_request_diffs.id LIMIT 25) DELETE FROM merge_request_diffs WHERE id IN (SELECT id FROM picked)Trigger times (long plan shortened)
100 to 1,000 files each, 3,760 file rows:
Trigger RI_ConstraintTrigger_a_35893 for constraint fk_06067f5644: time=1.521 calls=25
Trigger RI_ConstraintTrigger_a_45208 for constraint fk_rails_86f4d24ecd: time=25.301 calls=25
Trigger RI_ConstraintTrigger_a_808079492 for constraint fk_rails_6fff895059: time=326.386 calls=25
Trigger incident_19206_merge_request_diffs_trigger: time=1.834 calls=25
Trigger merge_request_diffs_loose_fk_trigger: time=25.853 calls=1700 to 1,000 files each, 19,734 file rows:
Trigger RI_ConstraintTrigger_a_35893 for constraint fk_06067f5644: time=9.998 calls=25
Trigger RI_ConstraintTrigger_a_45208 for constraint fk_rails_86f4d24ecd: time=7.082 calls=25
Trigger RI_ConstraintTrigger_a_808079492 for constraint fk_rails_6fff895059: time=2604.292 calls=25
Trigger incident_19206_merge_request_diffs_trigger: time=1.968 calls=25
Trigger merge_request_diffs_loose_fk_trigger: time=12.311 calls=14. Find the note diff file IDs for a slice of 100 SHAs (modified)
Code: https://gitlab.com/gitlab-org/gitlab/-/blob/c13a8930c90b/app/services/projects/cleanup_service.rb#L117 and https://gitlab.com/gitlab-org/gitlab/-/blob/c13a8930c90b/app/models/note_diff_file.rb
The notes are found by commit_id in a materialized CTE first, and filtered on project and type afterwards. See the measurements above.
Before (still used while the flag is disabled):
Raw SQL
SELECT "note_diff_files"."id" FROM "note_diff_files" INNER JOIN "notes" ON "notes"."id" = "note_diff_files"."diff_note_id" AND "notes"."type" = 'DiffNote' WHERE "notes"."project_id" = 278964 AND "notes"."commit_id" IN ('57be57423c67cb0c376e8d32bc5b840bfbb752b8', '8efd8b59f207394bbe4bc484bd9d8d01b5470846', 'ba51af758db8d85bfb4fdf82b70691bc1ebffee7', 'd84eaad3e6ccb756275e234e2b8c5acea74ac206', 'e33010610a4811e5c1f73276294a5d343e2b8402', 'd78e1e52993f9bd7bd86a0bf30ab008909cbe2cc', '8a6605d39d30d9a9a548479badf12cf16c225227', '366163d0ac27db52b243e806fe204fc2c0563f90', '3f45ce3e1bf0541e0714f187d4bfc976b3334bd5', '2d5fa4b4bd2f7772dcba99c099bea9c2ff28bcbb', '9e86308287963b27b26a0588ab33f0cf292ad797', '567601176dfd5b798e3f9dad41331e8aa76c5a1e', 'de15e640f5aba976293b4e49bc93bc8c6f4bb811', '3e08dee967847ee5ce70c2129cfeff4e409a6209', 'e61c4e6918ac9a8017a719e65f542bb7c905726a', '4eaf915d8b75ee35a9aca0fcfd412d991f9b8bc9', '78a5fb80b09260f21ed6b628f5d59f79f15c3e6c', '740f887e6c9411659ef9ee81e8c0755f789835c6', '6a70e653e1bf2873b23d0cc084f544c1c7ee0c6e', '90551fac956c16c1e8b23c0a547813eee5de720d', 'fb92743cab3d29e1faf3f70b56219785e7305a19', '309ebc7cad4b7f436e023d31876d2ccb973d253a', 'e5d29c48dd380ab8ae02d39f28933c959e15a937', '73b11879e42789249047acf10c3b32985f7d32c7', '5deca60e612a59323d5c35c0c5c77a9cfb3590df', '56ab2e7dbb7aba15a03b7a3ccf30db3f66cad9d4', '0c2a6c1d8fbcf5d9b9c2973a1d9f2f5ec4ef5029', '46e278531950ec47213bfd66c42e1a9c4b0e19bc', '311fea1fc538c493e1f1fc92232024455d7c4388', '70373c82b747ce4ebf3f673c49aba0ff01ceeb16', '5ea25fc06846b6626db9478849ae3bb9b97a098e', 'dd4ddf4b1f3319d71adc439222900d462dfb40e8', 'c712074b53950a7e885173a75cd5790225530bde', '4fb3f184500d79cd3e7e791cd0c7e803bf054880', 'b89b5b2a730f7f3ca969fd0371ec9cc00224934a', 'a46815e1aeb39cfc367c1249db7c7c216b86e7f2', '1f697afd16acdf4aff93ae77a858f6fc6fa46856', 'c155e7ceaa990444b63070463ac81fbab5767849', 'b5c5412a5822fd9b76c1e501aaec3771bc1ca1cd', 'c1c39432eb92d51b4822f85254436cb0dbd8eae4', '8683782a6e21be3db3d55b7bc5a7d159146e7af7', '4e0fa9397a5341429580a0f6cca1e7d03debc7e8', '6536479ddc31d65455411aac775fecf65ca1d658', 'f99d7bdac1d0b8953e988bdffedbd0935f3683ff', 'eb2ce61d91427adfaa06a2f38c182c17c3bf8ce0', 'ae636f5e2a8bd349f5c034e9dcb212efc924a667', '8ea0fa3451c68622a7bea7620245cf02aa941e0f', '76d172652df3c5115c516548369f7b88f3f0fe2c', '15a24d7030c8afb2b9ff504cc228c291ed524f6b', 'b7da0b8fc04351fab4f12e2494411cb1acfa8865', '5bfd21ef6e2ae6cf94ec683c46e46bf323e7a446', 'eb72b466b38cfe22412441364d2830db3d7a096f', '814756272de7c0b78cad78182dbca88d94b9efc9', '5e03b86ccb07eb9d4db699888b2997a681731347', '3c3b07d28e49c087d7a00922c8e8a83aef5a63e5', 'bc439919e90e2b0a9852e6825e3ffd3de5c5f4ec', 'b988504047d7704e54d05ed4093213ae2d8e1e6e', 'bca128ead24fbcf0ce4c9948b937cfd525638c01', 'd89153f6e5223dd1ab3dce2c38e9cb49e5615a1c', 'f5a963c67a60bed1fa2094c211fa72162642eeee', 'ff7f05dbd4bb69e998b7812ccfeb96bc7f77ecc2', '89f89ed05b911d5975fde1847377cc60e89900cf', 'a1ad01f429f689ed8b5849b4c0f2e7823eb7eea4', '55896a2f3ea510453bbbb689aeda80a3ef9e5206', '317c0b6aca2f47270bcccecda83529015713e46c', '5135cacfa9b0efe43e8b1ad9428fb42047259c6c', 'b27cf9badf60a19fc81e0522f5fa82c23e1a0dc9', '22cef05b5df544be316c1efd29d05e71264a90c7', '3460df0b31878cf4ec60751019462561a8d9942c', 'e177142b9b81667110a1d5d092033129fd836578', 'eae3467bbce1ca63a788daa2fa9a3659d2c080fd', '0c7eac3011d2b4f075cac34433822fdf57785634', 'ec7e0d8d39146bbdd4385ba133f1c11dbb6e2c0f', 'bcafd0ba9ccaa641c3b10c280b58ffc0cfb60862', '8c23430be675a303242c3711ebce818782171ae2', '283fa124a13d9675de4d10dbfdcfad7c8d0fdc0b', 'e6f596760d1c999976fd930cc2eb191a0fd0dc2f', 'abeccf0939d43785be539e62fb07ae633e6824ea', 'a7c25c5c036ce5ce4f12d30e4291ed8ab9e4e220', '782f5699ba9b79a9c11173bdf31dbe297aa2078a', '201cde9681a0a7cf0f907910529896bbdf1dcb37', 'e3625f425c5e8ed0ae395ee51733620cf8f52d3d', '6016fd683902a43e0dcdf6f6100901288be22b2c', '9248d15b05cb49a2c5808a8b5fc977d84eee9948', '6f3946defb918eb41760d14ee1d3ae137349c9cb', '5fefe721bcc47879978950484c987e0a15b6e8f5', '88a53dff9c1cdd4dbc9328838ba2056b89c3fc6f', 'a017a39fdbaca6848482b1c32340e1d2bfcb6b48', '8e19e0732930e020ccf2dae1473511cb0f18a136', '6e9d6a6e153b3940a74ee4c0158d8c59b1be88bf', '06acc2d55d4f764f10a856257d3e4ced9ec8f377', '93fa0a5d8676077b0d18231e14fa7c52d43d6fb2', 'eea60098544fd9156efbd57e12bf3cf8ca6ca638', '614d5901b6882d272f7d2bd638ac44ef898bace6', '863a320704eb6850e5d476d457dda4cf4db1af3a', '4fb95fa4aba2a53bb9110af22bcc2688ed72e8a4', 'dbccf483b0a265e146e2d6f5e8f5345b37f19989', '9deed13d272bc45b7595fcda8310ef4314f7bbe9', '95e28d8cd522ed8008a3fdd09257591069849bf4', '335994661c1641ff200e2deb6792ba0e28c4cd12')Plan on `gitlab-org/gitlab` (long lists shortened)
Plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/80865/commands/188724
Nested Loop (cost=1.27..224800.13 rows=5 width=4) (actual time=278.394..278.396 rows=0 loops=1)
Buffers: shared hit=227 read=324
-> Index Scan using index_notes_on_commit_id on notes (cost=0.70..224423.45 rows=105 width=8) (actual time=278.393..278.394 rows=0 loops=1)
Index Cond: (commit_id = ANY ('{57be57423c67..., 8efd8b59f207..., ... 100 SHAs}'::text[]))
Filter: ((type = 'DiffNote') AND (project_id = 278964))
Rows Removed by Filter: 51
-> Index Scan using index_note_diff_files_on_diff_note_id on note_diff_files (never executed)
Index Cond: (diff_note_id = notes.id)
Time: 297.744 ms (planning: 19.309 ms, execution: 278.435 ms, I/O read: 276.071 ms)Plan on `gitlab-org/gitlab-foss`, project 13083 (long lists shortened)
Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84310/commands/191516
A BitmapAnd that reads the project's whole range of index_notes_on_project_id_and_noteable_type, 1.78 million rows.
Nested Loop (cost=17162.09..17275.83 rows=1 width=4) (actual time=5973.083..5973.086 rows=0 loops=1)
Buffers: shared hit=221 read=6623
I/O Timings: read=5520.524 write=0.000
-> Bitmap Heap Scan on public.notes (cost=17161.52..17254.30 rows=6 width=8) (actual time=5973.082..5973.084 rows=0 loops=1)
Recheck Cond: (((notes.commit_id)::text = ANY ('{... 100 SHAs}'::text[])) AND (notes.project_id = 13083))
Filter: ((notes.type)::text = 'DiffNote'::text)
Rows Removed by Filter: 8
Heap Blocks: exact=8
Buffers: shared hit=221 read=6623
I/O Timings: read=5520.524 write=0.000
-> BitmapAnd (cost=17161.27..17161.27 rows=61 width=0) (actual time=5951.694..5951.696 rows=0 loops=1)
Buffers: shared hit=221 read=6615
I/O Timings: read=5499.652 write=0.000
-> Bitmap Index Scan on index_notes_on_commit_id (cost=0.00..1611.75 rows=145480 width=0) (actual time=519.759..519.759 rows=8 loops=1)
Index Cond: ((notes.commit_id)::text = ANY ('{... 100 SHAs}'::text[]))
Buffers: shared hit=221 read=279
I/O Timings: read=518.029 write=0.000
-> Bitmap Index Scan on index_notes_on_project_id_and_noteable_type (cost=0.00..15549.26 rows=1534758 width=0) (actual time=5426.509..5426.510 rows=1778419 loops=1)
Index Cond: (notes.project_id = 13083)
Buffers: shared read=6336
I/O Timings: read=4981.624 write=0.000
-> Index Scan using index_note_diff_files_on_diff_note_id on public.note_diff_files (cost=0.57..3.59 rows=1 width=12) (never executed)
Index Cond: (note_diff_files.diff_note_id = notes.id)
I/O Timings: read=0.000 write=0.000
Time: 5.994 s (planning: 20.680 ms, execution: 5.973 s, I/O read: 5.521 s)
Buffers: hits 221, reads 6623After:
Raw SQL
WITH "candidate_notes" AS MATERIALIZED (SELECT "notes"."id", "notes"."project_id", "notes"."type" FROM "notes" WHERE "notes"."commit_id" IN ('57be57423c67cb0c376e8d32bc5b840bfbb752b8', '8efd8b59f207394bbe4bc484bd9d8d01b5470846', 'ba51af758db8d85bfb4fdf82b70691bc1ebffee7', 'd84eaad3e6ccb756275e234e2b8c5acea74ac206', 'e33010610a4811e5c1f73276294a5d343e2b8402', 'd78e1e52993f9bd7bd86a0bf30ab008909cbe2cc', '8a6605d39d30d9a9a548479badf12cf16c225227', '366163d0ac27db52b243e806fe204fc2c0563f90', '3f45ce3e1bf0541e0714f187d4bfc976b3334bd5', '2d5fa4b4bd2f7772dcba99c099bea9c2ff28bcbb', '9e86308287963b27b26a0588ab33f0cf292ad797', '567601176dfd5b798e3f9dad41331e8aa76c5a1e', 'de15e640f5aba976293b4e49bc93bc8c6f4bb811', '3e08dee967847ee5ce70c2129cfeff4e409a6209', 'e61c4e6918ac9a8017a719e65f542bb7c905726a', '4eaf915d8b75ee35a9aca0fcfd412d991f9b8bc9', '78a5fb80b09260f21ed6b628f5d59f79f15c3e6c', '740f887e6c9411659ef9ee81e8c0755f789835c6', '6a70e653e1bf2873b23d0cc084f544c1c7ee0c6e', '90551fac956c16c1e8b23c0a547813eee5de720d', 'fb92743cab3d29e1faf3f70b56219785e7305a19', '309ebc7cad4b7f436e023d31876d2ccb973d253a', 'e5d29c48dd380ab8ae02d39f28933c959e15a937', '73b11879e42789249047acf10c3b32985f7d32c7', '5deca60e612a59323d5c35c0c5c77a9cfb3590df', '56ab2e7dbb7aba15a03b7a3ccf30db3f66cad9d4', '0c2a6c1d8fbcf5d9b9c2973a1d9f2f5ec4ef5029', '46e278531950ec47213bfd66c42e1a9c4b0e19bc', '311fea1fc538c493e1f1fc92232024455d7c4388', '70373c82b747ce4ebf3f673c49aba0ff01ceeb16', '5ea25fc06846b6626db9478849ae3bb9b97a098e', 'dd4ddf4b1f3319d71adc439222900d462dfb40e8', 'c712074b53950a7e885173a75cd5790225530bde', '4fb3f184500d79cd3e7e791cd0c7e803bf054880', 'b89b5b2a730f7f3ca969fd0371ec9cc00224934a', 'a46815e1aeb39cfc367c1249db7c7c216b86e7f2', '1f697afd16acdf4aff93ae77a858f6fc6fa46856', 'c155e7ceaa990444b63070463ac81fbab5767849', 'b5c5412a5822fd9b76c1e501aaec3771bc1ca1cd', 'c1c39432eb92d51b4822f85254436cb0dbd8eae4', '8683782a6e21be3db3d55b7bc5a7d159146e7af7', '4e0fa9397a5341429580a0f6cca1e7d03debc7e8', '6536479ddc31d65455411aac775fecf65ca1d658', 'f99d7bdac1d0b8953e988bdffedbd0935f3683ff', 'eb2ce61d91427adfaa06a2f38c182c17c3bf8ce0', 'ae636f5e2a8bd349f5c034e9dcb212efc924a667', '8ea0fa3451c68622a7bea7620245cf02aa941e0f', '76d172652df3c5115c516548369f7b88f3f0fe2c', '15a24d7030c8afb2b9ff504cc228c291ed524f6b', 'b7da0b8fc04351fab4f12e2494411cb1acfa8865', '5bfd21ef6e2ae6cf94ec683c46e46bf323e7a446', 'eb72b466b38cfe22412441364d2830db3d7a096f', '814756272de7c0b78cad78182dbca88d94b9efc9', '5e03b86ccb07eb9d4db699888b2997a681731347', '3c3b07d28e49c087d7a00922c8e8a83aef5a63e5', 'bc439919e90e2b0a9852e6825e3ffd3de5c5f4ec', 'b988504047d7704e54d05ed4093213ae2d8e1e6e', 'bca128ead24fbcf0ce4c9948b937cfd525638c01', 'd89153f6e5223dd1ab3dce2c38e9cb49e5615a1c', 'f5a963c67a60bed1fa2094c211fa72162642eeee', 'ff7f05dbd4bb69e998b7812ccfeb96bc7f77ecc2', '89f89ed05b911d5975fde1847377cc60e89900cf', 'a1ad01f429f689ed8b5849b4c0f2e7823eb7eea4', '55896a2f3ea510453bbbb689aeda80a3ef9e5206', '317c0b6aca2f47270bcccecda83529015713e46c', '5135cacfa9b0efe43e8b1ad9428fb42047259c6c', 'b27cf9badf60a19fc81e0522f5fa82c23e1a0dc9', '22cef05b5df544be316c1efd29d05e71264a90c7', '3460df0b31878cf4ec60751019462561a8d9942c', 'e177142b9b81667110a1d5d092033129fd836578', 'eae3467bbce1ca63a788daa2fa9a3659d2c080fd', '0c7eac3011d2b4f075cac34433822fdf57785634', 'ec7e0d8d39146bbdd4385ba133f1c11dbb6e2c0f', 'bcafd0ba9ccaa641c3b10c280b58ffc0cfb60862', '8c23430be675a303242c3711ebce818782171ae2', '283fa124a13d9675de4d10dbfdcfad7c8d0fdc0b', 'e6f596760d1c999976fd930cc2eb191a0fd0dc2f', 'abeccf0939d43785be539e62fb07ae633e6824ea', 'a7c25c5c036ce5ce4f12d30e4291ed8ab9e4e220', '782f5699ba9b79a9c11173bdf31dbe297aa2078a', '201cde9681a0a7cf0f907910529896bbdf1dcb37', 'e3625f425c5e8ed0ae395ee51733620cf8f52d3d', '6016fd683902a43e0dcdf6f6100901288be22b2c', '9248d15b05cb49a2c5808a8b5fc977d84eee9948', '6f3946defb918eb41760d14ee1d3ae137349c9cb', '5fefe721bcc47879978950484c987e0a15b6e8f5', '88a53dff9c1cdd4dbc9328838ba2056b89c3fc6f', 'a017a39fdbaca6848482b1c32340e1d2bfcb6b48', '8e19e0732930e020ccf2dae1473511cb0f18a136', '6e9d6a6e153b3940a74ee4c0158d8c59b1be88bf', '06acc2d55d4f764f10a856257d3e4ced9ec8f377', '93fa0a5d8676077b0d18231e14fa7c52d43d6fb2', 'eea60098544fd9156efbd57e12bf3cf8ca6ca638', '614d5901b6882d272f7d2bd638ac44ef898bace6', '863a320704eb6850e5d476d457dda4cf4db1af3a', '4fb95fa4aba2a53bb9110af22bcc2688ed72e8a4', 'dbccf483b0a265e146e2d6f5e8f5345b37f19989', '9deed13d272bc45b7595fcda8310ef4314f7bbe9', '95e28d8cd522ed8008a3fdd09257591069849bf4', '335994661c1641ff200e2deb6792ba0e28c4cd12')) SELECT "note_diff_files"."id" FROM "note_diff_files" INNER JOIN candidate_notes ON candidate_notes.id = note_diff_files.diff_note_id WHERE "candidate_notes"."project_id" = 278964 AND "candidate_notes"."type" = 'DiffNote'Plan on `gitlab-org/gitlab` (long lists shortened)
Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84377/commands/191575
Nested Loop (cost=227639.24..231756.11 rows=103 width=4) (actual time=436.563..436.565 rows=0 loops=1)
Buffers: shared hit=274 read=277 dirtied=3
I/O Timings: read=433.162 write=0.000
CTE candidate_notes
-> Index Scan using index_notes_on_commit_id on public.notes (cost=0.70..227638.67 rows=149917 width=22) (actual time=24.663..436.478 rows=51 loops=1)
Index Cond: ((notes.commit_id)::text = ANY ('{... 100 SHAs}'::text[]))
Buffers: shared hit=274 read=277 dirtied=3
I/O Timings: read=433.162 write=0.000
-> CTE Scan on candidate_notes (cost=0.00..3747.93 rows=103 width=8) (actual time=436.562..436.563 rows=0 loops=1)
Filter: ((candidate_notes.project_id = 278964) AND ((candidate_notes.type)::text = 'DiffNote'::text))
Rows Removed by Filter: 51
Buffers: shared hit=274 read=277 dirtied=3
I/O Timings: read=433.162 write=0.000
-> Index Scan using index_note_diff_files_on_diff_note_id on public.note_diff_files (cost=0.57..3.59 rows=1 width=12) (never executed)
Index Cond: (note_diff_files.diff_note_id = candidate_notes.id)
I/O Timings: read=0.000 write=0.000
Time: 454.417 ms (planning: 17.806 ms, execution: 436.611 ms, I/O read: 433.162 ms)
Buffers: hits 274, reads 277, dirtied 3Plan on `gitlab-org/gitlab-foss`, project 13083 (long lists shortened)
Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84377/commands/191574
An index scan on index_notes_on_commit_id, then a filter. The cost follows the 8 notes on those SHAs, not the project's size.
Nested Loop (cost=227639.24..231415.30 rows=8 width=4) (actual time=527.143..527.145 rows=0 loops=1)
Buffers: shared hit=221 read=287
I/O Timings: read=524.670 write=0.000
CTE candidate_notes
-> Index Scan using index_notes_on_commit_id on public.notes (cost=0.70..227638.67 rows=149917 width=22) (actual time=86.035..527.097 rows=8 loops=1)
Index Cond: ((notes.commit_id)::text = ANY ('{... 100 SHAs}'::text[]))
Buffers: shared hit=221 read=287
I/O Timings: read=524.670 write=0.000
-> CTE Scan on candidate_notes (cost=0.00..3747.93 rows=8 width=8) (actual time=527.142..527.142 rows=0 loops=1)
Filter: ((candidate_notes.project_id = 13083) AND ((candidate_notes.type)::text = 'DiffNote'::text))
Rows Removed by Filter: 8
Buffers: shared hit=221 read=287
I/O Timings: read=524.670 write=0.000
-> Index Scan using index_note_diff_files_on_diff_note_id on public.note_diff_files (cost=0.57..3.59 rows=1 width=12) (never executed)
Index Cond: (note_diff_files.diff_note_id = candidate_notes.id)
I/O Timings: read=0.000 write=0.000
Time: 547.379 ms (planning: 20.186 ms, execution: 527.193 ms, I/O read: 524.670 ms)
Buffers: hits 221, reads 2875. Delete a batch of 1000 note diff files
Raw SQL
DELETE FROM "note_diff_files" WHERE "note_diff_files"."id" IN (SELECT "note_diff_files"."id" FROM "note_diff_files" ORDER BY "note_diff_files"."id" DESC LIMIT 1000)Plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/80865/commands/188726
Plan (long lists shortened)
Delete on note_diff_files (cost=35.46..3630.89 rows=0 width=0) (actual time=437.685..437.687 rows=0 loops=1)
Buffers: shared hit=8455 read=606 dirtied=569
WAL: records=2193 fpi=569 bytes=3473889
-> Nested Loop (cost=35.46..3630.89 rows=1000 width=34) (actual time=254.571..257.894 rows=1000 loops=1)
-> HashAggregate (rows=1000) Group Key: "ANY_subquery".id
-> Limit (rows=1000)
-> Index Only Scan Backward using note_diff_files_pkey on note_diff_files note_diff_files_1 (actual rows=1000)
-> Index Scan using note_diff_files_pkey on note_diff_files (actual time=0.003..0.003 rows=1 loops=1000)
Index Cond: (id = "ANY_subquery".id)
Time: 439.359 ms (planning: 1.517 ms, execution: 437.842 ms, I/O read: 414.161 ms)Validation
Tested locally in a GDK set up like GitLab.com (partitioned merge_request_diff_commits), with a real BFG object map, merge requests with many diff versions, and commit-scoped diff notes. The same diffs and note diff files are removed with the flag enabled and disabled, and the longest transaction is bounded by the batch size when it is enabled.
References
- https://gitlab.com/gitlab-org/gitlab/-/work_items/634218+
- Draft: Limit concurrency of repository cleanup ... (!266661)
- [FF] `bound_repository_cleanup_transactions` --... (#638388)
Screenshots or screen recordings
Not applicable, there is no UI change.
How to set up and validate locally
- In a Rails console, enable the flag for a project:
Feature.enable(:bound_repository_cleanup_transactions, Project.find(<project id>)). - Upload a BFG object map in the repository settings of that project, for a project with merge requests that reference the rewritten commits.
- Check that the merge request diffs and note diff files that reference the rewritten commits are deleted.
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.