Thread

1. space usage of NULL fields

   Jan Vicherek <honza@ied.com> — 1998-12-27T03:36:53Z

     Hello,
   
       a quick question (I couldn't contact postgresql.org, so I didn't check
   the FAQ, but I doubt it would be there.)
   
      When I create a table with field "nnn float8" and total row size, say
   140 bytes, and then I insert a row with NULL in this field, will my record
   take up those 8 bytes in physical page (say 140 bytes) or will it take up
   less (like 140-8=132), i.e. will it be shortened by those 8 bytes, since
   the value is NULL and no information is stored in that field ? ( I *hate*
   run-on sentences :)
   
      In general, what are the savings/overheads associated in PG with
   NULL/not NULL fields ? I.e. if will I save the space if I define the field
   as varchar(8) instead of float8  ?
   
         Thanx,
   
            Jan
   
    -- Gospel of Jesus is the saving power of God for all who believe --
                   ## To some, nothing is impossible. ##
                      http://Vicherek.Waterloo.on.ca/
   
   
   

2. Re: [HACKERS] space usage of NULL fields

   Bruce Momjian <maillist@candle.pha.pa.us> — 1998-12-27T16:00:54Z

   > 
   >   Hello,
   > 
   >     a quick question (I couldn't contact postgresql.org, so I didn't check
   > the FAQ, but I doubt it would be there.)
   > 
   >    When I create a table with field "nnn float8" and total row size, say
   > 140 bytes, and then I insert a row with NULL in this field, will my record
   > take up those 8 bytes in physical page (say 140 bytes) or will it take up
   > less (like 140-8=132), i.e. will it be shortened by those 8 bytes, since
   > the value is NULL and no information is stored in that field ? ( I *hate*
   > run-on sentences :)
   
   Shorter.  Nulls are not stored in the row.  There is a bitmask on every
   row that shows the number of NULL fields.
   
   > 
   >    In general, what are the savings/overheads associated in PG with
   > NULL/not NULL fields ? I.e. if will I save the space if I define the field
   > as varchar(8) instead of float8  ?
   
   no. float8 is shorter because varchar has 4-byte overhead.  The faq is
   in docs/FAQ too.
   
   
   -- 
     Bruce Momjian                        |  http://www.op.net/~candle
     maillist@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