Database Management SystemsDISTINCT and LIMIT

DISTINCT and LIMIT: Controlling Result Sets

LevelBeginner

Duration60 mins

TopicDISTINCT and LIMIT

5 / 5

Pagination: Complete Implementation Guide

Building Paginated Data Interfaces

Pagination is one of the most common features in database-backed applications. From e-commerce product listings to social media feeds, admin dashboards to API responses—nearly every application that displays lists of data requires pagination.

This page synthesizes everything we've learned about DISTINCT, LIMIT, and OFFSET into practical pagination implementation. We'll explore the mathematics of pagination, compare implementation approaches, address real-world challenges, and provide production-ready patterns you can apply immediately.

By the end of this page, you'll understand not just how to paginate, but how to paginate well—with appropriate performance, usability, and correctness for your specific use case.

What You Will Learn

By the end of this page, you will understand: (1) Pagination mathematics and formulas, (2) OFFSET-based pagination implementation, (3) Keyset/cursor pagination implementation, (4) Choosing between pagination approaches, (5) Handling edge cases and concurrency, (6) UI/UX patterns for pagination, (7) API design for paginated endpoints, and (8) Performance optimization strategies.

Pagination Mathematics

Understanding the fundamental calculations behind pagination ensures correct implementation and helps you reason about edge cases.

1.1 Core Pagination Variables

Total Count (N): Total number of items in the result set
Page Size (S): Number of items per page (often called 'limit' or 'per_page')
Current Page (P): The page number being requested (typically 1-indexed)
Total Pages (T): Number of pages needed to display all items
Offset (O): Number of items to skip before the current page

pagination_formulas.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- Core Pagination Formulas
-- ============================
 
-- Total Pages (ceiling division)
-- T = CEIL(N / S)
-- Example: 95 items, 20 per page → CEIL(95/20) = CEIL(4.75) = 5 pages
 
-- Offset (0-indexed positioning)
-- O = (P - 1) × S
-- Example: Page 3, 20 per page → (3-1) × 20 = 40
 
-- SQL Implementation:
SELECT * FROM products
ORDER BY product_id
LIMIT 20              -- S: page size
OFFSET 40;            -- O: offset for page 3
 
-- Page Range Calculation
-- First item on page: (P - 1) × S + 1
-- Last item on page: MIN(P × S, N)
-- Example: Page 3 of 95 items, 20 per page
-- First: (3-1)*20+1 = 41
-- Last: MIN(3*20, 95) = MIN(60, 95) = 60
-- Display: "Showing 41-60 of 95"
 
-- Check if page is valid
-- Valid if: 1 ≤ P ≤ T
-- Or equivalently: (P-1) × S < N
 
-- Example in SQL (PostgreSQL):
WITH counts AS (
    SELECT COUNT(*) as total_count FROM products
)
SELECT 
    total_count,
    CEIL(total_count::numeric / 20) as total_pages,
    (3 - 1) * 20 as offset_for_page_3
FROM counts;

Pagination Calculations Example (95 items, 20 per page)
Page	Offset	Items	Range	Status
1	0	20	1-20	Full page
2	20	20	21-40	Full page
3	40	20	41-60	Full page
4	60	20	61-80	Full page
5	80	15	81-95	Partial (last page)
6	100	0		Invalid (beyond data)

1.2 Zero-Indexed vs. One-Indexed Pages

Applications vary in whether page numbering starts at 0 or 1. Be consistent and document your choice.

page_indexing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- One-indexed pages (human-friendly, common in UIs)
-- Page 1 is the first page
-- Offset = (page - 1) × page_size
-- Page 1: offset = 0
-- Page 2: offset = 20
 
-- Zero-indexed pages (common in APIs, programming)
-- Page 0 is the first page
-- Offset = page × page_size
-- Page 0: offset = 0
-- Page 1: offset = 20
 
-- Conversion formulas:
-- Zero to One: one_indexed = zero_indexed + 1
-- One to Zero: zero_indexed = one_indexed - 1
 
-- API might accept both:
-- ?page=3              -- One-indexed (UI-oriented)
-- ?offset=40           -- Direct offset (programmer-oriented)

API Design Recommendation

For public APIs, one-indexed pages are more intuitive ('page 1' = first page). For internal APIs or when developers are the primary users, zero-indexed or direct offset parameters work well. Document your choice clearly in API documentation.

OFFSET-Based Pagination Implementation

OFFSET-based pagination is the traditional approach: calculate the offset from page number and page size, then use OFFSET/LIMIT to fetch the appropriate rows.

offset_pagination_impl.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Basic OFFSET Pagination Pattern
-- ================================
 
-- Given: page_number (1-indexed), page_size
-- Calculate: offset = (page_number - 1) × page_size
 
-- Page 1
SELECT * FROM products ORDER BY product_id LIMIT 20 OFFSET 0;
 
-- Page 2
SELECT * FROM products ORDER BY product_id LIMIT 20 OFFSET 20;
 
-- Page 3
SELECT * FROM products ORDER BY product_id LIMIT 20 OFFSET 40;
 
-- Generic pattern (parameterized):
-- Python example:
-- offset = (page_number - 1) * page_size
-- query = f"SELECT * FROM products ORDER BY product_id LIMIT {page_size} OFFSET {offset}"
 
-- With total count (for page navigation)
-- Query 1: Get total count
SELECT COUNT(*) as total FROM products WHERE category = 'Electronics';
 
-- Query 2: Get page data
SELECT * FROM products 
WHERE category = 'Electronics'
ORDER BY product_id
LIMIT 20 OFFSET 40;
 
-- Combined in one query (PostgreSQL):
SELECT 
    *,
    COUNT(*) OVER() as total_count
FROM products
WHERE category = 'Electronics'
ORDER BY product_id
LIMIT 20 OFFSET 40;
-- Each row includes total_count as a column

2.1 Optimizing Total Count

count_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Problem: COUNT(*) scans all matching rows
SELECT COUNT(*) FROM products WHERE category = 'Electronics';
-- May scan millions of rows for count
 
-- Solution 1: Estimate count (PostgreSQL)
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'products';
-- Uses table statistics, instant but approximate
 
-- Solution 2: Cached counts
-- Store counts in a separate table, update via triggers
-- trade freshness for speed
 
-- Solution 3: Limited counting
SELECT COUNT(*) FROM (
    SELECT 1 FROM products 
    WHERE category = 'Electronics'
    LIMIT 10001
) t;
-- If returns 10001, display "10,000+ results"
-- Caps work at 10,000 rows
 
