Date: Fri, 11 Apr 2008 16:24:40 -0500
From: Kenneth Marshall <ktm@rice.edu>
To: Chris Hoover <revoohc@gmail.com>
Cc: "pgsql-admin@postgresql.org Admin" <pgsql-admin@postgresql.org>
Subject: Re: What is the difference in storage between a blank string and
 null?
Message-ID: <20080411212439.GD769@it.is.rice.edu>
References: <1d219a6f0804111302r4adaf97dn4ecb0549c5ae85dc@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1d219a6f0804111302r4adaf97dn4ecb0549c5ae85dc@mail.gmail.com>
User-Agent: Mutt/1.5.16 (2007-06-09)

On Fri, Apr 11, 2008 at 04:02:36PM -0400, Chris Hoover wrote:
> I'm doing some testing on how to decrease our database size as I work on a
> partitioning scheme.
> 
> I have found that if I have the database store all empty strings as nulls, I
> get a significant savings over saving them as blank strings (i.e. '').
> Below is an example of savings I am seeing for the same table:
> 
> In my test case, storing empty strings  give me a table size of 20,635,648
> Storing empty strings as nulls gives me a table size of: 5,742,592.
> 
> As you can see, storing empty strings as nulls is saving me approximately
> 72% on this table.  So, I am wanting to understand what Postgres is doing
> differently with the nulls.  Would someone kindly enlighten me on this.
> 
> (P.S. I am using a nullif(trim(column),'') in my partition and view rules to
> store the nulls, and coalesce(column,'') to give my application the data
> back without nulls.)
> 
> Thanks,
> 
> Chris
> 
> PG 8.1
> 

PostgreSQL stores NULLs differently. This accounts for your space
difference. If you application can work with NULLs instead of ''
(not the same thing), go for it.

Cheers,
Ken