What happens when I exhaust a bigint generated key? How to handle it?

by

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…

More Related Contents:

  1. How to filter SQL results in a has-many-through relation
  2. How to select the nth row in a SQL database table?
  3. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  4. Simple Random Samples from a Sql database
  5. SQL: Repeat a result row multiple times, and number the rows
  6. Why do results from a SQL query not come back in the order I expect?
  7. Best practices for SQL varchar column length [closed]
  8. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  9. Are SELECT type queries the only type that can be nested?
  10. how to cast the hexadecimal to varchar(datetime)?
  11. Postgres LEFT JOIN with WHERE condition
  12. how to delete data from table A besides the customer service and director data in table B?
  13. Get records with max value for each group of grouped SQL results
  14. How to populate a table with a range of dates?
  15. MYSQL OR vs IN performance
  16. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  17. MySQL: View with Subquery in the FROM Clause Limitation
  18. MySql export schema without data
  19. MySQL combine two columns into one column
  20. MySQL: LIMIT by a percentage of the amount of records?
  21. limiting the rows to where the sum a column equals a certain value in MySQL
  22. MySQL multiple columns in IN clause
  23. MySQL Inner Join Query Multiple Tables
  24. GROUP BY – do not group NULL
  25. MySQL 1062 – Duplicate entry ‘0’ for key ‘PRIMARY’
  26. MySQL LEFT JOIN 3 tables
  27. FIND_IN_SET with multiple value [duplicate]
  28. MySql using correct syntax for the over clause
  29. MySQL update statement to store ranking positions
  30. How to convert a varchar column type to date type without losing the dates

Leave a Comment