Find N Plus One Queries in a Go Service Before Users Do

The N plus one is the most common performance bug in database backed code, and it hides well. One query fetches a list. A loop then runs one more query for every row: the author of each post, the items of each order, the permissions of each user. Every individual query is fast and correct, the code reads naturally, and the page is slow in proportion to its length. The bug does not show in development because a test list has five rows. This article builds a detector in Go 1.25.6 with pgx v5.11.0 against PostgreSQL 18.6 that counts the statements each request runs and names the repeated one, then fixes the handler two ways.

It belongs to the database cornerstone. The same pattern shows up when repositories are written per table, as measured in the repository pattern, and the next step after finding one is usually reading the plan.

What the bug looks like

for _, p := range posts {
    pool.QueryRow(ctx, "select name from authors where id = $1", p.AuthorID).Scan(&name)
}

That is the whole bug: a query inside a loop over query results. ORMs produce it through lazy loading, where touching post.Author runs a query the first time. Template code and serializers produce it too, because they look like plain field access.

A detector that needs no query changes

pgx lets you attach a QueryTracer to a connection configuration. It is called before and after every statement and gets the SQL and the context the query ran with. The lab uses that to keep a per request tally:

package main

import (
	"context"
	"fmt"
	"io"
	"log"
	"net/http"
	"net/http/httptest"
	"regexp"
	"sort"
	"strings"
	"sync"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

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

// tally counts the statements a request runs, grouped by their shape.
type tally struct {
	mu    sync.Mutex
	shape map[string]int
	total int
}

type tallyKey struct{}

var literal = regexp.MustCompile(`\$\d+`)

// tracer implements pgx.QueryTracer: it records every statement against the tally in the context.
type tracer struct{}

func (tracer) TraceQueryStart(ctx context.Context, _ *pgx.Conn, d pgx.TraceQueryStartData) context.Context {
	if t, ok := ctx.Value(tallyKey{}).(*tally); ok {
		t.mu.Lock()
		t.shape[strings.Join(strings.Fields(literal.ReplaceAllString(d.SQL, "?")), " ")]++
		t.total++
		t.mu.Unlock()
	}
	return ctx
}

func (tracer) TraceQueryEnd(context.Context, *pgx.Conn, pgx.TraceQueryEndData) {}

// detect wraps a handler and reports a statement that repeated within one request.
func detect(threshold int, next http.Handler, report func(string)) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		t := &tally{shape: map[string]int{}}
		next.ServeHTTP(w, r.WithContext(context.WithValue(r.Context(), tallyKey{}, t)))
		var repeated []string
		for s, n := range t.shape {
			if n >= threshold {
				repeated = append(repeated, fmt.Sprintf("%dx %s", n, s))
			}
		}
		sort.Strings(repeated)
		msg := fmt.Sprintf("%s ran %d statements", r.URL.Path, t.total)
		if len(repeated) > 0 {
			msg += "; repeated: " + strings.Join(repeated, " | ")
		}
		report(msg)
	})
}

func main() {
	ctx := context.Background()
	cfg, err := pgxpool.ParseConfig(dsn)
	if err != nil {
		log.Fatal(err)
	}
	cfg.ConnConfig.Tracer = tracer{}
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	for _, q := range []string{
		"drop table if exists posts626, authors626",
		"create table authors626 (id int primary key, name text not null)",
		"create table posts626 (id serial primary key, author_id int not null references authors626, title text not null)",
		"insert into authors626 select i, 'author ' || i from generate_series(1, 100) i",
		"insert into posts626 (author_id, title) select (i % 100) + 1, 'post ' || i from generate_series(1, 50) i",
	} {
		if _, err := pool.Exec(ctx, q); err != nil {
			log.Fatal(err)
		}
	}

	mux := http.NewServeMux()
	// The version that looks fine in a review: a loop with a lookup inside it.
	mux.HandleFunc("/posts-n-plus-one", func(w http.ResponseWriter, r *http.Request) {
		rows, _ := pool.Query(r.Context(), "select id, author_id, title from posts626 order by id")
		type post struct {
			ID, AuthorID int
			Title        string
		}
		var posts []post
		for rows.Next() {
			var p post
			_ = rows.Scan(&p.ID, &p.AuthorID, &p.Title)
			posts = append(posts, p)
		}
		rows.Close()
		for _, p := range posts {
			var name string
			_ = pool.QueryRow(r.Context(), "select name from authors626 where id = $1", p.AuthorID).Scan(&name)
			fmt.Fprintf(w, "%s by %s\n", p.Title, name)
		}
	})
	// Fix 1: one join.
	mux.HandleFunc("/posts-join", func(w http.ResponseWriter, r *http.Request) {
		rows, _ := pool.Query(r.Context(), "select p.title, a.name from posts626 p join authors626 a on a.id = p.author_id order by p.id")
		defer rows.Close()
		for rows.Next() {
			var title, name string
			_ = rows.Scan(&title, &name)
			fmt.Fprintf(w, "%s by %s\n", title, name)
		}
	})
	// Fix 2: load the related rows for all of them in one second query.
	mux.HandleFunc("/posts-batched", func(w http.ResponseWriter, r *http.Request) {
		rows, _ := pool.Query(r.Context(), "select author_id, title from posts626 order by id")
		var ids []int32
		var titles []string
		var authors []int32
		for rows.Next() {
			var a int32
			var t string
			_ = rows.Scan(&a, &t)
			authors, titles, ids = append(authors, a), append(titles, t), append(ids, a)
		}
		rows.Close()
		names := map[int32]string{}
		nrows, _ := pool.Query(r.Context(), "select id, name from authors626 where id = any($1)", ids)
		for nrows.Next() {
			var id int32
			var n string
			_ = nrows.Scan(&id, &n)
			names[id] = n
		}
		nrows.Close()
		for i, t := range titles {
			fmt.Fprintf(w, "%s by %s\n", t, names[authors[i]])
		}
	})

	var reports []string
	srv := httptest.NewServer(detect(5, mux, func(m string) { reports = append(reports, m) }))
	defer srv.Close()
	for _, path := range []string{"/posts-n-plus-one", "/posts-join", "/posts-batched"} {
		resp, err := http.Get(srv.URL + path)
		if err != nil {
			log.Fatal(err)
		}
		body, _ := io.ReadAll(resp.Body)
		resp.Body.Close()
		fmt.Printf("%-18s %d lines returned\n", path, strings.Count(string(body), "\n"))
	}
	for _, r := range reports {
		fmt.Println("report:", r)
	}
}

