Pagination: OFFSET and Cursor-Based

Every listing page needs pagination. This tutorial covers two approaches: the classic LIMIT/OFFSET pattern for most apps, and cursor-based pagination for lists that need to stay fast at any size.

The Basic Pattern: LIMIT/OFFSET

from flask import request, render_template
from utils.db import get_db

PER_PAGE = 20

@app.route("/posts")
def list_posts():
    page = request.args.get("page", 1, type=int)
    offset = (page - 1) * PER_PAGE

    with get_db() as conn:
        with conn.cursor() as cur:
            # Get one page of results
            cur.execute("""
                SELECT id, title, excerpt, published_at
                FROM posts
                ORDER BY published_at DESC
                LIMIT %s OFFSET %s
            """, (PER_PAGE, offset))
            posts = cur.fetchall()

            # Get total count for page links
            cur.execute("SELECT COUNT(*) FROM posts")
            total = cur.fetchone()["count"]

    total_pages = (total + PER_PAGE - 1) // PER_PAGE

    return render_template("posts.html",
        posts=posts,
        page=page,
        total_pages=total_pages
    )

Rendering Page Links in Jinja2

<nav class="pagination">
    {% if page > 1 %}
        <a href="?page={{ page - 1 }}">&larr; Previous</a>
    {% endif %}

    {% for p in range(1, total_pages + 1) %}
        {% if p == page %}
            <span class="current">{{ p }}</span>
        {% elif p == 1 or p == total_pages or (p >= page - 2 and p <= page + 2) %}
            <a href="?page={{ p }}">{{ p }}</a>
        {% elif p == page - 3 or p == page + 3 %}
            <span>...</span>
        {% endif %}
    {% endfor %}

    {% if page < total_pages %}
        <a href="?page={{ page + 1 }}">Next &rarr;</a>
    {% endif %}
</nav>

This renders compact pagination: first, last, and pages near the current one, with ellipsis gaps.

The Problem With OFFSET at Scale

OFFSET 10000 tells PostgreSQL to scan and discard 10,000 rows before returning your 20. With millions of rows, page 500 is noticeably slower than page 1.

For most apps with under 100,000 rows this is not a problem. If you have a high-traffic app with large datasets, use cursor-based pagination instead.

Cursor-Based Pagination

Instead of OFFSET, remember the last ID seen and query from there:

@app.route("/posts")
def list_posts():
    # after_id is the last ID from the previous page
    after_id = request.args.get("after", 0, type=int)

    with get_db() as conn:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT id, title, excerpt, published_at
                FROM posts
                WHERE id < %s OR %s = 0
                ORDER BY id DESC
                LIMIT %s
            """, (after_id if after_id else 2147483647, after_id, PER_PAGE + 1))
            rows = cur.fetchall()

    # If we got PER_PAGE + 1 rows, there is a next page
    has_next = len(rows) > PER_PAGE
    posts = rows[:PER_PAGE]
    next_cursor = posts[-1]["id"] if has_next else None

    return render_template("posts.html",
        posts=posts,
        next_cursor=next_cursor
    )

{% if next_cursor %}
    <a href="?after={{ next_cursor }}">Load more &rarr;</a>
{% endif %}

This is O(log n) no matter which page you are on, because WHERE id < X uses the primary key index directly.

Infinite Scroll With Vanilla JS

Replace the "Load more" link with automatic loading on scroll:

let loading = false;
let cursor = null; // set this from the initial server render

async function loadMore() {
    if (loading) return;
    loading = true;

    const url = cursor ? `/api/posts?after=${cursor}` : "/api/posts";
    const response = await fetch(url);
    const data = await response.json();

    const container = document.getElementById("posts-list");
    // Note: post data comes from your own database — trusted source.
    // Never interpolate unescaped user-generated content into insertAdjacentHTML.
    data.posts.forEach(post => {
        container.insertAdjacentHTML("beforeend", `
            <article>
                <h3><a href="/posts/${post.slug}">${post.title}</a></h3>
                <p>${post.excerpt}</p>
            </article>
        `);
    });

    cursor = data.next_cursor;
    loading = false;
}

// Trigger when user scrolls near the bottom
window.addEventListener("scroll", () => {
    if (window.innerHeight + window.scrollY >= document.body.offsetHeight - 200) {
        if (cursor) loadMore();
    }
});

The corresponding API route returns JSON with next_cursor:

@app.route("/api/posts")
def api_posts():
    after_id = request.args.get("after", 0, type=int)
    # ... same query as above ...
    return jsonify({"posts": posts, "next_cursor": next_cursor})

When to Use Which

AI Prompt That Generated This

"Create a Flask route for a paginated list of posts using LIMIT/OFFSET and a Jinja2 template that renders page links. Also create an alternative using cursor-based pagination returning JSON, with a next_cursor field. Use psycopg2, no ORM."

Next Steps

• Combine with Full-Text Search to paginate search results