Total Count

title: "Total Count in Pagination — Providing Result Set Estimates" description: "Total count in pagination tells clients how many total results match their query, enabling accurate page navigation and progress indicators." date: 2026-06-28 lastmod: 2026-06-28 weight: 19 tags: [apis, pagination] }

Total count metadata tells clients the total number of matching results, enabling page navigation, progress bars, and accurate "Showing X of Y" displays.

What You'll Learn

• Including total count in paginated responses
• Performance implications of COUNT queries
• Estimating counts for large datasets

Why It Matters

Without a total count, clients can't show page navigation, progress indicators, or "Load more" buttons correctly.

Code Examples

# Total count with pagination
@app.route('/users')
def list_users():
    # Base query without pagination
    base_query = "FROM users WHERE active = 1"
    base_params = []

    # Get total count
    count = db.execute(f"SELECT COUNT(*) {base_query}", base_params)[0][0]

    # Get page data
    page = request.args.get('page', 1, type=int)
    limit = min(request.args.get('limit', 20, type=int), 100)
    offset = (page - 1) * limit

    users = db.execute(
        f"SELECT * {base_query} ORDER BY id LIMIT ? OFFSET ?",
        [*base_params, limit, offset]
    )

    return jsonify({
        "data": users,
        "pagination": {
            "total": count,
            "page": page,
            "per_page": limit,
            "total_pages": math.ceil(count / limit)
        }
    })

# Estimated count for large datasets
@app.route('/search')
def search():
    # Use EXPLAIN or table statistics for estimated count
    estimated = db.execute("""
        SELECT reltuples::bigint AS estimate
        FROM pg_class WHERE relname = 'users'
    """)[0][0]
    return jsonify({"total_estimated": estimated})

Common Mistakes

1. COUNT Without Filters

The count must use the same filters and joins as the data query.

2. COUNT on Large Tables Without Indexes

COUNT(*) on unindexed large tables takes seconds.

3. Returning Total for Every Request

For cursor pagination, omit total or provide it as a separate endpoint.

4. Integer Overflow for Large Counts

Use 64-bit integers or strings for counts exceeding 2 billion.

5. COUNT with DISTINCT or Complex Joins

Distinct counts are expensive. Use approximations or separate queries.

Practice Questions

1. Why must count queries use the same filters as data queries?
2. How does COUNT affect database performance?
3. When should you skip returning total count?
4. How do you estimate counts for very large tables?
5. What data type should total count use?

Answers:

1. Otherwise the count doesn't match the filtered results.
2. COUNT can be slow on unindexed or large tables.
3. For cursor-based pagination where total is expensive to calculate.
4. Use table statistics (reltuples in PostgreSQL) or sampling.
5. 64-bit integer in most databases; string for values > 2^31.

Challenge: Optimize a paginated endpoint where COUNT takes 5 seconds on a 10M-row table. Implement an estimated count solution with acceptable accuracy.

FAQ