SQLite: Past, Present, and Future

Post by hnfong » Thu, Sep 01, 2022, 6:20 PM UTC

tiffanyh wrote: post #46

stonemetal12 wrote: post #44

>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.

> 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 d…

Post by jrochkind1 » Thu, Sep 01, 2022, 6:30 PM UTC

naikrovek wrote: post #37

my ipad won’t let me search through the PDF, but i couldn’t find where “SSB” was defined, if anywhere. i did not see it defined in the first paragraph, which is where it is first used. everyone: not all of your readers are domain experts. omissions like this are infuriating.

Post by ledgerdev » Thu, Sep 01, 2022, 7:03 PM UTC

endisneigh wrote: post #23

ledgerdev wrote: post #18

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

Post by simonw » Thu, Sep 01, 2022, 7:31 PM UTC

ergocoder wrote: post #29

simonw wrote: post #27

Earlier quoted context omitted.

Is it the SQL dialect there lacking or is it the built-in functions? I agree that SQLite default functionality is very thin compared to PostgreSQL - especially with respect to things like date manipulation - but you can extend it with more SQL functions (and table-valued functions) very easily.

Depends on what easily means. Sqlite can't do custom format date parsing and regex extract. How do we extend something like this? If we go beyond a simple function to window function, I imagine it would be even harder. At this point, we nlmight as well use postgres.

Post by glhaynes » Thu, Sep 01, 2022, 7:58 PM UTC

naikrovek wrote: post #37

my ipad won’t let me search through the PDF, but i couldn’t find where “SSB” was defined, if anywhere. i did not see it defined in the first paragraph, which is where it is first used. everyone: not all of your readers are domain experts. omissions like this are infuriating.