[iveqy]

I hope you've found https://stackoverflow.com/questions/1711631/improve-insert-p...

It's a very good writeup on how to do fast inserts in sqlite3

[zeroq]

Yes! That was actually quite helpful.

For my use case (recreating in-memory from scratch) it basically boils down to three points: (1) journal_mode = off (2) wrapping all inserts in a single transaction (3) indexes after inserts.

For whatever it's worth I'm getting 15M inserts per minute on average, and topping around 450k/s for trivial relationship table on a stock Ryzen 5900X using built-in sqlite from NodeJS.

[vlovich123]

Would it be useful for you to have a SQL database that’s like SQLite (single file but not actually compatible with the SQLite file format) but can do 100M/s instead?

[o11c]

It's worth noting that the data in that benchmark is tiny (28MB). While this varies between database engines, "one transaction for everything" means keeping some kind of allocations alive.

The optimal transaction size is difficult to calculate so should be measured, but it's almost certainly never beneficial to spend multiple seconds on a single transaction.

There will also be weird performance changes when the size of data (or indexed data) exceeds the size of main memory.

[jgalt212]

yes, but they punt on this issue:

CREATE INDEX then INSERT vs. INSERT then CREATE INDEX

i.e. they only time INSERTs, not the CREATE INDEX after all the INSERTs.

[gibibit]

Hilarious, 3000+ votes for a Stack Overflow question that's not a question. But it is an interesting article. Interesting enough that it gets to break all the rules, I guess?

[detaro]

It's a (quite old) community wiki post. These do (and especially did back then) work and are treated differently.