pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Performance: BigInt vs Decimal(19,0)
7+ messages / 5 participants
[nested] [flat]

* Performance: BigInt vs Decimal(19,0)
@ 2003-09-27 23:54  Yusuf W. <unicef2k@yahoo.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Yusuf W. @ 2003-09-27 23:54 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Performance: BigInt vs Decimal(19,0)
@ 2003-09-28 00:26  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Yusuf W. <unicef2k@yahoo.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2003-09-28 00:26 UTC (permalink / raw)
  To: Yusuf W. <unicef2k@yahoo.com>; +Cc: pgsql-performance

"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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Performance: BigInt vs Decimal(19,0)
@ 2003-09-28 02:39  Yusuf W. <unicef2k@yahoo.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: Yusuf W. @ 2003-09-28 02:39 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Performance: BigInt vs Decimal(19,0)
@ 2003-09-28 03:06  Josh Berkus <josh@agliodbs.com>
  parent: Yusuf W. <unicef2k@yahoo.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2003-09-28 03:06 UTC (permalink / raw)
  To: Yusuf W. <unicef2k@yahoo.com>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Performance: BigInt vs Decimal(19,0)
@ 2003-09-29 14:59  Franco Bruno Borghesi <franco@akyasociados.com.ar>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Franco Bruno Borghesi @ 2003-09-29 14:59 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Yusuf W. <unicef2k@yahoo.com>; pgsql-performance

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

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Performance: BigInt vs Decimal(19,0)
@ 2003-09-29 17:19  Josh Berkus <josh@agliodbs.com>
  parent: Franco Bruno Borghesi <franco@akyasociados.com.ar>
  0 siblings, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2003-09-29 17:19 UTC (permalink / raw)
  To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; +Cc: Yusuf W. <unicef2k@yahoo.com>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Performance: BigInt vs Decimal(19,0)
@ 2003-09-29 17:26  Andrew Rawnsley <ronz@ravensfield.com>
  parent: Yusuf W. <unicef2k@yahoo.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Andrew Rawnsley @ 2003-09-29 17:26 UTC (permalink / raw)
  To: Yusuf W. <unicef2k@yahoo.com>; +Cc: pgsql-performance


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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2003-09-29 17:26 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-27 23:54 Performance: BigInt vs Decimal(19,0) Yusuf W. <unicef2k@yahoo.com>
2003-09-28 00:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-28 02:39   ` Yusuf W. <unicef2k@yahoo.com>
2003-09-28 03:06     ` Josh Berkus <josh@agliodbs.com>
2003-09-29 14:59       ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-09-29 17:19         ` Josh Berkus <josh@agliodbs.com>
2003-09-29 17:26     ` Andrew Rawnsley <ronz@ravensfield.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox