Column size with nulls

CrateDB
1

Hello all,

Is there an actual storage cost with nulls ? i.e. in a double precision column with a null, is 8 bytes stored for that record ?

Many thanks
David.

2

Hi @djbestenergy,

NULL values do not consume storage. To validate this, I created two tables with identical values (1.5 million records), but the second table had an additional column with only NULL values. sys.segments can be used to calculate the total table size:

SELECT table_name, SUM(size)
FROM sys.segments
WHERE table_name IN ('t1', 't2')
GROUP BY 1;

Before comparing table sizes, it is recommended to run OPTIMIZE TABLE <table name> WITH (max_num_segments = 1) to merge all data into one segment (for better comparability).

In my test case, both tables had an identical size (< 1% difference).
When filling the additional column in the second table with non-null values for 50% of the rows, storage consumption only increased by 9%. The savings here are due to the compression, which is why total storage consumption isn’t easy to predict. But the experiment showed, that there is no storage cost for NULL values.

1 Like