Rails - Feb 2006 - Growing beyond unsigned integer for the id field

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.





-- 
Posted via http://www.ruby-forum.com/.

Peter Piper 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 :-)

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...

-- 
Alex

I could use BIGINT, but I can potentially outgrow that as well :)
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 Young wrote:
> Peter Piper 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 :-)
> 
> 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...

-- 
Posted via http://www.ruby-forum.com/.

Am 27.02.2006 um 10:42 schrieb Peter:
> I could use BIGINT, but I can potentially outgrow that as well :)
> 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 :)

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

> Peter,
Whee, that should have read "Alex," :)

*m

Am 27.02.2006 um 11:01 schrieb Manuel Holtgrewe:
>> Peter,
>
> Whee, that should have read "Alex," :)
Well, maybe I should go and get some more coffee. Of course I meant  
Peter. ;)

*m

> 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

-- 
Posted via http://www.ruby-forum.com/.

Thanks for the reply, hope you got that coffee ;)

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.


-- 
Posted via http://www.ruby-forum.com/.

Anthony Green 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 
:)


-- 
Posted via http://www.ruby-forum.com/.

Peter wrote:
> Thanks for the reply, hope you got that coffee ;)
> 
> 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.
> Im sure I read somewhere that BaseCamp uses a custom writtern id 
> incrementer, how can they have out grown a BigInt?
> 
> Many thanks, P.
> 
>