diff options
author | GitLab Bot <gitlab-bot@gitlab.com> | 2022-08-18 11:17:02 +0300 |
---|---|---|
committer | GitLab Bot <gitlab-bot@gitlab.com> | 2022-08-18 11:17:02 +0300 |
commit | b39512ed755239198a9c294b6a45e65c05900235 (patch) | |
tree | d234a3efade1de67c46b9e5a38ce813627726aa7 /doc/development/database/swapping_tables.md | |
parent | d31474cf3b17ece37939d20082b07f6657cc79a9 (diff) |
Add latest changes from gitlab-org/gitlab@15-3-stable-eev15.3.0-rc42
Diffstat (limited to 'doc/development/database/swapping_tables.md')
-rw-r--r-- | doc/development/database/swapping_tables.md | 51 |
1 files changed, 51 insertions, 0 deletions
diff --git a/doc/development/database/swapping_tables.md b/doc/development/database/swapping_tables.md new file mode 100644 index 00000000000..efb481ccf35 --- /dev/null +++ b/doc/development/database/swapping_tables.md @@ -0,0 +1,51 @@ +--- +stage: Data Stores +group: Database +info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#assignments +--- + +# Swapping Tables + +Sometimes you need to replace one table with another. For example, when +migrating data in a very large table it's often better to create a copy of the +table and insert & migrate the data into this new table in the background. + +Let's say you want to swap the table `events` with `events_for_migration`. In +this case you need to follow 3 steps: + +1. Rename `events` to `events_temporary` +1. Rename `events_for_migration` to `events` +1. Rename `events_temporary` to `events_for_migration` + +Rails allows you to do this using the `rename_table` method: + +```ruby +rename_table :events, :events_temporary +rename_table :events_for_migration, :events +rename_table :events_temporary, :events_for_migration +``` + +This does not require any downtime as long as the 3 `rename_table` calls are +executed in the _same_ database transaction. Rails by default uses database +transactions for migrations, but if it doesn't you need to start one +manually: + +```ruby +Event.transaction do + rename_table :events, :events_temporary + rename_table :events_for_migration, :events + rename_table :events_temporary, :events_for_migration +end +``` + +Once swapped you _have to_ reset the primary key of the new table. For +PostgreSQL you can use the `reset_pk_sequence!` method like so: + +```ruby +reset_pk_sequence!('events') +``` + +Failure to reset the primary keys results in newly created rows starting +with an ID value of 1. Depending on the existing data this can then lead to +duplicate key constraints from popping up, preventing users from creating new +data. |