Thread: NULL saves disk space?
Possibly a dumb question but there isn't much about this.
I have some BOOLEAN columns. 90% of the cases of the columns is FALSE. Do I save disk space by having them as NULL instead of FALSE? So my application would have conditional code for NULL and TRUE, instead of FALSE and TRUE.
Thanks...
Doubtful but not sure; Boolean isn't that large a structure anyway...
I'm not sure you'd want to introduce tri-value logic in this case anyway. If you know something is false why would you claim that you don't know what the value? Data should first and foremost be accurate and precise. In this case if you make the change then anyone else looking at or using the data needs to learn a very non-standard way of dealing with booleans.
If fact I can imagine few cases where a Boolean field would be null and even if they do not often it is sufficient to assume false.
What you might consider is whether a partial index for true values is warranted. This will add some space but make queries go faster. If you already index the column then a partial index alternative will make the index smaller while still being useful. A full index is not that useful since a false condition will likely use a table scan anyway.
Physical space for data should be a minor concern. The more pressing concern is efficient retrieval of that data. Using null instead of false, even if it were to save space, is not going to make a significant dent, and what savings you would get would be outweighed by the unusual data and how you'd go about querying it. You can't say, for instance, "ispresent = ?" and replace the ? With true/false anymore.
So while not a dumb question per se if you are considering this for anything more than curiosity I would humbly suggest looking at other area to improve performance and reduce size. Though, as mentioned, physical storage is fairly inexpensive anyway. If you are generating enough data to make storage a concern you should be able to justify the expense of getting more storage.
David J.
Possibly a dumb question but there isn't much about this.I have some BOOLEAN columns. 90% of the cases of the columns is FALSE. Do I save disk space by having them as NULL instead of FALSE? So my application would have conditional code for NULL and TRUE, instead of FALSE and TRUE.Thanks...
On Wed, Apr 27, 2011 at 5:24 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: > Possibly a dumb question but there isn't much about this. > http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=postgresql+null+value+disk+space > I have some BOOLEAN columns. 90% of the cases of the columns is FALSE. Do I > save disk space by having them as NULL instead of FALSE? So my application > would have conditional code for NULL and TRUE, instead of FALSE and TRUE. > Thanks... Yes, NULL values take no additional space, but the row needs a null bitmap so it is possible that if this was the only NULL then it could occupy more space. If you have multiple columns, then you should use NULLs. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
>Do I save disk space by having them as NULL instead of FALSE? So my >application would have conditional code for NULL andTRUE, instead of >FALSE and TRUE. The short answer: do not even think about it. NULL has a well defined meaning within SQL: "we do not know the value", with "well defined" being something totally different from "intuitive". With "using NULL as false" you woud get: select whatever from yourtable where not (booleancolumn = true ) would result in ....wait for it .... zero rows. Because "NULL" is not "not =" to true. There is no potential diskspace saving in the world that could be worth those troubles. Harald -- Harald Armin Massa www.2ndQuadrant.de PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH GF: Harald Armin Massa Amtsgericht Stuttgart, HRB 736399
On Thu, Apr 28, 2011 at 10:59 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > On Wed, Apr 27, 2011 at 5:24 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: >> Possibly a dumb question but there isn't much about this. >> http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=postgresql+null+value+disk+space >> I have some BOOLEAN columns. 90% of the cases of the columns is FALSE. Do I >> save disk space by having them as NULL instead of FALSE? So my application >> would have conditional code for NULL and TRUE, instead of FALSE and TRUE. >> Thanks... > > Yes, NULL values take no additional space, but the row needs a null > bitmap so it is possible that if this was the only NULL then it could > occupy more space. > > If you have multiple columns, then you should use NULLs. Thanks Simon. (And others for good advice, but that was not my question. I already know using boolean as TRUE/FALSE is sensible. But I have a peculiar reason for asking what I am.) Simon, if I understand you correctly -- more than one column in a row should have NULL for NULL to be useful in saving space? What if in a row there are many columns but only one will be NULL? Thanks
I figure you have a reason for considering using nulls but since you didn't explain the underlying problem you are tryingto solve it is hard to comment or consider. If you share there may someone who can provide a solution that doesn'tviolate best practices and common sense. David J. On Apr 29, 2011, at 10:03, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: > On Thu, Apr 28, 2011 at 10:59 PM, Simon Riggs <simon@2ndquadrant.com> wrote: >> On Wed, Apr 27, 2011 at 5:24 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: >>> Possibly a dumb question but there isn't much about this. >>> http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=postgresql+null+value+disk+space >>> I have some BOOLEAN columns. 90% of the cases of the columns is FALSE. Do I >>> save disk space by having them as NULL instead of FALSE? So my application >>> would have conditional code for NULL and TRUE, instead of FALSE and TRUE. >>> Thanks... >> >> Yes, NULL values take no additional space, but the row needs a null >> bitmap so it is possible that if this was the only NULL then it could >> occupy more space. >> >> If you have multiple columns, then you should use NULLs. > > > Thanks Simon. (And others for good advice, but that was not my > question. I already know using boolean as TRUE/FALSE is sensible. But > I have a peculiar reason for asking what I am.) > > Simon, if I understand you correctly -- more than one column in a row > should have NULL for NULL to be useful in saving space? What if in a > row there are many columns but only one will be NULL? > > Thanks > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general