pagination-and-filtering

Pagination & Filtering Patterns

Backend SQL Patterns

Cursor-Based Pagination (Feeds & Lists)

SocialApp uses cursor-based pagination for all user-facing lists (feed, likes, notifications, matches). Never use OFFSET-based pagination for these — it's slow on large tables and produces inconsistent results when rows are inserted/deleted between pages.

Composite cursor pattern — use (created_at, id) tuple for stable ordering:

func (r *LikeRepo) ListIncomingProfilesCursor(
    ctx context.Context,
    userID int64,
    limit int,
    cursorCreatedAt *time.Time,
    cursorID *int64,
) ([]IncomingLikeRecord, error) {
    hasCursor := cursorCreatedAt != nil && cursorID != nil

    query := `
SELECT l.id, l.from_user_id, p.display_name, l.created_at
FROM likes l
JOIN profiles p ON p.user_id = l.from_user_id
WHERE l.to_user_id = $1`

    var args []any
    args = append(args, userID)

    if hasCursor {
        // Composite cursor: skip rows at or before the cursor position
        query += `
    AND (l.created_at, l.id) < ($2, $3)`
        args = append(args, *cursorCreatedAt, *cursorID)
    }

    query += `
ORDER BY l.created_at DESC, l.id DESC
LIMIT $` + fmt.Sprintf("%d", len(args)+1)
    args = append(args, limit)

    rows, err := r.pool.Query(ctx, query, args...)
    // ...
}

Rules:

• Always use (timestamp, id) composite for cursor — timestamp alone is not unique
• Order and cursor comparison must match: ORDER BY created_at DESC, id DESC with (created_at, id) < ($2, $3)
• Cursor fields come from the last item of the previous page
• Parameter numbers are dynamic ($N) — build with fmt.Sprintf("%d", len(args)+1)
• Default limit: 20, max limit: 100 — enforce in repo:

  if limit <= 0 { limit = 20 }
  if limit > 100 { limit = 100 }
  

LIMIT + 1 Pattern for hasMore Detection

Request one extra row to know if more pages exist — avoids a separate COUNT query:

// Service layer
func (s *Service) ListLikes(ctx context.Context, userID int64, limit int, cursor *Cursor) (*Page, error) {
    // Fetch limit+1 rows
    items, err := s.repo.ListIncoming(ctx, userID, limit+1, cursor)
    if err != nil {
        return nil, fmt.Errorf("ListLikes: %w", err)
    }

    hasMore := len(items) > limit
    if hasMore {
        items = items[:limit] // trim the extra row
    }

    var nextCursor *Cursor
    if hasMore && len(items) > 0 {
        last := items[len(items)-1]
        nextCursor = &Cursor{CreatedAt: last.CreatedAt, ID: last.ID}
    }

    return &Page{Items: items, HasMore: hasMore, NextCursor: nextCursor}, nil
}