Size occupied on disk by a table in Sqlite

Question

In Oracle, when we want to know how much a table occupies disk we can make a query to the dictionary dba_segments.

SELECT segment_name, segment_type, bytes/1024/1024 MB
FROM dba_segments
WHERE segment_type='TABLE' and segment_name='<nome-tabela>';

In the Sqlite database how to know exactly how much a table is occupying on the disk? Is there a dictionary? I have seen bad matrix calculation solutions to return data that I would not like to use that gives a flawed calculation for obvious reasons (overestimated):

SELECT COUNT(*) *  -- The number of rows in the table
     ( 24 +        -- The length of all 4 byte int columns
       12 +        -- The length of all 8 byte int columns
       128 )       -- The estimate of the average length of all string columns
FROM MyTable

Answer 1

If you need to know the exact size while running your code, it is chipped. The only way I see it is to read the whole table and add up the real size occupied. And this can’t even be done directly. You have to see the internal implementation to calculate the size of some columns. And if the implementation changes, it’s gone. That is, don’t bother with this.

If you only need to have an idea at some point per your organization, you can use the sqlite_anlyzer. Maybe you can read the output via code, but it is gambiarra. Anyway there is not ready for Android. You’d have to compile, maybe even adapt to run it.

If it is very important to always know the size of the table (doubt) it is possible to do another gambiarra. I created a TRIGGER that keeps an accumulator in each writing done in the table. This accumulator will always have the size of the table. It is not so simple to do, has overhead and creates a burden for maintenance (although it can abstract this), but it is a solution (...questionable). Basically what Oracle does :)

I have seen other "solutions", one worse than the other.