1---
2stage: Enablement
3group: Database
4info: 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
5---
6
7# Swapping Tables
8
9Sometimes you need to replace one table with another. For example, when
10migrating data in a very large table it's often better to create a copy of the
11table and insert & migrate the data into this new table in the background.
12
13Let's say you want to swap the table "events" with "events_for_migration". In
14this case you need to follow 3 steps:
15
161. Rename "events" to "events_temporary"
171. Rename "events_for_migration" to "events"
181. Rename "events_temporary" to "events_for_migration"
19
20Rails allows you to do this using the `rename_table` method:
21
22```ruby
23rename_table :events, :events_temporary
24rename_table :events_for_migration, :events
25rename_table :events_temporary, :events_for_migration
26```
27
28This does not require any downtime as long as the 3 `rename_table` calls are
29executed in the _same_ database transaction. Rails by default uses database
30transactions for migrations, but if it doesn't you'll need to start one
31manually:
32
33```ruby
34Event.transaction do
35  rename_table :events, :events_temporary
36  rename_table :events_for_migration, :events
37  rename_table :events_temporary, :events_for_migration
38end
39```
40
41Once swapped you _have to_ reset the primary key of the new table. For
42PostgreSQL you can use the `reset_pk_sequence!` method like so:
43
44```ruby
45reset_pk_sequence!('events')
46```
47
48Failure to reset the primary keys will result in newly created rows starting
49with an ID value of 1. Depending on the existing data this can then lead to
50duplicate key constraints from popping up, preventing users from creating new
51data.
52