size cost for null fields

From: "Vance Maverick" <vmaverick(at)pgp(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: size cost for null fields
Date: 2007-12-14 17:29:50
Message-ID: DAA9CBC6D4A7584ABA0B6BEA7EC6FC0B947C8D@hq-exch01.corp.pgp.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 consists of a VARCHAR and a BYTEA, and it will almost always be null -- let's say only one row in 10,000 will have non-null values. I'm trying to decide whether to add the new data as columns in the existing table, or a side table linked by FK.

Looking at the Database Page Layout page <http://www.postgresql.org/docs/8.1/static/storage-page-layout.html>, it seems to me that I'll pay the minimum storage cost for these two fields (4 bytes for the VARCHAR, 4 bytes for the BYTEA) in every row, 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 values in the two fields.

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

Vance

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2007-12-14 17:31:23 Re: user name and password woes
Previous Message Ivan Sergio Borgonovo 2007-12-14 17:27:09 Re: How can i deal with "\n" when copy tables from sqlserver2005 to postgre?