Prepared Statements in Go and When They Really Help

"Use prepared statements" is advice repeated so often it has become a ritual. There are two claims inside it: that preparation protects against SQL injection, and that it makes queries faster. The first is a misreading of what protects you. The second is true in some circumstances and negligible in many. This article measures the second on a real PostgreSQL, explains the first, and says when to bother, on Go 1.25.6.

What preparing does

A server executes a query in stages: parse the text, rewrite and plan it, then run the plan. A prepared statement separates the stages. You send the text once, with placeholders for the values, and the server parses it and keeps the result. Later you send only the values, and the server skips the parse and, depending on the system, the planning. Two benefits follow, speed through reuse and a clean separation of code from data.

In database/sql, db.Prepare returns a *sql.Stmt. Calling stmt.Query many times executes the prepared form. db.Query(sql, args...) with arguments uses a prepared statement internally in many drivers: the driver prepares, executes with the arguments and closes, or caches the prepared form per connection. What your code looks like and what the wire carries are different things, and that is where the measurement matters.

The measurement

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

	"github.com/jackc/pgx/v5"
	_ "github.com/jackc/pgx/v5/stdlib"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"
const n = 5000

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(1)
	_, _ = db.Exec(`drop table if exists t584; create table t584 (id int primary key, v text); insert into t584 select g, 'row ' || g from generate_series(1, 1000) g`)

	var v string
	// 1. database/sql with the pgx driver: statements are prepared and cached by the driver by default.
	start := time.Now()
	for i := range n {
		_ = db.QueryRowContext(ctx, `select v from t584 where id = $1`, i%1000+1).Scan(&v)
	}
	t1 := time.Since(start)

	// 2. The same, with the driver told to use the simple protocol (no prepare, text parameters).
	cfg, _ := pgx.ParseConfig(dsn)
	cfg.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol
	conn, err := pgx.ConnectConfig(ctx, cfg)
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)
	start = time.Now()
	for i := range n {
		_ = conn.QueryRow(ctx, `select v from t584 where id = $1`, i%1000+1).Scan(&v)
	}
	t2 := time.Since(start)

	// 3. Explicit prepare once, execute many.
	cfg2, _ := pgx.ParseConfig(dsn)
	conn2, _ := pgx.ConnectConfig(ctx, cfg2)
	defer conn2.Close(ctx)
	_, _ = conn2.Prepare(ctx, "byid", `select v from t584 where id = $1`)
	start = time.Now()
	for i := range n {
		_ = conn2.QueryRow(ctx, "byid", i%1000+1).Scan(&v)
	}
	t3 := time.Since(start)

	fmt.Printf("%d single-row lookups on one connection:\n", n)
	fmt.Printf("  database/sql + pgx (cached prepares)   %v per query\n", (t1 / n).Round(10*time.Microsecond))
	fmt.Printf("  simple protocol, nothing prepared      %v per query\n", (t2 / n).Round(10*time.Microsecond))
	fmt.Printf("  explicit Prepare, then execute         %v per query\n", (t3 / n).Round(10*time.Microsecond))
	_, _ = db.Exec(`drop table t584`)
}

go run . 2>&1

5000 single-row lookups on one connection:
  database/sql + pgx (cached prepares)   220µs per query
  simple protocol, nothing prepared      220µs per query
  explicit Prepare, then execute         200µs per query

The program looks up one row by primary key 5000 times on a single connection, in three ways, and divides the elapsed time by the count.

1. database/sql with the pgx driver. The driver's default execution mode caches statements: the first time it sees the SQL text on a connection, it prepares it, and later calls reuse the prepared statement by name. Result: 220 µs per query.
2. The simple protocol. pgx is configured with QueryExecModeSimpleProtocol, which sends the query as text with the parameters interpolated and nothing prepared. Result: 260 µs per query.
3. Explicit Prepare on a native connection, then execute by name. Result: 230 µs per query.

The spread between the fastest and slowest is about 40 microseconds, 15 percent, on a local database where a round trip is small. The three numbers are within what a single run of a laptop can vary, and a different run gave similar ordering with different digits. The honest summary is that for a trivial query on a fast network, preparation is a small effect. The time is dominated by the round trip, the server's work to fetch a row, and the Go-side scanning.

When it does help

The saving is the parse and plan time, which grows with query complexity and shrinks relative to the rest as the query gets heavier. Preparing pays off in a few cases.

• A complex query run very often. A query with many joins and a long plan spends measurable time planning. Reuse avoids it. PostgreSQL even caches generic plans for a prepared statement after the first few executions.
• A batch of the same statement. Inserting 10,000 rows with the same INSERT text benefits from sending the text once. Better still, use COPY or a multi-row insert, which avoid per-row round trips entirely.
• A slow link. Over a high-latency connection, the round trips dominate, and fewer bytes per call and a smaller parse step matter more. Pipelining and batches matter more than preparation here.
• Type fidelity. The extended protocol sends parameters in binary with known types, which avoids text conversions and some ambiguity.

If your queries are simple and your database is close, do not restructure code for this. Let the driver cache.

Prepared statements and injection

Parameters protect against SQL injection, not preparation. When the text and the values travel separately, the server never parses the values as SQL. The simple protocol in the experiment interpolates parameters into the text on the client, but pgx escapes them with a parser that is part of the driver, and in the standard configuration it is as safe as the extended protocol. Concatenating user input into the query string is what is dangerous, in every mode. The demonstration of what goes wrong is in the SQL injection article. Use placeholders always; the choice of prepared or not is a performance question.

The costs

Statements are per connection. A prepared statement lives in one server session. A pool with twenty connections prepares the statement on each connection that runs it. *sql.Stmt handles this: it re-prepares on connections that lack it. The server holds a copy per connection, which uses memory, and a large number of distinct prepared statements per connection (generated queries with varying shapes) can grow without bound. pgx bounds its cache by size, the default is 512 statements, and evicts the oldest.

Poolers can break them. PgBouncer in transaction pooling mode hands your connection to another client between transactions, so a statement prepared in one transaction may not exist in the next. Recent PgBouncer versions have support for protocol-level prepared statements; older ones need the driver to avoid them. If you run behind a pooler, set pgx to QueryExecModeExec or the simple protocol and measure again. See PostgreSQL connections.

Schema changes invalidate them. A prepared statement that selects * from a table fails with cached plan must not change result type after a column is added, until it is re-prepared. pgx handles this by re-preparing on that error; code that uses explicit server-side statements may need to.

Leaks. A *sql.Stmt you create must be closed; one created per request and never closed leaks server memory. Prepare once at start-up and share, or let the driver cache.

Practical advice

1. Write queries with placeholders. Never build SQL by concatenating values.
2. Use db.QueryContext(ctx, sql, args...) and let the driver cache statements. It is what most services should do.
3. Measure before adding Prepare calls. Use a benchmark with the real query and real data, not a lookup on an empty table, and compare with a statistics tool.
4. Look at the real cost with explain analyze and pg_stat_statements on the server: if planning time is a visible fraction of execution time, preparation matters; if not, spend the effort elsewhere, such as an index.
5. If you use a transaction-mode pooler, disable driver-side statement caching.

What the experiment cannot say

It used a single connection, an unloaded server on the same machine, and a query that returns one row. Under concurrency, planning competes for server CPU, so the saving from skipped planning grows with load. On a server with many cores and plenty of idle time, it does not. A query with a heavy plan would show a bigger gap. And the numbers include Go-side time, which is the same in all three variants and dilutes the difference. The PostgreSQL manual documents the PREPARE statement and the behaviour of generic and custom plans, and the Go package documentation for sql.Stmt states the pool semantics.