Using PostgreSQL what is the difference between a smallint and a bool for storing boolean?

What is the difference between the smallint type and the bool type for storing boolean values?

This question arose in the comments to a question on Geographic Information Systems Stack Exchange.

Always store boolean data as boolean. Only exotic exception imaginable.

Just to address the storage angle in addition to what you posted as answer:

boolean requires 1 byte on disk, smallint requires 2. But that's not the whole story.

smallint (like other integer types and unlike boolean) also has special needs for alignment padding. It can only start at an even offset from the start of the tuple data. So another byte is consumed every odd time (literally).

In a worst case scenario, when mixing with types that require 8-byte alignment like bigint or timestamp / timestamptz:

SELECT pg_column_size(row("char" 'a', FALSE   )) AS char_bool
     , pg_column_size(row("char" 'a', int2 '1')) AS char_int2
     , pg_column_size(row(text 'abcdef', TRUE    , now())) AS text7_bool_ts
     , pg_column_size(row(text 'abcdef', int2 '1', now())) AS text7_int2_ts;  -- worst case

char_bool | char_int2 | text7_bool_ts | text7_int2_ts
-----------+-----------+---------------+---------------
26 | 28 | 40 | 48


Details:

• Calculating and saving space in PostgreSQL

• Configuring PostgreSQL for read performance

• What is the overhead for varchar(n)?

If you have many boolean NOT NULL values and want to optimize space on disk:

• Should I use the PostgreSQL bit string?

StackExchange Database Administrators Q#156827, answer score: 29