[issue2551380] SQLite maintenance: running pragma optimize, vacuum (session db only??)

New submission from John Rouillard:

sqlite docs:

   https://www.sqlite.org/pragma.html#pragma_optimize

recommend for apps with short lived db connections:

   run optimize before closing database connection
   when schema changes/indexes are created (e.g. database updates)

Not sure we want to run this on every connection though.
Maybe make optimize part of roundup-admin migrate since it does schema changes.

Also VACUUM can compact the db. There is an autovacuum, but it runs on every
transaction close and IIRC every sql command has an implicit transaction.
So this seems like overkill for a sqlite main database. It might make sense added
to the end of a cleanup routine for the session or otk databases. But both
should be reusing deleted rows for new data. So vacuum might not make that much of
a difference. It did release 2M of space (9->7M, 22%) for one of my otk databases though.

So maybe just a mention of running the sqlite3 binary or sqlite python shell
to run optimize and vacuum manually is sufficient?

PRAGMA integrity_check; as a manual command might be a good idea to add to docs
in case of underlying disk corruption or other bugs.

----------
components: Database, Documentation
messages: 8227
nosy: rouilj
severity: normal
status: new
title: SQLite maintenance: running pragma optimize, vacuum (session db only??)
type: behavior

_________________________________________________
Roundup tracker <[email protected]>
<https://issues.roundup-tracker.org/issue2551380>
_________________________________________________