From: Tom Lane <tgl@sss.pgh.pa.us>
To: Basant Dagar <dagar.basant2@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Space taken by Null values
Date: Thu, 29 Jan 2015 14:40:24 -0500
Message-ID: <25494.1422560424@sss.pgh.pa.us> (raw)
In-Reply-To: <CAPJLWJtPgQtttujOo6s3np5hvg2qPf0E-y8cnhPPw-oL=CNC4g@mail.gmail.com>
References: <CAPJLWJtPgQtttujOo6s3np5hvg2qPf0E-y8cnhPPw-oL=CNC4g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, dagar.basant2@gmail.com
Subject: Re: Space taken by Null values
In-Reply-To: <25494.1422560424@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox