SQLite Forum

How to estimate a database disk file size or in-memory db memory usage ? ?
Login

How to estimate a database disk file size or in-memory db memory usage ? ?

(1) By anonymous on 2021-08-20 02:52:54 [source]

I have two questions regarding the database file size estimation as follows:

  1. If I have a database with a fixed length table, can I estimate the database file size or in-memory database memory usage ?

  2. If I only execute the UPDATE command to this fixed length table, which means I use it as a circular recording table, will the database page count be increased ?

(2) By Keith Medcalf (kmedcalf) on 2021-08-20 03:25:59 in reply to 1 [link] [source]

https://sqlite.org/fileformat.html

You can do a "very rough" calculation as follows:

one byte for the count of fields
one byte for each field to indicate the type
additional bytes for text/blob fields for the max length
(that is, if it is a text field of length 3000 then that will take 3 byes, lets say)
and then for each field
+ 8 bytes for a integer or float
+ N bytes for each text/blob field (max)
four bytes for the length of all the above

this is the "record size" (more or less)

Compute the number of "records" that will fit on a page
Divide the number of records by the number records per page

This will give you the "fully packed" size (min data size)

The size of the table will be somewhere between the above size and the size yielded if each record occupies one page (assuming that the record size is less than the page size). If you keep updating the table, eventually it will reach a "stable size" that is somewhere between the two aforestated extremes.