9

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

10

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.

  • 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 Aug 14 '15 at 5:43
  • Is there a problem changing a numeric column to int? – Racer SQL Oct 20 '17 at 16:39
4

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.

  • 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 '15 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 '15 at 14:58
  • Good point on rounding. I wish PostgreSQL had rounding policy support. Don't wish it enough to implement it though ;) – Craig Ringer Aug 13 '15 at 23:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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