Skip to content

WIP: Add a DB migration to remove all varchar limits

Gregory Stark requested to merge remove-all-varchar-limits into master

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

Edited by Gregory Stark

Merge request reports