From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Basant Dagar <dagar(dot)basant2(at)gmail(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Space taken by Null values |
Date: | 2015-01-29 19:40:24 |
Message-ID: | 25494.1422560424@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Basant Dagar <dagar(dot)basant2(at)gmail(dot)com> writes:
> I have big flat table with 100 columns in it. For about 40% of its data, 90
> columns would be null out of 100.And this table keeps growing and will
> initial have more than 100 million rows.
> May I know, will this impact the size in anyways as I heard nulls will
> take no or very little space in PostgreSQL unlike oracle. even for millions
> of rows with many columns in it. Is that true?
If a row has any nulls at all in it, there is a "nulls bitmap" added to
the row header which contains 1 bit per column, showing whether that
column is null or not. Null columns do not occupy any space in the
actual payload area. A row with no null columns omits the bitmap.
So, if you like, you can consider that the first null appearing in a row
of this table will cost you 16 bytes (100/8 = 12.5 rounded up to the next
alignment boundary), and then all the rest are free.
> And if that is not true, would you recommend to make this table into 2
> tables with the 2nd table containing those 40% records with only 10
> required columns in it?
It seems highly unlikely that such a scheme would be worth the trouble.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2015-01-29 19:42:12 | Re: Space taken by Null values |
Previous Message | Basant Dagar | 2015-01-29 19:32:15 | Space taken by Null values |