1. Hypothetically, if your INT column already has 4 billion rows, how long is the process of converting that column to BIGINT? I know this will depend on server resources but I just want to have a general idea, say server has 8GB RAM 4CPU.

  2. If you are certain you will hit INT's limit at some point, although years away, does it make sense to go straight to BIGINT?

1 Answer 1

  1. It depends on the size of the table and the speed of the disk. Maybe an hour, maybe days. Check out pt-online-schema-change; it may be able to avoid most of the downtime.

  2. How often does a schema survive intact for more than a couple of years? Sure, do the math to figure out how soon the id will overflow. If there is even a small risk, go with a bigger size.

If you are talking about AUTO_INCREMENT, then be away that there are a number of ways in which code could skip numbers -- INSERT IGNORE, REPLACE, etc. Also, if you are involved with Master-Master or cluster replication.

If you already have replication set up, there may be a technique (using failover) to avoid more than a little downtime.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.