PostgreSQL Column Types – Numeric vs Integer for Size and Performance

postgresql

I have an application which uses a PostgreSQL table. The table is very big (billions of rows) and has a column which is an integer.

The integer can be up to 6 digits, i.e. 0-999,999 , no negatives.

I thought about changing it to be numeric(6,0).

Would this be a good idea? Would numeric(6,0) take fewer bytes? How about the performance (this table is being queried a lot)?

Best Answer

Would this be a good idea?

No.

would numeric(6,0) take less bytes?

No.

test=> SELECT pg_column_size(INT4 '999999'), pg_column_size(NUMERIC(6,0) '999999');
 pg_column_size | pg_column_size 
----------------+----------------
              4 |             10
(1 row)

how about the performance (this table is being queried a lot)?

Slower. It's stored as binary-coded decimal because it's an arbitrary precision value.

Related Question