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):

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

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

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):

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

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)

// 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)

// 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)

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:

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

-- +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):

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)

// 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)

// 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)

// 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)

// 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

--- 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

--- 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

--- 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

--- 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

--- 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

--- 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

--- 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

--- 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

--- 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:

$ 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:

$ 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:

$ 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?