Обсуждение: Space taken by Null values

Поиск
Список
Период
Сортировка

Space taken by Null values

От
Basant Dagar
Дата:
Gurus--

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?


​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?

Please help!!


​Thanks--Learner​

Re: Space taken by Null values

От
Tom Lane
Дата:
Basant Dagar <dagar.basant2@gmail.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


Re: Space taken by Null values

От
David G Johnston
Дата:
Basant Dagar wrote
> Gurus--
>
> 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?
>
>
> ​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?

IIRC a single null occupies one bit.

I would indeed expect to be able to create two tables and minimize or
eliminate the number of columns defined to allow NULL.  If that is what you
are thinking (your question is unclear on this point) then go ahead; keeping
mind that I obviously have no clue what any of those 100 columns is...

David J.



--
View this message in context: http://postgresql.nabble.com/Space-taken-by-Null-values-tp5836008p5836010.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.