61

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

Chris
  • 4,325
  • 11
  • 51
  • 70

3 Answers3

83

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) is 23 bytes long, 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.

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 5
    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`)? – Patrick Brinich-Langlois Sep 28 '16 at 17:35
  • 3
    @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. – Erwin Brandstetter Jul 11 '17 at 14:51
  • 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."_ – Stefan Wuebbe Jul 12 '22 at 19:47
  • @StefanWuebbe: Better in any case. So why not. – Erwin Brandstetter Jul 13 '22 at 01:24
75

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

Un3qual
  • 1,332
  • 15
  • 27
Laramie
  • 5,457
  • 2
  • 39
  • 46
-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 :)

J V
  • 11,402
  • 10
  • 52
  • 72