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!

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.

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.

    OUT="${i}.sql.zst"
    PART="${OUT}.part"
    sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
    mv "${PART}" "${OUT}"

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

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

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.

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.

I just ran it on my self-hosted MediaWiki installation and it took the search from seconds to milliseconds.

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.

Query plans aren't that hard to read! [0]

0 - https://xkcd.com/2501/