CODEWITHSUNDEEP
sqlite
jingyin
jingyin

Reputation: 335

space consumption of null columns in sqlite db

Say I have a db column that's only used rarely by records in my sqlite db (for the rest of the records, the value is null), would those null columns consume as much space comparable to if those columns were non-existent?

Upvotes: 13

Views: 2562

Answers (2)

relatively_random
relatively_random

Reputation: 5166

TLDR: Adding a column should always add at least one extra byte per row. Nulls, booleans and empty strings consume only that one mandatory byte, other values also need additional storage for the contents. Declared column types don't matter, only the values you actually store.

According to the docs, each record (table row) contains 1 varint specifying the number of columns, followed by 1 varint per each column specifying content types, then the actual contents of each column. Content type null has zero bytes of contents, as do integers 0 and 1, and empty strings or blobs.

Since varint is 1 byte for integers <= 127, each row is usually 1 B + 1 B per column plus column contents. Nulls, 0s, 1s, empty strings and blobs need no contents. Other integers take either 1, 2, 3, 4, 6 or 8 bytes depending on their value. Floating point numbers which can be exactly represented by integers get stored as integers to save space.

On top of that, there is some overhead for organizing those records into a B-tree.

Upvotes: 0

hamstergene
hamstergene

Reputation: 24439

In my test program, NULL values have consumed one byte per row. If the average row size in your table will be above 100 bytes, then yes, it's comparable to nonexistent.

Upvotes: 12

Related Questions