Dear sqlite users, I noticed that when you delete lines from a FTS virtual table, somehow there is some data remaining in the sqlite db, so that's it does not shrink much in size.
$ sqlite3 test.sql "CREATE VIRTUAL TABLE tab USING fts5(x)" $ curl -s https://www.wikipedia.org | tr -cd '[:alnum:][:space:]' > wikipedia $ ls -lh wikipedia test.sql -rw-r--r-- 1 mkloss mkloss 24K feb. 25 06:55 test.sql -rw-r--r-- 1 mkloss mkloss 54K feb. 25 06:56 wikipedia $ sqlite3 test.sql ".import wikipedia tab" && ls -lh test.sql -rw-r--r-- 1 mkloss mkloss 148K feb. 25 06:56 test.sql $ sqlite3 test.sql "delete from tab" && ls -lh test.sql -rw-r--r-- 1 mkloss mkloss 148K feb. 25 06:56 test.sql $ sqlite3 test.sql "VACUUM" && ls -lh test.sql -rw-r--r-- 1 mkloss mkloss 124K feb. 25 06:56 test.sql I would expect the db size to be 24K (not 124K), as it was when the table "tab" was empty. I noticed that some data remains in the tab_XXX tables, but less than 700 bytes. That's nowhere near the 100K of added cruft: $ for t in tab_{config,content,data,docsize,idx}; do echo "select * from $t;"; done | sqlite3 test.sql | wc -c 682 So here is my questions: (1) How do you really cleanup a db with FTS tables after deleting some lines? (2) If there is no way to remove the cruft, does that mean that adding and deleting lines will constantly inflate the db size? $ sqlite3 --version 3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1 Thank you for your help, Regards, Matthew _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
