WeeWX Sqlite Datenbank aufräumen

Wer öfters mit seiner WeeWX Datenbank rumspielt, Daten importiert und wieder löscht, wird diese künstlich aufblähen. Das ist wohl das “normale” Verhalten einer Sqlite Datenbank, wenn auto_vacuum=FULL nicht aktiv ist.

The VACUUM command rebuilds the entire database. There are several reasons an application might do this:

• Unless SQLite is running in “auto_vacuum=FULL” mode, when a large amount of data is deleted from the database file it leaves behind empty space, or “free” database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.
• Frequent inserts, updates, and deletes can cause the database file to become fragmented - where data for a single table or index is scattered around the database file. Running VACUUM ensures that each table and index is largely stored contiguously within the database file. In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further…

The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space…

Lösung, VACUUM manuell ausführen. Ich konnte somit unnütze 17 Megabyte sparen. Spart Bandbreite beim Sichern mit Dorf-DSL.

weather ➜  weewx sqlite3 weewx_vac2.sdb 'VACUUM;'
weather ➜  weewx ls -llh
total 419M
-rw-r--r-- 1 blub blub 218M Jan 20 03:33 weewx_non_vac.sdb
-rw-r--r-- 1 blub blub 201M Jan 20 03:33 weewx.sdb