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

Поиск
Список
Период
Сортировка
От Chris Hoover
Тема What is the difference in storage between a blank string and null?
Дата
Msg-id 1d219a6f0804111302r4adaf97dn4ecb0549c5ae85dc@mail.gmail.com
обсуждение исходный текст
Ответы Re: What is the difference in storage between a blank string and null?
Re: What is the difference in storage between a blank string and null?
Список 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!

В списке pgsql-admin по дате отправления:

Предыдущее
От: Shane Ambler
Дата:
Сообщение: Re: Retore Postgres DB without dump
Следующее
От: Kenneth Marshall
Дата:
Сообщение: Re: What is the difference in storage between a blank string and null?