23

I can't imagine for myself a good answer for this, so I thought of asking it here. In my mind, I'm always wondering what will happen if the AUTO INCREMENT PRIMARY ID column in my MySQL table exhausted?

Say for example, I have a table which has two columns. An ID (auto increment, primary, BIGINT unsigned) and DESC (VARCHAR 255). I know for sure BIGINT is a lot, but it can reach its limit. How do I handle the scenario wherein in case the ID reach its limit? Do I need another server? If then how can I sync it? Is this the right way? Any insights friends.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
fishcracker
  • 2,401
  • 5
  • 23
  • 28
  • 2
    is it just a matter of curiosity that you're wondering this? If so, it shouldn't be that tough to write some code to test yourself. If you're worried about a large amount of records and you want to avoid this issue (if it is an issue), then don't use numbers at all and move to uniqueidentifiers. – Phil Oct 30 '12 at 05:13
  • Quite curiousity. I'm used to build some websites but not exactly generated a lot of traffic so I can't see for myself reaching the limit. You're right I'm worried about that large amount of records. Seeing uniqueidentifiers, are you pertaining to UUID? – fishcracker Oct 30 '12 at 05:19
  • 2
    Is this a real question? Won't you hit all kinds of real problems loooong before you hit *9 Quintillion records*? – Conrad Frix Oct 30 '12 at 05:23
  • yes i am, i often use them to avoid similar issues – Phil Oct 30 '12 at 05:23

4 Answers4

60

It won't run out.

The max bigint is 9223372036854775807 . At 1000 inserts/second that's 106751991167 days worth. Almost 300 million years, if my maths is right.

Even if you partition it out, using offsets where say 100 servers each have a dedicated sub-range of the values (x*100+0 ... x*100+99), you're not going to run out. 10,000 machines doing 100,000 inserts/second might get you there in about three centuries. Of course, that's more transactions per second than the New York Stock Exchange for hundreds of years solid...

If you do exceed the data type size limit of the generated key, new inserts will fail. In PostgreSQL (since you've tagged this PostgreSQL) with a bigserial you'll see:

CREATE TABLE bigserialtest ( id bigserial primary key, dummy text );
SELECT setval('bigserialtest_id_seq', 9223372036854775807);
INSERT INTO bigserialtest ( dummy ) VALUES ('spam');

ERROR:  nextval: reached maximum value of sequence "bigserialtest_id_seq" (9223372036854775807)

For an ordinary serial you'll get a different error, because the sequence is always 64-bit, so you'll reach the point where you have to change the key type to bigint or get an error like:

regress=# SELECT setval('serialtest_id_seq', 2147483647);
regress=# INSERT INTO serialtest (dummy) VALUES ('ham');
ERROR:  integer out of range

If you truly believe that it's possible for your site to reach the limit on a bigint in your application, you could use a composite key - say (shard_id, subkey) - or a uuid key.

Trying to deal with this in a new application is premature optimization. Seriously, from a new application to that kind of growth, will you be using the same schema? Or database engine? Or even codebase?

You might as well worry about GUID collisions in GUID keyed systems. After all, the birthday paradox means that GUID collisions are more likely than you think - at incredibly, insanely unlikely.

Furthermore, as Barry Brown points out in the comments, you'll never store that much data. This is only a concern for high churn tables with insanely high transaction rates. In those tables, the application just needs to be capable of coping with the key being reset to zero, entries renumbered, or other coping strategies. Honestly, though, even a high traffic message queue table isn't going to top out.

See:

Seriously, even if you build the next Gootwitfacegram, this won't be a problem until way past the use-by date of your third application rewrite...

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • but you can manually insert one record with id=9223372036854775807 and see what happens next. – lqs Oct 30 '12 at 05:16
  • 3
    The question isn't if it runs out, the question is how do you design tables to avoid an arbitrary limit on an incremental primary key. – Burhan Khalid Oct 30 '12 at 05:17
  • @BurhanKhalid If you truly believe that it's possible for your site to reach the limit on a bigint, you could use a composite key - say `(shard_id, subkey)` - or a uuid key. – Craig Ringer Oct 30 '12 at 05:21
  • 1
    Now what if you need more than 100 servers? You aren't solving the problem, you are just kicking the can down the road. – Burhan Khalid Oct 30 '12 at 05:24
  • 1
    @BurhanKhalid I call premature optimization. Do you really think you'll be using the same schema going from a new application to that kind of growth? Or even the same codebase? – Craig Ringer Oct 30 '12 at 05:27
  • 14
    Those 9223372036854775807 bigints will take 67,108,863 terabytes to store. Got that much disk space? – Barry Brown Oct 30 '12 at 05:27
  • Interesting lol That was exactly the question i had.. so thinking of reaching a BigInt value is out of place after seeing this answer. – Rushino Apr 25 '13 at 16:18
  • 1
    @BarryBrown It's more of a legitimate concern when there's an extremely high *churn rate* on the table. I certainly see reports of normal `SERIAL` columns (where the storage data type is `integer` not `bigint`, even though the sequence is still `bigint`) hit MAXINT. – Craig Ringer Apr 25 '13 at 23:31
  • 6
    @BurhanKhalid Absolutely everything is kicking the can down the road, the question is how far do you need to kick it? File systems have size limits (though some of those limits are exabytes), OSes have limits on how many file systems they can mount, disks they can address, etc. The clock has an upper limit on the time it can represent. The sane approach is to kick it far enough that other limitations come into effect first, then stop caring. Trying to come up with the perfect solution to keep your app running unchanged in 10,000 years makes very little sense. – Craig Ringer Apr 25 '13 at 23:34
15

Big int is 2^63 or approximately 10^19. Database benchmarks used to be all the rage a few years ago, using a standardised TPC-C Benchmark

As you can see the fastest relational score of 30,000,000 (3x10^7 transactions per minute) for a relational database. Keep in mind that profile will include a lot of reads, and it is very unlikely that same system can write 30,000,000 rows per minute.

Assuming it is though, you will need approx 3x10^11 minutes to exhaust BigInt. In time measurement we'd understand, that's something like 6 million years

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

If you do run out, you'll get the above error message, and move across to Guid for primary key. 2^128, there are less digital bits on earth than that number (by a factor of quadrillion).

M Afifi
  • 4,645
  • 2
  • 28
  • 48
  • At a minimum, wise to use bigint instead of int during scheme design. Looks like this action will suffice for most practical scenarios! – user9944315 Oct 19 '20 at 12:17
2

Large data sets don't use incrementing numbers as keys. Not only because you have an implicit upper limit, but it also creates problems when you have multiple servers; because you run the risk of having duplicate primary keys since they are incremental.

When you reach this limit on MySQL, you'll get a cryptic error like this:

Error: Duplicate entry '0' for key 1

Its better to use a unique id or some other sequence that you generate. MySQL doesn't support sequences, but postgresql does.

Burhan Khalid
  • 169,990
  • 18
  • 245
  • 284
2

Once Autoincrement hit the limit for the field size, INSERTs will generate an error.

In practical you will get the following type of error:

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

For more info visit:

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

Anam
  • 11,999
  • 9
  • 49
  • 63