Обсуждение: size cost for null fields

I have a table with lots and lots of rows (into the millions), and I want to add some information to it.  The new data
consistsof a VARCHAR and a BYTEA, and it will almost always be null -- let's say only one row in 10,000 will have
non-nullvalues.  I'm trying to decide whether to add the new data as columns in the existing table, or a side table
linkedby FK.
 

Looking at the Database Page Layout page <http://www.postgresql.org/docs/8.1/static/storage-page-layout.html>, it seems
tome that I'll pay the minimum storage cost for these two fields (4 bytes for the VARCHAR, 4 bytes for the BYTEA) in
everyrow, regardless of whether they're null.  When the fields are null, there'll be bits set for them in the null
mask,but the alignment and size of the row won't change -- everything else will be placed as if there were zero-length
valuesin the two fields.
 

Do I have this right?  If so, the side table sounds like the right choice....

    Vance

"Vance Maverick" <vmaverick@pgp.com> writes:

> Do I have this right?  If so, the side table sounds like the right choice....

No, if the null bit is set then the field isn't stored at all. You don't pay
the alignment or storage overhead at all.

The only cost is for the null bitmap itself. If you have *any* null fields
then you pay one bit for *every* field plus alignment to a 4-byte boundary.
Except there's one available free byte so if you have 8 or fewer fields even
that is entirely free.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!