/posts-n-plus-one  50 lines returned
/posts-join        50 lines returned
/posts-batched     50 lines returned
report: /posts-n-plus-one ran 51 statements; repeated: 50x select name from authors626 where id = ?
report: /posts-join ran 1 statements
report: /posts-batched ran 2 statements

Three pieces make it work.

The tally travels in the request context. A middleware, detect, creates a *tally, stores it in the context and passes the request on. Every query the handler runs with r.Context() carries it, which is why the handler passes that context down: a query run with context.Background() is invisible to the detector, and is usually a bug for other reasons.

The tracer groups statements by shape. TraceQueryStart receives the SQL text and adds one to a counter keyed by the SQL with placeholders such as $1 replaced by ? and whitespace collapsed. Fifty lookups with fifty different ids all have the same shape and land on one key. Counting by exact text would hide the problem when values are inlined, which is one more reason to use placeholders.

The middleware reports after the handler returns. Any shape that ran at least five times, the threshold, is listed with its count, along with the request's total. A threshold of two flags legitimate pairs; five or ten is a practical level. The report goes to a callback here and would go to a logger or a metric in a service.

What the run found

The program serves three versions of the same page, 50 posts with their author names, and the middleware reported each:

• /posts-n-plus-one ran 51 statements, and the report named the offender: 50x select name from authors626 where id = ?. One query for the posts, fifty for the authors.
• /posts-join ran 1 statement.
• /posts-batched ran 2 statements.

All three returned the same 50 lines. The only visible difference was in the report, and in latency in a real deployment. Locally each query costs a fraction of a millisecond, but with a database one millisecond away, 51 sequential statements are 51ms of waiting before any work, on every request, with the pool's connections tied up for all of it. The cost is invisible per query and large per request.

Two fixes

A join. select p.title, a.name from posts p join authors a on a.id = p.author_id asks for everything at once. It is the right answer when you need columns from both tables and the relation is to one row, and it is what the /posts-join handler does.

A batched second query. When the related data is awkward to join, because it is one to many or comes from a different store, collect the keys and fetch them together: select id, name from authors where id = any($1) with the slice of ids, then build a map and fill in the names. This is the /posts-batched handler, two statements however long the list is, and it is the pattern behind dataloaders in GraphQL servers. With database/sql and pgx/stdlib, pass the slice with pq.Array-style helpers or use pgx.Batch through a native pool.

Joins on one to many relations multiply rows, so a post with ten comments joined to its author repeats the author ten times; the batched form avoids that. Which to use depends on the shape of the data, and either removes the loop.

Making it stick

A detector that runs only on a developer's machine finds bugs that were already written. Wire it in three places:

1. Tests. In an integration test, run the handler through the middleware against a database with a realistic number of rows, and fail if any shape repeats above a threshold. A new loop that issues a query then breaks the build. The cost is a few lines and the test is deterministic.
2. Development. Log the report at warn level in local and staging, so the author sees it while writing the handler.
3. Production, sampled. Export the statements per request as a metric, or the largest repeated count as a histogram, and alert on a rising tail. An endpoint whose p99 statement count doubles after a deploy has gained a loop.

For database/sql code that does not use pgx directly, the same idea works with otelsql, whose spans give a per request statement count in a tracing backend; see instrumenting database/sql with OpenTelemetry. A trace of a slow request makes an N plus one obvious: a comb of identical short spans under one handler span.

Limits and false alarms

Not every repeated statement is a bug. A batch job that deliberately runs one statement per item, a retry, or a health probe will trip the threshold, so tag those paths and exclude them, or raise the threshold per route. A loop of two or three queries is often fine. The detector also cannot see queries that happen on other goroutines unless they receive the same context, and it says nothing about the cost of a single slow statement; that is a job for slow query logging. Finally, the fix has a cost of its own. A join that returns 50 rows with large columns repeated can be slower than the loop was, so measure the result with explain analyze and not by assumption. The tracer interface is in the pgx documentation, and the any array comparison is described in the PostgreSQL manual under array comparisons.