9

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?

1
  • 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, 2019 at 23:25

1 Answer 1

14

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.

6
  • 1
    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, 2019 at 7:56
  • There is also the tuple header. I have extended the answer to cover space requirements and alignment in general. Aug 27, 2019 at 8:20
  • 2
    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. Aug 27, 2019 at 9:38
  • 2
    Is the header aligned to 4 bytes too ? If yes, then the order does not save any space in the example since there's a gap of 3 unused bytes after the tuple.
    – bokan
    Mar 11, 2021 at 8:46
  • 2
    @bokan Quite true. You typically only can save some space if you have many columns. Mar 11, 2021 at 8:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.