(author here) Thank you for the fantastic discussion so far. I've added an Epilogue to the article that covers:
- Pragma synchronous "FULL"
- Giving PG a bigger connection pool
100k TPS over a billion rows: the unreasonable effectiveness of SQLite
131–140 of 169 posts
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#132I concur that sqlite is quite amazing. That said, I was a heavy user and have grown some skepticism as well: - it is not that hard to lock the db. Usually killing the process that caused the deadlock solves the issue - but you need to identify it / monitor for it. And yes, it happens with WAL too - but when it does happen, it is quite scary. Simply, anything that touches your DB suddenly stops working - can't read, c…
These are some really good points.
- WAL checkpointing is very important (litestream handles this well). As you said not checkpointing can cause massive query slow down.
- SQLITE_LOCK and SQLITE_BUSY can be avoided by ensuring your application only has a single write connection ideally behind an MPSC queue. After WAL this is probably one of the biggest SQLite quality of life improvements.
- 100% avoid cloud drives in this context you ideally want attached NVME.
- Postgres is great and there's nothing wrong with using it!
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#133Earlier quoted context omitted.
From your experience, would you call these behaviors bugs, or are they more known issues that result from SQLites specific implementation quirks? What kinds of workloads were you throwing at it when these types of issues happened? Asking as someone who really enjoys and respects SQLite but hasn't encountered these specific behaviors before.
I was pushing SQLite quite hard. My DB was at peak 25GB or so. Occasional queries of O(1e6) rows while simultaneously inserting etc. Many readers and a few writers too. Id expect some degradation, sure, but Id say it wasn't very graceful. I think, however, I was well within the parameters that SQLite maximalists would describe as within th envelope of heavy but fine usage. YMMV. I found a very small number of people…
You don't need to truncate the WAL, you can checkpoint PASSIVE and the WAL will be overwritten (so your queries won't slow). Generally if you're using litestream for backups it will do checkpointing for you. If you aren't depending on the after each batch (always be batching!) works well too.
I'd say the hardest part of using SQLite is its defaults are rough, and a lot of drivers don't handle batching for you.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#134Earlier quoted context omitted.
Can you elaborate on what the bureaucracy is you experienced? I'm a Hetzner customer since last month and so far I thoroughly enjoy it. Have not encountered any bureaucracy yet.
I was asked for a passport photo when I tried to open an account. They literally asked for a passport photo immediately after the signup form. Like WHAT? I couldn't believe my eyes. The most insane shit I've ever seen.
If I was letting some random person rent one of my servers without oversight, I'd sure want to see some ID first.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#135Earlier quoted context omitted.
Right - but SQLite handily beats the case where postgres is on the same box as well. And it's completely reasonable to test technology in the configuration in which it would actually run. As an industry, we seem to have settled on patterns that actually are quite inefficient. There's no problem that requires the solution of doing things inefficiently just because someone said databases should run on a different host.
If you're going to run on more than one piece of hardware, something is going to be remote to your single writer database. As an industry, we've generally decided against "one big box", for reasons that aren't necessarily performance related.
I sometimes dream of a local-first world in which all software works with local DB and only writes to the cloud as an afterthought, maybe as a backup or a way to pick up work on another machine. It just boggles my mind that more software nowadays relies on an always on internet connection for no good reason other then the design itself.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#136Earlier quoted context omitted.
Or rent a bare-metal machine from hetzner with 2-3x performance per core and 90% less costs[1]. [1] Various HN posts regarding Hetzner vs AWS in terms of costs and perf.
This might be true in terms of direct monetary costs. I want to like Hetzner but the bureaucratic paper process of interacting with them and continuing to interact with them is just... awful. Not that the other clouds don't also have their own insane bureaucracies so I guess it's a wash. I'm just saying, I want a provider that leaves me alone and lets me just throw money at them to do so. Otherwise, I think I'd rathe…
Yes there is some bureaucratic paper churn to deal with them, but it's a one time cost. I did it once probably more than 10 years ago. Since then, login to the website takes me Compare that with AWS, where login is slow and unreliable (anyone else got an error message after every login and has to refresh to get in?), the website is a giant mess collapsing under its own weight, and slow like it's still running websphere.
Over the last 10 years, I've certainly lost way more time working through aws paperless bureaucracy than complying with Hetzner paper bureaucracy. And I'm not even using aws for that long.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#137Earlier quoted context omitted.
This might be true in terms of direct monetary costs. I want to like Hetzner but the bureaucratic paper process of interacting with them and continuing to interact with them is just... awful. Not that the other clouds don't also have their own insane bureaucracies so I guess it's a wash. I'm just saying, I want a provider that leaves me alone and lets me just throw money at them to do so. Otherwise, I think I'd rathe…
It's weird seeing people on HN complain about this aspect regarding Hetzner because it's the complete opposite of my experience. Two years I've rented a dedicated server for around 40 euros monthly from Hetzner as a business customer and I had no issues whatsoever. They didn't ask for a business license or personal ID or anything really, I provided a VAT ID along with a business name and address but it wasn't anythin…
Used them for more than 10 years. There was a one off, straightforward process of providing some details back then, and then nothing more.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#138Earlier quoted context omitted.
Can you elaborate on what the bureaucracy is you experienced? I'm a Hetzner customer since last month and so far I thoroughly enjoy it. Have not encountered any bureaucracy yet.
I was asked for a passport photo when I tried to open an account. They literally asked for a passport photo immediately after the signup form. Like WHAT? I couldn't believe my eyes. The most insane shit I've ever seen.
Quite commonly required by law in Europe; but often times not implemented very seriously by hosting providers, but Germany seems to be an exception.
I remember a time in France for instance, about 15years ago, it was mandatory to provide your ID when bying a mere prepaid sim card. No seller would actually check, and a coworker of mine who used to work for one of the largest french telcos at the time told me that once they ran some stats over the customer database and noticed that most names where from popular comics and TV show. They laughted and moved on. These days, the seller would at least ask for some ID.
aka circling the cattle.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#139Earlier quoted context omitted.
Dqlite and Rqlite are primarily for buildling fault-tolerant clusters. But if you just take the network access part, then ok sure, but also so what?
rqlite[1] creator here. Nit: dqlite is a library, it is not a network-exposed database like rqlite is. Sure, it requires connecting to other nodes over the network, but local access is via in-process. In contrast one connects with rqlite over the network - HTTP specifically. [1] https://rqlite.io
Massive fan rqlite. Awesome work!
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#140How does SQLite handle HA setups? The minimum I want is reliable automatic failover in reasonable time for user-facing service. Ideally an active-active setup.
sqlite is just a library (in C) A few projects: * https://github.com/rqlite/rqlite Distributed, fault tolerant cluster * https://litestream.io/ Replication to S3 (or compatible) - more disaster recovery than fail over * https://fly.io/docs/litefs/ Same Author as litestream). Distributed replication. Requires writes to be redirected to the primary. I am debating Postgres vs sqlite (probably with litestream) for a proj…
Yeah if you're comfortable scaling vertically and potentially a little downtime. Sqlite massively simplifies your ops, backups litestream is fantastic.
It's also as you mentioned dirt cheap (VPS or a hetzner box).