Storage cost of a null column

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)

Randall Skelton <skelton@brutus.uwaterloo.ca> writes:[color=blue]
> What is the storage cost of a null entry in a column?[/color]

If you have even one, all the rest in that row are free, so your scheme
sounds reasonable.

Null columns are simply not stored. There is a bitmap at the start of
the row with one bit per column, indicating which ones are null or not
null. However we omit the bitmap if all columns are nonnull in a
particular row. So it's reasonable to consider the cost of the first
null as being the size of the bitmap (N bits for an N-column table,
rounded up). The rest are free.
[color=blue]
> Because of this, we made a decision that we would have hundreds of 2
> column tables (timestamp, value) and use unions, intersections, and[/color]

Narrow tables are a dead loss if you're concerned about storage space
--- you'll get eaten by the per-row overhead, which is a minimum of 28
bytes per row.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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:[color=blue]
> 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)[/color]

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/[color=blue]
> This space intentionally left blank[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAbb9cY5Twig3Ge+YRAp8VAKCRR+QeYE9enrm0132bdp YRoXlKQQCbBfeu
fhLJtGh3Gd0wiva4eNEYM/E=
=5blo
-----END PGP SIGNATURE-----

Many thanks Tom and Martijn. It seems that I have a few tables to
reformat...
[color=blue]
> Narrow tables are a dead loss if you're concerned about storage space
> --- you'll get eaten by the per-row overhead, which is a minimum of 28
> bytes per row.[/color]


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org