Re: Decimal vs. Bigint memory usage

On Thu, Sep 29, 2011 at 15:15, Gregor Vollmer
<vollmer(at)ekp(dot)uni-karlsruhe(dot)de> wrote:
> We do not do any arithmetic on the columns, only saving and retrieval, is
> decimal as fast as bigint in that case?

It's slightly slower because numeric is passed around by pointer, not
by value. Bigint is by-value in 64-bit Postgres versions. But that's
probably only noticeable if you're processing lots of values in a
single query, or running CREATE INDEX.

Personally I'd choose bigint for efficiency reasons. But always keep
in mind that arithmetic works differently on integers and numerics:

db=# select 1::bigint/10 as x;
x
---
0
db=# select 1::numeric/10 as x;
x
------------------------
0.10000000000000000000

> How does decimal store the number internally, is it a fixed size through-out
> all rows and how does it compare to bigint?

Numeric (decimal) is always variable-length. The specification in
column type doesn't affect storage. Bigint is always 8 bytes.

For numbers with less than 8 digits, numeric is slightly smaller than
bigint. For larger numbers, bigint is smaller.

create table dec (i numeric);
insert into dec values(0), (1), (11), (101), (1001), (10001),
(100001), (1000001), (10000001), (100000001), (1000000001),
(10000000001), (100000000001), (1000000000001);

select pg_column_size(i), i::text from dec;
pg_column_size | i
----------------+---------------
3 | 0
5 | 1
5 | 11
5 | 101
5 | 1001
7 | 10001
7 | 100001
7 | 1000001
7 | 10000001
9 | 100000001
9 | 1000000001
9 | 10000000001
9 | 100000000001
11 | 1000000000001

Regards,
Marti