Thread: Performance: BigInt vs Decimal(19,0)
For the application that I'm working on, we want to use data types that are database independent. (most databases has decimal, but not big int). Anyhow, we are planning on using decimal(19,0) for our primary keys instead of a big int, would there be a performance difference in using a bigint over using decimals? __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
"Yusuf W." <unicef2k@yahoo.com> writes:
> For the application that I'm working on, we want to
> use data types that are database independent. (most
> databases has decimal, but not big int).
Most databases have bigint, I think.
> Anyhow, we are planning on using decimal(19,0) for our
> primary keys instead of a big int, would there be a
> performance difference in using a bigint over using decimals?
You'll be taking a very large performance hit, for very little benefit
that I can see. How hard could it be to change the column declarations
if you ever move to a database without bigint? There's not normally
much need for apps to be explicitly aware of the column type names.
regards, tom lane
Now, I've got to convince my project's software architech, that a bigint would be better than a decimal. Does anyone know where I could get some documentation on how the int and decimal are implemented so I could prove to him that ints are better? Can people suggest good points to make in order to prove it? Thanks in advance. --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Yusuf W." <unicef2k@yahoo.com> writes: > > For the application that I'm working on, we want > to > > use data types that are database independent. > (most > > databases has decimal, but not big int). > > Most databases have bigint, I think. > > > Anyhow, we are planning on using decimal(19,0) for > our > > primary keys instead of a big int, would there be > a > > performance difference in using a bigint over > using decimals? > > You'll be taking a very large performance hit, for > very little benefit > that I can see. How hard could it be to change the > column declarations > if you ever move to a database without bigint? > There's not normally > much need for apps to be explicitly aware of the > column type names. > > regards, tom lane __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
Yusuf, > Does anyone know where I could get some documentation > on how the int and decimal are implemented so I could > prove to him that ints are better? Can people suggest > good points to make in order to prove it? RTFM: http://www.postgresql.org/docs/7.3/interactive/datatype.html#DATATYPE-NUMERIC -- Josh Berkus Aglio Database Solutions San Francisco
Wouldn't it be the most portable solution to work with a domain?
CREATE DOMAIN BIG_NUMBER AS BIGINT;
If I use BIG_NUMBER everywhere I need it in my database, porting it to other database products should be easy... any SQL 92 compliant dbms should support domains.
On Sun, 2003-09-28 at 00:06, Josh Berkus wrote:
CREATE DOMAIN BIG_NUMBER AS BIGINT;
If I use BIG_NUMBER everywhere I need it in my database, porting it to other database products should be easy... any SQL 92 compliant dbms should support domains.
On Sun, 2003-09-28 at 00:06, Josh Berkus wrote:
Yusuf, > Does anyone know where I could get some documentation > on how the int and decimal are implemented so I could > prove to him that ints are better? Can people suggest > good points to make in order to prove it? RTFM: http://www.postgresql.org/docs/7.3/interactive/datatype.html#DATATYPE-NUMERIC
Attachment
On Saturday, September 27, 2003, at 10:39 PM, Yusuf W. wrote: > Now, I've got to convince my project's software > architech, that a bigint would be better than a > decimal. > > Does anyone know where I could get some documentation > on how the int and decimal are implemented so I could > prove to him that ints are better? Can people suggest > good points to make in order to prove it? > Print out Tom's reply and give it to him. Saying 'one of the people who develops the thing says so' ought to carry some weight. I would hope... > Thanks in advance. > > --- Tom Lane <tgl@sss.pgh.pa.us> wrote: >> "Yusuf W." <unicef2k@yahoo.com> writes: >>> For the application that I'm working on, we want >> to >>> use data types that are database independent. >> (most >>> databases has decimal, but not big int). >> >> Most databases have bigint, I think. >> >>> Anyhow, we are planning on using decimal(19,0) for >> our >>> primary keys instead of a big int, would there be >> a >>> performance difference in using a bigint over >> using decimals? >> >> You'll be taking a very large performance hit, for >> very little benefit >> that I can see. How hard could it be to change the >> column declarations >> if you ever move to a database without bigint? >> There's not normally >> much need for apps to be explicitly aware of the >> column type names. >> >> regards, tom lane > > > __________________________________ > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > http://shopping.yahoo.com > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > -------------------- Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com
Franco, > Wouldn't it be the most portable solution to work with a domain? > CREATE DOMAIN BIG_NUMBER AS BIGINT; > > If I use BIG_NUMBER everywhere I need it in my database, porting it to > other database products should be easy... any SQL 92 compliant dbms > should support domains. This is a good idea, on general principles. Abstracted design is a good thing. Regrettably, though, a lot of commercial databases do not support DOMAIN. You'll need to check which databases you are thinking of porting to first. -- Josh Berkus Aglio Database Solutions San Francisco