Im not recommending to use int/bigint as ids at all, cause you will get into the same problem when you will want to scale your DB. Better use uuid or other hash function that will guaranty uniqueness around all DBs instances.

Also if you want to use quick altering you should use async index update.

As for your problem i think you can fix this with such steps:
1) create second unique index
2) lock tables for updates
3) drop primary constrain on former index
4) rename new unique index to ‘id’ or how you name it
5) add primary constrain to new index

But this should be tested, cause i don’t know if this will be faster then data copy.

--

Machine Learning and Computer Vision Researcher. Founder LearnML.Today

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Andrey Nikishaev

Andrey Nikishaev

Machine Learning and Computer Vision Researcher. Founder LearnML.Today