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

From: "Chris Hoover" <revoohc(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org Admin" <pgsql-admin(at)postgresql(dot)org>
Subject: What is the difference in storage between a blank string and null?
Date: 2008-04-11 20:02:36
Message-ID: 1d219a6f0804111302r4adaf97dn4ecb0549c5ae85dc@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

--
Tired of HIGH Gas prices? Visit http://colafuelguy.mybpi.com to start
saving at the pump no matter where you live!

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kenneth Marshall 2008-04-11 21:24:40 Re: What is the difference in storage between a blank string and null?
Previous Message Shane Ambler 2008-04-11 16:50:53 Re: Retore Postgres DB without dump