Postgres Pro
Downloads
Home   >   mailing lists

Thread: size of NULL field?

size of NULL field?

From
nazgul@punkass.spam (nazgul)
Date:
Hi, I'm wondering how much storage space a NULL value takes up?  Does it
still use up as much space as the column's datatype, or just 1 byte, or some
other amount?

Re: size of NULL field?

From
Tom Lane
Date:
nazgul@punkass.spam (nazgul) writes:
> Hi, I'm wondering how much storage space a NULL value takes up?

None.

However, as soon as you have any NULLs in a particular table row, the
row needs to store a NULL-value bitmap, which has one bit per table
column to show which ones are nulls.  So you could say that the first
NULL in a given row costs you 4 bytes (more if you have > 32 columns).
Additional NULLs in the row are free.

            regards, tom lane

PS: if you're on a machine where MAXALIGN is 8, the cost quantum is
8 bytes not 4.  But I think it's usually 4 on peecee hardware.

Re: size of NULL field?

From
Bruce Momjian
Date:
Tom Lane wrote:
> nazgul@punkass.spam (nazgul) writes:
> > Hi, I'm wondering how much storage space a NULL value takes up?
>
> None.
>
> However, as soon as you have any NULLs in a particular table row, the
> row needs to store a NULL-value bitmap, which has one bit per table
> column to show which ones are nulls.  So you could say that the first
> NULL in a given row costs you 4 bytes (more if you have > 32 columns).
> Additional NULLs in the row are free.

Oh, I didn't realize the size of the NULL bitmask was zero if no nulls
exist.  Nice.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: size of NULL field?

From
dalgoda@ix.netcom.com (Mike Castle)
Date:
In article <200204151933.g3FJXT912721@candle.pha.pa.us>,
Bruce Momjian  <pgman@candle.pha.pa.us> wrote:
>Oh, I didn't realize the size of the NULL bitmask was zero if no nulls
>exist.  Nice.

So, how does the row know if the NULL bitmask exists or not?  I'm guessing
there's a structure per row somewhere that has as at least one of it's
members, a bit on whether a NULL bitmask exists or not?

If all of the members of a row become non-NULL, does the NULL bitmask go
away?

mrc

--
     Mike Castle      dalgoda@ix.netcom.com      www.netcom.com/~dalgoda/
    We are all of us living in the shadow of Manhattan.  -- Watchmen
fatal ("You are in a maze of twisty compiler features, all different"); -- gcc

Re: size of NULL field?

From
Tom Lane
Date:
dalgoda@ix.netcom.com (Mike Castle) writes:
>> Oh, I didn't realize the size of the NULL bitmask was zero if no nulls
>> exist.  Nice.

> So, how does the row know if the NULL bitmask exists or not?

There's a bit in the t_infomask field for it.

            regards, tom lane