Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

Use of assignment to $[ is deprecated at /usr/local/sbin/apxs line 86. , wwwxxx vim, mysql closing table and opening table, 800c5000, setgid operation not permitted, pciehp: acpi_pciehprm on IBM, WWWXXX.DBF, 078274121, info0a ip, should prodicers of software_based services be held liable or not liable for economic injuries

Links

XODOX
Impressum

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

Posted on 2008-04-11 22:02:36 by Chris Hoover

------=_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--

Report this message

#2: Re: What is the difference in storage between a blank

Posted on 2008-04-11 23:24:40 by Kenneth Marshall

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Report this message

#3: Re: What is the difference in storage between a blank stringand null?

Posted on 2008-04-12 07:14:34 by Shane Ambler

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
>

Without looking at the exact storage algorithms or being real picky
about exact specifics -

NULL only needs one bit of storage for each row to indicate that there
is no value stored. This may become one byte if there is only one column
in the table. This is most likely in row storage overhead anyway.

An empty string will use one byte to save the string length (being 0).

So one bit against 1 byte...

It really depends on how many real world rows will have empty strings as
to how much you save.

Chapter 53 in the manual gives a brief overview of data storage but most
of the details will be found in the source.


--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Report this message