# Part 9: Pagination and API performance

`GET /users` *returns every user. That works until the day it takes the database down. Keyset pagination, the index that makes it fast, and three cheap wins.*

---

**Series:** Build a production-grade REST API in Go · **Part 9 of 17** **Where we are:** the Dockerised, observable API from Part 8.

Our list endpoints are time bombs. With ten rows in development they're instant; with ten million in production, one request loads everything into memory, serializes it, and sends hundreds of megabytes. A single curious client can degrade the service for everyone. **Every list endpoint must be paginated**, and *how* you paginate decides whether it stays fast.

## Two ways to paginate

**OFFSET pagination** (the one every tutorial shows):

```sql
SELECT * FROM orders ORDER BY id OFFSET 400000 LIMIT 20;
```

**Keyset pagination** (also called "seek" or cursor-based):

```sql
SELECT * FROM orders WHERE id > 400000 ORDER BY id LIMIT 20;
```

They return the same 20 rows. Let's measure what the database does. On a table with 500,000 rows (`EXPLAIN ANALYZE` in PostgreSQL 17):

```plaintext
OFFSET 400000 LIMIT 20
  Index Scan using orders_pkey (actual rows=400020)
  Execution Time: 15.270 ms

WHERE id > 400000 ... LIMIT 20
  Index Scan using orders_pkey (actual rows=20)
  Execution Time: 0.025 ms
```

Look at `actual rows`. With OFFSET the database **reads and throws away 400,000 rows** to find the 20 you want. Page 1 is cheap, page 20,000 is expensive, and cost grows linearly with depth. Keyset jumps straight to the right place using the index: 20 rows, about **600 times faster** here, and the gap widens with table size.

OFFSET has a second flaw: if rows are inserted or deleted while someone pages through, items get skipped or repeated, because "row number 40" shifted. Keyset continues "after the last thing I saw", which is stable.

The trade-off: with keyset you can't jump to "page 57". You get *next* (and with extra work *previous*). For APIs consumed by apps and scripts that's almost always what you want. If you truly need numbered pages (an admin UI over a small table), OFFSET is acceptable. Choose deliberately.

## The API contract

```plaintext
GET /users?limit=20&cursor=<opaque string>

{
  "data": [ ... up to `limit` items ... ],
  "next_cursor": "Mg"          ← absent on the last page
}
```

Design decisions:

- **The response is an envelope** (`{"data": [...], "next_cursor": ...}`) instead of a bare array. A bare array has nowhere to put new fields, so adding pagination later would be a breaking change. An envelope can grow.
- **The cursor is opaque.** Today it encodes the last id; clients just copy it back. Because clients never parse it, we can change what's inside (add a sort key, sign it) without breaking anyone.
- `limit` **is clamped** to a server maximum (default 20, max 100, both configurable). A huge limit is the same problem as no pagination.
- Invalid `limit` or `cursor` is a `400`, not a silent fallback.

## Step 1: the store

`internal/storage/users.go (excerpt)`

```go
// List returns up to limit users with id > afterID, ordered by id, plus whether
// more exist. This is KEYSET ("seek") pagination: the client sends the last id
// it saw and we continue after it using the primary-key index.
//
// Why not OFFSET? `OFFSET 100000` makes Postgres read and throw away 100000
// rows every time (cost grows with page number), and rows inserted/deleted
// between requests make pages skip or repeat items. Keyset cost is constant and
// the sequence is stable.
func (s *UserStore) List(ctx context.Context, afterID, limit int) ([]*User, bool, error) {
	// Ask for ONE extra row: if it comes back, a next page exists. This avoids
	// a separate (and expensive) COUNT(*) query.
	rows, err := s.pool.Query(ctx,
		"SELECT "+userCols+" FROM users WHERE id > $1 ORDER BY id LIMIT $2", afterID, limit+1)
	if err != nil {
		return nil, false, fmt.Errorf("query users: %w", err)
	}
	defer rows.Close()

	users := make([]*User, 0, limit+1) // non-nil so JSON encodes [] rather than null
	for rows.Next() {
		u, err := scanUser(rows)
		if err != nil {
			return nil, false, fmt.Errorf("scan user: %w", err)
		}
		users = append(users, u)
	}
	// rows.Err reports errors that ended iteration early (e.g. cancelled
	// context). Skipping it can silently return a truncated list.
	if err := rows.Err(); err != nil {
		return nil, false, fmt.Errorf("iterate users: %w", err)
	}
	more := len(users) > limit
	if more {
		users = users[:limit]
	}
	return users, more, nil
}
```

Study this function:

1. `WHERE id > $1 ORDER BY id LIMIT $2` is the keyset query. It needs the sort column to be **unique and indexed**, and `id` is the primary key, so both hold. (Sorting by a non-unique column like `created_at` needs a tie-breaker: `ORDER BY created_at, id` with `WHERE (created_at, id) > ($1, $2)`.)
2. **We ask for** `limit+1` **rows.** If the extra row arrives, there's a next page, and we drop it. This avoids a separate `SELECT COUNT(*)`, which is slow on big tables, since it must scan them. We deliberately return **no total count**. If a UI really needs one, an approximate count is usually enough.
3. `rows.Err()` after the loop, as always.

## Step 2: the HTTP side

