Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 4EE6A2E005D
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Fri, 11 Apr 2008 17:09:45 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 70841-10
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Fri, 11 Apr 2008 17:09:41 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from gv-out-0910.google.com (gv-out-0910.google.com
 [216.239.58.184])
	by developer.postgresql.org (Postfix) with ESMTP id AB0FE2E004F
	for <pgsql-admin@postgresql.org>; Fri, 11 Apr 2008 17:09:41 -0300 (ADT)
Received: by gv-out-0910.google.com with SMTP id y18so249642gvf.7
	for <pgsql-admin@postgresql.org>; Fri, 11 Apr 2008 13:09:30 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type;
	bh=GWUWk7W9Zce2of9Qu/I9F7nPgBbzo9WNbOeQtxPjsUw=;
	b=nJ9NgISVd8yww4NE5QJ0+LJsPKEafKaYutq3+mMGFrvdyJQxjHIBasCcocDNTgniFTh53gbmC4HNmllSp/ObymCzHdote6Hxd0n47+25oToAL1YadUV0o+wuhvRox6F3q1YNy+rrUk7AJNoM1knIYkIN73GereSBLw/alqz0WXM=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type;
	b=V+2EMVh/06k7I3kFKR9ATu5Fw38VUiY4qNSFdHD/a79a8YORWIduTeublE9U4yuSEvFaqQxczXZBAx3VXlC4eUwTOncMLQHKdpgKce6lx1EHBbVgBtiwNxLZMA3wBneAGjp4XKNr+PKl+2w5YeT0UnpxSwHUJanb9p0TmmBmR+w=
Received: by 10.151.39.2 with SMTP id r2mr3741056ybj.243.1207944156312;
	Fri, 11 Apr 2008 13:02:36 -0700 (PDT)
Received: by 10.150.225.12 with HTTP; Fri, 11 Apr 2008 13:02:36 -0700 (PDT)
Message-ID: <1d219a6f0804111302r4adaf97dn4ecb0549c5ae85dc@mail.gmail.com>
Date: Fri, 11 Apr 2008 16:02:36 -0400
From: "Chris Hoover" <revoohc@gmail.com>
To: "pgsql-admin@postgresql.org Admin" <pgsql-admin@postgresql.org>
Subject: What is the difference in storage between a blank string and null?
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_26082_24101719.1207944156311"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.476 tagged_above=0 required=5 tests=AWL=-0.657,
	HTML_MESSAGE=0.001, URIBL_OB_SURBL=2.132
X-Spam-Level: *
X-Archive-Number: 200804/99
X-Sequence-Number: 28863

------=_Part_26082_24101719.1207944156311
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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!

------=_Part_26082_24101719.1207944156311
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I&#39;m doing some testing on how to decrease our database size as I work on a partitioning scheme.&nbsp; <br><br>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. &#39;&#39;).&nbsp; Below is an example of savings I am seeing for the same table:<br>
<br>In my test case, storing empty strings&nbsp; give me a table size of 20,635,648<br><meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8"><title></title><meta name="GENERATOR" content="OpenOffice.org 2.4  (Linux)">Storing empty strings as nulls gives me a table size of: 5,742,592.<br>
<br>As you can see, storing empty strings as nulls is saving me approximately 72% on this table.&nbsp; So, I am wanting to understand what Postgres is doing differently with the nulls.&nbsp; Would someone kindly enlighten me on this.<br>
<br>(P.S. I am using a nullif(trim(column),&#39;&#39;) in my partition and view rules to store the nulls, and coalesce(column,&#39;&#39;) to give my application the data back without nulls.)<br><br>Thanks,<br><br>Chris<br>
<br>PG 8.1<br clear="all"><br>-- <br>Tired of HIGH Gas prices?&nbsp; Visit <a href="http://colafuelguy.mybpi.com">http://colafuelguy.mybpi.com</a> to start saving at the pump no matter where you live!<br>

------=_Part_26082_24101719.1207944156311--