Posts tagged "sqlite"

I killed a program 200 times while it wrote documents and their search entries in two transactions: 104 documents became invisible to search, without any error. One transaction, or triggers, gave zero. When the copy lives in another system, only an outbox and reconciliation are left.

I deleted half of a SQLite table and the file kept exactly the same size. The table gave its pages to a freelist, the index stayed half empty, and new rows used the holes. VACUUM fixed everything in 0.4 s, but it wrote three times the size of the result to do it.

A partial index of the 2% active users took 138 pages instead of 9,096 and answered as fast. But SQLite uses it only when the query says the condition in the same way, or binds the same value, and an index on json_extract() is ignored by the ->> operator, which returns the same value.

Without statistics, SQLite picked the wrong index and took 97 ms instead of 0.9 ms. ANALYZE fixed it. Then I changed the data, and the old statistics kept a slow plan that PRAGMA optimize did not see. Statistics are derived data too, and they can also live in an open connection.

An index exists, and the plan still says SCAN. I tested six cases on SQLite: a function on the column, LIKE, a leading wildcard, OR, a join between a TEXT and an INTEGER column, and a value that matches 93% of the rows. In the last one, ignoring the index is the right choice.