Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation.

Advantage: 2 bytes instead of 12 bytes (we have many such columns).

Is this line of thinking ok or am I overlooking something?


1 Answer 1


Perfectly ok - especially since you can add and subtract integer to / from date directly in Postgres.

However, smallint (int2) might not be better than a plain integer (int4). True, integer occupies 4 bytes instead of 2, but many operations are optimized for integer. Among other things, integer is the default numeric type for (sufficiently small) numbers without decimal point. For int2 you often need to add an explicit cast.

Also, you probably gain nothing for a single column in regard to storage or RAM. Some closer understanding of storage mechanisms is needed here, in particular alignment padding.

For several int2 columns in a table (or even "many" as you mention) and know what you are doing, the odds for int2 get better.


If in doubt, run a test and measure:

  • Plus I always think that modern 64bit processors can't handle a two-byte integer anyway. I might be mistaken, but I don't think they can physically handle less than 64bit, everything else is just setting all the other bits to zero (but I could be mistaken there) Mar 10, 2014 at 22:17
  • @a_horse_with_no_name from this link stackoverflow.com/questions/163254/… what I understood is performance penalty, if any, is negligible as compared to memory storing and cache hits. But, a good point which should be considered. in fact int8 will be better for 64bit but I am more focused on diskIO.
    – vedic
    Mar 11, 2014 at 9:26
  • @Erwin Brandsetter I do have several columns in a table and many such tables :). Some of the table do have 8 columns (or more) of INT2. Casting of INT2 is definitely I will keep in mind.
    – vedic
    Mar 11, 2014 at 9:32
  • @vedic: I didn't really think that an int2 will be slower than an int8 on processor level. My point was that it won't be a difference once the data is in memory. Mar 11, 2014 at 9:37

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.