Migrating from legacy sequential IDs (INT, BIGINT) to UUIDs can feel like defusing a bomb — especially in a live production environment.
But here’s the good news: with careful planning and a gradual migration strategy, you can make the switch without downtime, data loss, or user disruption.
Let’s walk through a proven, zero-downtime migration plan — step-by-step.
🎯 Why Migrate to UUIDs?
Reasons for switching to UUIDs:
- Enable global uniqueness across services
- Prevent ID collisions in distributed systems
- Avoid leaking business information (e.g., order counts via auto-increment)
- Support future sharding or multi-region replication
But you don't want to:
- Lock your tables
- Break APIs
- Lose referential integrity
So here's how to migrate safely.
🧱 Step 1: Add a UUID Column
Add a new uuid column to your existing table, nullable at first.
ALTER TABLE users ADD COLUMN uuid UUID;Don’t drop your legacy id column — yet.
If your database supports it, generate UUIDs automatically for new rows:
PostgreSQL
ALTER TABLE users ALTER COLUMN uuid SET DEFAULT gen_random_uuid();> Make sure the pgcrypto or uuid-ossp extension is enabled.
MySQL (8.0+)
ALTER TABLE users ADD COLUMN uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID()));🔁 Step 2: Backfill Existing UUIDs
Populate UUIDs for existing rows.
In small batches:
UPDATE users
SET uuid = gen_random_uuid()
WHERE uuid IS NULL
LIMIT 1000;Repeat until every record has a UUID. You can script this with your application or a background worker.
✍️ Step 3: Write to Both Columns
Update your application to write both id and uuid for new records.
In your ORM:
user = User.create!(name: "Jane Doe", uuid: SecureRandom.uuid)In code, you can fallback to generating UUID if not supplied by DB:
import uuid
record = db.insert({"uuid": uuid.uuid4(), "name": "Alice"})✅ At this point, all new and existing records have UUIDs — but your app still relies on the legacy id.
🕵️ Step 4: Dual Reads and Joins
Update your app to support reading by either ID:
- API: allow both
/users/123and/users/uuid-abc... - DB: join using
uuidfor newer services,idfor older ones
You may need database views or helper functions for backward compatibility.
🔐 Step 5: Update Foreign Keys and Relationships
This is where many migrations get stuck. You must repeat the same steps for all related tables:
1. Add uuid columns to referencing tables
2. Backfill based on JOIN with legacy ID
3. Add foreign key constraints (optional during transition)
Example:
ALTER TABLE orders ADD COLUMN user_uuid UUID;
UPDATE orders
SET user_uuid = users.uuid
FROM users
WHERE orders.user_id = users.id;Once all joins and lookups are UUID-compatible, you're ready to cut over.
🔄 Step 6: Flip the Primary Key
Once:
- All new writes use UUIDs
- All reads and joins work on UUIDs
- All foreign keys are migrated
You can flip your schema.
ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users ADD PRIMARY KEY (uuid);Optionally drop the legacy ID:
ALTER TABLE users DROP COLUMN id;Or keep it around if legacy support is still required.
🚀 Step 7: Celebrate (Then Clean Up)
You've now migrated to UUIDs with:
- No downtime
- No broken joins
- No lost data
Next steps:
- Remove any old
id-based API routes - Normalize internal tooling to use UUIDs
- Monitor for unexpected joins or fallback behavior
🧠 Bonus Tips
- Use
uuidv7orULIDfor ordered UUIDs with better index performance - Compress UUIDs using
UUID_TO_BIN()in MySQL for smaller indexes - Keep dual write code in place during a soft launch — it’s great for rollback
- Don’t forget to update test fixtures and seed data
Final Thoughts
Migrating from legacy IDs to UUIDs doesn’t have to be scary.
By breaking it into safe, incremental steps, you can move at your own pace — without locking tables or waking up at 3am.
UUIDs offer a scalable, secure, and modern foundation for your systems. Migrating to them just requires a little care, a lot of coffee, and a solid plan.
📦 Migration: complete. Downtime: zero.