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
IFI recommends 6TB of disk space for an on-site PostgreSQL instance.
A null byte is a byte with a zero in it: 00000000 (8-bit byte, all bits zero). It takes the same amount of space as any other byte of any other value (0–255). It is often used to end strings in programming languages.
Once there is a null value, t_bits is stored in the tuple to record the null value status of all columns.
One common misconception about NULL values is that they represent “nothing” or “no value.” NULLs are indeed a value. They take up space on your database hard drive as opposed to “nothing” that indicates there is no value.
Laramie is right about the bitmap and he 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:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With