Re: What is the difference in storage between a blank string and null?

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