Growing beyond unsigned integer for the id field

Rails
1

I am creating an app that will out grow an int(10) unsigned for the id
field, ie. 4,294,967,295 records.

What are my options, I have looked at the GUID plugin that lets you use
a 32 char GUID, but I still want the id’s to be sequential, and (I
think) that the overhead of searching a varchar field will be too big
for such a large number of records.

It does not appear you can use a double as an autoincrement field in
MySQL. Maybe because it is a float? So that can not be used.

Im guessing there is some sort of pattern for this problem. My first
thought was to create a second id field and increment that every time
the first id field rolls over to zero.

To do this I would need to override the get/set methods for id in the
models I want to apply this to. Would that be the right way of going
about such a solution?

Many thanks, K.

2

Peter P. wrote:

I am creating an app that will out grow an int(10) unsigned for the id
field, ie. 4,294,967,295 records.

What are my options, I have looked at the GUID plugin that lets you use
a 32 char GUID, but I still want the id’s to be sequential, and (I
think) that the overhead of searching a varchar field will be too big
for such a large number of records.

It does not appear you can use a double as an autoincrement field in
MySQL. Maybe because it is a float? So that can not be used.
You can use a BIGINT. 63 bits should be enough for anybody :slight_smile:

If that’s not applicable, there’s some multi-column voodoo you can use
here:
http://dev.mysql.com/doc/refman/4.1/en/example-auto-increment.html
but don’t expect much help from ActiveRecord when dealing with it…

3

I could use BIGINT, but I can potentially outgrow that as well :slight_smile:
I essentially want the ability to have infinity records.
I will be doing archiving of data to cut down table size but I need to
know I have the capacity…

I was hoping for something I could impliment myself, so that it works
with any database, not something particular to any one database.

Many thanks, Peter.

Alex Y. wrote:

Peter P. wrote:

I am creating an app that will out grow an int(10) unsigned for the id
field, ie. 4,294,967,295 records.

What are my options, I have looked at the GUID plugin that lets you use
a 32 char GUID, but I still want the id’s to be sequential, and (I
think) that the overhead of searching a varchar field will be too big
for such a large number of records.

It does not appear you can use a double as an autoincrement field in
MySQL. Maybe because it is a float? So that can not be used.
You can use a BIGINT. 63 bits should be enough for anybody :slight_smile:

If that’s not applicable, there’s some multi-column voodoo you can use
here:
http://dev.mysql.com/doc/refman/4.1/en/example-auto-increment.html
but don’t expect much help from ActiveRecord when dealing with it…

4

Peter,

Whee, that should have read “Alex,” :slight_smile:

*m

5

Am 27.02.2006 um 10:42 schrieb Peter:

I could use BIGINT, but I can potentially outgrow that as well :slight_smile:
I essentially want the ability to have infinity records.
I will be doing archiving of data to cut down table size but I need to
know I have the capacity…

I was hoping for something I could impliment myself, so that it works
with any database, not something particular to any one database.

Peter,

did you do some caculation of what it would take to break BIGINT?

BIGINT ranges from 0 to 922,3372,036,854,775,807 let’s call that n.
Each BIGINT uses 8 bytes of space. So n * 8 is
147,573,952,589,676,412,920. So to store n BIGINTs you need
137,438,953,471 GiByte which is 15 Exabyte. And that’s without any
overhead or additional data.

If you wanted to go beyond that in the next 20 years (7300 days =
630,720,000 seconds), you would have to write n / 630,720,000 bytes
per second = 29,247,120,867 bytes per second = 27 GiB per second).

Really, good luck with this :slight_smile:

If you ever come into the danger of breaking BIGINT (or any
equivalent on any other database system), it will be in so many years
that our cars will fly and quantum computers are there to take your
BIGINT++.

Regards,

Manuel

6

Am 27.02.2006 um 11:01 schrieb Manuel H.:

Peter,

Whee, that should have read “Alex,” :slight_smile:

Well, maybe I should go and get some more coffee. Of course I meant
Peter. :wink:

*m

7

If you ever come into the danger of breaking BIGINT (or any
equivalent on any other database system), it will be in so many years
that our cars will fly and quantum computers are there to take your
BIGINT++.

Indeed. Adopt the KISS principle, Rails is about Agile development.

_Tony

8

Thanks for the reply, hope you got that coffee :wink:

I see the logic in your explanation of BigInt. Do most database support
8 byte unsigned integers? Or is it particular to MySQL.

Im sure I read somewhere that BaseCamp uses a custom writtern id
incrementer, how can they have out grown a BigInt?

Many thanks, P.

9

Anthony G. wrote:

Indeed. Adopt the KISS principle, Rails is about Agile development.

_Tony

Its only simple if you have less than 922,3372,036,854,775,807 records
:slight_smile:

10

Peter wrote:

Thanks for the reply, hope you got that coffee :wink:

I see the logic in your explanation of BigInt. Do most database support
8 byte unsigned integers? Or is it particular to MySQL.

Postgresql has a bigint.