PostgreSQL: Migrating from INT to BIGINT

postgres-migrating-from-int-to-bigint.md

PostgreSQL: Migrating from int to bigint

Resources

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 existing int column until it is time to swap them out.

The Integer at the End of the Universe: Integer Overflow in Postgres

• 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 called new_id and swap it out with the existing id column. This approach does not require a trigger.

Advanced int to bigint conversions (youtube video)

Altering a Postgres Column with Minimal Downtime

Column migration from INT to BIGINT in PostgreSQL

Notes

• When switching from int to bigint on a primary key column, you'll also need to update any foreign key columns that reference that id making them bigint as well.

Other

• A case for using bigint by default