Thread: Performance: BigInt vs Decimal(19,0)

Performance: BigInt vs Decimal(19,0)

From
"Yusuf W."
Date:
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

Re: Performance: BigInt vs Decimal(19,0)

From
Tom Lane
Date:
"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

Re: Performance: BigInt vs Decimal(19,0)

From
"Yusuf W."
Date:
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

Re: Performance: BigInt vs Decimal(19,0)

From
Josh Berkus
Date:
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

Re: Performance: BigInt vs Decimal(19,0)

From
Franco Bruno Borghesi
Date:
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:
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

Re: Performance: BigInt vs Decimal(19,0)

From
Andrew Rawnsley
Date:
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


Re: Performance: BigInt vs Decimal(19,0)

From
Josh Berkus
Date:
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