Add and backfill lists sharding key

Mario Celirequested to merge
514597-lists-sharding-key into master

What does this MR do and why?

Add composite sharding key (group_id, project_id). Add invalid constraints that will be validated after the backfill is finalized

DB review

Query plans

UPDATE sharding key for batch

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/38272/commands/117445

WITH sub_batch AS MATERIALIZED (
  SELECT 
    "lists"."id", 
    "lists"."board_id" 
  FROM 
    "lists" 
  WHERE 
    "lists"."id" BETWEEN 2 
    AND 22748 
    AND "lists"."id" >= 199 
    AND "lists"."id" < 462 
  LIMIT 
    100
) 
UPDATE 
  "lists" 
SET 
  "group_id" = "boards"."group_id", 
  "project_id" = "boards"."project_id" 
FROM 
  "sub_batch" 
  INNER JOIN "boards" ON "boards"."id" = "sub_batch"."board_id" 
WHERE 
  "lists"."id" = "sub_batch"."id"

Migration output

UP 
main: == [advisory_lock_connection] object_id: 133880, pg_backend_pid: 57316
main: == 20250404095645 AddShardingKeyToListsTable: migrating =======================
main: -- add_column(:lists, :group_id, :bigint)
main:    -> 0.0014s
main: -- add_column(:lists, :project_id, :bigint)
main:    -> 0.0008s
main: == 20250404095645 AddShardingKeyToListsTable: migrated (0.0064s) ==============

main: == [advisory_lock_connection] object_id: 133880, pg_backend_pid: 57316
ci: == [advisory_lock_connection] object_id: 134180, pg_backend_pid: 57318
ci: == 20250404095645 AddShardingKeyToListsTable: migrating =======================
ci: -- add_column(:lists, :group_id, :bigint)
ci:    -> 0.0008s
ci: -- add_column(:lists, :project_id, :bigint)
ci:    -> 0.0004s
ci: == 20250404095645 AddShardingKeyToListsTable: migrated (0.0105s) ==============

ci: == [advisory_lock_connection] object_id: 134180, pg_backend_pid: 57318
main: == [advisory_lock_connection] object_id: 134420, pg_backend_pid: 57322
main: == 20250404100126 AddListsShardingKeyNotNullConstraint: migrating =============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE lists\nADD CONSTRAINT check_6dadb82d36\nCHECK ( num_nonnulls(group_id, project_id) = 1 )\nNOT VALID;\n")
main:    -> 0.0023s
main: == 20250404100126 AddListsShardingKeyNotNullConstraint: migrated (0.0267s) ====

main: == [advisory_lock_connection] object_id: 134420, pg_backend_pid: 57322
ci: == [advisory_lock_connection] object_id: 134580, pg_backend_pid: 57324
ci: == 20250404100126 AddListsShardingKeyNotNullConstraint: migrating =============
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE lists\nADD CONSTRAINT check_6dadb82d36\nCHECK ( num_nonnulls(group_id, project_id) = 1 )\nNOT VALID;\n")
ci:    -> 0.0026s
ci: == 20250404100126 AddListsShardingKeyNotNullConstraint: migrated (0.0149s) ====

ci: == [advisory_lock_connection] object_id: 134580, pg_backend_pid: 57324
main: == [advisory_lock_connection] object_id: 134820, pg_backend_pid: 57327
main: == 20250404100407 AddListsProjectIdIndex: migrating ===========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0005s
main: -- index_exists?(:lists, :project_id, {:name=>"index_lists_on_project_id", :algorithm=>:concurrently})
main:    -> 0.0029s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:lists, :project_id, {:name=>"index_lists_on_project_id", :algorithm=>:concurrently})
main:    -> 0.0039s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20250404100407 AddListsProjectIdIndex: migrated (0.0216s) ==================

main: == [advisory_lock_connection] object_id: 134820, pg_backend_pid: 57327
ci: == [advisory_lock_connection] object_id: 134980, pg_backend_pid: 57329
ci: == 20250404100407 AddListsProjectIdIndex: migrating ===========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:lists, :project_id, {:name=>"index_lists_on_project_id", :algorithm=>:concurrently})
ci:    -> 0.0035s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:lists, :project_id, {:name=>"index_lists_on_project_id", :algorithm=>:concurrently})
ci:    -> 0.0035s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20250404100407 AddListsProjectIdIndex: migrated (0.0223s) ==================

