Skip to content

Draft: Create partitioned version of merge_request_diff_commits

Patrick Bajaorequested to merge
527228-merge-request-diff-commits-partitioned-table into master

What does this MR do and why?

This creates the partitioned version of merge_request_diff_commits table which will later on replace the existing one. This table has fewer columns since most of the columns will be moved to merge_request_commits_metadata table. A trigger is added so deletion, updates, and inserts coming from the old table will also happen on this new table. The table will have fewer records at first and will be backfilled in a separate MR.

We cannot use the existing helpers to create a partitioned copy of merge_request_diff_commits because this partitioned version is not a direct copy of the old one (fewer columns).

Some spec contexts were removed in this MR since they're not needed (they're failing but we don't need to fix). At the point wherein this MR can be merged, phase 1 should be done so the dedup (merge_request_diff_commits_dedup) FF will be gone. These removals will be likely removed from this MR once we get to that point.

DO NOT MERGE YET UNTIL PHASE 1 IS COMPLETE

db:migrate

Click to expand 
$ bundle exec rake db:migrate
main: == [advisory_lock_connection] object_id: 130600, pg_backend_pid: 91840
main: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: migrating =========
main: -- create_table("merge_request_diff_commits_b5377a7a34", {:options=>"PARTITION BY RANGE(project_id)", :primary_key=>[:merge_request_diff_id, :relative_order, :project_id]})
main:    -> 0.0088s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_1 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (1) TO (2000001)\n")
main:    -> 0.0087s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_2000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (2000001) TO (4000001)\n")
main:    -> 0.0023s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_4000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (4000001) TO (6000001)\n")
main:    -> 0.0028s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_6000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (6000001) TO (8000001)\n")
main:    -> 0.0024s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_8000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (8000001) TO (10000001)\n")
main:    -> 0.0027s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_10000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (10000001) TO (12000001)\n")
main:    -> 0.0023s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_12000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (12000001) TO (14000001)\n")
main:    -> 0.0025s
main: -- execute("CREATE  FUNCTION table_sync_function_0992e728d3()\nRETURNS TRIGGER AS\n$$\nBEGIN\nIF (TG_OP = 'DELETE') THEN\n  DELETE FROM merge_request_diff_commits_b5377a7a34\n  WHERE \"merge_request_diff_id\" = OLD.\"merge_request_diff_id\"\n  AND \"relative_order\" = OLD.\"relative_order\"\n  AND \"project_id\" = COALESCE(OLD.\"project_id\", (SELECT mrd.project_id FROM merge_request_diffs mrd WHERE mrd.id = OLD.\"merge_request_diff_id\"));\nELSIF (TG_OP = 'INSERT') THEN\n  INSERT INTO merge_request_diff_commits_b5377a7a34 (\"merge_request_commits_metadata_id\",\n    \"project_id\",\n    \"merge_request_diff_id\",\n    \"relative_order\")\n  VALUES (NEW.\"merge_request_commits_metadata_id\",\n    COALESCE(NEW.\"project_id\", (SELECT mrd.project_id FROM merge_request_diffs mrd WHERE mrd.id = NEW.\"merge_request_diff_id\")),\n    NEW.\"merge_request_diff_id\",\n    NEW.\"relative_order\");\nEND IF;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main:    -> 0.0014s
main: -- execute("CREATE TRIGGER table_sync_trigger_57c8465cd7\nAFTER INSERT OR UPDATE OR DELETE ON merge_request_diff_commits\nFOR EACH ROW\n\nEXECUTE FUNCTION table_sync_function_0992e728d3()\n")
main:    -> 0.0012s
main: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: migrated (0.1789s) 

main: == [advisory_lock_connection] object_id: 130600, pg_backend_pid: 91840
ci: == [advisory_lock_connection] object_id: 130600, pg_backend_pid: 91841
ci: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: migrating =========
ci: -- create_table("merge_request_diff_commits_b5377a7a34", {:options=>"PARTITION BY RANGE(project_id)", :primary_key=>[:merge_request_diff_id, :relative_order, :project_id]})
ci:    -> 0.0093s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_1 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (1) TO (2000001)\n")
ci:    -> 0.0050s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_2000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (2000001) TO (4000001)\n")
ci:    -> 0.0027s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_4000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (4000001) TO (6000001)\n")
ci:    -> 0.0023s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_6000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (6000001) TO (8000001)\n")
ci:    -> 0.0279s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_8000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (8000001) TO (10000001)\n")
ci:    -> 0.0033s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_10000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (10000001) TO (12000001)\n")
ci:    -> 0.0020s
ci: -- execute("CREATE  FUNCTION table_sync_function_0992e728d3()\nRETURNS TRIGGER AS\n$$\nBEGIN\nIF (TG_OP = 'DELETE') THEN\n  DELETE FROM merge_request_diff_commits_b5377a7a34\n  WHERE \"merge_request_diff_id\" = OLD.\"merge_request_diff_id\"\n  AND \"relative_order\" = OLD.\"relative_order\"\n  AND \"project_id\" = COALESCE(OLD.\"project_id\", (SELECT mrd.project_id FROM merge_request_diffs mrd WHERE mrd.id = OLD.\"merge_request_diff_id\"));\nELSIF (TG_OP = 'INSERT') THEN\n  INSERT INTO merge_request_diff_commits_b5377a7a34 (\"merge_request_commits_metadata_id\",\n    \"project_id\",\n    \"merge_request_diff_id\",\n    \"relative_order\")\n  VALUES (NEW.\"merge_request_commits_metadata_id\",\n    COALESCE(NEW.\"project_id\", (SELECT mrd.project_id FROM merge_request_diffs mrd WHERE mrd.id = NEW.\"merge_request_diff_id\")),\n    NEW.\"merge_request_diff_id\",\n    NEW.\"relative_order\");\nEND IF;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
ci:    -> 0.0012s
ci: -- execute("CREATE TRIGGER table_sync_trigger_57c8465cd7\nAFTER INSERT OR UPDATE OR DELETE ON merge_request_diff_commits\nFOR EACH ROW\n\nEXECUTE FUNCTION table_sync_function_0992e728d3()\n")
ci:    -> 0.0013s
ci: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: migrated (0.1120s) 

ci: == [advisory_lock_connection] object_id: 130600, pg_backend_pid: 91841

db:migrate:down

Click to expand 
$ VERSION=20251015070354 bundle exec rake db:migrate:down:main db:migrate:down:ci
main: == [advisory_lock_connection] object_id: 129600, pg_backend_pid: 31786
main: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: reverting =========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7 ON merge_request_diff_commits")
main:    -> 0.0017s
main: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3()")
main:    -> 0.0006s
main: -- drop_table("merge_request_diff_commits_b5377a7a34")
main:    -> 0.0183s
main: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: reverted (0.0637s) 

main: == [advisory_lock_connection] object_id: 129600, pg_backend_pid: 31786
ci: == [advisory_lock_connection] object_id: 129600, pg_backend_pid: 31907
ci: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: reverting =========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7 ON merge_request_diff_commits")
ci:    -> 0.0008s
ci: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3()")
ci:    -> 0.0004s
ci: -- drop_table("merge_request_diff_commits_b5377a7a34")
ci:    -> 0.0131s
ci: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: reverted (0.0259s) 

ci: == [advisory_lock_connection] object_id: 129600, pg_backend_pid: 31907

References

https://gitlab.com/gitlab-org/gitlab/-/issues/527228

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.

Edited by Patrick Bajao

Merge request reports