Re: Space taken by Null values

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Space taken by Null values
Дата
Msg-id 25494.1422560424@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Space taken by Null values  (Basant Dagar <dagar.basant2@gmail.com>)
Список pgsql-admin
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


В списке pgsql-admin по дате отправления:

Предыдущее
От: Basant Dagar
Дата:
Сообщение: Space taken by Null values
Следующее
От: David G Johnston
Дата:
Сообщение: Re: Space taken by Null values