71

let's say I have a column on my table defined the following:

"MyColumn" smallint NULL

Storing a value like 0, 1 or something else should need 2 bytes (1). But how much space is needed if I set "MyColumn" to NULL? Will it need 0 bytes?

Are there some additional needed bytes for administration purpose or such things for every column/row?

(1) http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html

Improve this question

3 Answers 3

Reset to default
97

Laramie is right about the bitmap and links to the right place in the manual. Yet, this is almost, but not quite correct:

So for any given row with one or more nulls, the size added to it would be that of the bitmap(N bits for an N-column table, rounded up).

One has to factor in data alignment. The HeapTupleHeader (per row) occupies 23 bytes, actual column data always starts at a multiple of MAXALIGN (typically 8 bytes). That leaves one byte of padding that can be utilized by the null bitmap. In effect null storage is absolutely free for tables up to 8 columns.

After that, another MAXALIGN (typically 8) bytes are allocated for the next MAXALIGN * 8 (typically 64) columns. Etc. Always for the total number of user columns (all or nothing). But only if there is at least one actual null value in the row.

Notably, dropped columns still listed in the system catalog pg_attribute (marked as dropped) also occupy one bit in the null bit map. Not even VACUUM FULL can get rid of this residue, only a dump/restore cycle does. (So beware of anti-patterns that would add and drop table columns routinely.)

I ran extensive tests to verify all of that. More details:

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Adding columns without a default value to large tables is usually a quick operation. Can it be slow when you cross the threshold between 8 and 9 columns (or 72 and 73 with MAXALIGN = 8)?
@PatrickBrinich-Langlois: Yes, that's a possible consequence of the mechanism. The physical table size also goes up more than one might expect in these cases. The effect is not necessarily linear as other factors like dead tuples are involved. The table does not grow at all if existing NULL bitmaps (per row) have space for another NULL bit, which is the common case.
Good Answer. As a side note: if Laramie is possibly not He but She or their, you could save three Bytes to get a gender neutral sentence "Laramie is right about the bitmap and links to the right place in the manual."
@StefanWuebbe: Better in any case. So why not.
84

Null columns are not stored. The row has a bitmap at the start and one bit per column that indicates which ones are null or non-null. The bitmap could be omitted if all columns are non-null in a row. So for any given row with one or more nulls, the size added to it would be that of the bitmap(N bits for an N-column table, rounded up).

More in depth discussion from the docs here

Improve this answer

Comments

-1

It should need 1 byte (0x00) however it's the structure of the table that makes up most of the space, adding this one value might change something (Like adding a row) which needs more space than the sum of the data in it.

Edit: Laramie seems to know more about null than me :)

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.