Joins, transactions, and the N+1 problem

A performance bug that turns up in almost any codebase that talks to a database.

Topic 53 of 98 · 5 min to read · 50 min to build

After this topic you can

Spot an N+1 query loop and fix it with a join or one batch query.
Write a transaction with a deferred rollback that commits only on success.
Read an EXPLAIN ANALYZE plan to find a missing index.

N+1: one query, then one per row

You list twenty notes. For each one you fetch its attachment count. That is 1 + 20 = 21 round trips to the database. At two hundred notes it is 201. At a few milliseconds per round trip, the endpoint takes most of a second, and nothing in the Go code looks slow.

n-plus-one.go

1// SLOW — a query per note
2notes, _ := repo.List(ctx, userID, limit, offset)
3for i := range notes {
4	notes[i].Attachments, _ = attRepo.CountByNote(ctx, notes[i].ID)   // N queries
5}
6
7// FAST — one query
8`SELECT n.id, n.title, n.body, COUNT(a.id) AS attachments
9   FROM notes n
10   LEFT JOIN attachments a ON a.note_id = n.id
11  WHERE n.user_id = $1
12  GROUP BY n.id
13  ORDER BY n.created_at DESC
14  LIMIT $2 OFFSET $3`

The other fix: fetch in one batch

When a join is awkward, collect the ids and do a second query for all of them at once. Two queries total, no matter how many rows.

batch.go

1ids := make([]int64, len(notes))
2for i, n := range notes {
3	ids[i] = n.ID
4}
5
6rows, err := db.QueryContext(ctx,
7	`SELECT note_id, id, filename FROM attachments WHERE note_id = ANY($1)`,
8	ids) // pgx encodes a Go slice as a Postgres array
9
10// then group them into a map[int64][]Attachment and attach in one pass

Note: Turn on your database’s slow query log during development, or count queries in a test. When you read the Go code, the loop looks harmless. Count the round trips and N+1 is obvious.

Transactions: all of it, or none of it

Some operations must not half-happen. Creating a note and its first attachment row is one unit: if the second insert fails, the first must be undone.

tx.go

1func (r *Repository) CreateWithAttachment(ctx context.Context, n *Note, a *Attachment) (err error) {
2	tx, err := r.db.BeginTx(ctx, nil)
3	if err != nil {
4		return err
5	}
6	defer func() {
7		if p := recover(); p != nil {
8			tx.Rollback()
9			panic(p)                 // roll back, then let the panic continue
10		}
11		if err != nil {
12			tx.Rollback()            // named return: this sees the real error
13		}
14	}()
15
16	if err = tx.QueryRowContext(ctx, insertNote, n.UserID, n.Title, n.Body).Scan(&n.ID); err != nil {
17		return err
18	}
19	a.NoteID = n.ID
20	if _, err = tx.ExecContext(ctx, insertAttachment, a.NoteID, a.Filename); err != nil {
21		return err
22	}
23
24	return tx.Commit()
25}

The named return value `err` is what lets the deferred function know whether to roll back. This is one of the few places where naming a return value pays for itself.

Transaction rules

Keep them short. A transaction holds a pooled connection for its whole life. A few slow ones at once can use up the pool.
Never make an HTTP call inside one. A slow third party then ties up a database connection that other requests need.
Take row locks in a consistent order across the codebase, or two transactions grabbing the same two rows in opposite orders will deadlock.
Do not wrap reads in a transaction out of habit. A single SELECT is already atomic.

Read your query plan

psql

1EXPLAIN ANALYZE
2SELECT id, title FROM notes WHERE user_id = 1 ORDER BY created_at DESC LIMIT 20;
3
4-- "Seq Scan on notes" means no index is being used. Add one.
5-- "Index Scan using notes_user_id_created_at_idx" is what you want.

Adding a missing index usually beats any Go optimisation you could write. Check the plan for every query on a table that grows. On a tiny table, Postgres may choose a Seq Scan even when an index exists, so test with realistic row counts.

The short version

A query inside a loop is N+1. Fix it with a JOIN, or one batched WHERE id = ANY($1).
A transaction is all-or-nothing. Use a named return so the deferred rollback sees the error.
Transactions hold a connection: keep them short, and never call an external service inside one.
EXPLAIN ANALYZE every query on a growing table. A sequential scan on a large table usually means a missing index.

Remember: The database is where your latency lives. One join beats twenty round trips, and one index beats a month of Go micro-optimisation.

Build this before moving on

Add an attachment count to your notes list — first with the naive loop, then with a JOIN. Time both with 200 notes. Then run EXPLAIN ANALYZE on the list query and confirm it uses your index.

Then try these in your own editor

1Build the N+1 version with 200 rows and time it. Then do it with one JOIN and time that.
2Run EXPLAIN ANALYZE on your list query before and after adding the index.
3Write a two-statement transaction where the second fails, and confirm the first was rolled back.
4Open a transaction and sleep for 30 seconds inside it while running other requests. Watch the pool.

Official documentation

Check yourselfTake the Databases test