SQLite: Past, Present, and Future

Re:SQLite: Past, Present, and Future by samatman on Thursday September 01, 2022 @10:30AM (#32676773)

Postgres obviously.

Sorry, just thought I'd buck the trend and assume a very write-heavy workload with like 64 cores.

If you don't have significant write contention, SQLite every time.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by ledgerdev on Thursday September 01, 2022 @11:01AM (#32677283)

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?

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by lvass on Thursday September 01, 2022 @11:10AM (#32677483)

Keyword here is transactions, not processes. You can model any workload to be transaction-efficient, but it might not be easy.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by endisneigh on Thursday September 01, 2022 @11:23AM (#32677707)

No durability guarantee is a showstopper for any serious use case

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by axelthegerman on Thursday September 01, 2022 @01:39PM (#32680067)

Not sure what you mean by durability. Sqlite has WAL that can be replicated (see litestream)

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by endisneigh on Thursday September 01, 2022 @01:53PM (#32680314)

https://en.m.wikipedia.org/wiki/Durability_(database_systems...

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by dinosaurdynasty on Thursday September 01, 2022 @04:29PM (#32682666)

https://www.sqlite.org/atomiccommit.html

sqlite is as good at durability as any non-replicated database, though you can configure it to be non-durable (most other databases too tbf).

https://www.sqlite.org/pragma.html#pragma_synchronous

By default WAL mode can rollback committed transactions in cases of power failure, but you can do `PRAGMA synchronous = FULL` to trade speed for durability.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by endisneigh on Thursday September 01, 2022 @04:54PM (#32683052)

I’m talking about the post I originally commented on. Things were disabled so durability is not guaranteed.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by dinosaurdynasty on Thursday September 01, 2022 @05:49PM (#32683757)

Yeah that's not great

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by ledgerdev on Thursday September 01, 2022 @03:03PM (#32681391)

If it's good enough for avionics and nuclear subs, it's probably good enough for most web apps.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by endisneigh on Thursday September 01, 2022 @04:14PM (#32682442)

Web apps do more concurrent writes than subs, plus you can configure SQLite for more durability

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by sph on Thursday September 01, 2022 @05:08PM (#32683217)

I don't have the data for subs, but there's web app and web app. No one is talking about using SQLite for 5k queries/s.

It might work, but I reckon 90% of web applications live beneath this relatively small threshold and 80% probably don't even reach 50 q/s.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by samatman on Thursday September 01, 2022 @12:25PM (#32678775)

That's correct, I meant the many cores to allude to many processes.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by dinosaurdynasty on Thursday September 01, 2022 @04:39PM (#32682821)

If you can have one "database" thread and 63 "worker" threads, send messages back and forth, and don't hold open transactions, this would probably work with sqlite. Aka treat sqlite like redis.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by skyde on Thursday September 01, 2022 @10:33PM (#32685945)

so in your example the database thread is the Redis thread and the worker thread are your http server thread I assume.

This is a good analogy, but there are still lot of wire heavy scenario a real database like postgresql or mysql will have better throughput than redis.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by innocenat on Thursday September 01, 2022 @10:45AM (#32677025)

Where is write contention coming from if it's operated locally?

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by Thaxll on Thursday September 01, 2022 @10:51AM (#32677118)

SQLite is "single" threaded for writes.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by d3nj4l on Thursday September 01, 2022 @11:00AM (#32677260)

... you can get tons of requests on a server?

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by dinosaurdynasty on Thursday September 01, 2022 @04:51PM (#32683007)

Redis has the same limitation (only one transaction at a time) and is used a lot for webapps. It solves this by requiring full transactions up front. The ideal case for sqlite for performance is to have only a single process/thread directly interacting with the database and having other process/threads send messages to and from the database process.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by innocenat on Friday September 02, 2022 @09:58PM (#32697840)

But that isn't "locally"?

[ Parent | on HN ]