Learning a few things about running SQLite

Learning a few things about running SQLite(jvns.ca)

77 points by surprisetalk 3 hours ago | 18 comments

striking 1 hour ago |

> Maybe one day I’ll learn to read a query plan.

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

Also wrt

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

simonw 1 hour ago | |

I've worked with large MySQL databases that used row-based replication and things like an UPDATE or DELETE that affected millions of rows had to be applied in batches there, because otherwise one SQL query might result in a million updated rows needing to be sent to all of the replicas at once.

cogman10 1 hour ago | | |

Yeah, I think anyone that's done significant database work has come to the understanding that large updates need to be done in batches, otherwise you nuke performance.

Once you get to about 1M rows of data, batching is essential.

DANmode 1 hour ago | |

> they just tend to make it less obvious you're doing something unperformant

Is this being positioned as a strength, in your comment?

striking 47 minutes ago | | |

It just is what it is. Sometimes you want to write the obvious query without the DB getting in your way, and other times you want to know as soon as possible that you're doing something that won't scale under exponential load. At this point in my career I prefer the latter, but the former will always have a special place in my heart.

simonw 1 hour ago |

> I’ve been backing up to AWS, which is always a pain because it’s annoying to navigate the AWS console to generate credentials.

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

This spits out read-write credentials that are scoped JUST for that bucket. You can add --read-only or --write-only to have credentials that are further locked down, or even add --prefix foo/bar for credentials that can only read/write keys that start with that prefix within the 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.

noxer 40 minutes ago |

As for the DELETE issue the easy solutions are:

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

zbentley 15 minutes ago | |

Row ID preloading is an extremely effective technique—and not just for SQLite. I’ve also used it to great effect on massive Aurora MySQL or Postgres clusters since I could send the SELECT to a replica, and the whole point of deletions was that index memory pressure from the row filtering was putting tons of CPU and buffer cache pressure on the db.

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.

masklinn 1 hour ago |

> and presumably other things?

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.

arlattimore 40 minutes ago |

I wonder if the ORM deletes were slow due to looping through a list calling delete on each object vs having a bulk delete method which accepts a list of IDs?

pianopatrick 40 minutes ago |

If you are worried about the cleanup operation having python code running in it, maybe you could use the SQLite CLI to run that operation instead.

Kalanos 44 minutes ago |

It's great! However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests, you need something like postgres.

gabeio 31 minutes ago | |

> However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests

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)

andrewaylett 1 hour ago |

I run my backups like this:

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

That doesn't block writers (when the writer uses WAL), and gives me a dump that's compressed well while also being easy to sync. My Home Assistant DB is 1.8GB, my dump is 286MB compressed, and I'd guess 90% of that is consistent from one day to the next.

ryan42 1 hour ago |

If you're not using them, adding in silk and/or debug toolbar to your django app will be able to get some good automatic reporting and guidance on performance issues.

m0ose 1 hour ago |

What does he mean by "I do usually try to monitor them with a dead man’s switch.", when talking about backups?

kevincox 41 minutes ago | |

Dead-man's switch means triggering when something doesn't happen. (The name comes from a switch that an alive operator would need to hold in such a way that if they died they would stop holding.) So in this case she means that her monitoring will fire if there wasn't a successful backup within some configured period of time.

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.

The_Fox 46 minutes ago | |

https://en.wikipedia.org/wiki/Dead_man%27s_switch

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.

EvanAnderson 50 minutes ago | |

I don't call it a "dead man's switch", but I absolutely monitor some directories for new newest file. If the backup monitoring script doesn't find a file less than 24-ish hours old in the backup destination directory at any time it should send me an alert.

datadrivenangel 1 hour ago |

"Maybe one day I’ll learn to read a query plan."

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

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

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