How to calculate the size of my database for n records ?

[Nicolas Gaiffe]

Hello,

I'm quite new at sqlite and I need to estimate the size the database I am
creating could reach. Basically, estimating for any table how many bytes
would be used for an average record.
I mainly use INTEGER and REAL values, along with a few TEXT attributes but I
couldn't find the number of bytes used to store those datatypes. Anyone
could help me on this ?
Is there any header or separator I need to take into account ?

By the way, it seems INTEGER are stored on 2 to 8 bytes (from
http://theopensourcery.com/sqlitedocs/sqdatatype3.html ). What about 16-byte
integers ? Do I have to declare them as REAL to store them ?

Many thanks in advance
Best regards

Nicolas

[Igor Tandetnik]

"Nicolas Gaiffe"

Post by Nicolas Gaiffe
By the way, it seems INTEGER are stored on 2 to 8 bytes (from
http://theopensourcery.com/sqlitedocs/sqdatatype3.html ). What about
16-byte integers ?

SQLite doesn't support those.

Post by Nicolas Gaiffe
Do I have to declare them as REAL to store them ?

That's one option, but note that you'll lose precision. REAL is stored
as 64-bit IETF floating point value, which has 52 mantissa bits (so not
even all 64-bit integers can be exactly represented, let alone 128
bits).

You could store such integers as BLOBs, but then you won't be able to do
any math on them within SQL. Or, you could store them as two 64-bit
integers (high and low halves in two separate columns), then you could
do some math but it'll be rather awkward.

Igor Tandetnik

[Nicolas Gaiffe]

Thank you Igor.

Does anyone have a clue about estimating the size of a database ?

Post by Nicolas Gaiffe
I'm quite new at sqlite and I need to estimate the size the database I am

creating could reach.

Post by Nicolas Gaiffe
Basically, estimating for any table how many bytes would be used for an

average record.

Post by Nicolas Gaiffe
I mainly use INTEGER and REAL values, along with a few TEXT attributes but

I couldn't find

Post by Nicolas Gaiffe
the number of bytes used to store those datatypes. Anyone could help me on

this ?

Post by Nicolas Gaiffe
Is there any header or separator I need to take into account ?

[John Machin]

Post by Nicolas Gaiffe
Thank you Igor.
Does anyone have a clue about estimating the size of a database ?

Post by Nicolas Gaiffe
I'm quite new at sqlite and I need to estimate the size the database I am

creating could reach.

Post by Nicolas Gaiffe
Basically, estimating for any table how many bytes would be used for an

average record.

Post by Nicolas Gaiffe
I mainly use INTEGER and REAL values, along with a few TEXT attributes but

I couldn't find

Post by Nicolas Gaiffe
the number of bytes used to store those datatypes. Anyone could help me on

this ?

Post by Nicolas Gaiffe
Is there any header or separator I need to take into account ?

If you want to spend a lot of time in calculations, and probably get it
wrong, you could rummage through the incredibly well documented database
structure, available via the "Documentation" tab on the SQLite3 home page.

A practical alternative: create an empty table, generate a script that
does N inserts with typical values, run the script several times (noting
the file size after each run), fit a regression line. If you have
columns that can vary wildly in width, it would be a good idea to use a
spread of values rather than repeat the average value N times -- this is
especially true if you have rows so big that they will go into
overflow pages.

HTH,
John