PostgreSQL zero-downtime migration of a primary key from int to bigint (with Ruby on Rails specific notes)
- This approach requires setting up a PostgreSQL trigger to keep the mirrored
bigint
column in sync with the existingint
column until it is time to swap them out.
- This includes a handy query that you can run against your database to identify columns at risk to int overflow.
- One approach (quick fix, not long-term solution) suggested in this article is to switch to negative integers and auto-decrement the primary key sequence.
- The main approach suggested in this article is to create a secondary
bigint
column callednew_id
and swap it out with the existingid
column. This approach does not require a trigger.
- When switching from
int
tobigint
on a primary key column, you'll also need to update any foreign key columns that reference thatid
making thembigint
as well.