Measure zeros and missing values

Measure the actual values before deciding whether a column's representation needs attention. A zero value, an SQL NULL and an absent row have different meanings and should be counted separately.

sql

CREATE TABLE sampled_measurements(value INTEGER);
INSERT INTO sampled_measurements VALUES (0), (0), (5), (NULL);

SELECT count(*) AS total_rows,
       count(*) FILTER (WHERE value = 0) AS zero_rows,
       count(*) FILTER (WHERE value IS NULL) AS null_rows,
       1.0 * count(*) FILTER (WHERE value = 0) / nullif(count(*), 0)
           AS zero_fraction
FROM sampled_measurements
FORMAT csv;

The denominator includes every row. If the reader task concerns only present measurements, use count(value) as the denominator and explain that choice. nullif protects the calculation for empty input rather than turning an undefined ratio into a misleading zero.

This distribution describes the selected rows, not on-disk compression savings. Coordinator DB does not document a sparse-serialization tuning switch tied to a zero ratio. Choose a suitable declared type, preserve the meaning of missing values and measure representative query behavior before changing the schema.

See aggregate functions, NULL handling and type selection.

Measure zeros and missing values ​

Measure zeros and missing values