SQLite Forum

Anton Dyachenko
Yeah, it is complicated, I don't know where to begin. And I can't address all of your concerns.

> but what I am really looking for is something like a vacuum (but much more performant)

If VACUUM is a working, but too slow solution, this could be used instead:

migration steps
PRAGMA schema_version = 1+(SELECT schema_version FROM pragma_schema_version);

[Some warnings apply](https://www.sqlite.org/pragma.html#pragma_schema_version)

I have no idea how safe this is when more than one connection updates the same database schema concurrently, except "they shouldn't do that".

> but we have plans to start using WAL where this is possible, and it is not clear for me what is the proper handling of this case. Should already running statements be interrupted or not? Interruption is "correct" but not always reasonable due to change in the schema can be quite often compatible with running statements so there is no point interrupting them.

As I understand it, any readers (SELECT) that are already sqlite_stepping() before another connection to the database changes the schema will proceed to see the schema and table definitions that were valid when the SELECT was sqlite_prepared() and the first sqlite_step() executed.

In other words, (cached) prepared statements can only be invalidated and recompiled when the first step is made and the engine sees the schema has just been changed. Once the first step is made, it will proceed do execute all further steps without recompiling again, because they run within a transaction (implicit or explicit).