Stage 7 · Master
Phase 18 — Performance Engineering
Query Optimization
Turn a per-row N+1 loop into one join, replace deep OFFSET pagination with a keyset cursor, and read EXPLAIN's join and sort strategies to know which rewrite actually mattered.
Find the Query Hiding Inside a Loop
The organization membership screen renders each member's role next to their display name. The repository method that backs it reads cleanly in Go and hides an expensive shape: it selects every membership row for the organization, then, inside the same handler, calls a second repository method once per membership to fetch that user's profile. For an organization with 40 staff members, that is 41 round trips to PostgreSQL for one HTTP request — and the cost does not show up in any single slow query log line, because no individual query is slow.
func (s *MembershipService) ListWithProfiles(ctx context.Context, orgID uuid.UUID) ([]MemberView, error) {
memberships, err := s.memberships.ListByOrganization(ctx, orgID)
if err != nil {
return nil, fmt.Errorf("list memberships: %w", err)
}
views := make([]MemberView, 0, len(memberships))
for _, m := range memberships {
profile, err := s.users.GetByID(ctx, m.UserID) // one round trip per membership
if err != nil {
return nil, fmt.Errorf("get user %s: %w", m.UserID, err)
}
views = append(views, MemberView{Membership: m, Profile: profile})
}
return views, nil
}Every individual query in this function is fast. The pattern — one query, then N more inside a loop — is the defect, and it is invisible to a slow-query log tuned for single-statement latency.
pg_stat_statements aggregates by normalized query text, so 41 fast one-row SELECTs look like a cheap, popular query — never like the 41x-round-trip cost the handler actually paid. Catching N+1 requires looking at request-scoped query counts, not per-query latency.
Collapse the Loop Into One Statement
organization_memberships and the user service's users table live in different services in this platform, which is exactly the constraint that made the N+1 loop look reasonable in the first place — there is no foreign key across service boundaries to JOIN against. The real fix is not a database join at all; it is batching the second call so N round trips become 1, using the same interface the loop already called.
func (s *MembershipService) ListWithProfiles(ctx context.Context, orgID uuid.UUID) ([]MemberView, error) {
memberships, err := s.memberships.ListByOrganization(ctx, orgID)
if err != nil {
return nil, fmt.Errorf("list memberships: %w", err)
}
userIDs := make([]uuid.UUID, len(memberships))
for i, m := range memberships {
userIDs[i] = m.UserID
}
profiles, err := s.users.GetByIDs(ctx, userIDs) // one batched round trip
if err != nil {
return nil, fmt.Errorf("batch get %d users: %w", len(userIDs), err)
}
byID := make(map[uuid.UUID]UserProfile, len(profiles))
for _, profile := range profiles {
byID[profile.ID] = profile
}
views := make([]MemberView, 0, len(memberships))
for _, m := range memberships {
profile, ok := byID[m.UserID]
if !ok {
continue // user deleted after membership was recorded; surfaced, not silently joined away
}
views = append(views, MemberView{Membership: m, Profile: profile})
}
return views, nil
}GetByIDs runs one query with = ANY($1::uuid[]) against the user service's own database. The round-trip count drops from 1+N to exactly 2, regardless of organization size.
SELECT id, display_name, email, status
FROM users
WHERE id = ANY($1::uuid[]);= ANY($1::uuid[]) with a single array parameter is one planned statement PostgreSQL executes once — distinct from building 41 OR-chained placeholders, which some ORMs do and which forces a fresh plan for every different batch size.
OFFSET Pagination Gets Slower With Every Page
The flats list endpoint paginates with ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET $n, and it is the same page 1 users always see that stays fast — page 400, which almost no one requests but which the property-export job walks through sequentially, is the one that times out.
Limit (cost=1231.40..1234.55 rows=20 width=96) (actual time=41.203..41.298 rows=20 loops=1)
-> Index Scan using flats_tenant_building_page on flats (cost=0.43..1554.90 rows=9880 width=96) (actual time=0.038..41.180 rows=8020 loops=1)
Index Cond: (organization_id = '3fce...')
Planning Time: 0.121 ms
Execution Time: 41.331 msThe index scan still reads and discards all 8,000 rows before the OFFSET, then returns the next 20. Every deeper page repeats work every shallower page already paid — cost grows linearly with page number even though the index itself is correct.
Replace OFFSET With a Keyset Cursor
The cursor mechanics are not new — User Service built the signed keyset cursor, and Notice Service extended it to score-ordered results. What this lesson adds is the plan-level proof of why it matters: the index flats_tenant_building_page already orders by (organization_id, building_id, created_at DESC, id DESC), and the EXPLAIN output above shows OFFSET reading and discarding 4,000 rows it was never going to return. Same index, same data, different question asked of it.
SELECT id, building_id, number, created_at
FROM flats
WHERE organization_id = $1
AND (created_at, id) < ($2, $3) -- cursor: last row's own (created_at, id)
ORDER BY created_at DESC, id DESC
LIMIT 20;The row-value comparison (created_at, id) < ($2, $3) is what makes this a true seek: it matches the index's own column order exactly, so PostgreSQL walks the index tree directly to the cursor position instead of counting past it.
type FlatsCursor struct {
CreatedAt time.Time `json:"created_at"`
ID uuid.UUID `json:"id"`
}
// Encode produces an opaque, URL-safe cursor. Clients treat it as opaque;
// only this package knows it is base64(created_at,id).
func (c FlatsCursor) Encode() string {
raw := fmt.Sprintf("%d:%s", c.CreatedAt.UnixNano(), c.ID)
return base64.RawURLEncoding.EncodeToString([]byte(raw))
}Encoding the cursor opaquely keeps the API contract stable even if the underlying sort key changes later — clients pass back exactly what they were given, never construct one themselves.
| Approach | Page 1 cost | Page 400 cost | Correct under concurrent inserts |
|---|---|---|---|
| OFFSET/LIMIT | ~0.05 ms | ~41 ms, growing linearly | No — a new row can shift every later page by one, duplicating or skipping rows |
| Keyset cursor | ~0.05 ms | ~0.06 ms, effectively flat | Yes — position is anchored to a real row's value, immune to inserts elsewhere |
Watch Aggregates Spill to Disk
The finance export groups every invoice line by organization and category for the monthly report. On the three largest tenants, this query's execution time doubled between one release and the next with no code change — the row count crossed a threshold where PostgreSQL's chosen in-memory hash table no longer fit in work_mem and the planner's own sort/hash strategy started spilling to disk.
HashAggregate (cost=98213.11..98512.40 rows=29929 width=48) (actual time=1821.442..2402.981 rows=29811 loops=1)
Group Key: organization_id, category
Planned Partitions: 4 Batches: 5 Memory Usage: 4145kB Disk Usage: 91264kB
-> Seq Scan on invoice_lines (cost=0.00..48213.00 rows=1842211 width=24) (actual time=0.012..302.552 rows=1842211 loops=1)
Execution Time: 2431.117 msDisk Usage: 91264kB is the tell — the hash table spilled to temporary files on disk because work_mem (4MB in this session) was smaller than the working set, turning an in-memory operation into one bottlenecked by disk I/O.
A query with two sort nodes and a hash aggregate can use up to three times work_mem in one execution. Raising it globally in postgresql.conf multiplies that by every concurrent connection running similar queries — this is why the fix below is scoped to one session, not the server default.
-- services/payment-service/internal/finance-export/internal/adapter/postgres/report_repository.go issues this
-- immediately after acquiring its connection, before running the report query.
SET LOCAL work_mem = '64MB';
SELECT organization_id, category, sum(amount_cents)
FROM invoice_lines
GROUP BY organization_id, category;SET LOCAL scopes the setting to the current transaction only — it reverts automatically at COMMIT, so the finance job's one wide aggregate never inflates work_mem for every other connection sharing the same pool.
Nested Loop, Hash Join, and Merge Join Are Not Interchangeable
- Nested Loop: cheap when the outer side is small and the inner side has a usable index — the default for the flats-by-organization lookups in this lesson's earlier examples.
- Hash Join: builds an in-memory hash table from the smaller input, then probes it with the larger — chosen for the membership/user batch fetch's underlying equi-join shape once row counts grow past what a nested loop can do cheaply.
- Merge Join: requires both inputs pre-sorted on the join key — rare here since few of this platform's join keys share a natural sort order across two different tables' indexes.
A query that regresses after a schema change or a data-volume shift is frequently a join strategy the planner silently switched away from, not a missing index. Comparing EXPLAIN output before and after a regression — specifically which join node type appears — is the fastest way to tell whether the fix is an index, a statistics refresh (ANALYZE), or a work_mem adjustment.
Applied exercise
Fix the visitor-approval dashboard's N+1 and its deep page
The visitor-approval dashboard lists pending admission requests, then calls the resident service once per request to show the requesting resident's name, and paginates history with OFFSET that a security-audit export walks to page 250 nightly.
- Rewrite the per-row resident lookup as a single batched call using an = ANY($1::uuid[]) query.
- Replace the OFFSET-based history pagination with a keyset cursor anchored on the same columns as its existing index.
- Capture EXPLAIN (ANALYZE, BUFFERS) for the deep page before and after the keyset change.
- State, in one sentence, why the batched fetch is not itself a database JOIN.
Deliverable
An updated service method with one batched call instead of a loop, a keyset-based repository method, and a before/after EXPLAIN comparison for the formerly-deep page.
Completion checks
- The round-trip count for a 50-row dashboard drops from 51 to 2.
- The keyset query's WHERE clause uses a row-value comparison matching the index's exact column order.
- Execution time for the equivalent of 'page 250' is within a small constant factor of page 1, not linearly worse.
Why does batching the user-profile lookup into GetByIDs fix the N+1 problem without introducing a cross-service SQL JOIN?
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.