CODEWITHSUNDEEP
androidsqlite
Munish Kapoor
Munish Kapoor

Reputation: 3339

Check how much space a single row takes in Sqlite

Is there a way to check how much space a single row takes in Sqlite?

I have tried using this way:

I have 4 fields in my table:

id(int)
name(text)
pat(text)
is_default(bool) 


 select *, sum(length(id)+length(name)+length(pat)+length(is_default)) 
    from color;

But it returns character length (for example: 43). How can I determine the row length in bytes?

Upvotes: 3

Views: 8733

Answers (2)

Davor Josipovic
Davor Josipovic

Reputation: 5504

As hinted in this answer, determining the exact space occupied by a single row is not possible because -- among other reasons -- a row may not be a multiple of the page size, which is the main space unit of a database file.

The best one can do is compute an approximate size of each row, and then sum them up. sqlite3_analyzer is a viable option, but what if you need the row size within a DML statement? The following is focusing on the latter.

SQLite data storage is complicated because of its dynamic typing system, unless STRICT is used on the table. Then there is the TEXT encoding that needs to be taken into account, which is solved in v3.43.2 with octet_length(TEXT) that gives its exact binary size. length(BLOB) already does that for BLOBs. Then there is the variable size of INTEGER and even REAL in some cases. Then the question of whether there is a PK and an invisible ROWID column. And should indexes be taken into account as part of the table? And then you have all the meta-data.

All these aspects could be summarized into a GENERATE ALWAYS ... VIRTUAL column to give a quick and efficient row size estimate with a simple SELECT size FROM table; query.

I encourage people to propose insights on how to improve these estimates.

Upvotes: 0

CL.
CL.

Reputation: 180020

SQLite itself does not give you this information, but the sqlite-analyzer tool does.

If you want to do this from your app, you have to read all the record values and from those deduce how many bytes are used in the on-disk record format.

Upvotes: 5

Related Questions