`internal/handlers/base.go (excerpt)`

```go
// Page is the envelope for every list endpoint. Wrapping the array (instead of
// returning a bare []) lets us add fields like next_cursor now and others later
// without breaking clients.
type Page[T any] struct {
	Data       []T    `json:"data"`
	NextCursor string `json:"next_cursor,omitempty"` // absent on the last page
}

// The cursor is OPAQUE to clients: they just echo it back. Today it encodes the
// last id, but because clients never parse it we can later change it (add a sort
// key, sign it) without an API break. It is not secret or tamper-proof; a
// forged cursor only lets a caller choose where in THEIR OWN visible data to
// start, so that is harmless.
func encodeCursor(id int) string {
	return base64.RawURLEncoding.EncodeToString([]byte(strconv.Itoa(id)))
}

func decodeCursor(c string) (int, error) {
	raw, err := base64.RawURLEncoding.DecodeString(c)
	if err != nil {
		return 0, err
	}
	id, err := strconv.Atoi(string(raw))
	if err != nil || id < 0 {
		return 0, errors.New("bad cursor")
	}
	return id, nil
}

// pageParams parses ?limit= and ?cursor=. Bad input is a 400; an over-large
// limit is clamped (a documented, harmless adjustment) rather than rejected.
func (b base) pageParams(w http.ResponseWriter, r *http.Request) (afterID, limit int, ok bool) {
	limit = b.limits.DefaultPageSize
	if v := r.URL.Query().Get("limit"); v != "" {
		n, err := strconv.Atoi(v)
		if err != nil || n < 1 {
			b.writeError(w, r, http.StatusBadRequest, "limit must be a positive integer")
			return 0, 0, false
		}
		limit = min(n, b.limits.MaxPageSize)
	}
	if c := r.URL.Query().Get("cursor"); c != "" {
		var err error
		if afterID, err = decodeCursor(c); err != nil {
			b.writeError(w, r, http.StatusBadRequest, "invalid cursor")
			return 0, 0, false
		}
	}
	return afterID, limit, true
}

// writePage builds the envelope. lastID extracts the id of an item so the
// cursor can point just after the final one.
func writePage[T any](b base, w http.ResponseWriter, r *http.Request, items []T, more bool, lastID func(T) int) {
	p := Page[T]{Data: items}
	if more && len(items) > 0 {
		p.NextCursor = encodeCursor(lastID(items[len(items)-1]))
	}
	b.writeJSON(w, r, http.StatusOK, p)
}
```

