sqlite - 如何对部分填充的 sqlite 页面进行碎片整理/合并以回收空间？

Question

我正在设计一个带有本地数据存储的小型服务器应用程序，而 sqlite3 似乎是管理持久数据的方式。但我担心知道内部逻辑的恶意用户可能会欺骗服务器创建(并随后删除)大量记录，从而使每个数据页中保留一些有效记录。数据库大小可能很快就会爆炸。

Following the documentation of 以及类似 https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/ 的建议暗示即使 auto_vacuum=incremental 在这种情况下也无济于事，因为它只对已发布的页面有效，对具有内部间隙(即碎片)的已用页面无效。

有没有什么好方法可以让 sqlite 即时整合这些数据？

由于长期存在的全局数据库锁，VACUUM 操作不是一个选项。

Answer 1

Sqlite 会自动将几乎空白的页面与相邻页面合并，以帮助减少您描述的碎片。

来自 an email from D Richard Hipp在 sqlite 邮件列表上:

Once a sufficient number of rows are removed from a page, and the free space on that page gets to be a substantial fraction of the total space for the page, then the page is merged with adjacent pages, freeing up a whole page for reuse. But as doing this reorganization is expensive, it is deferred until a lot of free space accumulates on the page. (The exact thresholds for when a rebalance occurs are written down some place, but they do not come immediately to my mind, as the whole mechanism just works and we haven't touched it in about 15 years.)