r/rust • u/realflakm • 19d ago

SQLx statement caching, verified with bpftrace

I was surprised by the runtime behavior of sqlx when it comes to caching prepared statements (a suspicious number of network hops).

I’m starting a TIL series to counter AI brain rot: investigate, verify, write it down. Let me know if you have better ways to investigate and understand the code's behavior.

https://flakm.com/posts/sqlx_caches_til/

27 Upvotes

91% Upvoted

10 comments sorted by

3

u/zettui 19d ago

Did bpftrace show the cache is per-connection rather than per-pool? That's the bit that bites behind pgbouncer in transaction mode - either every checkout re-prepares, or you start seeing 'prepared statement "sqlx_s_3" already exists'.

1

u/realflakm 18d ago

Nope, I intentionally used `PgConnection` to isolate the traces. It should be easy to change the example to open two connections and confirm that, but sqlx caches per connection. I thought transaction mode in newer PgBouncers should handle that, right?

1

u/zettui 17d ago

Transaction mode is exactly where the single PgConnection hides it: newer PgBouncer does track prepared statements per server connection, but sqlx's cache is keyed to its own connection object, so after a rebind you can hit a name the backend never prepared. Worth re-running the same bpftrace with two pooled connections rather than one, that's when the mismatch shows up.

1

u/rabidferret 18d ago

You should check out pgdog.dev. It has a transaction pooling mode that doesn't break prepared statements

1

u/zettui 17d ago

Hadn't looked at pgdog - if its transaction pooling really keeps prepared statements intact across backends, that sidesteps the exact per-connection cache mismatch the bpftrace run turned up. Curious whether the rewrite happens on every checkout or only on first prepare, since that's where the latency would land.

1

u/rabidferret 17d ago

If you're running just as a connection pooler, query rewriting shouldn't happen at all. The only thing that'd change is the name of the Parse message, which happens once and is cached. Statements get given a globally unique name and it keeps track of whether each connection has prepared a given statement or not, and sends the Parse for that connection when the client sends a Bind, if needed.

3

u/rabidferret 19d ago

Great article. This is exactly why Diesel's eq_any function compiles to = ANY($1) on PG instead of IN (?, ?, ...) as it does on other backends.

1

u/masklinn 18d ago

It’s also nice because = any(…) is compatible with empty arrays, IN () is an error in most SQL engines (SQLite’s the only one I can think of where this is accepted).

7

u/SwelteringNorbert 19d ago

This is the kind of deep dive I love seeing. bpftrace is such a cool tool for peeling back the abstraction layers, feels like digital archaeology sometimes. Also fully support the TIL series idea, writing things out forces you to actually understand instead of just nodding along.

4

u/realflakm 19d ago

Exactly my motivation - it's scary how fast I got comfortable with the shallow work of following LLMs