Storage cost of a null column

From: Randall Skelton <skelton(at)brutus(dot)uwaterloo(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Storage cost of a null column
Date: 2004-04-02 17:18:47
Message-ID: CD28D203-84C9-11D8-BEF7-000393C92230@brutus.uwaterloo.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

What is the storage cost of a null entry in a column? i.e. does a null
entry of type integer, float8 or text consume the same amount of
storage as one that is filled? I ask because I have satellite data
which is transmitted via a dodgy RF link that drops data packets. This
means I have a number of columns in a table that are null. Moreover,
the operations people decided to use a compression scheme whereby
non-changing bit/integer values are not output at regular intervals
which also adds a considerable number of null entries into the columns.
Because of this, we made a decision that we would have hundreds of 2
column tables (timestamp, value) and use unions, intersections, and
joins to get what was needed. Unfortunately, this has made application
programming a real nightmare as we are often forced to reconstruct a
snapshot frame for the range of times either in C or have the app
create temporary tables in SQL and insert the relevant data prior to
selecting it. As it stands, we've ordered a new disk array and
provided that the storage costs are not that high, I will probably be
reorganising all this next week. If anyone has any other suggestions,
I'd be very keen to hear them.

Cheers,
Randall

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Manfred Koizar 2004-04-02 18:05:01 Re: Large DB
Previous Message wespvp 2004-04-02 17:08:21 Re: Compound keys and foreign constraints