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?
Date: Fri, 11 Apr 2008 16:24:40 -0500
Message-ID: <20080411212439.GD769@it.is.rice.edu> (raw)
In-Reply-To: <1d219a6f0804111302r4adaf97dn4ecb0549c5ae85dc@mail.gmail.com>
References: <1d219a6f0804111302r4adaf97dn4ecb0549c5ae85dc@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ktm@rice.edu, revoohc@gmail.com
Subject: Re: What is the difference in storage between a blank string and null?
In-Reply-To: <20080411212439.GD769@it.is.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox