Are there any reasons to not use smallint when it fits the data?

Question

Since PostgreSQL doesn't have 1-byte tinyint, the second best option would be smallint. However, I've read from various posts* that it may actually be slower because CPU's are optimized to operate on 32-bit integers, or there may be implicit conversions to 32-bit integers.

Besides these reasons, are there any other reasons I'm not yet aware of for not using integers smaller than 32-bits?

* I can't find it now, and I could be wrong about this. What I understood from it is that smaller integers would save disk/index space. So, good for the database, but the web/computation server would need to convert it to 32-bit

Answer 1

There is also the special (internal) type "char" (quotes required) that uses 1 byte, but it is not recommended for use. If one was going to use it, one would have to convert it to (and form) some nice format, say integer 0-255, for display purposes. - ypercube™

---

The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character.

There are two other fixed-length character types in PostgreSQL, shown in Table 8.5. These are not intended for general-purpose use, only for use in the internal system catalogs.

Table 8.5. Special Character Types

Name	Storage Size	Description
"char"	1 byte	single-byte internal type
name	64 bytes	internal type for object names

The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage, and therefore can store only a single ASCII character. It is used in the system catalogs as a simplistic enumeration type.

Answer 2

If you have determined that using smallint is appropriate for your scenario, you can simply declare the column as smallint when creating the table. Here's an example of creating a table with a smallint column:

CREATE TABLE example_table (
  id serial PRIMARY KEY,
  value smallint
);