ci: == [advisory_lock_connection] object_id: 134980, pg_backend_pid: 57329
main: == [advisory_lock_connection] object_id: 135180, pg_backend_pid: 57332
main: == 20250404114140 AddListsGroupIdIndex: migrating =============================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0007s
main: -- index_exists?(:lists, :group_id, {:name=>"index_lists_on_group_id", :algorithm=>:concurrently})
main:    -> 0.0027s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:lists, :group_id, {:name=>"index_lists_on_group_id", :algorithm=>:concurrently})
main:    -> 0.0018s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250404114140 AddListsGroupIdIndex: migrated (0.0144s) ====================

main: == [advisory_lock_connection] object_id: 135180, pg_backend_pid: 57332
ci: == [advisory_lock_connection] object_id: 135300, pg_backend_pid: 57334
ci: == 20250404114140 AddListsGroupIdIndex: migrating =============================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- index_exists?(:lists, :group_id, {:name=>"index_lists_on_group_id", :algorithm=>:concurrently})
ci:    -> 0.0035s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:lists, :group_id, {:name=>"index_lists_on_group_id", :algorithm=>:concurrently})
ci:    -> 0.0015s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250404114140 AddListsGroupIdIndex: migrated (0.0206s) ====================

ci: == [advisory_lock_connection] object_id: 135300, pg_backend_pid: 57334
main: == [advisory_lock_connection] object_id: 135500, pg_backend_pid: 57337
main: == 20250404114449 AddListsEnsureShardingKeyTrigger: migrating =================
main: -- execute("CREATE OR REPLACE FUNCTION ensure_lists_sharding_key()\nRETURNS TRIGGER AS\n$$\nBEGIN\nIF NEW.\"project_id\" IS NULL AND NEW.\"group_id\" IS NULL THEN\n  SELECT \"boards\".\"project_id\", \"boards\".\"group_id\"\n  INTO NEW.\"project_id\", NEW.\"group_id\"\n  FROM \"boards\"\n  WHERE \"boards\".\"id\" = NEW.\"board_id\";\nEND IF;\nRETURN NEW;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main:    -> 0.0042s
main: -- execute("CREATE TRIGGER trigger_ensure_lists_sharding_key\nBEFORE INSERT OR UPDATE ON lists\nFOR EACH ROW\n\nEXECUTE FUNCTION ensure_lists_sharding_key()\n")
main:    -> 0.0016s
main: == 20250404114449 AddListsEnsureShardingKeyTrigger: migrated (0.0094s) ========

main: == [advisory_lock_connection] object_id: 135500, pg_backend_pid: 57337
ci: == [advisory_lock_connection] object_id: 135660, pg_backend_pid: 57339
ci: == 20250404114449 AddListsEnsureShardingKeyTrigger: migrating =================
ci: -- execute("CREATE OR REPLACE FUNCTION ensure_lists_sharding_key()\nRETURNS TRIGGER AS\n$$\nBEGIN\nIF NEW.\"project_id\" IS NULL AND NEW.\"group_id\" IS NULL THEN\n  SELECT \"boards\".\"project_id\", \"boards\".\"group_id\"\n  INTO NEW.\"project_id\", NEW.\"group_id\"\n  FROM \"boards\"\n  WHERE \"boards\".\"id\" = NEW.\"board_id\";\nEND IF;\nRETURN NEW;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
ci:    -> 0.0031s
ci: -- execute("CREATE TRIGGER trigger_ensure_lists_sharding_key\nBEFORE INSERT OR UPDATE ON lists\nFOR EACH ROW\n\nEXECUTE FUNCTION ensure_lists_sharding_key()\n")
ci:    -> 0.0017s
ci: == 20250404114449 AddListsEnsureShardingKeyTrigger: migrated (0.0130s) ========

ci: == [advisory_lock_connection] object_id: 135660, pg_backend_pid: 57339
main: == [advisory_lock_connection] object_id: 135900, pg_backend_pid: 57342
main: == 20250404120803 AddListsProjectIdInvalidFk: migrating =======================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE projects, lists IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0004s
main: -- execute("ALTER TABLE lists ADD CONSTRAINT fk_67f2498cc9 FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0027s
main: == 20250404120803 AddListsProjectIdInvalidFk: migrated (0.0204s) ==============

main: == [advisory_lock_connection] object_id: 135900, pg_backend_pid: 57342
ci: == [advisory_lock_connection] object_id: 136100, pg_backend_pid: 57344
ci: == 20250404120803 AddListsProjectIdInvalidFk: migrating =======================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE projects, lists IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE lists ADD CONSTRAINT fk_67f2498cc9 FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0030s
ci: == 20250404120803 AddListsProjectIdInvalidFk: migrated (0.0238s) ==============

ci: == [advisory_lock_connection] object_id: 136100, pg_backend_pid: 57344
main: == [advisory_lock_connection] object_id: 136380, pg_backend_pid: 57349
main: == 20250404120839 AddListsGroupIdInvalidFk: migrating =========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE namespaces, lists IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0004s
main: -- execute("ALTER TABLE lists ADD CONSTRAINT fk_f8b2e8680c FOREIGN KEY (group_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0022s
main: == 20250404120839 AddListsGroupIdInvalidFk: migrated (0.0188s) ================

main: == [advisory_lock_connection] object_id: 136380, pg_backend_pid: 57349
ci: == [advisory_lock_connection] object_id: 136580, pg_backend_pid: 57351
ci: == 20250404120839 AddListsGroupIdInvalidFk: migrating =========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE namespaces, lists IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE lists ADD CONSTRAINT fk_f8b2e8680c FOREIGN KEY (group_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0028s
ci: == 20250404120839 AddListsGroupIdInvalidFk: migrated (0.0271s) ================

ci: == [advisory_lock_connection] object_id: 136580, pg_backend_pid: 57351
main: == [advisory_lock_connection] object_id: 136860, pg_backend_pid: 57354
main: == 20250404130722 QueueBackfillListsShardingKey: migrating ====================
main: == 20250404130722 QueueBackfillListsShardingKey: migrated (0.0450s) ===========

main: == [advisory_lock_connection] object_id: 136860, pg_backend_pid: 57354
ci: == [advisory_lock_connection] object_id: 137220, pg_backend_pid: 57356
ci: == 20250404130722 QueueBackfillListsShardingKey: migrating ====================
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_ci_cell_local, :gitlab_internal, :gitlab_shared].
ci: == 20250404130722 QueueBackfillListsShardingKey: migrated (0.0095s) ===========

ci: == [advisory_lock_connection] object_id: 137220, pg_backend_pid: 57356
DOWN 
main: == [advisory_lock_connection] object_id: 133580, pg_backend_pid: 56548
main: == 20250404130722 QueueBackfillListsShardingKey: reverting ====================
main: == 20250404130722 QueueBackfillListsShardingKey: reverted (0.0379s) ===========

main: == 20250404120839 AddListsGroupIdInvalidFk: reverting =========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:lists, {:column=>:group_id})
main:    -> 0.0033s
main: == 20250404120839 AddListsGroupIdInvalidFk: reverted (0.0127s) ================

main: == 20250404120803 AddListsProjectIdInvalidFk: reverting =======================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:lists, {:column=>:project_id})
main:    -> 0.0026s
main: == 20250404120803 AddListsProjectIdInvalidFk: reverted (0.0107s) ==============

main: == 20250404114449 AddListsEnsureShardingKeyTrigger: reverting =================
main: -- execute("DROP TRIGGER IF EXISTS trigger_ensure_lists_sharding_key ON lists")
main:    -> 0.0004s
main: -- execute("DROP FUNCTION IF EXISTS ensure_lists_sharding_key()")
main:    -> 0.0007s
main: == 20250404114449 AddListsEnsureShardingKeyTrigger: reverted (0.0039s) ========

main: == 20250404114140 AddListsGroupIdIndex: reverting =============================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:lists, :group_id, {:name=>"index_lists_on_group_id", :algorithm=>:concurrently})
main:    -> 0.0030s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:lists, {:name=>"index_lists_on_group_id", :algorithm=>:concurrently, :column=>:group_id})
main:    -> 0.0041s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20250404114140 AddListsGroupIdIndex: reverted (0.0213s) ====================

