Use of integer instead of interval (of one type)

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?

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.

See:

• Configuring PostgreSQL for read performance

• Calculating and saving space in PostgreSQL

If in doubt, run a test and measure:

• Measure the size of a PostgreSQL table row

StackExchange Database Administrators Q#60470, answer score: 4