I got so annoyed with that a few years ago that I ended up building a whole tool just to solve that one problem:
uvx s3-credentials create my-existing-s3-bucket
> Maybe one day I’ll move away to some other S3-compatible alternative.
I've used Restic with Cloudflare R2 and it worked great.
when would you want write only?
I think write-only is worthwhile for database backups to guard against a ransomware compromise.
Query plans aren't that hard to read! [0]
With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
sqlite> CREATE TABLE x1(a, b, c); -- Create table in database
sqlite> .expert
sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Analyze this SELECT
CREATE INDEX x1_idx_000123a7 ON x1(a, b);
0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b>?)
sqlite> CREATE INDEX x1ab ON x1(a, b); -- Create the recommended index
sqlite> .expert
sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Re-analyze the same SELECT
(no new indexes)
0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?)
> My approach so far has been to just do these cleanup operations in small batches so that I don’t need to do database queries that take more than 5 seconds to run. This whole experience has given me more of an appreciation for why someone might want to use a “real” database like Postgres which can have more than one writer at the same time though.
The advice for those " “real” " databases is generally to also do cleanup operations in small batches, they just tend to make it less obvious you're doing something unperformant in the smaller case. You're more right than you thought!
Is this being positioned as a strength, in your comment?
Once you get to about 1M rows of data, batching is essential.
Every DB needs it, eventually. Even NoSQL darlings like Cassandra - I've seen it go into a resource-constrained death-spiral on stuff that should be async / non-blocking and safe. If you need to stay up, it's always worth planning on, and making sure your logic works during long-running gradual migrations.
I wasn't clear exactly what the author was doing. "The worker crashes because it couldn’t write to the database and the VM shuts down" - why would the VM shut down? I assume VM here means the Virtual Machine (OS).
Raw EXPLAIN dumps bytecode, which is usually much more verbose than you want. EXPLAIN QUERY PLAN dumps a summary.
Also, you don't get to choose your competitors. SQLite is single-writer focused and an embedded library, but it's a sophisticated database engine after all, so comparisons with other "real databases" are more than appropriate. This meme really needs to die. Even more so when all you're comparing is query plan debugging output where there is no justification for unreadable output.
Various statistical views over the value distributions of the indexes, so that the planner can estimate how useful (selective) the index should be.
sqlite_stat1 just gives an average (number of records in the index, and average number of records per value), and if enabled sqlite_stat4 stores histogram data.
In this context it means upon a successful backup, update a timestamp somewhere. Some other system monitors the timestamp and if it ever becomes more than for example 1 day ago, it fires an alert.
I assume this is opposed to alerting when the backup job fails, which is an issue if the job never runs, or hangs forever, or crashes in a way that doesn't trigger your monitoring.
However I don't see how any of this solves the issue of not testing your backup. Because you can definitely have a backup task succeed regularly but the thing it is backing up is still unusable.
Turns out the backups took 30 hours. The daily backups. That then overwrote each other on the assumption that the backups would not take that long.
Of course we found out the hard way.
OUT="${i}.sql.zst"
PART="${OUT}.part"
sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
mv "${PART}" "${OUT}"
Neither does VACUUM INTO or ".backup" (which uses the backup API) or sqlite3_rsync or litestream.
Anyway, the sync-friendly output is the really important part for me, because it means I can point borg at the zstd-compressed file and it'll only need to store what's changed.
What's in the DB that makes the HA DB that big? You keep lots of historical time-series?
The SQLite backup script is part of my separate backup routine that predates HA's backups, and eventually makes it into a Borg backup hosted on rsync.net. Belt and braces.
I have only my own version of your SQLite backup. I suppose I shall learn the risks of this method with the first failure haha!
That’s not really accurate any longer.
Mostly depends on how you layout your tables & files. If you shard the databases then multiple machines can act as writers for their shard. You can also split read requests from write requests and have read only machines scale up/down as much as you’d like. You can use multiple files in a query (there is a limit there).
So for example you can split the user table based on the first letter of the username and then depending on the rest of the database either a database file per user or per customer (organization). Of course more of everything is manual but it’s not as hard as you’d expect if you build for it.
https://rivet.dev/blog/2025-02-16-sqlite-on-the-server-is-mi...
If you need sqlite over the network you can look at https://turso.tech/ it’s a almost drop in replacement for sqlite (https://github.com/tursodatabase/turso/blob/main/COMPAT.md)
If your entire system is sharded by username anyway for other reasons, then maybe what you've described works for you.
> SQLite strives to provide local data storage for individual applications and devices.
> Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.
Those are writes not hits so not directly comparable.
Don't move to the network unless you have to - every single request gets massively slowed down because it has replaced local reads with network connections.
Of course if you are building a startup you must consider scaling.
-Delete it batches
-Delay between batches
-Preload the rowids before deleteing with SELECT (Select does not block)
Additionally if data was added sequentially primary to the same table the data is likely stored this way in the file and deleting it in this or in reversed order can be faster (depends on storage medium and other factors).
If you’re in a situation where partition pruning or other strategies for getting useless data out of the hot path don’t make sense, this is a killer strategy.
For example, I have a compiler that compiles a DSL to a DB query which returns a list. Now that query can either be on data in the browser or can be on data on the server. Now I don't have to write the logic twice!
And yes, never allow the files to be deleted from outside. The transfer is a one way valve. If uploading, it’s a write-only operation, no delete unless the file has meta data for expiry.
It’s as much work, and scales 1000x farther than the email approach.
and
> my best guess
and
> and presumably other things?)
and
> maybe there’s a bunch of Python code running inside a transaction
Basically, this article has no substance. The author didn't bother to learn anything, didn't look things up. And is then wildly guessing, sometimes wrong.
This is BTW the reason why (as a Debian user) if I search something Linux related and a Ubuntu forum pops up, I don't even open that anymore. Sure, Ubuntu is similar to Debian, but the amount of wrong guessworks in these forums is hefty. I however usually open the Arch Wiki pages, despite Arch very != Debian. But the articles there are written by knowledgeable people.
Off the top of my head, yesterday in work I used 2 programming languages, 2 build systems, a cloud provider, a secrets manager, a very intricate framework for client server communication in both languages, plus my VCS, editor and CI tool. That’s a fairly typical set of tools to use for one feature for me, before you go into the weeds of OS versions, specific runtime versions, databases, reverse proxies, caches, and the domain logic!
If I went deep on every single thread exposed to me, I’d never get anything done, so I have to choose my battles just like OP has done here
She's also one of the best out there at demystifying technology and helping people understand what solving problems actually looks like.
This article doesn't pretend to be a world expert's take on using SQLite. The clue is right there in the title - "learning a few things about running SQLite" - which sets expectations right from the start.
The wider message is consistent throughout all of her writing. You can do this stuff. Here are simple practices to show how to figure out problems and build your knowledge. You don't have to know everything, and you certainly don't have to pretend to know everything. Sharing what you've figured out so far, in as clear a way as possible, is a virtue.
I've worked with many web developers who get mental blockage around DB tooling (granted, I have similar mental blockage when it comes to some operations stuff like K8s), and you can go far in life without really having to ask _that many questions_.
But going in and finding out how your SQL turns into data gotten from disk/written to disk is very helpful in just "knowing" what might be a decent idea. That and understanding your DB's locking system (or lack thereof...).
Figuring that stuff out can help reduce the surprise level when you can't seem to get a "simple COUNT" working quickly in Postgres or the like...
A db table with only 10k rows? Even a full table scan should be extremely fast.
And with SQLite - which I assumed runs in-process, but even if not, surely is running on the same physical server? Faster still.
Of course, the magic phrase in my head is “create index”.
I hope Julia posts an update!
Edit: I highly suspect the “slow deletes” problem is a classic “n+1” problem suffered by many ORM users, until they come to understand more about the underlying db interactions.
I just ran it on my self-hosted MediaWiki installation and it took the search from seconds to milliseconds.
An standard S3 upload like "aws s3 cp" doing OOM is surprising (to me)
SQLite gets really slow when using very large BLOB's (100+ MB). I ended up having to store the BLOB's externally and refer to them from the SQLite DB. Not ideal of course (the BLOB's are not transacted) but works OK in practice using hashing/checks etc. to detect and handle invalid BLOB's.