Is there any reason to avoid using bigint for a surrogate primary key?

Question

I am designing a database and I have a number of tables that could potentially outgrow the maximum size of a standard 32-bit int. However, it will likely be years before this happens and there is no guarantee that it will ever even actually happen.

However, given that there is a chance it could happen, should I go ahead and choose bigint for the primary key? What are the implications of doing it now vs changing it later? Is it even possible to convert an int primary key to a bigint later on, and if so, how difficult is it and is it feasible?

Answer 1

Going BIG will cost you on storage and performance - especially if it means your foreign key references also have to be BIGINT.

Looking "years" ahead isn't necessarily a prudent thing to do. Most (not all) IT projects are expected to recover their costs within 3 years. You will most likely have to contend with plenty of changes and upgrades over the years and if your database has grown so much in that time then it shouldn't be so much effort to change an INT to a BIGINT if and when you need to. By then maybe your business and the database world in general will have moved on and it won't be an issue any more. YAGNI rules.