Thanks for the comments. I'm a fan.

Yeah, I think "PRAGMA optimize" here on startup is basically a no-op and only costs microseconds but I should just remove it.

Seems like the ideal thing is to spawn a goroutine and run optimize periodically. Many Go daemons I run will have uptime measured in months so running on shutdown isn't super useful there.

Another one I might add is running `PRAGMA wal_checkpoint(PASSIVE)` periodically from a goroutine, which is something I've done in some of my daemons. Because otherwise a random write suffers from increased latency due to having to checkpoint.

Probably makes sense to open a dedicated read/write connection just for running `PRAGMA wal_checkpoint(PASSIVE)` at some regular interval to keep ahead of any autocheckpointing?

▲

ncruces a day ago | parent [-]

> Seems like the ideal thing is to spawn a goroutine and run optimize periodically. Many Go daemons I run will have uptime measured in months so running on shutdown isn't super useful there.

Connections don't necessarily need to be kept forever. Between SetConnMaxLifetime/SetConnMaxIdleTime that's one way of periodically optimizing from “warm” connections. But doing it on a timer from a fresh goroutine is another.

	▲	jacob2161 a day ago \| parent [-]
		That's a good point too. It seems a bit weird to me to garbage collect connections that I know I'll just recreate in most cases, especially when they're so cheap and limited in number. Cycling connections may cover for certain classes of bugs but I dislike doing things for this reason, but maybe there are others. I'll put some thinking/testing into it at some point. Thanks!