-- Solution 4: Skip total count
-- Don't show "Page 5 of 123" - just show "Next" button
-- Determine if "Next" exists by fetching page_size + 1 rows
 
SELECT * FROM products
ORDER BY product_id
LIMIT 21  -- Request one extra
OFFSET 40;
 
-- Application logic:
-- if (rows_returned == 21):
--     has_next_page = True
--     return first 20 rows only
-- else:
--     has_next_page = False
--     return all rows returned

COUNT(*) OVER() Performance

Using COUNT(*) OVER() to include total count in each row is convenient but still requires processing the entire result set. It's cleaner syntax but not faster than separate queries. For large datasets, consider approximate counts or skipping total count entirely.

2.2 OFFSET Pagination Limitations Recap

•Performance degrades with page depth — Page 1000 reads 20,000 rows to return 20
•Inconsistent under concurrent modification — Inserts/deletes shift row positions between requests
•Can't efficiently jump to arbitrary page — Every page requires reading from row 1
•Requires stable ORDER BY — Must include unique column to prevent row position drift

Keyset/Cursor Pagination Implementation

Keyset pagination (also called cursor pagination or seek method) fetches rows relative to a specific row's values rather than by position. This provides O(1) performance regardless of how 'deep' into the dataset you go.

3.1 Basic Keyset Implementation

The core idea: instead of 'skip N rows,' specify 'get rows after this key value.'

keyset_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- First Page (no cursor yet)
SELECT product_id, product_name, price
FROM products
ORDER BY product_id ASC
LIMIT 20;
 
-- Returns rows with product_id: 1, 2, 3, ... 20
-- Last row has product_id = 20
-- Save this as the cursor: cursor = 20
 
-- Next Page (using cursor)
SELECT product_id, product_name, price
FROM products
WHERE product_id > 20  -- After the cursor
ORDER BY product_id ASC
LIMIT 20;
 
-- Returns rows with product_id: 21, 22, 23, ... 40
-- New cursor = 40
 
-- Continue pattern for subsequent pages
WHERE product_id > 40  -- Page 3
WHERE product_id > 60  -- Page 4
-- etc.
 
-- Why this is fast:
-- With index on product_id, the database:
-- 1. Seeks directly to product_id = 20 in the index
-- 2. Scans forward 20 entries
-- 3. Done! No rows are read and discarded.

3.2 Keyset with Descending Order (Reverse Chronological)

keyset_descending.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Common use case: Recent items first (newest to oldest)
 
-- First page (most recent)
SELECT order_id, order_date, total
FROM orders
ORDER BY order_id DESC
LIMIT 20;
 
