71–80 of 85 posts
Earlier quoted context omitted.
this comments seems to say, that you could corrupt your copy, if you copy it while it is in use https://news.ycombinator.com/item?id=39838753
The ".backup" mechanism is safe - it's directly copying the file with "cp" that doesn't work. Here's how to use .backup: sqlite3 data.db ".backup 'backup.db'" You can also do this: sqlite3 data.db 'VACUUM INTO "backup.db";' That's slower, but results in a smaller backup file: https://www.sqlite.org/lang_vacuum.html
> First is that SQLite is simple. The database is a literal file on disk. The engine is a single executable. No, it's not a single executable. There's no database executable as far as your app goes. There's no engine. The "engine" is a library, meant to be embedded into other code and may be concurrent and have many "executables" if you implement it that way or have different apps access the database. Think of SQLite…
Yes. If you write your server in eg Go or Rust, you can have a “single executable deployment” so to say. But the main limitation is no horizontal scalability. So if you want web scale, you have to use something faster, like /dev/null.
Is there a similar effort for Django? Django Sqlite driver is slow and not tested for production.
https://code.djangoproject.com/ticket/24018
With the proper PRAGMAs set and modern hardware, you can do 400+ writes/sec with 4 uvicorn workers and ~100 clients connected. The achilles heal is writing lots of large files. That will cause concurrent requests to wait around for the disk to finish writing to the WAL.
I went through this presentation looking for "how do you do backups" and it glosses over it. But the author blogged about that separately [1]. It seems he uses Litestream with DigitalOcean Spaces for this. Looks like they start at $5 per month for 250 GB [2]. Would that be the best way for a hobbyist to get started? [1] https://fractaledmind.github.io/2023/09/09/enhancing-rails-s... [2] https://www.digitalocean.com/p…
I checked your blog and I am impressed you are still very curious to learn things which didn't exist before you retired (generative AI).
Earlier quoted context omitted.
In my experience most SQLite writes take less than 1ms. Do your writes really need to be concurrent if they run that fast? Hard to get upset about waiting for the current write to complete before you get your turn when we are talking delays measured in thousandths of a second. If you have more than 1000 writes per second then maybe this is something to worry about. The solution there is probably to run a slightly mor…
Sure the actual write might only take 1s but the transaction might lock for 10ms. Does SQLite support overlapping write transactions with locks on different rows? Also do you know if WAL2 mode changes anything? https://www.sqlite.org/cgi/src/timeline?r=wal2
Doesn't help that most ORMs (or other migration tools) don't generate you the correct migration you need
I went through this presentation looking for "how do you do backups" and it glosses over it. But the author blogged about that separately [1]. It seems he uses Litestream with DigitalOcean Spaces for this. Looks like they start at $5 per month for 250 GB [2]. Would that be the best way for a hobbyist to get started? [1] https://fractaledmind.github.io/2023/09/09/enhancing-rails-s... [2] https://www.digitalocean.com/p…
For my project I've been using Litestream to Cloudflare's R2, which is compatible with Amazon S3, and has a very generous free tier.
I interpret this as saying that you can't get R2 literally for free. Looks like Cloudflare KV could work, with values that go up to a bit over 25MB.
$5 per month is very reasonable if you're using it for real, though.
[1] https://blog.cloudflare.com/workers-pricing-scale-to-zero/
Crazy to me that the whole thread here doesn't mention how schema changes are more difficult and sometimes also lead to inconsistent data https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-... Doesn't help that most ORMs (or other migration tools) don't generate you the correct migration you need
Another one of my rule of thumbs is that unless there is massive evidence to the contrary, you should simply use what "most people use" for each specific use case. In my world, when it comes to web applications, that is Postgres.
Postgres. Only postgres. Why? Because once your app gets more than _n_ users you will run into scaling problems and then you end up with huge tech debt
But n can be so big that it you never run into it. My company has a read-heavy SQLite-backed service that serves roughly 150,000 daily users with p99.9 at about 5 milliseconds. We did some rough projection and determined we could reach the total addressable market of our product in the US without even approaching having a problem.