Search code examples
databasesqlitenulldiskspace

Is disk space consumed when storing null data?

I use the sqlite database.
When adding records, disk space is obviously used for non null data. But what about data records that contain null values? Why Im asking is that I will have a large number of columns (50+) and millions of records that will contain null data and Im wondering if its the most efficient way of designing my database

Solution

  • In SQLite's record format, exactly one byte is need to specify that a value is NULL.

    If you normalize your database so that you have one record per non-NULL value, you save those NULL bytes, but you have additional overhead for the non-NULL values. SQLite's minimum record overhead is:

    • two bytes cell pointer,
    • at least one byte payload length,
    • several bytes for the rowid, and
    • for your case, several bytes for the foreign key that points back to the original record.

    Additionally, storing non-NULL values in a separate table might require an index to get efficient lookups.