Batch size one billion: SQLite insert speedups, from the useful to the absurd

Re:Batch size one billion: SQLite insert speedups, from the ... by cogman10 on Tuesday September 26, 2023 @11:48AM (#37661104)

One big caveat to this sort of test. With DBs you are very often trading throughput for latency.

Meaning, it's certainly possible to get "the absolute fastest inserts possible" but at the same time, you are impacting both other readers of that table AND writers across the db.

This also gets more messy when you are talking about multiple instances.

[ Parent | on HN ]

Re:Batch size one billion: SQLite insert speedups, from the ... by voidstarcpp on Tuesday September 26, 2023 @12:34PM (#37661828)

With SQLite by default nobody else can even read the database while it's being written, so your comment would be better directed to a conventional database server.

[ Parent | on HN ]

Re:Batch size one billion: SQLite insert speedups, from the ... by argiopetech on Tuesday September 26, 2023 @12:39PM (#37661881)

Note that the default can be changed with a PRAGMA at compile time to Write-Ahead Logging (WAL) which is both faster and allows reading during writes.

[0] https://www.sqlite.org/wal.html

[ Parent | on HN ]

Re:Batch size one billion: SQLite insert speedups, from the ... by voidstarcpp on Tuesday September 26, 2023 @12:52PM (#37662141)

In my limited experiments WAL wasn't faster for high-row-speed writes.

When rows/s are maximized SQLite is CPU limited by internal bytecode operations rather than waiting for disk stuff.

[ Parent | on HN ]

Re:Batch size one billion: SQLite insert speedups, from the ... by argiopetech on Tuesday September 26, 2023 @03:50PM (#37664936)

Too late to edit: This should have said "at run time", as it's set by making a relatively normal (can't be inside a transaction) SQL query.

[ Parent | on HN ]

Re:Batch size one billion: SQLite insert speedups, from the ... by RedShift1 on Tuesday September 26, 2023 @01:21PM (#37662660)

If you enable the WAL, reading while writing is possible. IMO it should be the default.

[ Parent | on HN ]