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.


2 Answers 2


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


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

  • It seems strange that an ENUM is 4 bytes, rather than just 1 (assuming it had < 256 values.
    – hayd
    Jun 19, 2019 at 22:22
  • @hayd: Enums can have more than 256 values. You could build your own lookup table with a 1-byte PK column to squeeze out more: dba.stackexchange.com/a/159916/3684 Jun 20, 2019 at 19:28
  • Thanks, I suspect majority of enums in the wild are a lot small, but this makes sense once you know enums are defined in pg_enum. It's interesting/surprising that the pg_enum table uses real/float4 for enumsortorder.
    – hayd
    Jun 20, 2019 at 20:48

First, the size of a smallint is two bytes which is twice the size of a bool:

  pg_column_size(1::bool) AS "bool"
  , pg_column_size(1::smallint) AS "si";

 bool | si 
    1 |   2

Let's create a small table with 1000 rows and check it out:

    CASE WHEN x>0.5 THEN 1::smallint ELSE 0::smallint END AS si,
    CASE when x>0.5 THEN true ELSE false END AS b
  FROM ( SELECT random() AS x FROM generate_series(1,1e3) ) AS t;

CREATE INDEX si ON foo (si);
CREATE INDEX b ON foo (b);

Now we can see the table with 40kB:

test=# \dt+ foo
            List of relations
 Schema | Name | Type  | Size 
 public | foo  | table | 40 kB

Both indexes are the same size (40kB). And all self-joins (returning 500050 rows in my case) using a seq scan and index scan (with SET enable_seqscan=off) complete in the same amount of time:


So really the benefits are that:

  • the col size is one byte smaller
  • the table shows bool, rather than smallint which is more descriptive
  • eliminates room for error if you store 3.
  • it's likely to work in third party libraries a bit better.
  • it permits slightly more terse SQL, WHERE isEmpty rather than WHERE empty = 1
  • 1
    The index disk usage will be identical I think for a single int2 vs a single boolean index. But try an (int4, int2, int2, int2) vs an (int4, bool, bool, bool) index and you'll see a difference. Nov 30, 2016 at 23:27
  • 1
    @ypercubeᵀᴹ Feel free to update/improve answer with that other stuff about int4, int2 (etc). Sounds like a good contribution. I know a little bit about that but I'm approaching the limits of my knowledge there. I was just trying to answer a question on gis.se and I figured i'd write it down here since no one else had. Nov 30, 2016 at 23:33
  • I assume at some point you cross the pagesize and then every row becomes two pages whereas if you used bool you may not hit that limit as quick? Sounds like a rare occurrence though, and I think it only matters if you pack them next to each other. Ie., bool int2 bool int2 is the same size as int2 int2 int2 int2 but bool bool int int is smaller than all of them. iirc. Nov 30, 2016 at 23:39
  • 3
    A int4-int2-int2-int2 index needs 10 bytes per row (which is larger than 8. int4-bool-bool-bool needs 7 bytes (<= 8). I'm not entirely sure but the packing is in multiples of 8. Or it could be page splits, as you say. The order of columns also plays a role, at least in tables. Not sure about indexes. Nov 30, 2016 at 23:43
  • 1
    @ypercubeᵀᴹ: All basically true. For btree indexes as well. And mostly for other index types, too. (The storage principles are the same, just some special index features.) Dec 1, 2016 at 5:08

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.