Other notes in addition to the previous responses. File size will never go down without some sort of vacuum operation. Basically since you can only truncate files, and not snip out sections in the middle. If you delete from the front of a large file, in order to shrink it you'd have to re-write the entire file from that point to the end. So instead of doing that it just recycles pages when possible, and only expands the file when it runs out of free space and needs more.
Here's the blurb on autovacuum: http://www.sqlite.org/pragma.html#pragma_auto_vacuum INCREMENTAL mode will allow you free up unused pages when you explicitly tell it to, FULL mode will do it automatically. Autovacuum does not do the extra nifty steps that a regular vacuum does. From the above linked blurb: "When the auto-vacuum mode is 1 or "full", the freelist pages are moved to the end of the database file and the database file is truncated to remove the freelist pages at every transaction commit. Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse." A full fledged vacuum (http://www.sqlite.org/lang_vacuum.html) will re-write the entire file (twice actually) and do extra things, for example re-ordering all of the records of a table so that they're all nicely packed together in a continguous section, already in primary key order, and no longer semi-randomly scattered throughout. -----Original Message----- From: sqlite-users [mailto:[email protected]] On Behalf Of Jacky Lam Sent: Friday, September 01, 2017 12:42 PM To: SQLite mailing list Subject: Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0 Hi All, While using my own implemented file system, the db file size will only expand and not prune even remove record from the db. Could anyone advise me that what I am missing in order to pruning the db size when removing a number of records? Jacky _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
