databasepostgresqldatetimenullstorage

Does Null save space for TIMESTAMP data type in PostgreSQL?


I'm trying to create tables to store very large volume of data, and I wonder if I can use null TIMESTAMP to save storage?

I have a variable update_at, which will be updated to NOW() whenever I modify the associated row - most of the rows will not be modified. In this case, when creating the table, will update_at TIMESTAMPTZ save me some space compared to update_at TIMESTAMPTZ NOT NULL DEFAULT NOW()?

Given Checking if string has letter e (case insensitive) sql query using one where condition, I know that:

For fixed width fields like nullable int the storage space required is always the same regardless of whether the value is null or not.

For variable width nullable fields the value ’NULL‘ takes zero bytes of storage space (ignoring the bit to store whether the value is null or not).

But how about TIMESTAMPTZ or DATE data types? I'm not sure if they were "variable width nullable fields" or "fixed width nullable fields" - consequently, not sure if I can save some storage space using Null.

Thank you so much for your help!


Solution

  • A simple test could resolve this, as

    --create tables with 1 million rows
    create table test_null as
    select id::int8, null::timestamptz
    from generate_series(1, 1000000) as id;
    
    create table test_notnull as
    select id::int8, now()::timestamptz
    from generate_series(1, 1000000) as id;
    
    --refresh table statistics
    analyze test_null;
    analyze test_notnull;
    

    Checking table size in stoge

    select 'test_null' table_name, pg_size_pretty(pg_total_relation_size('test_null')) size
    union all
    select 'test_notnull' table_name, pg_size_pretty(pg_total_relation_size('test_notnull')) size;
    
    table_name size
    test_null 35 MB
    test_notnull 42 MB

    Fiddle to test