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