Re: Storage cost of a null column - Mailing list pgsql-general

From Martijn van Oosterhout
Subject Re: Storage cost of a null column
Date
Msg-id 20040402193036.GA1406@svana.org
Whole thread Raw
In response to Storage cost of a null column  (Randall Skelton <skelton@brutus.uwaterloo.ca>)
List pgsql-general
A NULL costs almost nothing, in anycase, less than a real value.

There is the cost of the NULL bitmap, 1 bit per column for each column
if there are *any* NULLs in a row, but once you'ce got one, the rest
are free for that row.

This is in the documentation somewhere...

On Fri, Apr 02, 2004 at 12:18:47PM -0500, Randall Skelton wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> This space intentionally left blank

Attachment

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Storage cost of a null column
Next
From: Randall Skelton
Date:
Subject: Re: Storage cost of a null column