0

I'm comparing two very barebones SQLite databases with the same data stored differently.

Database A has tables like this:

string (TEXT UNIQUE) count_1 (INTEGER) count_2 (INTEGER)
the 23 10
it 56 44
was 32 89

Database B has tables like this:

string (TEXT) count_1 (INTEGER) count_2 (INTEGER)
the 23 null
the null 10
it 56 null
it null 44
was 32 null
was null 89

Strangely, database A takes up almost 50% more disk space than database B (4.8MB vs 2.5MB) after a VACUUM on both. This defies what I would expect, since database B has double the rows and has to repeat each string value twice. I'm wondering if there's any explanation for this, or if I'm just generating the tables wrong in some way. Apologies if I'm missing something very basic here — I'm very new to RDBs.


UPDATE: I've now determined that the UNIQUE constraint on the string column in database A is what's causing the size doubling — I'm curious as to why it does that!

1 Answer 1

1

Adding a UNIQUE constraint forces SQLite to create an Unique index for the table in the database.

The index has a copy of the string column and a reference to the ROWID in the table (which is an integer) for every row in the table, so its size is comparable to that of the table.

SQLite is very efficient in storing values, so the NULLs in Database B don't occupy unnecessary space and INTEGERS are stored using varints, which have variable length depending on the stored value.

count_1 and count_2 have low values that can be stored in single bytes. ROWIDs in the unique index can instead occupy 2,3 or more bytes (if there are millions of rows), so a string+ROWID in the index can occupy more space than string+NULL+byte in the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.