Skip to content

Rename table without downtime helper

Adam Hegyi requested to merge rename-table-without-downtime into master

What does this MR do?

This MR adds a few new database migration helper methods to rename a table without downtime, in one release.

Contributes to #121607 (closed).

How does it work?

Renaming a table currently requires downtime, since two versions (M, M-1) of the app will access the same database. If the table is renamed, the old version of the app will raise errors because the old table is no longer there. The solution in this MR leverages database views. In PostgreSQL database views "behave" like a table so they proxy back changes to the underlying relation (INSERT, UPDATE, DELETE works).

Known issues

When using rename_table, rails will try renaming the indexes where the index name is generated:

This can cause issues in the old version of the app if .insert_all is used with the old index name.

Workaround: Do not use the built in .rename_table helper, provide additional helpers for renaming the indexes.

Testing

Rename projects and run the tests

I renamed the projects table, however I did not change table_name. The Project model will use the view.

== 20210217093000 ProjectsTest: migrating =====================================
-- rename_table(:projects, :renamed_projects)
   -> 0.0218s
-- execute("CREATE VIEW projects AS SELECT * FROM renamed_projects")
   -> 0.0022s
-- execute("INSERT INTO renamed_tables (old_name, new_name) VALUES ('projects', 'renamed_projects')")
   -> 0.0004s
== 20210217093000 ProjectsTest: migrated (0.0276s) ============================

Before running the tests, I create the renamed_tables record manually and clear the column cache.

Result: tests are passing.

I was also able to boot up the application and things worked as expected.

Rename projects on a replica

== 20210217093000 ProjectsTest: migrating =====================================
-- rename_table(:projects, :renamed_projects)
   -> 12.5183s
-- execute("CREATE VIEW projects AS SELECT * FROM renamed_projects")
   -> 0.1787s
-- execute("INSERT INTO renamed_tables (old_name, new_name) VALUES ('projects', 'renamed_projects')")
   -> 0.1651s
== 20210217093000 ProjectsTest: migrated (13.6895s) ===========================

rename_table runs for quite a while. I suppose altering metadata for constraints and indexes took some time.

Projects in namespace query (recursive), index is picked up nicely: https://explain.depesz.com/s/FM0w

Bulk insert with unique_by + index name:

project = Project.find_by_full_path("gitlab-org/gitlab")
Project.insert_all([{name: 'other project name', id: project.id, created_at: Time.now, updated_at: Time.now, namespace_id: 9970, archived: false }], unique_by: :index_projects_on_id_partial_for_visibility)

# if a bogus index name is passed:

Project.insert_all([{name: 'other project name', id: project.id, created_at: Time.now, updated_at: Time.now, namespace_id: 9970, archived: false }], unique_by: :unknown)
ArgumentError: No unique index found for unknown

Interesting find: the primary key index is not discovered by Rails: ActiveRecord::Base.connection.schema_cache.indexes('issues')

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Adam Hegyi

Merge request reports