SQLite Forum

Is it safe to VACUUM on a new database connection?
> I continuously get an error when I run VACUUM that some statements are still running.

`VACUUM` is best run at a time when you can predict that the DB will be idle. Good times include:

* App shutdown time, just before the last conn is closed
* App startup time, before any of these other threads start
* 3 a.m. in an always-running app serving a single regional organization

If you can never predict a time when the DB will be idle, you might not want to `VACUUM` automatically at all, since it can take a long time, and such always-in-use apps generally have tight service levels. You might not be *allowed* to lock the whole system up for arbitrarily long times. In such cases, you'd schedule the `VACUUM` for manual downtime, such as during upgrades.

Frequent `VACUUM` isn't super useful in a lot of apps anyway. Once a month or once a year might suffice.

> ...even though I try to close all readers right away.

If you literally mean that you [`sqlite3_close()`](http://sqlite.org/c3ref/close.html) the conn after each query, it's both unnecessary from the point of view of this question and it'll also slow SQLite down considerably.

It's unnecessary because `VACUUM` will still run if there's simply an idle connection to the DB. It takes an unfinalized transaction to do that. If you aren't using explicit `BEGIN`/`COMMIT`, you're still creating transactions, one per SQL statement. Thus, an app doing hundreds of simple overlapping queries a second will likely have some transaction open at any given instant.

It slows SQLite down to reopen the conn for each query because SQLite has to do a lot of work (parsing the schema, priming caches, etc.) on each connection. For a threaded app like yours, you probably want to set up a connection pool, so that the conn gets put back into the pool when a thread is done with it rather than close it, so another thread can then reuse that opened conn.