A Go reindexer makes the server's prepared-statement count climb all day — how do you find and fix it?

## The symptom A background job re-runs one query shape over every row in a table. It runs for hours. Meanwhile the database server's memory climbs and the count of prepared statements it is holding climbs with it, until either a per-session limit is hit or the server starts to hurt. The Go process, meanwhile, looks fine: modest heap, no goroutine growth, no errors in the log. That asymmetry is the tell. Server-side state is not Go memory, and the Go garbage collector cannot reclaim it. Something is asking the server to keep statements and never asking it to stop. ## Distinguishing the two causes There are exactly two reasons the server holds more statements than you expected, and telling them apart is the whole diagnosis. **1. Legitimate fan-out.** A `*sql.Stmt` prepared on a `*sql.DB` re-prepares itself on each connection it executes on. Its footprint is therefore *statements* times *connections it has touched*. That number is bounded: it warms up, then settles. If the count rises early and plateaus, this is what you are looking at, and it is normal. **2. A leak.** A `*sql.Stmt` that is created and never closed leaves a server-side statement behind permanently. Do that in a loop and the count grows in lockstep with rows processed, without bound, until the connection dies. If the count keeps climbing all day and roughly tracks progress through the work, this is your answer. "Plateaus" versus "keeps climbing" is the single most useful question to ask before reading any code. ## Working it from the server List the server's prepared statements and group them two ways: - **By session.** A leak concentrates: a handful of sessions hold thousands of statements each, and those sessions are the connections the job is using. Healthy fan-out is flat across sessions and small. - **By statement text.** Thousands of copies of one identical query shape is not a diverse workload; it is the same `PrepareContext` call executed repeatedly. The text names the code path for you. That pairing — one text, one set of sessions, unbounded count — is nearly conclusive on its own. ## Confirming it from Go Take a heap profile of the running process (the in-use heap, not the cumulative allocation view) and look at what retains `database/sql` statement objects. In a real leak the retainer is rarely subtle: a slice appended to per batch, a map keyed by row id, or a struct field reassigned each iteration while the previous value is still referenced. If the Go-side count tracks the server-side count, you have both ends of the same string. If the handles are *not* retained in Go — the profile is flat while the server count climbs — that is still a leak, just a completed one: the handles were dropped without `Close`, the Go objects were collected, and the server-side statements they represented are now unreachable garbage that only the connection closing will clear. That distinction matters for the fix only in that no amount of Go-side tuning will help. ## Reading the code The shapes to look for, in order of frequency: - A `db.PrepareContext` **inside** the loop, with no `Close` at all. - A `defer stmt.Close()` inside a loop body in a long-running function — deferred calls run at function return, so this piles up handles until the function ends rather than releasing them per iteration. - A `Close` that exists but sits after an early `return` or is skipped on the error path. - A helper that prepares a statement and returns it, with callers that ignore the fact they now own a lifetime. ## The fix Prepare once, outside the loop, and close it once: ```go stmt, err := db.PrepareContext(ctx, q) if err != nil { return err } defer stmt.Close() for _, d := range docs { /* stmt.ExecContext(ctx, ...) */ } ``` That is also the faster program: the whole point of preparing was reuse, and the leaking version was re-paying the parse cost on every row while accumulating state. If the job's structure makes a clean lifetime awkward, the alternative is to drop the explicit preparation entirely and call `db.ExecContext(ctx, sql, args...)` — depending on the driver that either uses a one-shot path or prepares, executes and closes on your behalf, which at least cannot leak. ## What not to do Raising the server's per-session statement limit buys hours and hides the trend. Restarting the job resets the count and teaches you nothing. Neither is a fix, and both make the next occurrence harder to spot, because the metric that would have told you is now noisy. ## The durable prevention Make the rule reviewable: every `PrepareContext` has a paired `Close` in the same function, or the handle is owned by a long-lived component and closed at shutdown. Handles created in a loop are a defect on sight. That rule is cheap to check in review and catches the whole class.