CODEWITHSUNDEEP
databasesqlitenulldiskspace
Eminem
Eminem

Reputation: 7484

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

Upvotes: 7

Views: 2742

Answers (1)

CL.
CL.

Reputation: 180200

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.

Upvotes: 12

Related Questions