main: == 20250404100407 AddListsProjectIdIndex: reverting ===========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- index_exists?(:lists, :project_id, {:name=>"index_lists_on_project_id", :algorithm=>:concurrently})
main:    -> 0.0020s
main: -- remove_index(:lists, {:name=>"index_lists_on_project_id", :algorithm=>:concurrently, :column=>:project_id})
main:    -> 0.0032s
main: == 20250404100407 AddListsProjectIdIndex: reverted (0.0126s) ==================

main: == 20250404100126 AddListsShardingKeyNotNullConstraint: reverting =============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("            ALTER TABLE lists\n            DROP CONSTRAINT IF EXISTS check_6dadb82d36\n")
main:    -> 0.0005s
main: == 20250404100126 AddListsShardingKeyNotNullConstraint: reverted (0.0048s) ====

main: == [advisory_lock_connection] object_id: 133580, pg_backend_pid: 56548
ci: == [advisory_lock_connection] object_id: 134420, pg_backend_pid: 56576
ci: == 20250404130722 QueueBackfillListsShardingKey: reverting ====================
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_ci_cell_local, :gitlab_internal, :gitlab_shared].
ci: == 20250404130722 QueueBackfillListsShardingKey: reverted (0.0099s) ===========

ci: == 20250404120839 AddListsGroupIdInvalidFk: reverting =========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:lists, {:column=>:group_id})
ci:    -> 0.0033s
ci: == 20250404120839 AddListsGroupIdInvalidFk: reverted (0.0204s) ================