- `Page[T any]` is a **generic** envelope, so users and orders share one implementation.
- `base64.RawURLEncoding` produces a URL-safe string (no `+`, `/` or `=` padding to escape in a query string).
- The cursor isn't signed. That's fine: forging one only lets a caller choose where in *their own visible* data to start. (If the cursor ever encoded something sensitive, you'd sign or encrypt it.)

The user handler becomes tiny:

`internal/handlers/users.go (excerpt)`

```go
func (h *UserHandler) List(w http.ResponseWriter, r *http.Request) {
	after, limit, ok := h.pageParams(w, r)
	if !ok {
		return
	}
	users, more, err := h.store.List(r.Context(), after, limit)
	if err != nil {
		h.internalError(w, r, "list users", err)
		return
	}
	writePage(h.base, w, r, users, more, func(u *storage.User) int { return u.ID })
}
```

## Step 3: the index that makes it fast

For "one user's orders" the query is:

```sql
WHERE user_id = $1 AND id > $2 ORDER BY id LIMIT $3
```

This needs a **composite index** on `(user_id, id)`. Postgres can seek to the first entry for that user after the cursor, read 20 entries in order and stop, with no sorting. A migration replaces the old single-column index:

**Create** `internal/storage/migrations/00003_orders_keyset_index.sql`

```sql
-- +goose Up
-- Keyset pagination for "one user's orders" is:
--     WHERE user_id = $1 AND id > $2 ORDER BY id LIMIT n
-- A composite index on (user_id, id) serves the filter AND the ordering, so
-- Postgres walks the index and stops after n rows, never sorting. It also
-- makes the old single-column index redundant (a composite index can answer
-- any query on its leading column), so we replace it.
-- On a large live table use CREATE INDEX CONCURRENTLY (needs NO TRANSACTION).
CREATE INDEX idx_orders_user_id_id ON orders (user_id, id);
DROP INDEX idx_orders_user_id;

-- +goose Down
CREATE INDEX idx_orders_user_id ON orders (user_id);
DROP INDEX idx_orders_user_id_id;
```

Again on 500,000 rows (1,000 users, 500 orders each):

```plaintext
with (user_id, id) index:   Index Scan using ...user_id_id   Execution Time: 0.044 ms
with only (user_id) index:  Index Scan using orders_pkey      Execution Time: 0.811 ms
                            + Filter: user_id = 7  (walks the pkey, discarding other users' rows)
```

Roughly **18x** faster with the right index, and the first number stays flat as the table grows while the second does not. Notice the composite index also makes the old `user_id`\-only index redundant: an index on `(user_id, id)` can answer any query on `user_id` alone, and every extra index slows down writes. So the migration drops it.

> Production note: on a big live table, create indexes with `CREATE INDEX CONCURRENTLY` (it doesn't block writes), which must run outside a transaction (`-- +goose NO TRANSACTION`).

One more trap we avoided. The store has *two* queries for orders, one with a user filter and one without, instead of a single clever query with `($1 = 0 OR user_id = $1)`. That "optional filter" pattern looks tidy but forces PostgreSQL into a generic plan that can't use the composite index well. Simple, separate queries each get the best plan.

`internal/storage/orders.go (excerpt)`

```go
// List returns up to limit orders with id > afterID (keyset pagination, see
// UserStore.List), optionally only one user's (userID > 0), plus a "more" flag.
//
// Two separate queries instead of one with `($1 = 0 OR user_id = $1)`: that OR
// form forces Postgres to use a generic plan that cannot use the
// (user_id, id) index well. Distinct, simple queries each get the right plan.
func (s *OrderStore) List(ctx context.Context, userID, afterID, limit int) ([]*Order, bool, error) {
	var (
		rows pgx.Rows
		err  error
	)
	if userID > 0 {
		rows, err = s.pool.Query(ctx,
			"SELECT "+orderCols+" FROM orders WHERE user_id = $1 AND id > $2 ORDER BY id LIMIT $3",
			userID, afterID, limit+1)
	} else {
		rows, err = s.pool.Query(ctx,
			"SELECT "+orderCols+" FROM orders WHERE id > $1 ORDER BY id LIMIT $2",
			afterID, limit+1)
	}
	if err != nil {
		return nil, false, fmt.Errorf("query orders: %w", err)
	}
	defer rows.Close()

	orders := make([]*Order, 0, limit+1)
	for rows.Next() {
		o, err := scanOrder(rows)
		if err != nil {
			return nil, false, fmt.Errorf("scan order: %w", err)
		}
		orders = append(orders, o)
	}
	if err := rows.Err(); err != nil {
		return nil, false, fmt.Errorf("iterate orders: %w", err)
	}
	more := len(orders) > limit
	if more {
		orders = orders[:limit]
	}
	return orders, more, nil
}
```

**Don't take our word for it; test the plan.** This test fails if a future change makes the planner stop using the index:

`internal/storage/storage_test.go (excerpt)`

```go
// The composite index must actually be USED for the per-user keyset query; a
// regression to a sequential scan would be silent otherwise. Seq scans are
// disabled so the planner picks an index whenever one is applicable.
func TestOrdersKeysetUsesIndex(t *testing.T) {
	ctx := context.Background()
	pool := testdb.New(t)
	conn, err := pool.Acquire(ctx)
	if err != nil {
		t.Fatal(err)
	}
	defer conn.Release()
	if _, err := conn.Exec(ctx, "SET enable_seqscan = off"); err != nil {
		t.Fatal(err)
	}
	var plan string
	rows, _ := conn.Query(ctx, "EXPLAIN SELECT id FROM orders WHERE user_id = 1 AND id > 0 ORDER BY id LIMIT 10")
	for rows.Next() {
		var line string
		_ = rows.Scan(&line)
		plan += line + "\n"
	}
	rows.Close()
	if !strings.Contains(plan, "idx_orders_user_id_id") {
		t.Fatalf("composite index not used:\n%s", plan)
	}
}
```

## Step 4: three cheap wins

These are small but valuable on any API.

**1\. Compress responses (gzip).** JSON compresses 5-10x. We use a well-tested library rather than hand-rolling `compress/gzip`, because the edge cases are nasty (skip tiny bodies, set `Vary: Accept-Encoding` so caches stay correct, fix `Content-Length`, keep `Flusher` working).

`internal/middleware/middleware.go (excerpt)`

```go
// Gzip compresses responses when the client sends Accept-Encoding: gzip. JSON
// compresses 5-10x, which matters most for list endpoints. The library (rather
// than hand-rolled compress/gzip) handles the sharp edges: skips tiny bodies,
// sets Vary: Accept-Encoding, fixes Content-Length, keeps http.Flusher working.
var Gzip = func(next http.Handler) http.Handler { return gzhttp.GzipHandler(next) }

// SecurityHeaders sets conservative defaults for a JSON API.
//   - nosniff: browsers must trust our Content-Type, not guess.
//   - no-store: responses may hold per-user data; shared caches/proxies must
//     not keep them. (Public, cacheable data would use a different policy.)
func SecurityHeaders(next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		h := w.Header()
		h.Set("X-Content-Type-Options", "nosniff")
		h.Set("Cache-Control", "no-store")
		next.ServeHTTP(w, r)
	})
}
```

**2\. Security headers.** `X-Content-Type-Options: nosniff` stops browsers from guessing content types; `Cache-Control: no-store` prevents shared caches from keeping responses that may be per-user.

**3\. A database** `statement_timeout`**.** Our HTTP request timeout (Part 4) cancels the *request*. But what if a query goes wrong (a missing index, an accidental cross join) and the client is gone? PostgreSQL itself can abort any single statement that runs too long:

`internal/storage/storage.go (excerpt)`

```go
// PoolConfig holds connection-pool tuning. MaxConns caps connections per instance, so replicas x MaxConns must stay below the server's limit.
type PoolConfig struct {
	URL             string
	MaxConns        int32
	MinConns        int32
	MaxConnLifetime time.Duration
	MaxConnIdleTime time.Duration
	// StatementTimeout makes Postgres itself abort any single query that runs
	// longer. It is a safety net below the HTTP request timeout: a runaway
	// query (missing index, accidental cross join) can't hold a connection and
	// CPU forever, even if the client is gone.
	StatementTimeout time.Duration
}
```

`DB_STATEMENT_TIMEOUT` (default 8 s) is a safety net *below* the HTTP timeout: a runaway query can't hold a connection and CPU forever.

## Complete code for this part

Everything to type, file by file. This part touches many files a little (the list endpoints, config, wiring), so most appear as small diffs. Then `go mod tidy && go build ./...`.

*Already given earlier in this post:* `internal/storage/migrations/00003_orders_keyset_index.sql`*.*

**Change** `internal/config/config.go`

```diff
--- a/internal/config/config.go
+++ b/internal/config/config.go
@@ -15,11 +15,12 @@ type Config struct {
 	Addr      string // public API listener
 	AdminAddr string // metrics + health; keep OFF the public internet
 
-	DatabaseURL   string
-	DBMaxConns    int32
-	DBMinConns    int32
-	DBMaxConnLife time.Duration
-	DBMaxConnIdle time.Duration
+	DatabaseURL        string
+	DBMaxConns         int32
+	DBMinConns         int32
+	DBMaxConnLife      time.Duration
+	DBMaxConnIdle      time.Duration
+	DBStatementTimeout time.Duration
 
 	// http.Server timeouts: enforced by net/http, below the handler layer.
 	ReadHeaderTimeout time.Duration
@@ -35,8 +36,10 @@ type Config struct {
 	// WriteTimeout so we can still send a reply.
 	RequestTimeout time.Duration
 
-	MaxBodyBytes int64
-	MaxNameLen   int
+	MaxBodyBytes    int64
+	MaxNameLen      int
+	DefaultPageSize int
+	MaxPageSize     int
 
 	ServiceName      string
 	LogLevel         slog.Level
@@ -46,24 +49,27 @@ type Config struct {
 
 func Load() (Config, error) {
 	c := Config{
-		Addr:              env("ADDR", ":7800"),
-		AdminAddr:         env("ADMIN_ADDR", ":9090"),
-		DatabaseURL:       os.Getenv("DATABASE_URL"),
-		DBMaxConns:        10,
-		DBMinConns:        2,
-		DBMaxConnLife:     30 * time.Minute,
-		DBMaxConnIdle:     5 * time.Minute,
-		ReadHeaderTimeout: 5 * time.Second,
-		ReadTimeout:       10 * time.Second,
-		WriteTimeout:      15 * time.Second,
-		IdleTimeout:       60 * time.Second,
-		ShutdownTimeout:   15 * time.Second,
-		RequestTimeout:    10 * time.Second,
-		MaxBodyBytes:      1 << 20,
-		MaxNameLen:        100,
-		ServiceName:       env("SERVICE_NAME", "rest-go"),
-		OTLPEndpoint:      os.Getenv("OTLP_ENDPOINT"),
-		TraceSampleRatio:  1.0,
+		Addr:               env("ADDR", ":7800"),
+		AdminAddr:          env("ADMIN_ADDR", ":9090"),
+		DatabaseURL:        os.Getenv("DATABASE_URL"),
+		DBMaxConns:         10,
+		DBMinConns:         2,
+		DBMaxConnLife:      30 * time.Minute,
+		DBMaxConnIdle:      5 * time.Minute,
+		DBStatementTimeout: 8 * time.Second,
+		ReadHeaderTimeout:  5 * time.Second,
+		ReadTimeout:        10 * time.Second,
+		WriteTimeout:       15 * time.Second,
+		IdleTimeout:        60 * time.Second,
+		ShutdownTimeout:    15 * time.Second,
+		RequestTimeout:     10 * time.Second,
+		MaxBodyBytes:       1 << 20,
+		MaxNameLen:         100,
+		DefaultPageSize:    20,
+		MaxPageSize:        100,
+		ServiceName:        env("SERVICE_NAME", "rest-go"),
+		OTLPEndpoint:       os.Getenv("OTLP_ENDPOINT"),
+		TraceSampleRatio:   1.0,
 	}
 
 	var err error
@@ -73,6 +79,7 @@ func Load() (Config, error) {
 	}{
 		{"DB_MAX_CONN_LIFETIME", &c.DBMaxConnLife},
 		{"DB_MAX_CONN_IDLE_TIME", &c.DBMaxConnIdle},
+		{"DB_STATEMENT_TIMEOUT", &c.DBStatementTimeout},
 		{"READ_HEADER_TIMEOUT", &c.ReadHeaderTimeout},
 		{"READ_TIMEOUT", &c.ReadTimeout},
 		{"WRITE_TIMEOUT", &c.WriteTimeout},
@@ -110,6 +117,16 @@ func Load() (Config, error) {
 			return c, fmt.Errorf("MAX_NAME_LEN: %w", err)
 		}
 	}
+	for _, i := range []struct {
+		key string
+		dst *int
+	}{{"DEFAULT_PAGE_SIZE", &c.DefaultPageSize}, {"MAX_PAGE_SIZE", &c.MaxPageSize}} {
+		if v := os.Getenv(i.key); v != "" {
+			if *i.dst, err = strconv.Atoi(v); err != nil {
+				return c, fmt.Errorf("%s: %w", i.key, err)
+			}
+		}
+	}
 	if v := os.Getenv("TRACE_SAMPLE_RATIO"); v != "" {
 		if c.TraceSampleRatio, err = strconv.ParseFloat(v, 64); err != nil {
 			return c, fmt.Errorf("TRACE_SAMPLE_RATIO: %w", err)
@@ -129,6 +146,8 @@ func Load() (Config, error) {
 		return c, fmt.Errorf("limits and request timeout must be positive")
 	case c.RequestTimeout >= c.WriteTimeout:
 		return c, fmt.Errorf("REQUEST_TIMEOUT (%s) must be below WRITE_TIMEOUT (%s)", c.RequestTimeout, c.WriteTimeout)
+	case c.DefaultPageSize < 1 || c.MaxPageSize < c.DefaultPageSize:
+		return c, fmt.Errorf("need 1 <= DEFAULT_PAGE_SIZE <= MAX_PAGE_SIZE")
 	case c.DBMaxConns < 1 || c.DBMinConns < 0 || c.DBMinConns > c.DBMaxConns:
 		return c, fmt.Errorf("need 0 <= DB_MIN_CONNS <= DB_MAX_CONNS and DB_MAX_CONNS >= 1")
 	case c.TraceSampleRatio < 0 || c.TraceSampleRatio > 1:
```

**Change** `internal/handlers/base.go`

```diff
--- a/internal/handlers/base.go
+++ b/internal/handlers/base.go
@@ -2,6 +2,7 @@ package handlers
 
 import (
 	"context"
+	"encoding/base64"
 	"encoding/json"
 	"errors"
 	"io"
@@ -19,6 +20,9 @@ type Limits struct {
 	// gigabytes at json.Decoder and exhaust memory.
 	MaxBodyBytes int64
 	MaxNameLen   int
+
+	DefaultPageSize int // used when the client sends no ?limit
+	MaxPageSize     int // larger requested limits are clamped to this
 }
 
 // base holds what EVERY resource handler needs: logger, limits, and the
@@ -104,3 +108,66 @@ func (b base) writeJSON(w http.ResponseWriter, r *http.Request, status int, v an
 		b.log.ErrorContext(r.Context(), "encode response", "path", r.URL.Path, "err", err)
 	}
 }
+
+// ---- pagination -------------------------------------------------------------
+
+// Page is the envelope for every list endpoint. Wrapping the array (instead of
+// returning a bare []) lets us add fields like next_cursor now and others later
+// without breaking clients.
+type Page[T any] struct {
+	Data       []T    `json:"data"`
+	NextCursor string `json:"next_cursor,omitempty"` // absent on the last page
+}
+
+// The cursor is OPAQUE to clients: they just echo it back. Today it encodes the
+// last id, but because clients never parse it we can later change it (add a sort
+// key, sign it) without an API break. It is not secret or tamper-proof; a
+// forged cursor only lets a caller choose where in THEIR OWN visible data to
+// start, so that is harmless.
+func encodeCursor(id int) string {
+	return base64.RawURLEncoding.EncodeToString([]byte(strconv.Itoa(id)))
+}
+
+func decodeCursor(c string) (int, error) {
+	raw, err := base64.RawURLEncoding.DecodeString(c)
+	if err != nil {
+		return 0, err
+	}
+	id, err := strconv.Atoi(string(raw))
+	if err != nil || id < 0 {
+		return 0, errors.New("bad cursor")
+	}
+	return id, nil
+}
+
+// pageParams parses ?limit= and ?cursor=. Bad input is a 400; an over-large
+// limit is clamped (a documented, harmless adjustment) rather than rejected.
+func (b base) pageParams(w http.ResponseWriter, r *http.Request) (afterID, limit int, ok bool) {
+	limit = b.limits.DefaultPageSize
+	if v := r.URL.Query().Get("limit"); v != "" {
+		n, err := strconv.Atoi(v)
+		if err != nil || n < 1 {
+			b.writeError(w, r, http.StatusBadRequest, "limit must be a positive integer")
+			return 0, 0, false
+		}
+		limit = min(n, b.limits.MaxPageSize)
+	}
+	if c := r.URL.Query().Get("cursor"); c != "" {
+		var err error
+		if afterID, err = decodeCursor(c); err != nil {
+			b.writeError(w, r, http.StatusBadRequest, "invalid cursor")
+			return 0, 0, false
+		}
+	}
+	return afterID, limit, true
+}
+
+// writePage builds the envelope. lastID extracts the id of an item so the
+// cursor can point just after the final one.
+func writePage[T any](b base, w http.ResponseWriter, r *http.Request, items []T, more bool, lastID func(T) int) {
+	p := Page[T]{Data: items}
+	if more && len(items) > 0 {
+		p.NextCursor = encodeCursor(lastID(items[len(items)-1]))
+	}
+	b.writeJSON(w, r, http.StatusOK, p)
+}
```

**Change** `internal/handlers/orders.go`

```diff
--- a/internal/handlers/orders.go
+++ b/internal/handlers/orders.go
@@ -16,7 +16,7 @@ import (
 const maxQuantity = 10_000
 
 type OrderStore interface {
-	List(ctx context.Context, userID int) ([]*storage.Order, error) // userID 0 = all
+	List(ctx context.Context, userID, afterID, limit int) ([]*storage.Order, bool, error) // userID 0 = all
 	Get(ctx context.Context, id int) (*storage.Order, error)
 	Create(ctx context.Context, userID int, item string, quantity int) (*storage.Order, error)
 	Delete(ctx context.Context, id int) error
@@ -37,7 +37,8 @@ type orderInput struct {
 	Quantity int    `json:"quantity"`
 }
 
-// List supports an optional filter: GET /orders?user_id=3
+// List supports an optional filter and pagination:
+// GET /orders?user_id=3&limit=20&cursor=...
 func (h *OrderHandler) List(w http.ResponseWriter, r *http.Request) {
 	userID := 0
 	if v := r.URL.Query().Get("user_id"); v != "" {
@@ -47,12 +48,16 @@ func (h *OrderHandler) List(w http.ResponseWriter, r *http.Request) {
 			return
 		}
 	}
-	orders, err := h.store.List(r.Context(), userID)
+	after, limit, ok := h.pageParams(w, r)
+	if !ok {
+		return
+	}
+	orders, more, err := h.store.List(r.Context(), userID, after, limit)
 	if err != nil {
 		h.internalError(w, r, "list orders", err)
 		return
 	}
-	h.writeJSON(w, r, http.StatusOK, orders)
+	writePage(h.base, w, r, orders, more, func(o *storage.Order) int { return o.ID })
 }
 
 func (h *OrderHandler) Get(w http.ResponseWriter, r *http.Request) {
```

**Change** `internal/handlers/users.go`

```diff
--- a/internal/handlers/users.go
+++ b/internal/handlers/users.go
@@ -15,7 +15,7 @@ import (
 // used* ("accept interfaces, return structs"), so handlers can be tested with
 // a fake store. *storage.UserStore satisfies it implicitly.
 type UserStore interface {
-	GetAll(ctx context.Context) ([]*storage.User, error)
+	List(ctx context.Context, afterID, limit int) ([]*storage.User, bool, error)
 	Get(ctx context.Context, id int) (*storage.User, error)
 	Create(ctx context.Context, name string) (*storage.User, error)
 	Update(ctx context.Context, id int, name string) (*storage.User, error)
@@ -39,12 +39,16 @@ type userInput struct {
 }
 
 func (h *UserHandler) List(w http.ResponseWriter, r *http.Request) {
-	users, err := h.store.GetAll(r.Context())
+	after, limit, ok := h.pageParams(w, r)
+	if !ok {
+		return
+	}
+	users, more, err := h.store.List(r.Context(), after, limit)
 	if err != nil {
 		h.internalError(w, r, "list users", err)
 		return
 	}
-	h.writeJSON(w, r, http.StatusOK, users)
+	writePage(h.base, w, r, users, more, func(u *storage.User) int { return u.ID })
 }
 
 func (h *UserHandler) Get(w http.ResponseWriter, r *http.Request) {
```

**Change** `internal/middleware/middleware.go`

```diff
--- a/internal/middleware/middleware.go
+++ b/internal/middleware/middleware.go
@@ -11,6 +11,7 @@ import (
 	"net/http"
 	"time"
 
+	"github.com/klauspost/compress/gzhttp"
 	"go.opentelemetry.io/otel/attribute"
 	"go.opentelemetry.io/otel/trace"
 
@@ -35,6 +36,25 @@ func RequestID(next http.Handler) http.Handler {
 	})
 }
 
+// Gzip compresses responses when the client sends Accept-Encoding: gzip. JSON
+// compresses 5-10x, which matters most for list endpoints. The library (rather
+// than hand-rolled compress/gzip) handles the sharp edges: skips tiny bodies,
+// sets Vary: Accept-Encoding, fixes Content-Length, keeps http.Flusher working.
+var Gzip = func(next http.Handler) http.Handler { return gzhttp.GzipHandler(next) }
+
+// SecurityHeaders sets conservative defaults for a JSON API.
+//   - nosniff: browsers must trust our Content-Type, not guess.
+//   - no-store: responses may hold per-user data; shared caches/proxies must
+//     not keep them. (Public, cacheable data would use a different policy.)
+func SecurityHeaders(next http.Handler) http.Handler {
+	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+		h := w.Header()
+		h.Set("X-Content-Type-Options", "nosniff")
+		h.Set("Cache-Control", "no-store")
+		next.ServeHTTP(w, r)
+	})
+}
+
 // Timeout puts a deadline on each request's context. Handlers pass r.Context()
 // to the store, so a slow query is cancelled when the deadline hits instead of
 // running on after the client has given up.
```

**Change** `internal/storage/orders.go`

```diff
--- a/internal/storage/orders.go
+++ b/internal/storage/orders.go
@@ -40,30 +40,47 @@ func scanOrder(row pgx.Row) (*Order, error) {
 	return o, nil
 }
 
-// List returns all orders, or only one user's when userID > 0.
-func (s *OrderStore) List(ctx context.Context, userID int) ([]*Order, error) {
-	// One static query for both cases. Values are always bound parameters,
-	// never concatenated into SQL (no injection). The ::bigint cast tells
-	// Postgres the type of $1, which it uses in two different places.
-	rows, err := s.pool.Query(ctx,
-		"SELECT "+orderCols+" FROM orders WHERE $1::bigint = 0 OR user_id = $1 ORDER BY id", userID)
+// List returns up to limit orders with id > afterID (keyset pagination, see
+// UserStore.List), optionally only one user's (userID > 0), plus a "more" flag.
+//
+// Two separate queries instead of one with `($1 = 0 OR user_id = $1)`: that OR
+// form forces Postgres to use a generic plan that cannot use the
+// (user_id, id) index well. Distinct, simple queries each get the right plan.
+func (s *OrderStore) List(ctx context.Context, userID, afterID, limit int) ([]*Order, bool, error) {
+	var (
+		rows pgx.Rows
+		err  error
+	)
+	if userID > 0 {
+		rows, err = s.pool.Query(ctx,
+			"SELECT "+orderCols+" FROM orders WHERE user_id = $1 AND id > $2 ORDER BY id LIMIT $3",
+			userID, afterID, limit+1)
+	} else {
+		rows, err = s.pool.Query(ctx,
+			"SELECT "+orderCols+" FROM orders WHERE id > $1 ORDER BY id LIMIT $2",
+			afterID, limit+1)
+	}
 	if err != nil {
-		return nil, fmt.Errorf("query orders: %w", err)
+		return nil, false, fmt.Errorf("query orders: %w", err)
 	}
 	defer rows.Close()
 
-	orders := make([]*Order, 0)
+	orders := make([]*Order, 0, limit+1)
 	for rows.Next() {
 		o, err := scanOrder(rows)
 		if err != nil {
-			return nil, fmt.Errorf("scan order: %w", err)
+			return nil, false, fmt.Errorf("scan order: %w", err)
 		}
 		orders = append(orders, o)
 	}
 	if err := rows.Err(); err != nil {
-		return nil, fmt.Errorf("iterate orders: %w", err)
+		return nil, false, fmt.Errorf("iterate orders: %w", err)
+	}
+	more := len(orders) > limit
+	if more {
+		orders = orders[:limit]
 	}
-	return orders, nil
+	return orders, more, nil
 }
 
 func (s *OrderStore) Get(ctx context.Context, id int) (*Order, error) {
```

**Change** `internal/storage/storage.go`

```diff
--- a/internal/storage/storage.go
+++ b/internal/storage/storage.go
@@ -6,6 +6,7 @@ import (
 	"errors"
 	"fmt"
 	"io/fs"
+	"strconv"
 	"time"
 
 	"github.com/exaring/otelpgx"
@@ -34,6 +35,11 @@ type PoolConfig struct {
 	MinConns        int32
 	MaxConnLifetime time.Duration
 	MaxConnIdleTime time.Duration
+	// StatementTimeout makes Postgres itself abort any single query that runs
+	// longer. It is a safety net below the HTTP request timeout: a runaway
+	// query (missing index, accidental cross join) can't hold a connection and
+	// CPU forever, even if the client is gone.
+	StatementTimeout time.Duration
 }
 
 // NewPool creates a pgx connection pool. Unlike database/sql, pgxpool speaks
@@ -51,6 +57,9 @@ func NewPool(ctx context.Context, c PoolConfig, opts ...func(*pgxpool.Config)) (
 	cfg.MaxConnLifetime = c.MaxConnLifetime // recycle conns so DNS/failover changes are picked up
 	cfg.MaxConnIdleTime = c.MaxConnIdleTime
 	cfg.HealthCheckPeriod = 30 * time.Second
+	if c.StatementTimeout > 0 {
+		cfg.ConnConfig.RuntimeParams["statement_timeout"] = strconv.FormatInt(c.StatementTimeout.Milliseconds(), 10)
+	}
 
 	// otelpgx emits a trace span for every query, nested under the HTTP
 	// request's span because we always pass the request context.
```

**Change** `internal/storage/users.go`

```diff
--- a/internal/storage/users.go
+++ b/internal/storage/users.go
@@ -38,27 +38,42 @@ func scanUser(row pgx.Row) (*User, error) {
 // disconnects or the request deadline passes, the in-flight query is
 // cancelled server-side and the connection returns to the pool.
 
-func (s *UserStore) GetAll(ctx context.Context) ([]*User, error) {
-	rows, err := s.pool.Query(ctx, "SELECT "+userCols+" FROM users ORDER BY id")
+// List returns up to limit users with id > afterID, ordered by id, plus whether
+// more exist. This is KEYSET ("seek") pagination: the client sends the last id
+// it saw and we continue after it using the primary-key index.
+//
+// Why not OFFSET? `OFFSET 100000` makes Postgres read and throw away 100000
+// rows every time (cost grows with page number), and rows inserted/deleted
+// between requests make pages skip or repeat items. Keyset cost is constant and
+// the sequence is stable.
+func (s *UserStore) List(ctx context.Context, afterID, limit int) ([]*User, bool, error) {
+	// Ask for ONE extra row: if it comes back, a next page exists. This avoids
+	// a separate (and expensive) COUNT(*) query.
+	rows, err := s.pool.Query(ctx,
+		"SELECT "+userCols+" FROM users WHERE id > $1 ORDER BY id LIMIT $2", afterID, limit+1)
 	if err != nil {
-		return nil, fmt.Errorf("query users: %w", err)
+		return nil, false, fmt.Errorf("query users: %w", err)
 	}
 	defer rows.Close()
 
-	users := make([]*User, 0) // non-nil so JSON encodes [] rather than null
+	users := make([]*User, 0, limit+1) // non-nil so JSON encodes [] rather than null
 	for rows.Next() {
 		u, err := scanUser(rows)
 		if err != nil {
-			return nil, fmt.Errorf("scan user: %w", err)
+			return nil, false, fmt.Errorf("scan user: %w", err)
 		}
 		users = append(users, u)
 	}
 	// rows.Err reports errors that ended iteration early (e.g. cancelled
 	// context). Skipping it can silently return a truncated list.
 	if err := rows.Err(); err != nil {
-		return nil, fmt.Errorf("iterate users: %w", err)
+		return nil, false, fmt.Errorf("iterate users: %w", err)
 	}
-	return users, nil
+	more := len(users) > limit
+	if more {
+		users = users[:limit]
+	}
+	return users, more, nil
 }
 
 func (s *UserStore) Get(ctx context.Context, id int) (*User, error) {
```

**Change** `cmd/rest/main.go`

```diff
--- a/cmd/rest/main.go
+++ b/cmd/rest/main.go
@@ -98,11 +98,12 @@ func healthcheck() error {
 
 func poolConfig(cfg config.Config) storage.PoolConfig {
 	return storage.PoolConfig{
-		URL:             cfg.DatabaseURL,
-		MaxConns:        cfg.DBMaxConns,
-		MinConns:        cfg.DBMinConns,
-		MaxConnLifetime: cfg.DBMaxConnLife,
-		MaxConnIdleTime: cfg.DBMaxConnIdle,
+		URL:              cfg.DatabaseURL,
+		MaxConns:         cfg.DBMaxConns,
+		MinConns:         cfg.DBMinConns,
+		MaxConnLifetime:  cfg.DBMaxConnLife,
+		MaxConnIdleTime:  cfg.DBMaxConnIdle,
+		StatementTimeout: cfg.DBStatementTimeout,
 	}
 }
 
@@ -133,7 +134,12 @@ func serve() error {
 	metrics := observability.NewMetrics(pool)
 	health := observability.NewHealth(pool)
 
-	limits := handlers.Limits{MaxBodyBytes: cfg.MaxBodyBytes, MaxNameLen: cfg.MaxNameLen}
+	limits := handlers.Limits{
+		MaxBodyBytes:    cfg.MaxBodyBytes,
+		MaxNameLen:      cfg.MaxNameLen,
+		DefaultPageSize: cfg.DefaultPageSize,
+		MaxPageSize:     cfg.MaxPageSize,
+	}
 	users := handlers.NewUserHandler(storage.NewUserStore(pool), log, limits)
 	orders := handlers.NewOrderHandler(storage.NewOrderStore(pool), log, limits)
 	mux := http.NewServeMux()
@@ -152,6 +158,8 @@ func serve() error {
 		Handler: middleware.Chain(mux,
 			func(next http.Handler) http.Handler { return otelhttp.NewHandler(next, "http.server") },
 			middleware.RequestID,
+			middleware.Gzip,
+			middleware.SecurityHeaders,
 			middleware.Timeout(cfg.RequestTimeout),
 			middleware.TraceRoute,
 			middleware.Logging(log),
```

## Try it

Create some users and page through them:

```bash
$ curl -s 'http://localhost:7800/users?limit=2'
{"data":[{"id":1,"name":"user1", ...},{"id":2,"name":"user2", ...}],"next_cursor":"Mg"}

$ curl -s 'http://localhost:7800/users?limit=2&cursor=Mg'      # the next_cursor we just got
{"data":[{"id":3,...},{"id":4,...}],"next_cursor":"NA"}
```

(`Mg` and `NA` are just base64 for `2` and `4`. At this point in the series the server speaks plain HTTP; TLS arrives in Part 14.) Check compression and headers:

```bash
$ curl -s -H 'Accept-Encoding: gzip' -D- -o /dev/null 'http://localhost:7800/users?limit=100'
Cache-Control: no-store
Content-Encoding: gzip
Vary: Accept-Encoding
```

And bad input:

```bash
$ curl -i 'localhost:7800/users?limit=abc'
HTTP/1.1 400 Bad Request
{"error":"limit must be a positive integer"}
```

## Recap

- Every list endpoint is paginated. Always.
- Keyset beats OFFSET for APIs: constant cost, stable under writes. You trade away "jump to page N".
- Fetch `limit+1` instead of counting.
- Design the index for the exact query (`(user_id, id)`), then **test the plan**.
- Envelope responses and opaque cursors keep the contract evolvable.
- gzip, safe headers and `statement_timeout` are cheap protection.

## Exercises

1. Add sorting by `created_at` (newest first) with a `(created_at, id)` cursor. What must the cursor contain now, and which index do you need?
2. Reproduce the benchmark: `INSERT ... SELECT ... FROM generate_series(1, 500000)` into a scratch table, then compare OFFSET and keyset with `EXPLAIN (ANALYZE)`.
3. Make `limit` above the max return `400` instead of clamping. Which behavior is friendlier to API clients, and why might you still choose to clamp?