These are my PRAGMAs and not your PRAGMAs. Be very careful about blindly copying something that may or may not match your needs.

    PRAGMA foreign_keys=ON
    PRAGMA recursive_triggers=ON
    PRAGMA journal_mode=WAL
    PRAGMA busy_timeout=30000
    PRAGMA synchronous=NORMAL
    PRAGMA cache_size=10000
    PRAGMA temp_store=MEMORY
    PRAGMA wal_autocheckpoint=1000
    PRAGMA optimize <- run on tx start

defer_foreign_keys is useful if you understand the pros and cons of enabling it.

Explanation of sqlite performance PRAGMAs

https://kerkour.com/sqlite-for-servers

Although not what you asked for, the SQLite authors maintain a list of recommended compilation options that should be used where applicable.

https://sqlite.org/compile.html#recommended_compile_time_opt...

SQLite is fairly fork-resistant due to much of its test suite being proprietary: https://www.sqlite.org/testing.html

It has been forked at least once:

https://docs.turso.tech/libsql

The real fork is DuckDB in a way, it has SQLite compatibility and so much more.

The SQLite team also has 2 branches that address concurrency that may someday merge to trunk, but by their very nature they are quite conservative and it may never happen unless they feel it passes muster.

https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co... https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html

As to the problem that prompted the article, there's another way of addressing the problem that is kind of a kludge but is guaranteed to work in scenarios like theirs: Have each thread in the parallel scan write to it's own temporary database and then bulk import them once the scan is done.

It's easy to get hung up on having "a database" but sharding to different files by use is trivial to do.

Another thing to bear in mind with a lot of SQLite use cases is that the data is effectively read only save for occasional updates. Read only databases are a lot easier to deal with regarding locking.