Skip to content

Create NamespaceDetails table

Serena Fang requested to merge create-namespace-details-table into master

What does this MR do and why?

Issue: #358141 (closed)

The namespaces table is very wide and used in many places. Sometimes it contributes to slow performance. Removing all the columns relating to the description of the group from the namespaces table and moving them to a description-related table would make the namespaces table slimmer and more performant.

In this MR, we create the NamespaceDetails table and a trigger that fills NamespaceDetails when there is an update or insert on Namespace. In a future MR (!86506 (closed)) we will backfill NamespaceDetails with existing namespaces' description, description_html, and cached_markdown_version from the Namespaces table.

Migration output:

% rails db:migrate
main: == 20220316022505 CreateNamespaceDetails: migrating ===========================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- create_table(:namespace_details, {:id=>false})
main: -- quote_column_name(:description)
main:    -> 0.0000s
main: -- quote_column_name(:description_html)
main:    -> 0.0000s
main:    -> 0.0488s
main: == 20220316022505 CreateNamespaceDetails: migrated (0.0592s) ==================

main: == 20220506154054 CreateSyncNamespaceDetailsTrigger: migrating ================
main: -- execute("CREATE OR REPLACE FUNCTION update_namespace_details_from_namespaces()\nRETURNS TRIGGER AS\n$$\nBEGIN\nINSERT INTO\n  namespace_details (\n    description,\n    description_html,\n    cached_markdown_version,\n    updated_at,\n    created_at,\n    namespace_id\n  )\nVALUES\n  (\n    NEW.description,\n    NEW.description_html,\n    NEW.cached_markdown_version,\n    NEW.updated_at,\n    NEW.updated_at,\n    NEW.id\n  ) ON CONFLICT (namespace_id) DO\nUPDATE\nSET\n  description = NEW.description,\n  description_html = NEW.description_html,\n  cached_markdown_version = NEW.cached_markdown_version,\n  updated_at = NEW.updated_at\nWHERE\n  namespace_details.namespace_id = NEW.id;RETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main:    -> 0.0063s
main: -- execute("CREATE TRIGGER trigger_update_details_on_namespace_update\nAFTER UPDATE ON namespaces\nFOR EACH ROW\nWHEN (\n  OLD.description IS DISTINCT FROM NEW.description OR\n  OLD.description_html IS DISTINCT FROM NEW.description_html OR\n  OLD.cached_markdown_version IS DISTINCT FROM NEW.cached_markdown_version\n)\nEXECUTE PROCEDURE update_namespace_details_from_namespaces();\n")
main:    -> 0.0014s
main: -- execute("CREATE TRIGGER trigger_update_details_on_namespace_insert\nAFTER INSERT ON namespaces\nFOR EACH ROW\nEXECUTE PROCEDURE update_namespace_details_from_namespaces();\n")
main:    -> 0.0007s
main: == 20220506154054 CreateSyncNamespaceDetailsTrigger: migrated (0.0100s) =======

main: == 20220524184149 CreateSyncProjectNamespaceDetailsTrigger: migrating =========
main: -- execute("CREATE OR REPLACE FUNCTION update_namespace_details_from_projects()\nRETURNS TRIGGER AS\n$$\nBEGIN\nINSERT INTO\n  namespace_details (\n    description,\n    description_html,\n    cached_markdown_version,\n    updated_at,\n    created_at,\n    namespace_id\n  )\nVALUES\n  (\n    NEW.description,\n    NEW.description_html,\n    NEW.cached_markdown_version,\n    NEW.updated_at,\n    NEW.updated_at,\n    NEW.project_namespace_id\n  ) ON CONFLICT (namespace_id) DO\nUPDATE\nSET\n  description = NEW.description,\n  description_html = NEW.description_html,\n  cached_markdown_version = NEW.cached_markdown_version,\n  updated_at = NEW.updated_at\nWHERE\n  namespace_details.namespace_id = NEW.project_namespace_id;RETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main:    -> 0.0013s
main: -- execute("CREATE TRIGGER trigger_update_details_on_project_update\nAFTER UPDATE ON projects\nFOR EACH ROW\nWHEN (\n  OLD.description IS DISTINCT FROM NEW.description OR\n  OLD.description_html IS DISTINCT FROM NEW.description_html OR\n  OLD.cached_markdown_version IS DISTINCT FROM NEW.cached_markdown_version\n)\nEXECUTE PROCEDURE update_namespace_details_from_projects();\n")
main:    -> 0.0039s
main: -- execute("CREATE TRIGGER trigger_update_details_on_project_insert\nAFTER INSERT ON projects\nFOR EACH ROW\nEXECUTE PROCEDURE update_namespace_details_from_projects();\n")
main:    -> 0.0008s
main: == 20220524184149 CreateSyncProjectNamespaceDetailsTrigger: migrated (0.0063s) 

ci: == 20220524184149 CreateSyncProjectNamespaceDetailsTrigger: migrating =========
ci: -- execute("CREATE OR REPLACE FUNCTION update_namespace_details_from_projects()\nRETURNS TRIGGER AS\n$$\nBEGIN\nINSERT INTO\n  namespace_details (\n    description,\n    description_html,\n    cached_markdown_version,\n    updated_at,\n    created_at,\n    namespace_id\n  )\nVALUES\n  (\n    NEW.description,\n    NEW.description_html,\n    NEW.cached_markdown_version,\n    NEW.updated_at,\n    NEW.updated_at,\n    NEW.project_namespace_id\n  ) ON CONFLICT (namespace_id) DO\nUPDATE\nSET\n  description = NEW.description,\n  description_html = NEW.description_html,\n  cached_markdown_version = NEW.cached_markdown_version,\n  updated_at = NEW.updated_at\nWHERE\n  namespace_details.namespace_id = NEW.project_namespace_id;RETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
ci:    -> 0.0053s
ci: -- execute("CREATE TRIGGER trigger_update_details_on_project_update\nAFTER UPDATE ON projects\nFOR EACH ROW\nWHEN (\n  OLD.description IS DISTINCT FROM NEW.description OR\n  OLD.description_html IS DISTINCT FROM NEW.description_html OR\n  OLD.cached_markdown_version IS DISTINCT FROM NEW.cached_markdown_version\n)\nEXECUTE PROCEDURE update_namespace_details_from_projects();\n")
ci:    -> 0.0103s
ci: -- execute("CREATE TRIGGER trigger_update_details_on_project_insert\nAFTER INSERT ON projects\nFOR EACH ROW\nEXECUTE PROCEDURE update_namespace_details_from_projects();\n")
ci:    -> 0.0011s
ci: == 20220524184149 CreateSyncProjectNamespaceDetailsTrigger: migrated (0.0170s)

Rollback:

% rails db:migrate:down:main VERSION=20220524184149                                                             
main: == 20220524184149 CreateSyncProjectNamespaceDetailsTrigger: reverting =========
main: -- execute("DROP TRIGGER IF EXISTS trigger_update_details_on_project_update ON projects")
main:    -> 0.0117s
main: -- execute("DROP TRIGGER IF EXISTS trigger_update_details_on_project_insert ON projects")
main:    -> 0.0010s
main: -- execute("DROP FUNCTION IF EXISTS update_namespace_details_from_projects()")
main:    -> 0.0015s
main: == 20220524184149 CreateSyncProjectNamespaceDetailsTrigger: reverted (0.0163s) 

% rails db:migrate:down:main VERSION=20220506154054                                                     
main: == 20220506154054 CreateSyncNamespaceDetailsTrigger: reverting ================
main: -- execute("DROP TRIGGER IF EXISTS trigger_update_details_on_namespace_update ON namespaces")
main:    -> 0.0044s
main: -- execute("DROP TRIGGER IF EXISTS trigger_update_details_on_namespace_insert ON namespaces")
main:    -> 0.0056s
main: -- execute("DROP FUNCTION IF EXISTS update_namespace_details_from_namespaces()")
main:    -> 0.0015s
main: == 20220506154054 CreateSyncNamespaceDetailsTrigger: reverted (0.0132s) =======

% rails db:migrate:down:main VERSION=20220316022505                                   
main: == 20220316022505 CreateNamespaceDetails: reverting ===========================
main: -- drop_table(:namespace_details)
main:    -> 0.0113s
main: == 20220316022505 CreateNamespaceDetails: reverted (0.0114s) ==================

% rails db:migrate:down:ci VERSION=20220524184149
ci: == 20220524184149 CreateSyncProjectNamespaceDetailsTrigger: reverting =========
ci: -- execute("DROP TRIGGER IF EXISTS trigger_update_details_on_project_update ON projects")
ci:    -> 0.0036s
ci: -- execute("DROP TRIGGER IF EXISTS trigger_update_details_on_project_insert ON projects")
ci:    -> 0.0006s
ci: -- execute("DROP FUNCTION IF EXISTS update_namespace_details_from_projects()")
ci:    -> 0.0008s
ci: == 20220524184149 CreateSyncProjectNamespaceDetailsTrigger: reverted (0.0067s) 

% rails db:migrate:down:ci VERSION=20220506154054
ci: == 20220506154054 CreateSyncNamespaceDetailsTrigger: reverting ================
ci: -- execute("DROP TRIGGER IF EXISTS trigger_update_details_on_namespace_update ON namespaces")
ci:    -> 0.0038s
ci: -- execute("DROP TRIGGER IF EXISTS trigger_update_details_on_namespace_insert ON namespaces")
ci:    -> 0.0007s
ci: -- execute("DROP FUNCTION IF EXISTS update_namespace_details_from_namespaces()")
ci:    -> 0.0006s
ci: == 20220506154054 CreateSyncNamespaceDetailsTrigger: reverted (0.0067s) =======

% rails db:migrate:down:ci VERSION=20220316022505
ci: == 20220316022505 CreateNamespaceDetails: reverting ===========================
ci: -- drop_table(:namespace_details)
ci:    -> 0.0126s
ci: == 20220316022505 CreateNamespaceDetails: reverted (0.0127s) ==================

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Alper Akgun

Merge request reports