summaryrefslogtreecommitdiff
path: root/doc/development
diff options
context:
space:
mode:
authorYorick Peterse <yorickpeterse@gmail.com>2017-09-11 14:55:14 +0200
committerYorick Peterse <yorickpeterse@gmail.com>2017-09-11 14:55:14 +0200
commit1d4cc240123c0247b864afa62385a69e1565fd28 (patch)
tree053a2b58fd66513ebf3b7c066d18a364a178acde /doc/development
parent5cc140b202a0fc9bf2056b481c977bbcf9255919 (diff)
downloadgitlab-ce-1d4cc240123c0247b864afa62385a69e1565fd28.tar.gz
Document how to swap database tables.docs/document-swapping-tables
Diffstat (limited to 'doc/development')
-rw-r--r--doc/development/README.md1
-rw-r--r--doc/development/swapping_tables.md53
2 files changed, 54 insertions, 0 deletions
diff --git a/doc/development/README.md b/doc/development/README.md
index dd150421b65..eeff14819da 100644
--- a/doc/development/README.md
+++ b/doc/development/README.md
@@ -60,6 +60,7 @@
- [Ordering Table Columns](ordering_table_columns.md)
- [Verifying Database Capabilities](verifying_database_capabilities.md)
- [Hash Indexes](hash_indexes.md)
+- [Swapping Tables](swapping_tables.md)
## i18n
diff --git a/doc/development/swapping_tables.md b/doc/development/swapping_tables.md
new file mode 100644
index 00000000000..6b990ece72c
--- /dev/null
+++ b/doc/development/swapping_tables.md
@@ -0,0 +1,53 @@
+# 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"
+2. Rename "events_for_migration" to "events"
+3. 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'll 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')
+```
+
+For MySQL however you need to do run the following:
+
+```ruby
+amount = Event.pluck('COALESCE(MAX(id), 1)').first
+
+execute "ALTER TABLE events AUTO_INCREMENT = #{amount}"
+```
+
+Failure to reset the primary keys will result 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.