41–50 of 147 posts
Earlier quoted context omitted.
Here's sqlite doing 100 million inserts in 33 seconds which should fit into nearly every workload, though it is batched. https://avi.im/blag/2021/fast-sqlite-inserts/ So write contention from multiple connections is what you're talking about, versus a single process using sqlite?
No durability guarantee is a showstopper for any serious use case
i wish it had an optional server for more concurrent and networked transactions in the cloud
you could make one pretty easily, no?
I found that claim to be fairly surprising, SQLite is pretty bad when it comes to transactions per second. SQLite even owns up to it in the FAQ:
>it will only do a few dozen transactions per second.
I shared some notes on this on my blog, because I'm guessing a lot of people aren't quite invested enough to read through the whole paper: https://simonwillison.net/2022/Sep/1/sqlite-duckdb-paper/
>SQLite is primarily designed for fast online transaction processing (OLTP), employing row-oriented execution and a B-tree storage format. I found that claim to be fairly surprising, SQLite is pretty bad when it comes to transactions per second. SQLite even owns up to it in the FAQ: >it will only do a few dozen transactions per second.
That is an extremely poor quote taken way out of context.
The full quote is:
FAQ: "[Question] INSERT is really slow - I can only do few dozen INSERTs per second. [Answer] Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second."
Earlier quoted context omitted.
Adding user-defined functions to SQLite is not difficult, and the mechanism is quite flexible. You can create extensions and load them when you create the SQLite connection to have the functions available in queries. I wrote a blog post explaining how to do that using Rust, and the example is precisely a `regex_extract` function [0]. If you need them, you also have a "stdlib" implemented for Go [1] and a pretty exten…
Wow this is helpful. I'm using sqlite for some of my projects and always bothered that some functions are missing. WITH RECURSIVE is too mind bending. This seems like I can add a lot more functions to it, not just regex extract. Came here to complain and learned something useful.
SQLite is an in-process database. You can give it a callback func to execute. So your regex-extract can literally just call a function in your code: https://sqlite.org/appfunc.html
edit: Python's stdlib documentation concisely shows how easy this can be: https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... Basically every SQLite library should have something similar. This extreme ease of extending is a big part of why SQLite has so little built-in.
>SQLite is primarily designed for fast online transaction processing (OLTP), employing row-oriented execution and a B-tree storage format. I found that claim to be fairly surprising, SQLite is pretty bad when it comes to transactions per second. SQLite even owns up to it in the FAQ: >it will only do a few dozen transactions per second.
> Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.
Earlier quoted context omitted.
No durability guarantee is a showstopper for any serious use case
Not sure what you mean by durability. Sqlite has WAL that can be replicated (see litestream)
SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
>most performance oriented configuration I am 99% sure SQLite is going to win unless you actually care about data durability at power loss time. Even if you do, I feel I could defeat Postgres on equal terms if you permit me access to certain ring-buffer-style, micro-batching, inter-thread communication primitives. Sqlite is not great at dealing with a gigantic wall of concurrent requests out of the box, but using a l…