Prepared statement caching

[daenney]

From what I can tell reading SQLite documentation and blog posts, it seems beneficial for frequently occuring queries to prepare a statement once and then reuse it multiple times (by calling Reset()). This means we don't spend time compiling the query repeatedly. You'd have to do that after Open() and ensure to finalise all statements before Close().

However, prepared statements aren't safe for concurrent use best I can tell. That would interact fairly poorly with a slew of Go routines handling incoming HTTP requests which all want to use the same statement as they'd clobber all over each other during Bind() calls. This would mean needing to serialise access to a prepared statement, since there doesn't appear to be any way to copy or clone one in the SQLite API.

I've been trying to think through what I could do to enable prepared statement reuse in an application:

1. Writes are effectively serialised so do it in that case, but skip it for read queries (at least in WAL mode).
2. Cache one instance of the statement where access is serialised, and if it's not available prepare one on the fly.
3. Do option 2 but with a (small) connection pool as well, to improve the prepared statement cache hit ratio.

What do people typically do here? Have folks found that statement reuse is beneficial, and if so how did you approach it?

[ncruces]

TBH, to the extent that I use this driver (mostly mucking around, nothing critical), relying on database/sql DB.PrepareContext has been enough. It's going to be suboptimal, but manages concurrency itself, reprepares on new connections as needed, etc.

Likely the biggest improvement to be had with database/sql is some way to manage read/write concurrency. But with attached databases and virtual tables and RETURNING doing it "automagically" quickly becomes non-trivial, so I've refrained from even doing that.

My advice for database/sql users is (prioritized):

1. start simple and correct: (Close your Rows!), use Context for cancellation, tweak your pool;
2. configure _txlock and use BeginTx with appropriate TxOptions;
3. maybe prepare hot statements.

Only if you hit concurrency issues, then create two separate read/write pools, and SetMaxOpenConns(1) on the write pool.

Or maybe try the bcw2 build and _txlock=concurrent, if your database is big enough for concurrent writes to optimistically work.

That said, I know you were probably looking for not database/sql advice. 😄

[ncruces]

For non database/sql advice, I don't have much to offer. Crawshaw wrote a few interesting blog posts awhile back; I used them to guide some decisions ¹. He was pretty critical of database/sql, but then Tailscale (he was CTO) uses a custom database/sql driver (so YMMV).

That said, crawshaw.io/sqlite has its own connection pool (though it is connection oriented, makes connections explicit) and caches all prepared statements (but stores them in an implicit map); database/sql is the exact opposite: you're encouraged to mostly ignore connections, and explicitly prepare statements.

API ergonomics aside, I agree that a connection pool matters, even more so with my driver, as it turns out setting up a new wazero instance is itself somewhat heavy. I'm really not sure how much you get from statement caching.

I don't necessarily agree enough with his API ergonomics decisions to think it is worth it for me to build another connection pool; zombiezen did it for modernc, though.

I believe database/sql is Good Enough™.

Footnotes

1. I took the SetContext method, and implemented savepoints for database/sql; shared cache and unlock-notify have no place in this driver. ↩

[daenney]

I've been on a database/sql detox. I'm not super fond of its API and a few other things it's doing, so I've been using this library The Hard Way ™️. It's actually been quite fun, and I have no need to support other DB engines in my own projects anyway. Useful learning exercise too, as a lot of what goes on with databases is a bit of a blind spot for me.

Since using the raw way the context is set on the sqlite3.Conn, there's a smiliar thing thing here I suppose. Traditionally I'd pass the http.Request.Context into (Exec|Prep)Context, as a way to try and guarantee a query is interrupted (I usually set a deadline on the request context). But if I were to do that with calls on sqlite3.Conn, then I'm swapping the context out from a running query it seems 😅. So I suppose that's another reason for the connection pool.

I might end up building a small connection pool or something for this. We'll see. I'm getting the sense that with a small wrapper around this I can actually get to something ergonomic pretty quickly. Query-to-struct binding should be pretty simple too.

[ncruces]

I don't love database/sql, I just don't hate it enough to replace it.

Using the same connection concurrently across requests means you can't use transactions. I wouldn't go there, but I tend to use transactions.

[daenney]

I don't love database/sql, I just don't hate it enough to replace it.

Haha, fair enough. I don't hate it enough for places where I need to collaborate with others. But for my own personal projects it's fun to experiment with other things. You learn a surprising amount by doing so.

I tend to use transactions by default, so yeap, another reason for a pool. I might peak a bit at what the zombiezen package does, especially for scaling pool sizes. Might be a fun thing for the winter holiday break.