Received: from noao.edu (noao.edu [140.252.1.54]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eAEHk9s50630 for ; Tue, 14 Nov 2000 12:46:09 -0500 (EST) (envelope-from swampler@noao.edu) Received: from reynaud.tuc.noao.edu (reynaud.tuc.noao.edu [140.252.38.6]) by noao.edu (8.9.3/8.8.8/SAG-27Sep99) with ESMTP id KAA31503; Tue, 14 Nov 2000 10:44:53 -0700 (MST) (envelope-from swampler@noao.edu) Received: from noao.edu by reynaud.tuc.noao.edu (8.9.3+Sun/SMI-SVR4-SAG05) id KAA18014; Tue, 14 Nov 2000 10:44:53 -0700 (MST) Message-ID: <3A117A15.6A247634@noao.edu> Date: Tue, 14 Nov 2000 10:44:53 -0700 From: Steve Wampler Organization: National Solar Observatory X-Mailer: Mozilla 4.7 [en] (X11; I; SunOS 5.8 sun4u) X-Accept-Language: en MIME-Version: 1.0 To: Thomas Swan CC: Michael Teter , Poet/Joshua Drake , postgres-sql Subject: Re: Using a postgres table to maintain unique id? References: <5.0.0.25.0.20001114113001.00aad5f8@sunset.backbone.olemiss.edu> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200011/111 X-Sequence-Number: 142 Thomas Swan wrote: > > At 11/13/2000 06:22 PM -0800, Michael Teter wrote: > > > > From what I can tell (both from docs and doing a > > describe on sequences in my database), a postgresql > > sequence is an int4, not an int8, and thus you are > > limited to a max of 2.1 billion values. > > > > If you require an int8 sequence, you'll probably have > > to manage your own and just use an int8 column. > > > I had originally started using int8 and creating custom sequences. However, > as mentioned in a previous post, there is an inherent performance penalty in > using int8 over int4. Tom Lane advised me that the int8 routines are an > emulated or synthesized data type. in the test I did on our 7.0.2 server I > notice about a 25-30% decrease in performance when using complex joins on > tables containing referential keys, primary keys all in the int8 data type. > > This might be something to think about as well. Thanks. Because of these and other comments people have made, I've gone back to using a flat_file-with-server approach instead of adding a table to my postgres DB. While an int4 *might* work, it doesn't handle the "worst-case" scenario (which is up around 15 billion values). Thanks to everyone for your comments and suggestions! -- Steve Wampler- SOLIS Project, National Solar Observatory swampler@noao.edu