15

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)?

Improve this question

2 Answers 2

Reset to default
16

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.

Improve this answer
1
  • All agreed, as a side note numeric has one advantage as it automatically enforces the 0-999999 domain. That can however be solved with a separate constraint in the int case
    – Lennart - Slava Ukraini
    Aug 14, 2015 at 5:43
7

The definitive answer is no to all of your questions. Integer is always the way to go for anything you can use it for. (Money, for instance)

Think about it for a minute. When the database engine encounters an integer, it handles it very efficiently because there is not much interpretation to it. It is a whole number. The numeric type behaves more like a string. The engine first has to figure out which parts are before and after the decimal point, and massage them appropriately to perform numeric operations.

Using an integer will always be more efficient than a numeric, although numeric types are often more convenient for humans.

Improve this answer
3
  • I disagree when it comes to money. Using a scaled integer, like storing decicents (1000 per dollar) is ok, but awkward. It quickly becomes more practical to use NUMERIC. A scaled integer is way better than using a floating point value for money though.
    – Craig Ringer
    Aug 13, 2015 at 13:25
  • 2
    @CraigRinger I don't think you actually disagree with me! I agree that using a decimal for money is always less awkward for the developer, but the question is query efficiency, right? Handling integers is always faster. Also, when writing banking applications, you can get into some weird rounding issues that most people would not care about, but are very important to banks. So, I also agree with you on not using floating point for money, too!
    – stubsthewizard
    Aug 13, 2015 at 14:58
  • 1
    Good point on rounding. I wish PostgreSQL had rounding policy support. Don't wish it enough to implement it though ;)
    – Craig Ringer
    Aug 13, 2015 at 23:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.