Create partitioned version of merge_request_diff_commits

Patrick Bajaorequested to merge
527228-merge-request-diff-commits-partitioned-table-v2 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 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). We're using FOR EACH STATEMENT instead of FOR EACH ROW since we allow bulk creating of merge_request_diff_commits rows (this should prevent N+1 inserts).

db:migrate

Click to expand 
$ bundle exec rake db:migrate
main: == [advisory_lock_connection] object_id: 136180, pg_backend_pid: 69606
main: == 20260217084532 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.0033s
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.0024s
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.0016s
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.0016s
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.0033s
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.0015s
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.0019s
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.0019s
main: -- execute("CREATE  FUNCTION table_sync_function_0992e728d3_insert()\nRETURNS TRIGGER AS\n$$\nBEGIN\nINSERT INTO merge_request_diff_commits_b5377a7a34\n  (merge_request_commits_metadata_id, project_id, merge_request_diff_id, relative_order)\nSELECT\n  new_table.merge_request_commits_metadata_id,\n  new_table.project_id,\n  new_table.merge_request_diff_id,\n  new_table.relative_order\nFROM new_table\nWHERE new_table.merge_request_commits_metadata_id IS NOT NULL\n  AND new_table.project_id IS NOT NULL;\n\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main:    -> 0.0012s
main: -- execute("CREATE  FUNCTION table_sync_function_0992e728d3_delete()\nRETURNS TRIGGER AS\n$$\nBEGIN\nDELETE FROM merge_request_diff_commits_b5377a7a34\nWHERE (merge_request_diff_id, relative_order, project_id) IN (\n  SELECT\n    old_table.merge_request_diff_id,\n    old_table.relative_order,\n    old_table.project_id\n  FROM old_table\n  WHERE old_table.project_id IS NOT NULL\n);\n\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main:    -> 0.0003s
main: -- execute("CREATE TRIGGER table_sync_trigger_57c8465cd7_insert AFTER INSERT ON merge_request_diff_commits REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION table_sync_function_0992e728d3_insert();")
main:    -> 0.0005s
main: -- execute("CREATE TRIGGER table_sync_trigger_57c8465cd7_delete AFTER DELETE ON merge_request_diff_commits REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION table_sync_function_0992e728d3_delete();")
main:    -> 0.0003s
main: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: migrated (0.0654s) 

main: == [advisory_lock_connection] object_id: 136180, pg_backend_pid: 69606
ci: == [advisory_lock_connection] object_id: 136180, pg_backend_pid: 69618
ci: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: migrating =========
ci: -- execute("CREATE  FUNCTION table_sync_function_0992e728d3_insert()\nRETURNS TRIGGER AS\n$$\nBEGIN\nINSERT INTO merge_request_diff_commits_b5377a7a34\n  (merge_request_commits_metadata_id, project_id, merge_request_diff_id, relative_order)\nSELECT\n  new_table.merge_request_commits_metadata_id,\n  new_table.project_id,\n  new_table.merge_request_diff_id,\n  new_table.relative_order\nFROM new_table\nWHERE new_table.merge_request_commits_metadata_id IS NOT NULL\n  AND new_table.project_id IS NOT NULL;\n\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
ci:    -> 0.0013s
ci: -- execute("CREATE  FUNCTION table_sync_function_0992e728d3_delete()\nRETURNS TRIGGER AS\n$$\nBEGIN\nDELETE FROM merge_request_diff_commits_b5377a7a34\nWHERE (merge_request_diff_id, relative_order, project_id) IN (\n  SELECT\n    old_table.merge_request_diff_id,\n    old_table.relative_order,\n    old_table.project_id\n  FROM old_table\n  WHERE old_table.project_id IS NOT NULL\n);\n\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
ci:    -> 0.0003s
ci: -- execute("CREATE TRIGGER table_sync_trigger_57c8465cd7_insert AFTER INSERT ON merge_request_diff_commits REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION table_sync_function_0992e728d3_insert();")
ci:    -> 0.0005s
ci: -- execute("CREATE TRIGGER table_sync_trigger_57c8465cd7_delete AFTER DELETE ON merge_request_diff_commits REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION table_sync_function_0992e728d3_delete();")
ci:    -> 0.0003s
ci: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: migrated (0.0199s) 

ci: == [advisory_lock_connection] object_id: 136180, pg_backend_pid: 69618
sec: == [advisory_lock_connection] object_id: 136180, pg_backend_pid: 69620
sec: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: migrating =========
sec: -- create_table("merge_request_diff_commits_b5377a7a34", {:options=>"PARTITION BY RANGE(project_id)", :primary_key=>[:merge_request_diff_id, :relative_order, :project_id]})
sec:    -> 0.0025s
sec: -- 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")
sec:    -> 0.0022s
sec: -- 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")
sec:    -> 0.0014s
sec: -- 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")
sec:    -> 0.0014s
sec: -- 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")
sec:    -> 0.0017s
sec: -- 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")
sec:    -> 0.0013s
sec: -- 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")
sec:    -> 0.0015s
sec: -- execute("CREATE  FUNCTION table_sync_function_0992e728d3_insert()\nRETURNS TRIGGER AS\n$$\nBEGIN\nINSERT INTO merge_request_diff_commits_b5377a7a34\n  (merge_request_commits_metadata_id, project_id, merge_request_diff_id, relative_order)\nSELECT\n  new_table.merge_request_commits_metadata_id,\n  new_table.project_id,\n  new_table.merge_request_diff_id,\n  new_table.relative_order\nFROM new_table\nWHERE new_table.merge_request_commits_metadata_id IS NOT NULL\n  AND new_table.project_id IS NOT NULL;\n\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
sec:    -> 0.0011s
sec: -- execute("CREATE  FUNCTION table_sync_function_0992e728d3_delete()\nRETURNS TRIGGER AS\n$$\nBEGIN\nDELETE FROM merge_request_diff_commits_b5377a7a34\nWHERE (merge_request_diff_id, relative_order, project_id) IN (\n  SELECT\n    old_table.merge_request_diff_id,\n    old_table.relative_order,\n    old_table.project_id\n  FROM old_table\n  WHERE old_table.project_id IS NOT NULL\n);\n\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
sec:    -> 0.0003s
sec: -- execute("CREATE TRIGGER table_sync_trigger_57c8465cd7_insert AFTER INSERT ON merge_request_diff_commits REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION table_sync_function_0992e728d3_insert();")
sec:    -> 0.0004s
sec: -- execute("CREATE TRIGGER table_sync_trigger_57c8465cd7_delete AFTER DELETE ON merge_request_diff_commits REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION table_sync_function_0992e728d3_delete();")
sec:    -> 0.0003s
sec: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: migrated (0.0326s) 

sec: == [advisory_lock_connection] object_id: 136180, pg_backend_pid: 69620

db:migrate:down

Click to expand 
VERSION=20260217084532 bundle exec rake db:migrate:down:main db:migrate:down:ci db:migrate:down:sec
main: == [advisory_lock_connection] object_id: 135340, pg_backend_pid: 69680
main: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: reverting =========
main: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7_insert ON merge_request_diff_commits")
main:    -> 0.0012s
main: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7_delete ON merge_request_diff_commits")
main:    -> 0.0003s
main: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3_insert()")
main:    -> 0.0003s
main: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3_delete()")
main:    -> 0.0003s
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.0003s
main: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3()")
main:    -> 0.0003s
main: -- drop_table("merge_request_diff_commits_b5377a7a34")
main:    -> 0.0060s
main: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: reverted (0.0266s) 

main: == [advisory_lock_connection] object_id: 135340, pg_backend_pid: 69680
ci: == [advisory_lock_connection] object_id: 135340, pg_backend_pid: 69694
ci: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: reverting =========
ci: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7_insert ON merge_request_diff_commits")
ci:    -> 0.0008s
ci: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7_delete ON merge_request_diff_commits")
ci:    -> 0.0004s
ci: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3_insert()")
ci:    -> 0.0004s
ci: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3_delete()")
ci:    -> 0.0003s
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.0003s
ci: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3()")
ci:    -> 0.0003s
ci: -- drop_table("merge_request_diff_commits_b5377a7a34")
ci:    -> 0.0049s
ci: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: reverted (0.0169s) 

ci: == [advisory_lock_connection] object_id: 135340, pg_backend_pid: 69694
sec: == [advisory_lock_connection] object_id: 135340, pg_backend_pid: 69716
sec: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: reverting =========
sec: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7_insert ON merge_request_diff_commits")
sec:    -> 0.0007s
sec: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7_delete ON merge_request_diff_commits")
sec:    -> 0.0004s
sec: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3_insert()")
sec:    -> 0.0004s
sec: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3_delete()")
sec:    -> 0.0004s
sec: -- transaction_open?(nil)
sec:    -> 0.0000s
sec: -- transaction_open?(nil)
sec:    -> 0.0000s
sec: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7 ON merge_request_diff_commits")
sec:    -> 0.0004s
sec: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3()")
sec:    -> 0.0003s
sec: -- drop_table("merge_request_diff_commits_b5377a7a34")
sec:    -> 0.0053s
sec: == 20260217084532 CreatePartitionedMergeRequestDiffCommits: reverted (0.0170s) 

sec: == [advisory_lock_connection] object_id: 135340, pg_backend_pid: 69716

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.

Related to #527228

Edited by Patrick Bajao

Merge request reports