I’ve deleted some tags that were assigned to over 100k stagsi files. Is it normal that the Stagsi.sqlite file (in \AppData\Roaming\Soletude\Stagsi\Database) remains the same size after such big changes? I expected it to be reduced. Is there something we must do to clean a sqlite database?
SQLite doesn’t clean up its databases by default because it doesn’t know when to do it. It’s a potentially long operation. So it relies on program authors to call VACUUM when they want (and if they want).
SQLite manages its database in such a way that space that was freed up by you deleting those tags will be reused later for other data (e.g. as you add more tags). So the database remains the old size even after huge delete operations. This isn’t usually a problem since .sqlite files are small (tens of megabytes at most) but if you wish, you can execute the “VACUUM” command (just “VACUUM;”) in SQLite Browser (whilst Stags is closed) to reduce and optimize the database. From SQLite docs:
Unless SQLite is running in “auto_vacuum=FULL” mode, when a large amount of data is deleted from the database file it leaves behind empty space, or “free” database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.
Your case can be actually extreme and VACUUM can give you a certain boost in performance. Overall, in your situation I think VACUUMing the database once a year or in 6 months can be useful. However, I haven’t dealt with such large SQLite databases so I can’t say if performance will improve or if SQLite manages your database well enough and reuses unused space efficiently.