SQLite Forum

Defragment a table ?
I have tried to set with PRAGMA cache_size up to 5 GB, running only queries on those tables so as not to "pollute" the cache, and only reads, it did not have any effect on performance. The table A is about 400 MB when exported table B is around 850 MB, the whole database on the other hand is around 350 GB.

When running against exported tables, queries against table A come out faster, and those involving full table scans are about twice as fast (as expected) compared to table B.

In the whole database, it's the opposite, any query against table A is quite slower than the same against table B.

Of note, when table A is updated, entries are routinely created/deleted, and when the integer values are updated, changes can span the whole 8bit to 64bits value range, and IIRC SQLite uses a more compact format for smaller integers.

Is it possible that this results in table pages that are effectively quite "empty" (having spilled over at some point), and thus uses many more pages than when exporting the table ?

Table B is created all at once, so its pages would be as compact as possible.