Autovacuum only defragments pages — doesn't return space to the OS unless you use PRAGMA auto_vacuum = FULL (and even then, not guaranteed).

Ran into this building a data store for an embedded device. SQLite's autovacuum helped with fragmentation, but for constrained storage I needed different cleanup semantics — append-only with periodic compaction, no in-place updates.

Ended up building moteDB as a custom store that treats deletion as a first-class operation rather than a cleanup afterthought. Different tradeoffs (sequential writes, index lookups for reads), but for embedded/edge use cases it's been worth it.

Anyone running SQLite on embedded devices — have you hit the autovacuum performance cliff where vacuuming blocks writes for too long?

The pointer-map page deep dive is excellent — I rarely see this covered outside the SQLite source code docs.

One thing worth adding to the tradeoff analysis: the overhead isn't just in commit time. On flash storage (eMMC, SD cards, NOR flash), autovacuum's write amplification can be brutal. Every page relocation means an erase-rewrite cycle on a flash block, and if you're doing high-frequency DELETE + INSERT patterns (think sensor data logging on an embedded device), you'll burn through write endurance fast.

We hit exactly this on a robotics project — SQLite in WAL mode with autovacuum enabled, running on an SD card. Card died in 3 months. Switched to manual VACUUM on idle intervals, and the same card lasted over a year.

Another subtlety: autovacuum + WAL mode together can create surprising behaviors. The WAL itself doesn't participate in autovacuum, so you can end up in a state where the main DB file is compact but the WAL is bloated with stale pages. Worth calling PRAGMA wal_checkpoint(FULL) before relying on file size as a health metric.

Do you have any benchmarks on the commit-time overhead difference between autovacuum=INCREMENTAL vs autovacuum=FULL? I've seen the docs but never found real-world numbers comparing the two.

The PhantomData approach in Rust is clean for simple state machines, but I've found it gets unwieldy fast when you have >4-5 states. My storage engine has a query lifecycle with 8 states (Parse→Validate→Plan→Optimize→Execute→Stream→Complete→Error) and the PhantomData boilerplate was doubling my struct count.

We moved to a sealed trait + enum combo instead: the enum handles runtime state transitions (with exhaustive match), and the sealed trait prevents external implementations. You lose the compile-time "this method doesn't exist on this state" guarantee, but exhaustive match on the enum gives you the same safety at a different point.

The Gleam approach is elegant though. Constrained parameter types feel like the right middle ground — you get compile-time enforcement without the generic explosion. Reminds me of how OCaml GADTs handle this.

I enjoyed this deep dive into SQLite's internals. The pointer-map pages section was particularly enlightening — I hadn't fully appreciated how much bookkeeping is required just to support relocation.

The "can only shrink from the end" limitation is something that doesn't matter much on servers with terabytes of storage, but becomes painful in embedded scenarios. I learned this the hard way when building a data store for a drone. The database file would grow during data collection (telemetry, video metadata, sensor readings), then deletions would free up pages internally — but the file never shrank. On a device with only 8GB storage, that "invisible" bloat adds up fast.

What the article doesn't fully explore is the commit-time overhead of autovacuum. When autovacuum is enabled, SQLite has to:

1. Check the freelist at commit time
2. Relocate pages (move data from end to free slots)
3. Update pointer-map entries
4. Truncate the file

That's a lot of I/O at commit time. For a drone processing sensor data at 200Hz, that overhead is noticeable. I ended up disabling autovacuum and running VACUUM manually during maintenance windows — but then I'd have to take the database offline briefly.

This is actually why I approached moteDB differently. Instead of B-tree + autovacuum, I went with LSM-tree (Log-Structured Merge-tree). LSM-tree always appends writes (no in-place updates), so there's no need to relocate pages or maintain pointer-maps. Compaction happens in the background, merging SSTables without blocking writes. For embedded AI workloads where you're ingesting vectors, time-series data, and state simultaneously, that non-blocking compaction matters.

The tradeoff is read amplification (you might need to check multiple SSTables), but for edge AI where writes dominate and reads are typically point-queries by timestamp or vector similarity — it works well.

Question: Have you benchmarked the commit-time overhead of autovacuum in resource-constrained environments? I'm curious if the overhead scales linearly with database size, or if there's a threshold where it becomes prohibitive.

The detail about shrinking from the end only is one of those quiet constraints that shapes everything downstream. It's easy to forget that filesystems are just big linear arrays with a length field. You can truncate. You can append. You cannot punch a hole in the middle without the OS's cooperation, and even then, the file metadata still thinks the space is allocated.

What's interesting is how this constraint forces SQLite into a kind of defragmentation at commit time. Moving valid pages from the end into holes earlier in the file, then truncating. That's not free. On a busy database with autovacuum enabled, every commit that frees pages might trigger a mini relocation dance. The pointer-map pages are the clever part—they make the dance possible—but they don't make it cheap.

It makes me think about the hidden cost of "automatic" in systems like this. Autovacuum sounds like a set-it-and-forget-it feature. In practice, it's a tradeoff between steady-state file size and commit-time latency. If your writes are bursty, you might not care. If you're on a mobile device with slow flash and a tight power budget, maybe you care a lot.

Do you have a sense of how much of a practical hit the relocation overhead actually is? Like, is it something you'd notice on a typical CRUD app, or is it more of a theoretical "exists but doesn't matter" kind of thing?