Обсуждение: ...
On the FAQ we have: CHAR() is best when storing strings that are usually the same length. VARCHAR() is best when storing variable-length strings, but you want to limit how long a string can be. TEXT is for strings of unlimited length, maximum 1 gigabyte. BYTEA is for storing binary data, particularly values that include NULL bytes. How much space does "NULL" takes on a varchar. I have a table where possibly less than 40% of the records will have a value. I am wondering if I should go with a second table for those that have the values or if NULL is small enough that it won't be much an overhead for the possibly 60% instances when the column would be empty.
Francisco Reyes wrote: > On the FAQ we have: > > CHAR() is best when storing strings that are usually the > same length. VARCHAR() is best when storing variable-length strings, > but you want to limit how long a string can be. TEXT is for strings > of unlimited length, maximum 1 gigabyte. BYTEA is for storing > binary data, particularly values that include NULL bytes. > > > How much space does "NULL" takes on a varchar. Zero space. We have a bitmask of NULL columns for every row. > I have a table where possibly less than 40% of the records will have a > value. I am wondering if I should go with a second table for those that > have the values or if NULL is small enough that it won't be much an > overhead for the possibly 60% instances when the column would be empty. -- 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
Francisco Reyes <lists@natserv.com> writes: > How much space does "NULL" takes on a varchar. This is getting to be a FAQ itself --- Bruce, would you like to add something along the following lines to the FAQ? Q. How much space does a NULL take? A. None; it's not stored. However, if a table row contains any NULLs then it must include a "nulls bitmap" that shows which columns are NULL. The bitmap has one bit per table column. The bitmap is omitted if the row contains no NULLs at all. So, in practice the first NULL you put in a row costs number-of-columns bits. Any additional NULLs in the same row are completely free as far as storage space is concerned. Also, the space effectively occupied by the bitmap depends on alignment considerations, because the total size of the row header and bitmap will be rounded up to a MAXALIGN boundary (either 4 or 8 bytes on most hardware). In 7.3 it's likely that a bitmap for a row of up to 8 columns will be completely free, because the rounded-up header size will be the same with or without it. regards, tom lane
On Thu, Jul 04, 2002 at 02:06:31PM -0400, Francisco Reyes wrote: > On the FAQ we have: > > CHAR() is best when storing strings that are usually the > same length. VARCHAR() is best when storing variable-length strings, > but you want to limit how long a string can be. TEXT is for strings > of unlimited length, maximum 1 gigabyte. BYTEA is for storing > binary data, particularly values that include NULL bytes. > > > How much space does "NULL" takes on a varchar. From http://developer.postgresql.org/docs/postgres/datatype.html: The storage requirement for data of these types is 4 bytes plus the actual string, and in case of character plus the padding. So at least 4 bytes. Another bit in the table will be used to indicate that it's a NULL value, AFAIK, but it will exist anyway. > I have a table where possibly less than 40% of the records will have a > value. I am wondering if I should go with a second table for those that > have the values or if NULL is small enough that it won't be much an > overhead for the possibly 60% instances when the column would be empty. If your overhead is about space, I don't think you'd win much, as you'd probably need an indicator of whether the value exists and its place in the other table anyway. And you'll lose convenience also. Regards, Luciano Rocha
On Thu, 4 Jul 2002 14:06:31 -0400 (EDT), Francisco Reyes <lists@natserv.com> wrote: >How much space does "NULL" takes on a varchar. Francisco, the simple answer is: no space. The more complete answer is: The NULL state of all columns is stored in a bitmap. If a row does not contain any NULL column, the bitmap is omitted. So the first NULL in a row causes the bitmap to be created, more NULLs in the same row are free. In v7.2 the size of the bitmap is one bit per column (sic!) rounded up to an architecture specific value, usually 4 or 8 bytes. >I have a table where possibly less than 40% of the records will have a >value. I am wondering if I should go with a second table for those that >have the values or if NULL is small enough that it won't be much an >overhead for the possibly 60% instances when the column would be empty. If there are no other reasons for having a second table, don't bother with it. Servus Manfred
Tom Lane wrote: > Francisco Reyes <lists@natserv.com> writes: > > How much space does "NULL" takes on a varchar. > > This is getting to be a FAQ itself --- Bruce, would you like to > add something along the following lines to the FAQ? > > Q. How much space does a NULL take? > > A. None; it's not stored. However, if a table row contains any NULLs > then it must include a "nulls bitmap" that shows which columns are NULL. > The bitmap has one bit per table column. The bitmap is omitted if the > row contains no NULLs at all. So, in practice the first NULL you put in > a row costs number-of-columns bits. Any additional NULLs in the same > row are completely free as far as storage space is concerned. > > Also, the space effectively occupied by the bitmap depends on alignment > considerations, because the total size of the row header and bitmap > will be rounded up to a MAXALIGN boundary (either 4 or 8 bytes on > most hardware). In 7.3 it's likely that a bitmap for a row of up to > 8 columns will be completely free, because the rounded-up header size > will be the same with or without it. I added this to the disk space FAQ item. Let's see how that works: <P><SMALL>NULL</SMALL>s are stored in bitmaps, so they use very little space.</P> -- 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
On Thu, 4 Jul 2002, Bruce Momjian wrote: > Tom Lane wrote: > > Q. How much space does a NULL take? > > > > A. None; it's not stored. However, if a table row contains any NULLs > > then it must include a "nulls bitmap" that shows which columns are NULL. > > The bitmap has one bit per table column. The bitmap is omitted if the > > row contains no NULLs at all. So, in practice the first NULL you put in > > a row costs number-of-columns bits. Any additional NULLs in the same > > row are completely free as far as storage space is concerned. > > > > Also, the space effectively occupied by the bitmap depends on alignment > > considerations, because the total size of the row header and bitmap > > will be rounded up to a MAXALIGN boundary (either 4 or 8 bytes on > > most hardware). In 7.3 it's likely that a bitmap for a row of up to > > 8 columns will be completely free, because the rounded-up header size > > will be the same with or without it. > > I added this to the disk space FAQ item. Let's see how that works: > > <P><SMALL>NULL</SMALL>s are stored in bitmaps, so they > use very little space.</P> That would work, although we would probably save further questions in the future if we had the longer explanation on the FAQ or pointed the user to see the Data Types section of the Docs for more detail.. Then on the data type section mention (if it already doesn't have it) how the bitmap is used when there are NULLs.