sql-query-performance

SQL Query Performance (Go)

Overview

Go has no ORM to blame for an N+1 — it's a loop you wrote yourself, calling the repository once per item instead of once for the batch. This skill is the Go-side half of query performance; java-persistence covers the JPA/Hibernate side.

Core principle: one round trip per request-shaped operation, not one per row.

N+1 in Go

// ❌ one query per item: N+1
for _, id := range ids {
    task, err := repo.Get(ctx, id)   // round trip per id
    ...
}

// ✅ one query for the batch
tasks, err := repo.GetMany(ctx, ids)   // SELECT ... WHERE id = ANY($1)

SELECT id, title, status FROM tasks WHERE id = ANY($1);

Bind ids as a []uuid.UUID (pgx encodes a Go slice as a Postgres array directly — no manual IN (...) string building). For a write-side batch, use pgx.Batch to pipeline multiple statements over one round trip instead of looping with individual Exec calls.

Database hygiene

• Every call takes a context.Context — QueryContext/ExecContext (database/sql) or pgx's ctx parameter — so a slow query is cancelable and traceable.
• defer rows.Close() immediately after a successful Query, and check rows.Err() after the loop — a Query that returns rows can still fail mid-stream, and an unclosed Rows leaks the connection.
• Use Exec, not Query, for statements that return no rows (INSERT/UPDATE/DELETE without RETURNING) — Query leaves a result set open that must still be drained.
• No SELECT * in application code — name the columns, so an added column doesn't silently change scan order or payload size.

Reading EXPLAIN

psql "$DATABASE_URL" -c "EXPLAIN (ANALYZE, BUFFERS) <query with literal values, not placeholders>"

Run it against realistically seeded data, not an empty table — an empty-table plan hides the index Postgres would actually need. Read for:

• Seq Scan on a table bigger than a few thousand rows → missing index.
• Estimated vs actual row counts far apart → stale statistics (ANALYZE <table>) or a predicate the planner can't estimate well.
• A Sort that spills to disk (Sort Method: external merge) → needs work_mem tuning or an index that avoids the sort.