Why the back-up database file does not shrink as the source database shrink?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Why the back-up database file does not shrink as the source database shrink?

Yihong Zhan
Hi sqlite experts,

I am using sqlite back up API of the latest sqlite version. I find an issue that the target database seems not shrink when the source shrinks.

Here are my steps:

The source database is an in-memory database

  1.  Open the source database and preserve the connection for later reuse. The subsequent back-up operations always reuses this connection.
  2.  Create a table within the source database and insert millions of records into the table.
  3.  Back-up the source database to disk. Verify that the database on disk contains the correct data inserted just now. Also verify that the size of the target database is now about 200M.
  4.  Delete all the data from the source database
  5.  Start back-up again. When back-up finishes, verify that the target database contains no data. However, the size of it is still 200M.

By the way, my back-up logic is copied from "Example 2: Online Backup of a Running Databaseā€ from https://www.sqlite.org/backup.html

Is it a bug, a known issue? I believe the size of the target database should be kept consistent with the source database. Otherwise, quite lots of disk space will be wasted in case source database shrinks.


Thanks

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why the back-up database file does not shrink as the source database shrink?

Stephan Beal-3
On Fri, May 13, 2016 at 8:29 AM, Yihong Zhan <[hidden email]> wrote:

> Hi sqlite experts,
>
> I am using sqlite back up API of the latest sqlite version. I find an
> issue that the target database seems not shrink when the source shrinks.
> ...
> Is it a bug, a known issue? I believe the size of the target database
> should be kept consistent with the source database. Otherwise, quite lots
> of disk space will be wasted in case source database shrinks



Not a bug: sqlite saves removed "pages" for later use. Use the VACUUM
command to tell it to get rid of them and shrink your file.


--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users