Changing a Primary Key Type from INT to UUID

The Growth Threshold and the Decision to Migrate

Our central transactional table was rapidly approaching the maximum limit of a signed 32-bit integer (2,147,483,647). Auto-incrementing IDs had served us well for years, but rapid growth pushed us to reconsider our strategy. Furthermore, our microservices architecture needed to generate IDs offline without round-trips to the central database. After comparing BigInt and UUID, we chose UUIDv4 to eliminate ID predictability and secure resource references.

The Dual-Write Strategy and Migration Plan

Altering a primary key in place on a high-throughput table locks the table, causing immediate service degradation. Instead, we executed a multi-stage rollout. First, we added a nullable new_uuid column of type UUID. Second, we modified the application layer to write to both columns while reading from the old INT ID. Third, we backfilled the existing records in batches, generating UUIDs retroactively.

-- Step 1: Add new UUID column
ALTER TABLE users ADD COLUMN new_uuid UUID DEFAULT NULL;

-- Step 2: Backfill in batches to avoid locking
UPDATE users 
SET new_uuid = gen_random_uuid() 
WHERE id >= 1000000 AND id < 1010000 AND new_uuid IS NULL;

-- Step 3: Add NOT NULL constraint and default after backfill
ALTER TABLE users ALTER COLUMN new_uuid SET NOT NULL;

Switchover and Integrity Check

After backfilling all 400 million rows, we verified that every single record had a unique UUID. The final and most critical phase involved making the UUID column the new primary key. We created a unique index concurrently on new_uuid, swapped the references in secondary foreign key tables, and finally updated the primary key constraint during a brief maintenance window.

• Prepared foreign keys concurrently to prevent long-duration table locks.
• Configured a temporary fallback mechanism in the API to resolve both ID types.
• Saved over 12 hours of potential downtime by avoiding direct in-place casting.