Wasted file space estimate
(1) By Max (Maxulite) on 2021-11-09 08:04:36 [link] [source]
as I see, one can estimate a free space inside an sqlite file by multiplying the big endian dword at the offset 36 by the page size. Is this correct assumption? I had an example recently when I didn't know whether a 4GB base was vacuumed or not and this gave me a 1.5 GB estimate more or less confirmed after the VACUUM.
Also I know that the file format is probably not going to change anytime soon so if this assumption is correct, one can even write a hard-coded function accessing this 36-offset value, but adding a pragma reporting the free space estimate would be great. In this case Sqlite can take some border cases or other wizard knowledge if I missed something.
(2) By Richard Hipp (drh) on 2021-11-09 10:35:35 in reply to 1 [source]
You really shouldn't open() and close() SQLite database files using your own code. Use SQLite APIs only. The reason for this is that if another thread in your application has the database open using SQLite, the close() call you make will clear the posix advisor locks. The SQLite connection has no way to prevent or detect this. Clearing locks out from under an SQLite database connection could lead to database corruption.
A better solution would be to open the database using sqlite3_open() or similar, and then run "PRAGMA page_size; PRAGMA freelist_count;" to get the values you are looking for.
(3) By Max (Maxulite) on 2021-11-09 11:14:26 in reply to 2 [link] [source]
Thanks, not messing up with file open sounds very reasonable.
I somehow missed the freelist_count pragma so no need for something new. The following query should do the trick probably
SELECT (freelist_count*page_size) as FreeSizeEstimate FROM pragma_freelist_count, pragma_page_size