SQLite is pretty good at using free space inside the file. So if inserting something is going to run you out of space, then it's going to run you out of space whether the file was previously vacuumed or not.
Also reminder that when vacuum is run, SQLite makes a brand new copy of the database, then goes through and updates the pages of the original file, which requires writes to the rollback journal. So if your database is size n. Then the worst case scenario is that vacuum will peak out at using 3n worth of disk space. (Original file, copy, journal) So if your database is already 90% of your storage, then you're gonna have a hard time vacuuming it anyway. You could consider using incremental vacuum to clean up free space without re-creating the whole file, but that has to be enabled when the database file is created. Again though, that only frees up unused space. If an insert is making your database size bigger, then you don't have any unused space to clean up. -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Zhu, Liang [AUTOSOL/ASSY/US] Sent: Thursday, December 5, 2019 1:32 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum? If I do not do Vacuum, my database size just keep raising, eventually the database size gets to over 90% of storage size, I can save data to the database any more. Thank you, Liang -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Gerry Snyder Sent: Thursday, December 5, 2019 12:12 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: [EXTERNAL] Re: [sqlite] what is the optimized way to do the vaccum? On Wed, Dec 4, 2019, 9:28 PM Zhu, Liang [AUTOSOL/ASSY/US] < liang....@emerson.com> wrote: > All Sqlite Expert, > > I have one table, I am inserting and deleting record to and from this > table very 250ms. I always maintain 1000 rows in this table. I have > another table, I am inserting and deleting data to and from this > table every 1s. The data record in this table maintains at 200,000 rows. > Can I get some recommendation on what is optimized technique to do the > vaccum for my database? > > Thank you, > Liang > Why do you think that you need to vacuum at all? > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlit > e.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8 > tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DF > EINZ4E6I&m=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M&s=1HGpuZT8Hu2Bp > siRzJ8yujtxh3m_XyAXLThncurjn-M&e= > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M&s=1HGpuZT8Hu2BpsiRzJ8yujtxh3m_XyAXLThncurjn-M&e= _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
