Blog · 8 October 2026

8 database trips down to 1: fixing the N+1 a join can't

The classic fix for N+1 queries is a join. But what if every item needs a different query? Our Goals panel did, so a site with eight Goals made eight trips to Postgres, one after another. A single UNION ALL turned them into one trip, and the trick works anywhere the queries differ but their columns don't.

Eight queries, eight shapes

A Foresite Goal counts conversions, and comes in three kinds:

• an event: name = 'signup'
• an exact page: pathname = '/thank-you'
• a page prefix: starts_with(pathname, '/blog/')

Some kinds can read daily rollups and some need raw events, so each Goal's SQL comes out a different shape. The obvious code ran them one at a time:

for _, g := range goals {
	d := q.goalDaily(g) // this Goal's SQL: visitors, events and revenue per day
	err := e.queryRow(ctx, `SELECT sum(visitors), sum(events), sum(revenue_micros) FROM (`+d.sql+`) d`, d.args...).
		Scan(&r.Visitors, &r.Events, &r.Revenue)
	// ...
}

One query, tagged

Every Goal's SQL returns the same columns. So we tag each one with its position, glue them together with UNION ALL, and group by the tag:

parts := make([]string, len(goals))
var args []any
for i, g := range goals {
	d := q.goalDaily(g)
	parts[i] = `(SELECT ` + strconv.Itoa(i) + ` AS goal, visitors, events, revenue_micros FROM (` + d.sql + `) d` + strconv.Itoa(i) + `)`
	args = append(args, d.args...)
}
rows, err := e.query(ctx, `
	SELECT goal, sum(visitors)::bigint, sum(events)::bigint, coalesce(sum(revenue_micros), 0)::bigint
	FROM (`+strings.Join(parts, " UNION ALL ")+`) g
	GROUP BY goal`, args...)

The details that matter:

• The tag is a literal, not a parameter. It's our own loop counter, never user input, so it's safe in the SQL text. Anything a user typed still goes through args.
• Arguments are appended in the same order as the parts, so they line up with their placeholders. If you write $1, $2 by hand, renumber each part.
• Every subquery needs its own alias (d0, d1...). Postgres insists.
• A Goal with no rows produces no output row. So fill in every Goal at zero first, and let the rows overwrite them:

out := make([]GoalResult, len(goals))
for i, g := range goals {
	out[i] = GoalResult{GoalID: g.ID, Kind: g.Kind, Match: g.Match}
}
for rows.Next() {
	var i int
	var visitors, events, revenue int64
	if err := rows.Scan(&i, &visitors, &events, &revenue); err != nil {
		return nil, err
	}
	out[i].Visitors, out[i].Events, out[i].Revenue = uint64(visitors), uint64(events), revenue
}

When to use it, and when not to

Use it when each item needs a different query but they all return the same columns: configurable widgets, conversion goals, saved searches, alerts.

Use something simpler when you can:

• Same query, different parameter: = ANY($1) with a GROUP BY, or unnest($1::bigint[]).
• Items that are rows in another table: a join. It was always going to be a join.

And mind the edges. Postgres allows at most 65,535 parameters in one statement, so split big batches into chunks. Run EXPLAIN ANALYZE to check each branch still uses its index. And one bad branch fails the whole statement, so check the items first.

The result

The Goals panel now costs one round trip however many Goals a site has. In fairness, our app and database share a data centre, so each saved trip was short, and this was the smallest win of the week. But its cost no longer grows with every Goal, and with a database in another region or behind a connection pooler, each saved trip is worth much more.

It was one of four changes in We made our dashboard 35% faster without touching a single query.