PostgreSQL's SERIAL (INT) primary keys risk integer overflow at 2.1 billion rows, but migrating to BIGINT costs virtually nothing due to 8-byte memory alignment. The article provides a complete zero-downtime migration strategy using an atomic swap technique: add a new BIGINT column, backfill data in batches with regular vacuuming, create indexes concurrently, handle foreign key dependencies with NOT VALID constraints, then perform a millisecond metadata swap in a single transaction. Includes production-tested code examples and troubleshooting guidance.
Table of contents
Cost difference between INT and BIGINTLive data type change in Postgres - the atomic swapConclusion26.5K Impressions