On 11 May 2014 00:01, Scott Robison-2 [via SQLite] < ml-node+s1065341n75608...@n5.nabble.com> wrote:
> I suspect the primary use case it was designed and tested for (and in fact > the way we use it at my place of employment) was more for "only growing > datasets" and less for an environment where stuff is continually being > added and deleted. We use SQLite as a "caching data structure" for > information read from a proprietary less functional third party database > product so as to speed up queries for a session, but the session is > discarded when the program is closed. (Trust me, it makes sense for us.) > Understood. I can see that there are lots of cases where FTS3, or sqlite in general could be used without needing to regularly delete content, or where there is the opportunity to rebuild indexes. I was a bit remiss in making use of FTS3 in our system without really investigating this behaviour. Having successfully made use of sqlite in our software (that runs on large servers and embedded devices) to store the application configuration and automatically generated data at runtime for many years, I assumed FTS would allow the same pattern when it came to storing searchable text. > > That does not mean that zero consideration was given to your use case, but > I suspect (though I have no evidence to back me up) that the test cases > for > deletion were focused on correct functionality in the face of relatively > random insertions and deletions, not for "record leaks" due to future > merge > failures. > > In any case, I was not aware of this problem, and can see where (if > validated, and it seems reproducible per your directions) it is a problem > for some use cases and some cleanup would be useful for those use cases. > Regardless, it does seem to "work correctly" though in a suboptimal way > per > resource usage. > How could I go about getting this issue 'validated'. The company I work for has considerable interest in seeing this resolved, and as myself and my team have other no experience of modifying sqlite (and we have several other projects needing progressed), we would like to pay someone to take this further. If anyone is interested in taking this on please get in touch! > Have you tried creating a new database at some point that consists of only > the "live active" records in a "leaky" database so as to compare what the > size could/should be if the "current data set" had been created with zero > deletions of data along the way? > If I understand your question correctly, then the python script (linked in original mail) provides this. It first adds 2000 documents, then goes into a cycle of deleting and adding 100. When 2000 have been added, the db size is 108Kb. 70 pages of which is the content, and 57 being made up by the metadata in the rest of the FTS tables. This would be better if the 'optimize' command was used, but that is a separate issue. For my purposes I don't really mind if the index size is the same size as the data, what matters is; 1) The index does not grow indefinitely. It can be predictably kept within some bounds. 2) Merge operations can still be undertaken incrementally. Turning 'merge' into 'optimize' isn't a solution as it takes too long to run on a large dataset. > -- > Scott Robison > _______________________________________________ > sqlite-users mailing list > [hidden email] <http://user/SendEmail.jtp?type=node&node=75608&i=1> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ------------------------------ > If you reply to this email, your message will be added to the discussion > below: > > http://sqlite.1065341.n5.nabble.com/FTS3-4-merge-function-behaviour-when-deleting-rows-tp75370p75608.html > To unsubscribe from FTS3/4 merge function behaviour when deleting rows, click > here<http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75370&code=Y3VyaW91c3NxdWlkQGdvb2dsZW1haWwuY29tfDc1MzcwfDExNzkwNTE1Mjc=> > . > NAML<http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> > -- View this message in context: http://sqlite.1065341.n5.nabble.com/FTS3-4-merge-function-behaviour-when-deleting-rows-tp75370p75637.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
