SQLite Forum

Defragment a table ?
Login
Thanks!
What would be the best way to vacuum that table using SQL commands as of 3.33.0 ?

I was thinking of running something like this in a transaction:

    create temporary table defrag as select * from A
    delete from A
    insert into A select * from defrag
    drop table defrag

I tried it and it fixed the "unused bytes" ratio, but it apparently reused the just-released non-sequential pages for the "insert", so there is still a very high number of non-sequential pages (analyzer results below), and the performance is still quite lower than table B.

Even on an SSD, the thousands of scattered random accesses take a toll, and it takes 6 seconds with a non-primed SQLite cache to do a "select count(*) from A" for instance (vs 400 ms when primed). 

By comparison "select count(*) from B" takes about 400ms with a non-primed cache, and 200 ms when primed.

Would using larger database pages help ? Or would it just compound the problem ?


*** Table A w/o any indices ******************************************

Percentage of total database......................   0.11%   
Number of entries................................. 30963748  
Bytes of storage consumed......................... 417886208 
Bytes of payload.................................. 176640955   42.3% 
Bytes of metadata................................. 240452642   57.5% 
B-tree depth...................................... 3         
Average payload per entry......................... 5.70      
Average unused bytes per entry.................... 0.03      
Average metadata per entry........................ 7.77      
Average fanout.................................... 334.00    
Non-sequential pages.............................. 63580       62.3% 
Maximum payload per entry......................... 9         
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 305       
Primary pages used................................ 101718    
Overflow pages used............................... 0         
Total pages used.................................. 102023    
Unused bytes on index pages....................... 154461      12.4% 
Unused bytes on primary pages..................... 638150       0.15% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 792611       0.19%