Search code examples
pythondjangosqlitevacuum

sqlite vacuum with django

after insert and delete lots records into sqlite, the size of sqlite db file keeps growing, is there any way to use django vacuum the tables?

  • some settings that let sqlite auto-vacuum
  • or manually write own django command to vacuum

Updated:

I use sqlite database browser to execute following SQL:

vacuum [my table];
commit;

it works great, I just want to do it program way

Solution

  • you can execute raw sql as follows:

    from django.db import connection, transaction
cursor = connection.cursor()

# Data modifying operation - commit required
cursor.execute("vacuum my_table")
transaction.commit_unless_managed()

    though, this was kind of interesting http://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_auto_vacuum

    maybe we can set the vacumm to be automatic, but it looks like it may worsen fragmentation. 

    cursor.execute("PRAGMA auto_vacuum=FULL")

    http://www.sqlite.org/pragma.html#pragma_auto_vacuum