The general knowledge is that numerics are slower than integers/float, but offer precision and ranges that are better.

While I understand what is slow, I don't really know how much slower numerics are. So let's test it.

There are couple of things to consider:

• table size (related to disk usage per column)
• speed of inserts
• speed of indexing
• searching using equality operator
• searching using range operator
• doing some math on the values

Table size. Initial thinking is that int2 column would make the table 4 times smaller than int8. But is it true?

First, let's consider very simplistic table:

CREATE TABLE test_type (
    some_column TYPE
);

where type is one of:

• int2
• int4
• int8
• float4
• float8
• numeric
• numeric(10,0) (in this case table name will be test_numeric10)
• numeric(20,0) (in this case table name will be test_numeric20)

In each of these tables, let's insert 1 million rows. In all but int2 case these will be unique, but int2 range is too small to fit 1 million unique values, so I'll end up with duplicates:

INSERT INTO test_int2 SELECT i % 32767 FROM generate_series(1,1000000) i;
INSERT INTO test_int4 SELECT i FROM generate_series(1,1000000) i;
INSERT INTO test_int8 SELECT i FROM generate_series(1,1000000) i;
INSERT INTO test_float4 SELECT i FROM generate_series(1,1000000) i;
INSERT INTO test_float8 SELECT i FROM generate_series(1,1000000) i;
INSERT INTO test_numeric SELECT i FROM generate_series(1,1000000) i;
INSERT INTO test_numeric10 SELECT i FROM generate_series(1,1000000) i;
INSERT INTO test_numeric20 SELECT i FROM generate_series(1,1000000) i;

Table sizes:

SELECT relname, pg_size_pretty( pg_table_size(oid) )
FROM pg_class WHERE relname ~ '^test_';
    relname     | pg_size_pretty 
----------------+----------------
 test_float4    | 35 MB
 test_float8    | 35 MB
 test_int2      | 35 MB
 test_int4      | 35 MB
 test_int8      | 35 MB
 test_numeric   | 35 MB
 test_numeric10 | 35 MB
 test_numeric20 | 35 MB
(8 ROWS)

Surprise, surprise.

But, let's redo the test, this time with 2 columns. Each table will get “some_column2", of the same datatype it had before. and all the data will be reinserted, to both columns (tables will get dropped and recreated to avoid any bloat). How will that change?

INSERT INTO test_int2 SELECT i % 32767, i % 32767 FROM generate_series(1,1000000) i;
INSERT INTO test_int4 SELECT i, i FROM generate_series(1,1000000) i;
INSERT INTO test_int8 SELECT i, i FROM generate_series(1,1000000) i;
INSERT INTO test_float4 SELECT i, i FROM generate_series(1,1000000) i;
INSERT INTO test_float8 SELECT i, i FROM generate_series(1,1000000) i;
INSERT INTO test_numeric SELECT i, i FROM generate_series(1,1000000) i;
INSERT INTO test_numeric10 SELECT i, i FROM generate_series(1,1000000) i;
INSERT INTO test_numeric20 SELECT i, i FROM generate_series(1,1000000) i;

and the sizes now:

SELECT relname, pg_size_pretty( pg_table_size(oid) )
FROM pg_class WHERE relname ~ '^test_';
    relname     | pg_size_pretty 
----------------+----------------
 test_float4    | 35 MB
 test_float8    | 42 MB
 test_int2      | 35 MB
 test_int4      | 35 MB
 test_int8      | 42 MB
 test_numeric   | 42 MB
 test_numeric10 | 42 MB
 test_numeric20 | 42 MB
(8 ROWS)

Yeah. So, basically what we are seeing here is effect of two things:

• alignment – data is aligned to certain number of bytes. In my case – 8 (that's why int2 and int4 take as much as int8, but when I added next column – int8 grew and int2/int4 didn't). This means that if something is using less than 8 bytes, then it will be “padded" to 8 bytes. It doesn't mean that each value will take 8 bytes – it's a bit more complex than that, and if you're looking for specific details – I guess sources have definitive answers
• what a lot of people don't seem to remember – each row contains more than what you just see. There are system columns. And these take 26 bytes. Per row.

So, as far as data size is concerned – there is difference, but it's not really all that big.

What about inserting speed? I'll exclude int2 from the tests, as its smaller range requires limiting range, and this makes the test more complicated (i.e. there are additional operations to do).

So, what about the other datatypes?

I inserted 1 million rows into each of the tables, between tests doing “truncate" on the table, and repeated the process 10 times to get sensible average. Results:

That's pretty interesting. I also ran the test, where there was unique index on each of the tables (only on one column). Results for the same insert test with index in place:

Clearly indexing has its toll on larger datatypes. Up to ~ 50% slower than int4.

So, we know now, what about writes. Let's see how searches perform.

For equality searching, I will search, using 10 different queries for values (in indexed column): 1, 100000, 200000, 300000 … 1000000.

Since the searches are fast, I repeated each 1000 times. Averages:

For me it means that there is no difference.

Now range queries. I'll check queries that return 10 rows, 1000 rows and 10000 rows.

All queries were in form of:

SELECT * FROM TABLE WHERE some_column BETWEEN ... AND ...

First the 10 row:

1000 rows:

And the final, 10k row test:

That's actually pretty interesting – searching using floats is slower than using numerics?!

And now for some math. I ran a test, which was running commands like:

SELECT 12::int2 * 13::int2

Decided to test 3 operators: addition, multiplication and modulo.

Results for addition test (123 + 456):

Multiplication (123 * 99):

Modulo (12345 % 9876). This test couldn't be done with floats, as there is no “float % float" operator:

So, what does it all tell us? It all depends on use case. Indexing is biggest problem, but math itself, or searching is not that big. Whether it's OK for your situation – you have to assess yourself.

UPDATE

Dim from irc asked me to check aggregates too. So I did.

Results for select sum(some_column):

and select avg(some_column):

This is pretty interesting. Especially how bad int8 seems to be …