Is the delete marker also set on old keys on UPDATE? Or just DELETE -> INSERT?
I ran into the ever-growing FTS index issue last year. I’m creating DB diffs which also contain some FTS3/4 tables. The tables get constantly updated for the checksum. The DBs were always vacuum’ed, but the growing FTS index caused the db to grow from 50MB to 150MB+ in my case (and I think the „non-grwon“ FTS tables including indices accounted for about 1/3 of the whole DB size according to the SQLite analyzer). On "server side“ (DBs are created on a server and then deployed to some smartphone apps) the issue was easily resolvable with the optimize command. On client side (smartphone) however each diff still causes the DB to grow - optimize() is there also not possible for the same reasons. Ben Am 02.05.14 11:22 schrieb "Dan Kennedy": >So I'm thinking a solution might be: > > * Fix FTS so that it picks this case - when a merge includes so many > delete markers that the output is small enough to be deemed a >level-N > b-tree, not a level-N+1 b-tree, and > > * Instead of using the default 16-way merges, the app could organize > to periodically invoke the "merge=X,Y" command with a smaller Y >value > (say 2) to limit the maximum size of the index to Y times its >optimal > size (instead of 16 times). > >It is an interesting problem. And the above is just guesswork... It would >be good to verify experimentally that the index really does grow >indefinitely >with this kind of input before trying to "fix" anything. > >Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
