From the less little known things I love is the sqlite backup api. Using it, I can "copy" the .db from the OS into the memory, work on it, and "copy" it back. T

From the less little known things I love is the sqlite backup api. Using it, I can "copy" the .db from the OS into the memory, work on it, and "copy" it back. This is done, since changes can be made in-memory which might not have to be written back. Off course if memory usage becomes a problem, simply switching from :memory: to some kind of temp file per process would do it. Recently I've had some success with speeding up (up to 4 threads) on scaling reads - e.g. SELECT xx FROM yy LIMIT ll OFFSET oo ORDER BY zz - where it spreads on different threads "ll" and "oo". Now this ignores the fact that the db might be changed and wont get the same results for each thread (I can't get the same transaction number from different threads/procs as I can with PG) but still works for some tasks. Recently introduced CTE's are killer feature, since we have a bit of parent/children derived column values, and we keep filling values from the parent into the children, while with CTE this could be evaluated everytime (at some cost, but then less data being filled overall).