I have a SQLite database with one large table, and I'd like to shrink
the size of that table to free up space in the filesystem.  My problem
is that the database is (for example) 100 MB, and I have 80 MB of free
filesystem space.  I figured that I could DELETE, say, half of the
records from the table, then VACUUM, and the VACUUM would
[temporarily] need ~50 MB of free space for the journal (since that's
how much real data there is).

Instead, I'm finding that it needs a full 100 MB for the journal, even
though once the VACUUM succeeds the resulting DB is only 50 MB.  As a
result, I'm stuck unable to shrink the database, since VACUUM fails
with a disk I/O error (out of space), seemingly no matter many entries
I remove ahead of time.  I know the space is being freed, since
"PRAGMA freelist_count" shows the expected numbers.  So presumably
this is just an artifact of the way VACUUM is implemented internally.

Is there anything that I can do to shrink the database in-place?  It
looks like auto-vacuum is out of the question, since it has to have
been enabled before the table was initially created.  FYI, this
situation exists on a few dozen devices in various locations, so I'd
need a programmatic solution - "move the DB somewhere else, VACUUM,
then move it back" won't work, unfortunately.  :-)

Any tips are appreciated.  Thanks!

Matthew L. Creech
sqlite-users mailing list

Reply via email to