WIP: Add a DB migration to remove all varchar limits
Some varchar columns seem to have spurious length limits in some people's schema.rb. This may be due to earlier Rails migration code having different behaviours or perhaps from manual tweaking of schemas. There were only a couple columns where these length limits were in fact intended it seems and even those are better handled in the application (2000 character limit to urls and 64 character limit to encrypted passwords -- note, not byte but unicode character).
Reconcile these discrepancies by just removing all length limits from varchar column definitions.
Here's the results when I run it after manually adding a couple varchar(255) to my test database:
stark@tweedle:~/gitlab/gdk/gitlab-development-kit/gitlab$ bundle exec rake db:migrate
== 20170919220540 RemoveVarcharLimits: migrating ==============================
-- Removing unnecessary varchar limits on adapter: postgresql
-- columns("abuse_reports")
-> 0.0010s
-- columns("appearances")
-> 0.0009s
-- columns("application_settings")
-> 0.0042s
-- columns("audit_events")
-> 0.0013s
-- columns("award_emoji")
-> 0.0014s
-- columns("broadcast_messages")
-> 0.0014s
-- columns("chat_names")
-> 0.0009s
-- columns("ci_pipeline_variables")
-> 0.0007s
-- columns("ci_runners")
-> 0.0010s
-- columns("ci_variables")
-> 0.0009s
-- columns("ci_triggers")
-> 0.0010s
-- columns("conversational_development_index_metrics")
-> 0.0020s
-- columns("emails")
-> 0.0010s
-- columns("events")
-> 0.0009s
-- columns("environments")
-> 0.0009s
-- columns("deploy_keys_projects")
-> 0.0008s
-- columns("deployments")
-> 0.0010s
-- columns("feature_gates")
-> 0.0014s
-- columns("features")
-> 0.0009s
-- columns("issue_metrics")
-> 0.0009s
-- columns("identities")
-> 0.0008s
-- columns("gpg_keys")
-> 0.0009s
-- columns("issue_assignees")
-> 0.0007s
-- columns("keys")
-> 0.0014s
-- columns("label_links")
-> 0.0011s
-- columns("lfs_objects")
-> 0.0007s
-- columns("lfs_objects_projects")
-> 0.0007s
-- columns("members")
-> 0.0008s
-- columns("merge_request_diffs")
-> 0.0008s
-- columns("merge_request_metrics")
-> 0.0008s
-- columns("milestones")
-> 0.0008s
-- columns("merge_requests_closing_issues")
-> 0.0008s
-- columns("notification_settings")
-> 0.0010s
-- columns("oauth_access_tokens")
-> 0.0007s
-- columns("oauth_applications")
-> 0.0008s
-- columns("oauth_access_grants")
-> 0.0008s
-- columns("project_auto_devops")
-> 0.0007s
-- columns("project_features")
-> 0.0008s
-- columns("project_statistics")
-> 0.0008s
-- columns("personal_access_tokens")
-> 0.0008s
-- columns("project_authorizations")
-> 0.0006s
-- columns("project_group_links")
-> 0.0008s
-- columns("project_import_data")
-> 0.0007s
-- columns("protected_tag_create_access_levels")
-> 0.0007s
-- columns("protected_tags")
-> 0.0007s
-- columns("protected_branches")
-> 0.0007s
-- columns("redirect_routes")
-> 0.0007s
-- columns("routes")
-> 0.0008s
-- columns("sent_notifications")
-> 0.0007s
-- columns("snippets")
-> 0.0008s
-- columns("services")
-> 0.0011s
-- columns("spam_logs")
-> 0.0010s
-- columns("todos")
-> 0.0008s
-- columns("taggings")
-> 0.0007s
-- columns("tags")
-> 0.0007s
-- columns("timelogs")
-> 0.0007s
-- columns("uploads")
-> 0.0008s
-- Removing 1 leftover varchar limits from MySQL migration from table uploads
-- change_table("uploads")
-> 0.0021s
-- columns("user_agent_details")
-> 0.0009s
-- columns("user_synced_attributes_metadata")
-> 0.0008s
-- columns("u2f_registrations")
-> 0.0008s
-- columns("web_hooks")
-> 0.0011s
-- Removing 1 leftover varchar limits from MySQL migration from table web_hooks
-- change_table("web_hooks")
-> 0.0020s
-- columns("projects")
-> 0.0017s
-- columns("forked_project_links")
-> 0.0009s
-- columns("chat_teams")
-> 0.0011s
-- columns("ci_pipelines")
-> 0.0011s
-- columns("ci_builds")
-> 0.0014s
-- columns("ci_stages")
-> 0.0009s
-- columns("container_repositories")
-> 0.0009s
-- columns("labels")
-> 0.0012s
-- columns("ci_group_variables")
-> 0.0016s
-- columns("ci_pipeline_schedule_variables")
-> 0.0008s
-- columns("issues")
-> 0.0012s
-- columns("ci_pipeline_schedules")
-> 0.0010s
-- columns("ci_runner_projects")
-> 0.0008s
-- columns("ci_trigger_requests")
-> 0.0009s
-- columns("boards")
-> 0.0010s
-- columns("users")
-> 0.0028s
-- columns("gpg_signatures")
-> 0.0016s
-- columns("namespaces")
-> 0.0016s
-- columns("users_star_projects")
-> 0.0011s
-- columns("web_hook_logs")
-> 0.0010s
-- columns("label_priorities")
-> 0.0009s
-- columns("lists")
-> 0.0009s
-- columns("merge_request_diff_commits")
-> 0.0008s
-- columns("merge_request_diff_files")
-> 0.0009s
-- columns("oauth_openid_requests")
-> 0.0008s
-- columns("pages_domains")
-> 0.0008s
-- columns("protected_branch_merge_access_levels")
-> 0.0008s
-- columns("protected_branch_push_access_levels")
-> 0.0009s
-- columns("push_event_payloads")
-> 0.0009s
-- columns("releases")
-> 0.0009s
-- columns("subscriptions")
-> 0.0009s
-- columns("notes")
-> 0.0015s
-- columns("system_note_metadata")
-> 0.0013s
-- columns("merge_requests")
-> 0.0020s
-- columns("trending_projects")
-> 0.0010s
== 20170919220540 RemoveVarcharLimits: migrated (0.1096s) =====================
Database Checklist
When adding migrations:
-
Updated db/schema.rb
-
Added a down
method so the migration can be reverted -
Added the output of the migration(s) to the MR body -
Added the execution time of the migration(s) to the MR body -
Added tests for the migration in spec/migrations
if necessary (e.g. when migrating data) -
Made sure the migration won't interfere with a running GitLab cluster, for example by disabling transactions for long running migrations
When adding or modifying queries to improve performance:
-
Included the raw SQL queries of the relevant queries -
Included the output of EXPLAIN ANALYZE
and execution timings of the relevant queries -
Added tests for the relevant changes
When adding indexes:
-
Described the need for these indexes in the MR body -
Made sure existing indexes can not be reused instead
When adding foreign keys to existing tables:
-
Included a migration to remove orphaned rows in the source table -
Removed any instances of dependent: ...
that may no longer be necessary
When adding tables:
-
Ordered columns based on their type sizes in descending order -
Added foreign keys if necessary -
Added indexes if necessary
When removing columns, tables, indexes or other structures:
-
Removed these in a post-deployment migration -
Made sure the application no longer uses (or ignores) these structures
General Checklist
-
Changelog entry added, if necessary -
Documentation created/updated -
API support added -
Tests added for this feature/bug - Review
-
Has been reviewed by UX -
Has been reviewed by Frontend -
Has been reviewed by Backend -
Has been reviewed by Database
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Squashed related commits together