-- Returns order_id: 1000, 999, 998, ... 981
-- Cursor = 981 (last row's ID)
 
-- Next page
SELECT order_id, order_date, total
FROM orders
WHERE order_id < 981  -- LESS than cursor (going backwards)
ORDER BY order_id DESC
LIMIT 20;
 
-- Returns order_id: 980, 979, 978, ... 961
-- New cursor = 961
 
-- Note: For DESC order, use < comparison
-- For ASC order, use > comparison

3.3 Keyset with Composite Keys (Multi-Column Ordering)

keyset_composite.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Challenge: ORDER BY created_at DESC, product_id DESC
-- Multiple products may have the same created_at
 
-- First page
SELECT product_id, product_name, created_at
FROM products
ORDER BY created_at DESC, product_id DESC
LIMIT 20;
 
-- Last row: created_at='2024-01-15 10:30:00', product_id=5000
-- Cursor = (created_at='2024-01-15 10:30:00', product_id=5000)
 
-- Next page: Use tuple comparison
SELECT product_id, product_name, created_at
FROM products
WHERE (created_at, product_id) < ('2024-01-15 10:30:00', 5000)
ORDER BY created_at DESC, product_id DESC
LIMIT 20;
 
-- Tuple comparison semantics:
-- (a, b) < (c, d) means:
--   a < c, OR
--   (a = c AND b < d)
 
-- Alternative without tuple comparison (any SQL):
WHERE created_at < '2024-01-15 10:30:00'
   OR (created_at = '2024-01-15 10:30:00' AND product_id < 5000)
 
-- Important: Index must match ORDER BY for efficiency
CREATE INDEX idx_products_keyset 
ON products(created_at DESC, product_id DESC);

3.4 Encoding Cursors for APIs

cursor_encoding.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Cursor needs to capture enough information to resume pagination
-- Common approach: Base64-encode the key values
 
-- Example cursor data:
{
    "created_at": "2024-01-15T10:30:00Z",
    "product_id": 5000
}
 
-- Base64 encoded:
-- ewogICJjcmVhdGVkX2F0IjogIjIwMjQtMDEtMTVUMTA6MzA6MDBaIiwKICAicHJvZHVjdF9pZCI6IDUwMDAKfQ==
 
-- API request:
-- GET /products?cursor=ewogICJjcmVhdGVkX2F0Ijo...
 
-- API response:
{
    "data": [...],
    "next_cursor": "ewogICJjcmVhdGVkX2F0Ijo...",  -- For next page
    "has_more": true
}
 
-- Opaque cursors: Don't expose internal structure
-- Allows changing pagination logic without breaking clients
-- Cursor format: base64(encrypt(json(key_values)))

Cursor Design Best Practices

Make cursors opaque (encoded/encrypted) so clients can't construct or modify them. Include version info in cursor format for backward compatibility. Consider adding timestamps to expire old cursors gracefully.

Bi-Directional Navigation

Keyset pagination naturally supports 'Next' navigation. Supporting 'Previous' requires slightly different queries.

bidirectional_keyset.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Current state: Viewing products 21-40
-- Cursor for "after": product_id = 40 (end of current page)
-- Cursor for "before": product_id = 21 (start of current page)
 
-- Next Page (products after 40)
SELECT product_id, product_name
FROM products
WHERE product_id > 40
ORDER BY product_id ASC
LIMIT 20;
 
-- Previous Page (products before 21)
-- Need to get 20 items BEFORE 21, but in correct display order
 
-- Step 1: Get previous 20 in reverse order
SELECT product_id, product_name
FROM products
WHERE product_id < 21
ORDER BY product_id DESC  -- Reverse order to get nearest 20
LIMIT 20;
-- Returns: 20, 19, 18, ... 1 (in that order)
 
-- Step 2: Reverse in application, or use subquery:
SELECT * FROM (
    SELECT product_id, product_name
    FROM products
    WHERE product_id < 21
    ORDER BY product_id DESC
    LIMIT 20
) reversed
ORDER BY product_id ASC;
-- Returns: 1, 2, 3, ... 20 (correct display order)
 
-- API provides both cursors:
{
    "data": [...],
    "cursors": {
        "before": "eyJwcm9kdWN0X2lkIjoyMX0=",  // start of page
        "after": "eyJwcm9kdWN0X2lkIjo0MH0="    // end of page
    },
    "has_previous": true,
    "has_next": true
}

Previous Page Edge Cases

When implementing 'Previous,' remember to handle the first page (no previous) and ensure you return rows in the correct display order after the reverse-order fetch. The subquery approach handles ordering correctly.

Choosing Between Pagination Approaches

Neither OFFSET nor keyset pagination is universally better—each has trade-offs that make it suitable for different scenarios.

OFFSET vs. Keyset Pagination Comparison
Factor	OFFSET Pagination	Keyset Pagination
Random page access	✓ Jump to any page	✗ Sequential only
Deep page performance	✗ Degrades linearly	✓ Constant O(1)
Total page count	✓ Easy to calculate	✗ Requires separate count
Implementation complexity	✓ Simple	✗ More complex
Concurrent modification	✗ May miss/duplicate rows	✓ Stable relative to cursor
Sort flexibility	✓ Any ORDER BY	△ Needs indexed columns
URL/API shareability	✓ page=5 is self-explanatory	✗ Cursor is opaque
Infinite scroll	✗ Poor performance at depth	✓ Designed for this

Use OFFSET When

•Result set is small (< 10,000 rows)
•Users need to jump to arbitrary pages ('Go to page 50')
•Displaying page numbers in UI ('1 2 3 ... 10')
•Admin/internal tools where performance is less critical
•Sort order changes frequently (user-selected sorts)
•Simplicity is prioritized over maximum performance

Use Keyset When

•Result set is large (> 100,000 rows)
•Implementing infinite scroll
•Real-time data that changes frequently
•Mobile apps requiring consistent performance
•Public APIs with quota/rate limits (efficient = fewer calls)
•Users primarily navigate sequentially (Next/Previous)
•Performance SLAs must be met regardless of page depth

hybrid_approach.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Hybrid Approach: OFFSET for early pages, keyset for deep pages
 
-- Application logic:
-- if page_number <= 50:
--     use OFFSET pagination (fast enough, supports page jumps)
-- else:
--     switch to keyset from page 50 onwards
--     or show "Load more" instead of page numbers
 
-- Alternative: Cap maximum OFFSET
-- if requested_offset > 1000:
--     return error("Please use search for results beyond page 50")
--     -- or
--     return keyset_cursor_for_page_50
 
-- API can support both:
-- GET /products?page=5          → OFFSET pagination
-- GET /products?cursor=abc123   → Keyset pagination

Handling Pagination Edge Cases

Production pagination must handle various edge cases gracefully. Here are common scenarios and solutions.

6.1 Empty Results

edge_empty.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- When no results match the query
SELECT * FROM products 
WHERE category = 'NonExistent'
ORDER BY product_id
LIMIT 20 OFFSET 0;
 
-- Returns 0 rows
-- Response should indicate:
{
    "data": [],
    "total_count": 0,
    "total_pages": 0,
    "current_page": 1,  -- or 0, based on your convention
    "message": "No products found matching your criteria"
}

6.2 Page Beyond Available Data

edge_beyond.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 95 products exist, user requests page 10 (page_size=20)
-- Page 10 = OFFSET 180, but only 95 products
 
SELECT * FROM products
ORDER BY product_id
LIMIT 20 OFFSET 180;
 
-- Returns 0 rows (offset beyond data)
 
-- Options:
-- 1. Return empty array with correct metadata
{
    "data": [],
    "current_page": 10,
    "total_pages": 5,
    "message": "Requested page exceeds available pages"
}
 
-- 2. Redirect to last valid page
{
    "data": [...last page data...],
    "current_page": 5,  // Silently adjusted
    "total_pages": 5,
    "redirected_from": 10
}
 
-- 3. Return 404 error for invalid pages
{
    "error": "Page 10 not found",
    "valid_range": [1, 5]
}

6.3 Invalid Page Size

edge_pagesize.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- User requests page_size=0 or page_size=10000000
 
-- Application should validate and constrain:
-- min_page_size = 1
-- max_page_size = 100  (or 1000, depending on data size)
-- default_page_size = 20
 
-- Pseudo-application code:
page_size = request.params['page_size']
page_size = max(min_page_size, min(page_size, max_page_size))
 
-- Response can indicate adjustment:
{
    "data": [...],
    "requested_page_size": 10000,
    "actual_page_size": 100,
    "message": "Page size capped at maximum allowed (100)"
}
 
-- For keyset APIs, also validate cursor format
-- Invalid cursor → return 400 Bad Request

6.4 Concurrent Data Changes

edge_concurrent.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Scenario: User on page 2, new product added with product_id=1
 
-- OFFSET Pagination Problem:
-- User loaded page 1: products 1-20
-- New product inserted (id=0.5, or all IDs shift)
-- User loads page 2: products 21-40
-- Product that WAS #20 is now #21 → appears on BOTH pages
 
-- Keyset Pagination Handling:
-- User loaded page 1: products 1-20
-- Cursor set: product_id > 20
-- New product inserted: id=25 (or 5, etc.)
-- User loads page 2: WHERE product_id > 20
-- Works correctly regardless of inserts!
 
-- If id=25 was inserted, it appears on page 2 (expected)
-- If id=5 was inserted, user already passed it (won't see it)
-- Keyset doesn't guarantee seeing EVERY row, but guarantees
-- no duplicates within a single pagination session
 
-- For strict consistency, consider:
-- 1. Snapshot/versioned data
-- 2. Created_at based cursors with timestamp from first page
-- 3. Accept eventual consistency for most use cases

Concurrent Modification Trade-offs

Perfect consistency during pagination requires either locking (impractical) or versioning (complex). Most applications accept that fast pagination may occasionally miss or duplicate rows during high-write periods. Document this behavior for users.

UI/UX Patterns for Pagination

Pagination isn't just a database concern—the user interface significantly impacts which pagination approach works best.

7.1 Traditional Page Numbers

Pattern: Display page numbers with Previous/Next buttons.

'Showing 21-40 of 95 results'
'← 1 2 [3] 4 5 →'

Best For: E-commerce catalogs, search results, admin tables Requires: Total count (for page numbers), random page access Pagination Type: OFFSET (or keyset with page calculation)

7.2 Infinite Scroll

Pattern: Automatically load more items as user scrolls down.

No visible pagination controls
Content streams continuously

Best For: Social media feeds, image galleries, content discovery Requires: Efficient deep pagination Pagination Type: Keyset (essential for performance)

7.3 Load More Button

Pattern: Explicit button to load next batch of results.

Shows current items
'Load More' button at bottom

Best For: Mobile interfaces, blogs, comment sections Requires: Has-more indicator Pagination Type: Either (keyset preferred for long lists)

7.4 Previous/Next Only

Pattern: Only Previous and Next buttons, no page numbers.

'Older Posts →' / '← Newer Posts'
No total count displayed

Best For: Blogs, news feeds, log viewers Requires: Just has-next/has-previous flags Pagination Type: Keyset (natural fit)

UI Pattern → Pagination Type Mapping
UI Pattern	Recommended Type	Count Needed?	Random Access?
Page numbers	OFFSET (or hybrid)	Yes	Yes
Infinite scroll	Keyset	No	No
Load more	Keyset	Optional	No
Prev/Next only	Keyset	No	No
Jump to page	OFFSET	Yes	Yes

API Design for Paginated Endpoints

Well-designed APIs make pagination easy for consumers while being efficient server-side.

8.1 OFFSET-Based API Design

api_offset.json
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// Request
GET /api/products?page=3&per_page=20&sort=price&order=desc
 
// Response
{
    "data": [
        {"id": 41, "name": "Product A", "price": 99.99},
        {"id": 42, "name": "Product B", "price": 89.99},
        // ... 20 items
    ],
    "pagination": {
        "current_page": 3,
        "per_page": 20,
        "total_items": 95,
        "total_pages": 5,
        "has_next": true,
        "has_previous": true
    },
    "links": {
        "first": "/api/products?page=1&per_page=20",
        "previous": "/api/products?page=2&per_page=20",
        "next": "/api/products?page=4&per_page=20",
        "last": "/api/products?page=5&per_page=20"
    }
}

8.2 Cursor-Based API Design

api_cursor.json
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Request (first page)
GET /api/feed
 
// Response
{
    "data": [
        {"id": 1000, "content": "...", "created_at": "2024-01-15T10:00:00Z"},
        {"id": 999, "content": "...", "created_at": "2024-01-15T09:55:00Z"},
        // ... 20 items
    ],
    "cursors": {
        "after": "eyJpZCI6OTgxLCJjcmVhdGVkX2F0IjoiMjAyNC0wMS0xNVQwOTozMDowMFoifQ=="
    },
    "has_more": true
}
 
// Request (next page)
GET /api/feed?after=eyJpZCI6OTgxLCJjcmVhdGVkX2F0IjoiMjAyNC0wMS0xNVQwOTozMDowMFoifQ==
 
// Response
{
    "data": [...next 20 items...],
    "cursors": {
        "before": "eyJpZCI6OTgxLCJjcmVhdGVkX2F0IjoiMjAyNC0wMS0xNVQwOTozMDowMFoifQ==",
        "after": "eyJpZCI6OTYxLCJjcmVhdGVkX2F0IjoiMjAyNC0wMS0xNVQwODo0NTowMFoifQ=="
    },
    "has_more": true
}

8.3 GraphQL Connections Pattern

GraphQL applications commonly use the Relay Connections specification:

graphql_pagination.graphql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# GraphQL schema following Relay Connections spec
type Query {
    products(first: Int, after: String, last: Int, before: String): ProductConnection!
}
 
type ProductConnection {
    edges: [ProductEdge!]!
    pageInfo: PageInfo!
    totalCount: Int  # Optional
}
 
type ProductEdge {
    node: Product!
    cursor: String!
}
 
type PageInfo {
    hasNextPage: Boolean!
    hasPreviousPage: Boolean!
    startCursor: String
    endCursor: String
}
 
# Query example
query {
    products(first: 20, after: "cursor123") {
        edges {
            node {
                id
                name
                price
            }
            cursor
        }
        pageInfo {
            hasNextPage
            endCursor
        }
    }
}

API Pagination Best Practices

Include navigation links in responses so clients don't construct URLs. Provide clear documentation of pagination approach. Return consistent response structure whether data exists or not. Consider rate limiting by pages to prevent abuse.

Performance Optimization Strategies

Beyond choosing the right pagination type, several optimization techniques improve pagination performance.

pagination_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Optimization 1: Covering Indexes
-- Include frequently-selected columns in index to avoid table lookups
CREATE INDEX idx_products_pagination 
ON products(created_at DESC, product_id DESC) 
INCLUDE (product_name, price);  -- PostgreSQL/SQL Server
 
SELECT product_id, product_name, price, created_at
FROM products
ORDER BY created_at DESC, product_id DESC
LIMIT 20;
-- All columns from index, no heap access needed
 
-- Optimization 2: Deferred Join (for complex queries)
SELECT p.* 
FROM products p
INNER JOIN (
    SELECT product_id 
    FROM products
    WHERE category = 'Electronics'
    ORDER BY created_at DESC
    LIMIT 20 OFFSET 1000
) keys ON p.product_id = keys.product_id
ORDER BY p.created_at DESC;
-- Inner query uses lean index scan
-- Outer query fetches full rows for only 20 products
 
-- Optimization 3: Avoid SELECT * in paginated queries
-- Select only needed columns
SELECT product_id, product_name, price  -- Not SELECT *
FROM products
ORDER BY product_id
LIMIT 20;
 
-- Optimization 4: Cached total counts
-- Store counts in a separate table, update via triggers or periodic jobs
CREATE TABLE table_counts (
    table_name TEXT PRIMARY KEY,
    row_count BIGINT,
    updated_at TIMESTAMP DEFAULT NOW()
);
 
-- Instead of: SELECT COUNT(*) FROM products WHERE active = true;
-- Query: SELECT row_count FROM table_counts WHERE table_name = 'products_active';
 
-- Optimization 5: Approximate counts for large tables (PostgreSQL)
SELECT reltuples::bigint FROM pg_class WHERE relname = 'products';

Performance Checklist

•Index exists on all ORDER BY columns
•Index order matches query ORDER BY (ASC/DESC alignment)
•WHERE clause predicates have supporting indexes
•SELECT list is minimal (no unused columns)
•Total count is cached or approximate for large datasets
•Maximum OFFSET is enforced to prevent deep pagination abuse
•EXPLAIN output shows index usage, not table scans

Summary: Mastering Pagination

Pagination is far more than LIMIT and OFFSET. Let's consolidate the comprehensive knowledge from this module:

Key Takeaways

•OFFSET pagination is simple but scales poorly — OFFSET reads and discards rows, making deep pages progressively slower.
•Keyset pagination is performant but less flexible — Uses indexed seeks for O(1) performance on any 'page' but doesn't support random page access.
•Choose based on use case — Small datasets or page-number UIs favor OFFSET; infinite scroll and large datasets favor keyset.
•Always use deterministic ORDER BY — Include unique column(s) to prevent rows from shifting between pages.
•Total counts are expensive — Cache them, estimate them, or skip them entirely (use 'has_more' instead).
•UI pattern dictates approach — Page numbers need OFFSET; infinite scroll needs keyset; hybrid is often the best solution.
•Design APIs for flexibility — Include navigation links, clear metadata, and consider supporting both page numbers and cursors.
•Index appropriately — Covering indexes on ORDER BY columns with INCLUDEd select columns provide optimal pagination performance.

Module Complete:

You've now completed the comprehensive study of DISTINCT and LIMIT—from eliminating duplicates, through result set limiting, to production-ready pagination implementation. These skills are fundamental to virtually every database-backed application you'll build or maintain.

Module Complete

Congratulations! You've mastered DISTINCT and LIMIT—essential SQL skills for controlling result sets. You understand: (1) Why duplicates occur and how to eliminate them, (2) DISTINCT syntax and semantics, (3) LIMIT/TOP/FETCH across databases, (4) OFFSET mechanics and limitations, and (5) Production-ready pagination patterns. You're equipped to build efficient, scalable paginated interfaces.

5 / 5

Loading learning content...

Database Management SystemsDISTINCT and LIMIT

DISTINCT and LIMIT: Controlling Result Sets

LevelBeginner

Duration60 mins

TopicDISTINCT and LIMIT

5 / 5

Pagination: Complete Implementation Guide

Building Paginated Data Interfaces

By the end of this page, you'll understand not just how to paginate, but how to paginate well—with appropriate performance, usability, and correctness for your specific use case.

What You Will Learn

Pagination Mathematics

Understanding the fundamental calculations behind pagination ensures correct implementation and helps you reason about edge cases.

1.1 Core Pagination Variables

Total Count (N): Total number of items in the result set
Page Size (S): Number of items per page (often called 'limit' or 'per_page')
Current Page (P): The page number being requested (typically 1-indexed)
Total Pages (T): Number of pages needed to display all items
Offset (O): Number of items to skip before the current page

pagination_formulas.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- Core Pagination Formulas
-- ============================
 
-- Total Pages (ceiling division)
-- T = CEIL(N / S)
-- Example: 95 items, 20 per page → CEIL(95/20) = CEIL(4.75) = 5 pages
 
-- Offset (0-indexed positioning)
-- O = (P - 1) × S
-- Example: Page 3, 20 per page → (3-1) × 20 = 40
 
-- SQL Implementation:
SELECT * FROM products
ORDER BY product_id
LIMIT 20              -- S: page size
OFFSET 40;            -- O: offset for page 3
 
-- Page Range Calculation
-- First item on page: (P - 1) × S + 1
-- Last item on page: MIN(P × S, N)
-- Example: Page 3 of 95 items, 20 per page
-- First: (3-1)*20+1 = 41
-- Last: MIN(3*20, 95) = MIN(60, 95) = 60
-- Display: "Showing 41-60 of 95"
 
-- Check if page is valid
-- Valid if: 1 ≤ P ≤ T
-- Or equivalently: (P-1) × S < N
 
-- Example in SQL (PostgreSQL):
WITH counts AS (
    SELECT COUNT(*) as total_count FROM products
)
SELECT 
    total_count,
    CEIL(total_count::numeric / 20) as total_pages,
    (3 - 1) * 20 as offset_for_page_3
FROM counts;

Pagination Calculations Example (95 items, 20 per page)
Page	Offset	Items	Range	Status
1	0	20	1-20	Full page
2	20	20	21-40	Full page
3	40	20	41-60	Full page
4	60	20	61-80	Full page
5	80	15	81-95	Partial (last page)
6	100	0		Invalid (beyond data)

1.2 Zero-Indexed vs. One-Indexed Pages

Applications vary in whether page numbering starts at 0 or 1. Be consistent and document your choice.

page_indexing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- One-indexed pages (human-friendly, common in UIs)
-- Page 1 is the first page
-- Offset = (page - 1) × page_size
-- Page 1: offset = 0
-- Page 2: offset = 20
 
-- Zero-indexed pages (common in APIs, programming)
-- Page 0 is the first page
-- Offset = page × page_size
-- Page 0: offset = 0
-- Page 1: offset = 20
 
-- Conversion formulas:
-- Zero to One: one_indexed = zero_indexed + 1
-- One to Zero: zero_indexed = one_indexed - 1
 
-- API might accept both:
-- ?page=3              -- One-indexed (UI-oriented)
-- ?offset=40           -- Direct offset (programmer-oriented)

API Design Recommendation

OFFSET-Based Pagination Implementation

OFFSET-based pagination is the traditional approach: calculate the offset from page number and page size, then use OFFSET/LIMIT to fetch the appropriate rows.

offset_pagination_impl.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Basic OFFSET Pagination Pattern
-- ================================
 
-- Given: page_number (1-indexed), page_size
-- Calculate: offset = (page_number - 1) × page_size
 
-- Page 1
SELECT * FROM products ORDER BY product_id LIMIT 20 OFFSET 0;
 
-- Page 2
SELECT * FROM products ORDER BY product_id LIMIT 20 OFFSET 20;
 
-- Page 3
SELECT * FROM products ORDER BY product_id LIMIT 20 OFFSET 40;
 
-- Generic pattern (parameterized):
-- Python example:
-- offset = (page_number - 1) * page_size
-- query = f"SELECT * FROM products ORDER BY product_id LIMIT {page_size} OFFSET {offset}"
 
-- With total count (for page navigation)
-- Query 1: Get total count
SELECT COUNT(*) as total FROM products WHERE category = 'Electronics';
 
-- Query 2: Get page data
SELECT * FROM products 
WHERE category = 'Electronics'
ORDER BY product_id
LIMIT 20 OFFSET 40;
 
-- Combined in one query (PostgreSQL):
SELECT 
    *,
    COUNT(*) OVER() as total_count
FROM products
WHERE category = 'Electronics'
ORDER BY product_id
LIMIT 20 OFFSET 40;
-- Each row includes total_count as a column

2.1 Optimizing Total Count

count_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Problem: COUNT(*) scans all matching rows
SELECT COUNT(*) FROM products WHERE category = 'Electronics';
-- May scan millions of rows for count
 
-- Solution 1: Estimate count (PostgreSQL)
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'products';
-- Uses table statistics, instant but approximate
 
-- Solution 2: Cached counts
-- Store counts in a separate table, update via triggers
-- trade freshness for speed
 
-- Solution 3: Limited counting
SELECT COUNT(*) FROM (
    SELECT 1 FROM products 
    WHERE category = 'Electronics'
    LIMIT 10001
) t;
-- If returns 10001, display "10,000+ results"
-- Caps work at 10,000 rows
 
-- Solution 4: Skip total count
-- Don't show "Page 5 of 123" - just show "Next" button
-- Determine if "Next" exists by fetching page_size + 1 rows
 
SELECT * FROM products
ORDER BY product_id
LIMIT 21  -- Request one extra
OFFSET 40;
 
-- Application logic:
-- if (rows_returned == 21):
--     has_next_page = True
--     return first 20 rows only
-- else:
--     has_next_page = False
--     return all rows returned

COUNT(*) OVER() Performance

2.2 OFFSET Pagination Limitations Recap

•Performance degrades with page depth — Page 1000 reads 20,000 rows to return 20
•Inconsistent under concurrent modification — Inserts/deletes shift row positions between requests
•Can't efficiently jump to arbitrary page — Every page requires reading from row 1
•Requires stable ORDER BY — Must include unique column to prevent row position drift

Keyset/Cursor Pagination Implementation

3.1 Basic Keyset Implementation

The core idea: instead of 'skip N rows,' specify 'get rows after this key value.'

keyset_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- First Page (no cursor yet)
SELECT product_id, product_name, price
FROM products
ORDER BY product_id ASC
LIMIT 20;
 
-- Returns rows with product_id: 1, 2, 3, ... 20
-- Last row has product_id = 20
-- Save this as the cursor: cursor = 20
 
-- Next Page (using cursor)
SELECT product_id, product_name, price
FROM products
WHERE product_id > 20  -- After the cursor
ORDER BY product_id ASC
LIMIT 20;
 
-- Returns rows with product_id: 21, 22, 23, ... 40
-- New cursor = 40
 
-- Continue pattern for subsequent pages
WHERE product_id > 40  -- Page 3
WHERE product_id > 60  -- Page 4
-- etc.
 
-- Why this is fast:
-- With index on product_id, the database:
-- 1. Seeks directly to product_id = 20 in the index
-- 2. Scans forward 20 entries
-- 3. Done! No rows are read and discarded.

3.2 Keyset with Descending Order (Reverse Chronological)

keyset_descending.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Common use case: Recent items first (newest to oldest)
 
-- First page (most recent)
SELECT order_id, order_date, total
FROM orders
ORDER BY order_id DESC
LIMIT 20;
 
-- Returns order_id: 1000, 999, 998, ... 981
-- Cursor = 981 (last row's ID)
 
-- Next page
SELECT order_id, order_date, total
FROM orders
WHERE order_id < 981  -- LESS than cursor (going backwards)
ORDER BY order_id DESC
LIMIT 20;
 
-- Returns order_id: 980, 979, 978, ... 961
-- New cursor = 961
 
-- Note: For DESC order, use < comparison
-- For ASC order, use > comparison

3.3 Keyset with Composite Keys (Multi-Column Ordering)

keyset_composite.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Challenge: ORDER BY created_at DESC, product_id DESC
-- Multiple products may have the same created_at
 
-- First page
SELECT product_id, product_name, created_at
FROM products
ORDER BY created_at DESC, product_id DESC
LIMIT 20;
 
-- Last row: created_at='2024-01-15 10:30:00', product_id=5000
-- Cursor = (created_at='2024-01-15 10:30:00', product_id=5000)
 
-- Next page: Use tuple comparison
SELECT product_id, product_name, created_at
FROM products
WHERE (created_at, product_id) < ('2024-01-15 10:30:00', 5000)
ORDER BY created_at DESC, product_id DESC
LIMIT 20;
 
-- Tuple comparison semantics:
-- (a, b) < (c, d) means:
--   a < c, OR
--   (a = c AND b < d)
 
-- Alternative without tuple comparison (any SQL):
WHERE created_at < '2024-01-15 10:30:00'
   OR (created_at = '2024-01-15 10:30:00' AND product_id < 5000)
 
-- Important: Index must match ORDER BY for efficiency
CREATE INDEX idx_products_keyset 
ON products(created_at DESC, product_id DESC);

3.4 Encoding Cursors for APIs

cursor_encoding.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Cursor needs to capture enough information to resume pagination
-- Common approach: Base64-encode the key values
 
-- Example cursor data:
{
    "created_at": "2024-01-15T10:30:00Z",
    "product_id": 5000
}
 
-- Base64 encoded:
-- ewogICJjcmVhdGVkX2F0IjogIjIwMjQtMDEtMTVUMTA6MzA6MDBaIiwKICAicHJvZHVjdF9pZCI6IDUwMDAKfQ==
 
-- API request:
-- GET /products?cursor=ewogICJjcmVhdGVkX2F0Ijo...
 
-- API response:
{
    "data": [...],
    "next_cursor": "ewogICJjcmVhdGVkX2F0Ijo...",  -- For next page
    "has_more": true
}
 
-- Opaque cursors: Don't expose internal structure
-- Allows changing pagination logic without breaking clients
-- Cursor format: base64(encrypt(json(key_values)))

Cursor Design Best Practices

Bi-Directional Navigation

Keyset pagination naturally supports 'Next' navigation. Supporting 'Previous' requires slightly different queries.

bidirectional_keyset.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Current state: Viewing products 21-40
-- Cursor for "after": product_id = 40 (end of current page)
-- Cursor for "before": product_id = 21 (start of current page)
 
-- Next Page (products after 40)
SELECT product_id, product_name
FROM products
WHERE product_id > 40
ORDER BY product_id ASC
LIMIT 20;
 
-- Previous Page (products before 21)
-- Need to get 20 items BEFORE 21, but in correct display order
 
-- Step 1: Get previous 20 in reverse order
SELECT product_id, product_name
FROM products
WHERE product_id < 21
ORDER BY product_id DESC  -- Reverse order to get nearest 20
LIMIT 20;
-- Returns: 20, 19, 18, ... 1 (in that order)
 
-- Step 2: Reverse in application, or use subquery:
SELECT * FROM (
    SELECT product_id, product_name
    FROM products
    WHERE product_id < 21
    ORDER BY product_id DESC
    LIMIT 20
) reversed
ORDER BY product_id ASC;
-- Returns: 1, 2, 3, ... 20 (correct display order)
 
-- API provides both cursors:
{
    "data": [...],
    "cursors": {
        "before": "eyJwcm9kdWN0X2lkIjoyMX0=",  // start of page
        "after": "eyJwcm9kdWN0X2lkIjo0MH0="    // end of page
    },
    "has_previous": true,
    "has_next": true
}

Previous Page Edge Cases

Choosing Between Pagination Approaches

Neither OFFSET nor keyset pagination is universally better—each has trade-offs that make it suitable for different scenarios.

OFFSET vs. Keyset Pagination Comparison
Factor	OFFSET Pagination	Keyset Pagination
Random page access	✓ Jump to any page	✗ Sequential only
Deep page performance	✗ Degrades linearly	✓ Constant O(1)
Total page count	✓ Easy to calculate	✗ Requires separate count
Implementation complexity	✓ Simple	✗ More complex
Concurrent modification	✗ May miss/duplicate rows	✓ Stable relative to cursor
Sort flexibility	✓ Any ORDER BY	△ Needs indexed columns
URL/API shareability	✓ page=5 is self-explanatory	✗ Cursor is opaque
Infinite scroll	✗ Poor performance at depth	✓ Designed for this

Use OFFSET When

•Result set is small (< 10,000 rows)
•Users need to jump to arbitrary pages ('Go to page 50')
•Displaying page numbers in UI ('1 2 3 ... 10')
•Admin/internal tools where performance is less critical
•Sort order changes frequently (user-selected sorts)
•Simplicity is prioritized over maximum performance

Use Keyset When

•Result set is large (> 100,000 rows)
•Implementing infinite scroll
•Real-time data that changes frequently
•Mobile apps requiring consistent performance
•Public APIs with quota/rate limits (efficient = fewer calls)
•Users primarily navigate sequentially (Next/Previous)
•Performance SLAs must be met regardless of page depth

hybrid_approach.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Hybrid Approach: OFFSET for early pages, keyset for deep pages
 
-- Application logic:
-- if page_number <= 50:
--     use OFFSET pagination (fast enough, supports page jumps)
-- else:
--     switch to keyset from page 50 onwards
--     or show "Load more" instead of page numbers
 
-- Alternative: Cap maximum OFFSET
-- if requested_offset > 1000:
--     return error("Please use search for results beyond page 50")
--     -- or
--     return keyset_cursor_for_page_50
 
-- API can support both:
-- GET /products?page=5          → OFFSET pagination
-- GET /products?cursor=abc123   → Keyset pagination

Handling Pagination Edge Cases

Production pagination must handle various edge cases gracefully. Here are common scenarios and solutions.

6.1 Empty Results

edge_empty.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- When no results match the query
SELECT * FROM products 
WHERE category = 'NonExistent'
ORDER BY product_id
LIMIT 20 OFFSET 0;
 
-- Returns 0 rows
-- Response should indicate:
{
    "data": [],
    "total_count": 0,
    "total_pages": 0,
    "current_page": 1,  -- or 0, based on your convention
    "message": "No products found matching your criteria"
}

6.2 Page Beyond Available Data

edge_beyond.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 95 products exist, user requests page 10 (page_size=20)
-- Page 10 = OFFSET 180, but only 95 products
 
SELECT * FROM products
ORDER BY product_id
LIMIT 20 OFFSET 180;
 
-- Returns 0 rows (offset beyond data)
 
-- Options:
-- 1. Return empty array with correct metadata
{
    "data": [],
    "current_page": 10,
    "total_pages": 5,
    "message": "Requested page exceeds available pages"
}
 
-- 2. Redirect to last valid page
{
    "data": [...last page data...],
    "current_page": 5,  // Silently adjusted
    "total_pages": 5,
    "redirected_from": 10
}
 
-- 3. Return 404 error for invalid pages
{
    "error": "Page 10 not found",
    "valid_range": [1, 5]
}

6.3 Invalid Page Size

edge_pagesize.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- User requests page_size=0 or page_size=10000000
 
-- Application should validate and constrain:
-- min_page_size = 1
-- max_page_size = 100  (or 1000, depending on data size)
-- default_page_size = 20
 
-- Pseudo-application code:
page_size = request.params['page_size']
page_size = max(min_page_size, min(page_size, max_page_size))
 
-- Response can indicate adjustment:
{
    "data": [...],
    "requested_page_size": 10000,
    "actual_page_size": 100,
    "message": "Page size capped at maximum allowed (100)"
}
 
-- For keyset APIs, also validate cursor format
-- Invalid cursor → return 400 Bad Request

6.4 Concurrent Data Changes

edge_concurrent.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Scenario: User on page 2, new product added with product_id=1
 
-- OFFSET Pagination Problem:
-- User loaded page 1: products 1-20
-- New product inserted (id=0.5, or all IDs shift)
-- User loads page 2: products 21-40
-- Product that WAS #20 is now #21 → appears on BOTH pages
 
-- Keyset Pagination Handling:
-- User loaded page 1: products 1-20
-- Cursor set: product_id > 20
-- New product inserted: id=25 (or 5, etc.)
-- User loads page 2: WHERE product_id > 20
-- Works correctly regardless of inserts!
 
-- If id=25 was inserted, it appears on page 2 (expected)
-- If id=5 was inserted, user already passed it (won't see it)
-- Keyset doesn't guarantee seeing EVERY row, but guarantees
-- no duplicates within a single pagination session
 
-- For strict consistency, consider:
-- 1. Snapshot/versioned data
-- 2. Created_at based cursors with timestamp from first page
-- 3. Accept eventual consistency for most use cases

Concurrent Modification Trade-offs

UI/UX Patterns for Pagination

Pagination isn't just a database concern—the user interface significantly impacts which pagination approach works best.

7.1 Traditional Page Numbers

Pattern: Display page numbers with Previous/Next buttons.

'Showing 21-40 of 95 results'
'← 1 2 [3] 4 5 →'

Best For: E-commerce catalogs, search results, admin tables Requires: Total count (for page numbers), random page access Pagination Type: OFFSET (or keyset with page calculation)

7.2 Infinite Scroll

Pattern: Automatically load more items as user scrolls down.

No visible pagination controls
Content streams continuously

Best For: Social media feeds, image galleries, content discovery Requires: Efficient deep pagination Pagination Type: Keyset (essential for performance)

7.3 Load More Button

Pattern: Explicit button to load next batch of results.

Shows current items
'Load More' button at bottom

Best For: Mobile interfaces, blogs, comment sections Requires: Has-more indicator Pagination Type: Either (keyset preferred for long lists)

7.4 Previous/Next Only

Pattern: Only Previous and Next buttons, no page numbers.

'Older Posts →' / '← Newer Posts'
No total count displayed

Best For: Blogs, news feeds, log viewers Requires: Just has-next/has-previous flags Pagination Type: Keyset (natural fit)

UI Pattern → Pagination Type Mapping
UI Pattern	Recommended Type	Count Needed?	Random Access?
Page numbers	OFFSET (or hybrid)	Yes	Yes
Infinite scroll	Keyset	No	No
Load more	Keyset	Optional	No
Prev/Next only	Keyset	No	No
Jump to page	OFFSET	Yes	Yes

API Design for Paginated Endpoints

Well-designed APIs make pagination easy for consumers while being efficient server-side.

8.1 OFFSET-Based API Design

api_offset.json
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// Request
GET /api/products?page=3&per_page=20&sort=price&order=desc
 
// Response
{
    "data": [
        {"id": 41, "name": "Product A", "price": 99.99},
        {"id": 42, "name": "Product B", "price": 89.99},
        // ... 20 items
    ],
    "pagination": {
        "current_page": 3,
        "per_page": 20,
        "total_items": 95,
        "total_pages": 5,
        "has_next": true,
        "has_previous": true
    },
    "links": {
        "first": "/api/products?page=1&per_page=20",
        "previous": "/api/products?page=2&per_page=20",
        "next": "/api/products?page=4&per_page=20",
        "last": "/api/products?page=5&per_page=20"
    }
}

8.2 Cursor-Based API Design

api_cursor.json
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Request (first page)
GET /api/feed
 
// Response
{
    "data": [
        {"id": 1000, "content": "...", "created_at": "2024-01-15T10:00:00Z"},
        {"id": 999, "content": "...", "created_at": "2024-01-15T09:55:00Z"},
        // ... 20 items
    ],
    "cursors": {
        "after": "eyJpZCI6OTgxLCJjcmVhdGVkX2F0IjoiMjAyNC0wMS0xNVQwOTozMDowMFoifQ=="
    },
    "has_more": true
}
 
// Request (next page)
GET /api/feed?after=eyJpZCI6OTgxLCJjcmVhdGVkX2F0IjoiMjAyNC0wMS0xNVQwOTozMDowMFoifQ==
 
// Response
{
    "data": [...next 20 items...],
    "cursors": {
        "before": "eyJpZCI6OTgxLCJjcmVhdGVkX2F0IjoiMjAyNC0wMS0xNVQwOTozMDowMFoifQ==",
        "after": "eyJpZCI6OTYxLCJjcmVhdGVkX2F0IjoiMjAyNC0wMS0xNVQwODo0NTowMFoifQ=="
    },
    "has_more": true
}

8.3 GraphQL Connections Pattern

GraphQL applications commonly use the Relay Connections specification:

graphql_pagination.graphql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# GraphQL schema following Relay Connections spec
type Query {
    products(first: Int, after: String, last: Int, before: String): ProductConnection!
}
 
type ProductConnection {
    edges: [ProductEdge!]!
    pageInfo: PageInfo!
    totalCount: Int  # Optional
}
 
type ProductEdge {
    node: Product!
    cursor: String!
}
 
type PageInfo {
    hasNextPage: Boolean!
    hasPreviousPage: Boolean!
    startCursor: String
    endCursor: String
}
 
# Query example
query {
    products(first: 20, after: "cursor123") {
        edges {
            node {
                id
                name
                price
            }
            cursor
        }
        pageInfo {
            hasNextPage
            endCursor
        }
    }
}

API Pagination Best Practices

Performance Optimization Strategies

Beyond choosing the right pagination type, several optimization techniques improve pagination performance.

pagination_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Optimization 1: Covering Indexes
-- Include frequently-selected columns in index to avoid table lookups
CREATE INDEX idx_products_pagination 
ON products(created_at DESC, product_id DESC) 
INCLUDE (product_name, price);  -- PostgreSQL/SQL Server
 
SELECT product_id, product_name, price, created_at
FROM products
ORDER BY created_at DESC, product_id DESC
LIMIT 20;
-- All columns from index, no heap access needed
 
-- Optimization 2: Deferred Join (for complex queries)
SELECT p.* 
FROM products p
INNER JOIN (
    SELECT product_id 
    FROM products
    WHERE category = 'Electronics'
    ORDER BY created_at DESC
    LIMIT 20 OFFSET 1000
) keys ON p.product_id = keys.product_id
ORDER BY p.created_at DESC;
-- Inner query uses lean index scan
-- Outer query fetches full rows for only 20 products
 
-- Optimization 3: Avoid SELECT * in paginated queries
-- Select only needed columns
SELECT product_id, product_name, price  -- Not SELECT *
FROM products
ORDER BY product_id
LIMIT 20;
 
-- Optimization 4: Cached total counts
-- Store counts in a separate table, update via triggers or periodic jobs
CREATE TABLE table_counts (
    table_name TEXT PRIMARY KEY,
    row_count BIGINT,
    updated_at TIMESTAMP DEFAULT NOW()
);
 
-- Instead of: SELECT COUNT(*) FROM products WHERE active = true;
-- Query: SELECT row_count FROM table_counts WHERE table_name = 'products_active';
 
-- Optimization 5: Approximate counts for large tables (PostgreSQL)
SELECT reltuples::bigint FROM pg_class WHERE relname = 'products';

Performance Checklist

•Index exists on all ORDER BY columns
•Index order matches query ORDER BY (ASC/DESC alignment)
•WHERE clause predicates have supporting indexes
•SELECT list is minimal (no unused columns)
•Total count is cached or approximate for large datasets
•Maximum OFFSET is enforced to prevent deep pagination abuse
•EXPLAIN output shows index usage, not table scans

Summary: Mastering Pagination

Pagination is far more than LIMIT and OFFSET. Let's consolidate the comprehensive knowledge from this module:

Key Takeaways

•OFFSET pagination is simple but scales poorly — OFFSET reads and discards rows, making deep pages progressively slower.
•Keyset pagination is performant but less flexible — Uses indexed seeks for O(1) performance on any 'page' but doesn't support random page access.
•Choose based on use case — Small datasets or page-number UIs favor OFFSET; infinite scroll and large datasets favor keyset.
•Always use deterministic ORDER BY — Include unique column(s) to prevent rows from shifting between pages.
•Total counts are expensive — Cache them, estimate them, or skip them entirely (use 'has_more' instead).
•UI pattern dictates approach — Page numbers need OFFSET; infinite scroll needs keyset; hybrid is often the best solution.
•Design APIs for flexibility — Include navigation links, clear metadata, and consider supporting both page numbers and cursors.
•Index appropriately — Covering indexes on ORDER BY columns with INCLUDEd select columns provide optimal pagination performance.

Module Complete:

Module Complete

5 / 5