4

I saw this question Bit vs. Boolean columns.

I'm asking myself the same for Postgres: does a single digit integer column occupy the same disk space of a boolean one? In big tables (~50 columns x ~50 million rows) which one perform best? How can I find this out?

  • Unlike the other 3 major RDBMSes, the Boolean type has vastly more convenient semantic behavior in PostgreSQL. First determine which one better meets the semantic needs of the column. PostgreSQL booleans enforce two (or three if you're allowing NULLs) exclusive values, can be used in conditionals, and provide meaningful (maybe even localized?) names for the values, among other useful features. Once you've determined the appropriate type semantically, profile to see if it meets your performance requirements and only consider changing if not. – jpmc26 Aug 26 at 23:25
9

You can find out the storage size with

SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen 
---------+--------
 bool    |      1
 int4    |      4
(2 rows)

However you need to take alignment into account:

SELECT typname, typlen, typalign FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen | typalign 
---------+--------+----------
 bool    |      1 | c
 int4    |      4 | i
(2 rows)

c is “character” (1 byte), while i is “integer”.

If you define a table like this:

CREATE TABLE a (
   b boolean,
   i integer
);

you will get three unused “padding” bytes between the columns, so that the integer can be stored at an address that is divisible by 4.

So the boolean would take up 4 rather than 1 bytes of storage.

If you specify the columns the other way around, the space taken up by the data in each row would only be 5 bytes.

The table row itself (the “tuple”) does not only consist on the raw data, but there is a 23-byte “tuple header” for each row (see the documentation). There may be padding after the header so that the actual tuple data are aligned at a multiple of MAXALIGN (typically 8).

So if you want to optimize your table to use as little storage as possible, you need to take the order of the columns in the table into account.

  • Thank you for the interesting insight, I didn't know about alignment! Let me try if I understood it right: a table like CREATE TABLE a ( b1 boolean, b2 boolean, b3 boolean ); uses 3 bytes per row (and with no padding between rows), right? – Sotis Aug 27 at 7:56
  • There is also the tuple header. I have extended the answer to cover space requirements and alignment in general. – Laurenz Albe Aug 27 at 8:20
  • Interesting! So, if I have some boolean columns I should put them all near each other. Or at least put them in groups of 4, so that there's no need for paddings (in the hypothesis of having only integer and boolean columns). Right? – Sotis Aug 27 at 9:18
  • 1
    Right. Normally, though, this will be your least consideration when you make a physical design. Don't scrimp and save prematurely - for example, it is better to declare an artificial primary key as bigint than to use integer (better safe than sorry). Or remember Y2K, which came from storing years as two digits. That said, carefully aligning your columns can save some space. – Laurenz Albe Aug 27 at 9:38

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.