ci: == 20250404120803 AddListsProjectIdInvalidFk: reverting =======================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:lists, {:column=>:project_id})
ci:    -> 0.0026s
ci: == 20250404120803 AddListsProjectIdInvalidFk: reverted (0.0189s) ==============

ci: == 20250404114449 AddListsEnsureShardingKeyTrigger: reverting =================
ci: -- execute("DROP TRIGGER IF EXISTS trigger_ensure_lists_sharding_key ON lists")
ci:    -> 0.0006s
ci: -- execute("DROP FUNCTION IF EXISTS ensure_lists_sharding_key()")
ci:    -> 0.0007s
ci: == 20250404114449 AddListsEnsureShardingKeyTrigger: reverted (0.0218s) ========

ci: == 20250404114140 AddListsGroupIdIndex: reverting =============================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:lists, :group_id, {:name=>"index_lists_on_group_id", :algorithm=>:concurrently})
ci:    -> 0.0033s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:lists, {:name=>"index_lists_on_group_id", :algorithm=>:concurrently, :column=>:group_id})
ci:    -> 0.0041s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0007s
ci: == 20250404114140 AddListsGroupIdIndex: reverted (0.0244s) ====================

ci: == 20250404100407 AddListsProjectIdIndex: reverting ===========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:lists, :project_id, {:name=>"index_lists_on_project_id", :algorithm=>:concurrently})
ci:    -> 0.0033s
ci: -- remove_index(:lists, {:name=>"index_lists_on_project_id", :algorithm=>:concurrently, :column=>:project_id})
ci:    -> 0.0051s
ci: == 20250404100407 AddListsProjectIdIndex: reverted (0.0219s) ==================

ci: == 20250404100126 AddListsShardingKeyNotNullConstraint: reverting =============
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE lists\n            DROP CONSTRAINT IF EXISTS check_6dadb82d36\n")
ci:    -> 0.0012s
ci: == 20250404100126 AddListsShardingKeyNotNullConstraint: reverted (0.0194s) ====

ci: == [advisory_lock_connection] object_id: 134420, pg_backend_pid: 56576

main: == [advisory_lock_connection] object_id: 133560, pg_backend_pid: 56864
main: == 20250404095645 AddShardingKeyToListsTable: reverting =======================
main: -- remove_column(:lists, :project_id, :bigint)
main:    -> 0.0021s
main: -- remove_column(:lists, :group_id, :bigint)
main:    -> 0.0006s
main: == 20250404095645 AddShardingKeyToListsTable: reverted (0.0088s) ==============

main: == [advisory_lock_connection] object_id: 133560, pg_backend_pid: 56864
ci: == [advisory_lock_connection] object_id: 134060, pg_backend_pid: 56885
ci: == 20250404095645 AddShardingKeyToListsTable: reverting =======================
ci: -- remove_column(:lists, :project_id, :bigint)
ci:    -> 0.0012s
ci: -- remove_column(:lists, :group_id, :bigint)
ci:    -> 0.0085s
ci: == 20250404095645 AddShardingKeyToListsTable: reverted (0.0193s) ==============

ci: == [advisory_lock_connection] object_id: 134060, pg_backend_pid: 56885

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

Edited by Mario Celi

Merge request reports