Paginating Confluence Content Search with CQL: A Reliable Bulk‑Fetch Pattern

Problem: Fetching every page in a Confluence site without missing or duplicating data

When building a backup, migration, or analytics pipeline you often need to pull the complete list of pages from Confluence. The /rest/api/content/search endpoint accepts a CQL query and returns a paginated JSON response. If you naively request a fixed page size and increment a counter, you can hit rate limits, get duplicate entries when pages are modified during the run, or miss newly created pages in a Data Center cluster that exhibits eventual consistency.

Thesis: Use a stable sort, respect the API’s limit, and implement exponential back‑off to iterate safely until start + size >= totalSize

1. How the endpoint works

The search endpoint returns:

• results – array of content objects for the current page
• start – index of the first result in this response
• limit – maximum number of results requested
• size – actual number of results returned (may be less than limit on the final page)
• totalSize – total number of items matching the CQL query

Pagination is driven by the start parameter. The client should increase start by the returned size after each request and stop when start + size >= totalSize.

2. Worked example: Bash loop with exponential back‑off

Assumptions:

• Confluence Cloud base URL: https://your-instance.atlassian.net
• API token with Read permission stored in $CONFLUENCE_TOKEN
• Email of the account: $CONFLUENCE_EMAIL

The script below fetches all pages, respects a page size of 200 (a common safe maximum), and backs off when HTTP 429 is received.

#!/usr/bin/env bash
set -euo pipefail

BASE_URL="https://your-instance.atlassian.net/wiki"
EMAIL="$CONFLUENCE_EMAIL"
TOKEN="$CONFLUENCE_TOKEN"
PAGE_SIZE=200
START=0
TOTAL_SIZE=1   # initialise to enter loop

while (( START + PAGE_SIZE >= TOTAL_SIZE )); do
    RESPONSE=$(curl -sS -w "\nHTTP_STATUS:%{http_code}" \
        -u "$EMAIL:$TOKEN" \
        "$BASE_URL/rest/api/content/search?cql=type=page&limit=$PAGE_SIZE&start=$START")
    HTTP_STATUS=$(echo "$RESPONSE" | tail -n1 | cut -d: -f2)
    BODY=$(echo "$RESPONSE" | sed '$d')

    if [[ "$HTTP_STATUS" == "429" ]]; then
        RETRY_AFTER=$(echo "$BODY" | grep -oPi '(?<=Retry-After: )\d+' || echo "5")
        echo "Rate limited – sleeping $RETRY_AFTER seconds"
        sleep "$RETRY_AFTER"
        continue
    fi

    if [[ "$HTTP_STATUS" != "200" ]]; then
        echo "Unexpected status $HTTP_STATUS"
        echo "$BODY"
        exit 1
    fi

    SIZE=$(echo "$BODY" | jq '.size')
    START=$((START + SIZE))
    TOTAL_SIZE=$(echo "$BODY" | jq '.totalSize')
    echo "Fetched $SIZE items, total so far: $START / $TOTAL_SIZE"
    # Process results here – e.g., extract IDs:
    echo "$BODY" | jq -r '.results[].id' >> page_ids.txt

done

echo "Finished. Retrieved $(wc -l < page_ids.txt) page IDs."

Explanation of key parts:

• The loop condition uses the previous TOTAL_SIZE; on the first iteration we set it to 1 to guarantee entry.
• After each successful request we update START by the actual size returned, protecting against a final page smaller than PAGE_SIZE.
• On 429 we read the Retry-After header (or default to 5 s) and sleep, then retry the same START value.
• All page IDs are appended to page_ids.txt for downstream processing.

3. Trade‑offs and limitations

Sort stability. The example sorts implicitly by Confluence’s default relevance. If you add an explicit ORDER BY clause (e.g., cql=type=page ORDER BY lastModified DESC), you must keep that sort constant across all pages. Changing the sort field mid‑session can cause the same page to appear in two different slices or be skipped entirely, especially when concurrent edits modify the sort key.

Eventual consistency in Data Center. In a clustered Data Center deployment, a page created just before your request may not yet be visible in the search index. The loop will therefore report a totalSize that excludes the newest page, and you will not see it until the index propagates (typically seconds to a minute). To mitigate, you can either:

• Run the fetch during a maintenance window with low write traffic, or
• After the loop, issue a second pass with a narrow CQL query targeting a recent time window (e.g., cql=type=page and lastModified >= -15m) to catch any missed items.

Rate‑limit ceiling. While 200 is a common maximum, some instances enforce a lower limit (e.g., 50). If you receive HTTP 400 with a message about exceeding the limit, reduce PAGE_SIZE and retry. The script does not auto‑adjust; you would need to inspect the error body and adjust accordingly.

Actionable closing

1. Choose a page size that respects your instance’s limit (start with 200, lower if you see 400 errors).
2. Keep the CQL sort clause unchanged for the entire fetch.
3. Implement exponential back‑off or simple Retry-After handling for 429 responses.
4. Verify completion by comparing the accumulated count to the final totalSize value returned in the last response.
5. If you run against a Data Center cluster, consider a follow‑up query for recently modified content to cover eventual‑consistency gaps.

By following this pattern you can reliably retrieve large page lists from Confluence while staying within rate limits and minimizing the risk of duplicates or missing data.