Pagination patterns in Supabase - Deep Dive

Overview - Pagination patterns

What is it?

Pagination patterns are ways to split large sets of data into smaller, manageable pieces called pages. This helps users and systems handle data without overload. In Supabase, pagination controls how many records you get at once and which part of the data you see. It makes data browsing faster and smoother.

Why it matters

Without pagination, loading all data at once can slow down apps and waste resources, making users wait or even crash the system. Pagination solves this by loading only a small chunk of data at a time, improving speed and user experience. It also helps servers handle many users efficiently.

Where it fits

Before learning pagination, you should understand basic database queries and how data is stored. After mastering pagination, you can explore advanced data fetching techniques like infinite scrolling or cursor-based navigation for better performance.

Mental Model

Core Idea

Pagination breaks big data into small pages so you only load and see a little at a time, making data handling faster and easier.

Think of it like...

Imagine a book with many pages. Instead of reading the whole book at once, you read one page or chapter at a time. Pagination is like flipping to the next page when you’re ready, so you don’t get overwhelmed.

┌───────────────┐
│   Full Data   │
└──────┬────────┘
       │ Split into pages
       ▼
┌──────┬───────┬───────┐
│Page1│ Page2 │ Page3 │ ...
└──────┴───────┴───────┘
       │
       ▼
  Load one page at a time

Build-Up - 8 Steps

1

Foundation

What is Pagination and Why Use It

Concept: Introducing the basic idea of pagination and its purpose.

Pagination means dividing a large list of data into smaller parts called pages. For example, if you have 1000 users, you might show only 10 users per page. This helps apps load data faster and keeps the screen clean.

Result

You understand that pagination helps manage big data by showing it in small chunks.

Knowing why pagination exists helps you appreciate its role in making apps faster and easier to use.

2

Foundation

Basic Pagination with Limit and Offset

Concept: Learn how to get a specific page of data using limit and offset.

In Supabase, you can use 'limit' to say how many records to get, and 'offset' to skip a number of records. For example, to get page 2 with 10 items per page, you skip 10 (offset) and get 10 (limit). This fetches records 11 to 20.

Result

You can fetch any page of data by changing offset and limit values.

Understanding limit and offset lets you control which slice of data you see.

3

Intermediate

Problems with Offset Pagination

🤔Before reading on: do you think offset pagination always works well with large or changing data? Commit to your answer.

Concept: Explore why offset pagination can be slow or inconsistent with big or changing data sets.

Offset pagination requires the database to skip many records, which slows down queries as data grows. Also, if data changes (like new rows added), pages can shift, causing duplicates or missing items when users browse.

Result

You see that offset pagination can cause slow loading and confusing user experience on big or live data.

Knowing offset pagination limits helps you avoid performance and consistency problems in real apps.

4

Intermediate

Cursor-Based Pagination Explained

🤔Before reading on: do you think cursor pagination uses page numbers or something else to track position? Commit to your answer.

Concept: Learn how cursor pagination uses a unique value from the last item to fetch the next set.

Instead of offset, cursor pagination uses a cursor, like the ID of the last item on the current page. To get the next page, you ask for records after that cursor. This is faster and stable even if data changes.

Result

You can fetch pages by remembering the last item’s cursor, avoiding slow skips and data shifts.

Understanding cursor pagination unlocks efficient and reliable data browsing for large or live data.

5

Intermediate

Implementing Cursor Pagination in Supabase

Concept: How to write Supabase queries using cursor pagination.

In Supabase, you can use 'gt' (greater than) or 'lt' (less than) filters on a unique column like 'id' to get records after or before a cursor. For example, to get next page after id 20, query where id > 20 with a limit.

Result

You can write queries that fetch pages based on cursor values, improving speed and consistency.

Knowing how to apply cursor filters in Supabase makes pagination practical and efficient.

6

Advanced

Handling Edge Cases in Pagination

🤔Before reading on: do you think pagination always returns the same number of items per page? Commit to your answer.

Concept: Learn about challenges like last page size, deleted items, and sorting changes.

Sometimes the last page has fewer items. If data is deleted or inserted, cursor positions might skip or repeat items. Sorting order changes can also confuse pagination. Handling these requires careful query design and sometimes extra metadata.

Result

You understand that pagination needs extra care to handle real-world data changes and keep user experience smooth.

Knowing these edge cases prepares you to build robust pagination that users trust.

7

Expert

Combining Pagination with Filtering and Sorting

🤔Before reading on: do you think pagination works the same regardless of filters or sorting? Commit to your answer.

Concept: Explore how filters and sorting affect pagination logic and cursor values.

When you add filters or change sorting, the data order changes, so cursors must reflect the new order. For example, if sorting by date, cursor should be the date value, not just an ID. Filters reduce data size, so pages may have fewer items. Queries must combine these carefully to keep pagination correct.

Result

You can build complex queries that paginate filtered and sorted data reliably.

Understanding how pagination interacts with filters and sorting is key to advanced, user-friendly data navigation.

8

Expert

Optimizing Pagination for Performance and UX

🤔Before reading on: do you think loading pages instantly is always possible? Commit to your answer.

Concept: Learn techniques to speed up pagination and improve user experience.

Use indexes on cursor columns to speed queries. Preload next page in background for smooth browsing. Limit page size to balance speed and data shown. Use caching to avoid repeated queries. Also, consider infinite scrolling as an alternative UX pattern.

Result

Your pagination is fast, smooth, and scales well with many users and large data.

Knowing performance tricks and UX patterns helps build professional-grade pagination.

Under the Hood

Pagination works by telling the database to return only a subset of rows. Offset pagination asks the database to skip a number of rows before returning results, which can be slow because the database still scans skipped rows. Cursor pagination uses a unique value from the last fetched row to directly jump to the next set, using indexes for fast access. This reduces scanning and keeps results consistent even if data changes.

Why designed this way?

Offset pagination is simple and intuitive, so it was widely used first. But as data grew and apps needed real-time updates, its flaws became clear. Cursor pagination was designed to solve these by using stable positions in data, improving speed and consistency. Supabase supports both to balance ease of use and performance.

Full Data Set
┌─────────────────────────────┐
│                             │
│  [Row1][Row2][Row3]...[RowN]│
│                             │
└─────────────┬───────────────┘
              │
      Offset Pagination
┌─────────────▼───────────────┐
│ Skip X rows, then take Y rows│
└─────────────────────────────┘

      Cursor Pagination
┌─────────────▼───────────────┐
│ Use last row's unique value  │
│ to fetch next rows directly  │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does offset pagination always return consistent pages if data changes? Commit yes or no.

Common Belief: Offset pagination always returns the same data for each page number.

Reality: If data is added or removed, offset pagination pages can shift, causing duplicates or missing items.

Why it matters: Users may see repeated or skipped data, leading to confusion and poor experience.

Quick: Is cursor pagination harder to implement than offset? Commit yes or no.

Common Belief: Cursor pagination is too complex and not worth the effort compared to offset.

Reality: Cursor pagination is straightforward once you understand cursors and offers better performance and consistency.

Why it matters: Avoiding cursor pagination limits app scalability and user experience on large or live data.

Quick: Does increasing page size always improve performance? Commit yes or no.

Common Belief: Loading more items per page always makes pagination faster.

Reality: Larger pages increase load time and memory use, slowing down the app and hurting user experience.

Why it matters: Choosing the right page size balances speed and usability.

Quick: Can you use the same cursor for different sorting orders? Commit yes or no.

Common Belief: A cursor value like an ID works for any sorting order.

Reality: Cursor must match the sorting column(s); using wrong cursor breaks pagination order.

Why it matters: Incorrect cursors cause wrong data pages and confuse users.

Expert Zone

1

Cursor pagination requires stable, unique sorting keys to avoid skipping or repeating items, which is often overlooked.

2

Combining pagination with complex filters or multi-column sorting needs careful cursor design to maintain consistency.

3

Preloading next pages and caching results can greatly improve user experience but add complexity to implementation.

When NOT to use

Offset pagination is not suitable for large or frequently changing datasets; use cursor pagination instead. For very small datasets, simple queries without pagination may suffice. Infinite scrolling can replace pagination in some user interfaces but requires careful loading management.

Production Patterns

Real-world apps use cursor pagination with indexed unique columns for fast queries. They combine pagination with filters and sorting, often storing cursors in URLs or state for navigation. Preloading and caching are common to reduce wait times. Some apps switch between offset and cursor pagination depending on data size and use case.

Connections

Database Indexing

Pagination performance depends on indexing strategies.

Understanding how indexes speed up cursor queries helps optimize pagination for large datasets.

User Experience Design

Pagination patterns affect how users interact with data lists.

Knowing pagination limits guides UX choices like page size, infinite scroll, or load more buttons.

Supply Chain Logistics

Both manage large flows by breaking them into smaller, manageable batches.

Seeing pagination like shipping goods in batches helps grasp why splitting data improves handling and speed.

Common Pitfalls

#1 Using offset pagination on large, live data causing slow queries and inconsistent pages.

Wrong approach:

const { data, error } = await supabase
.from('items')
.select('*')
.range(1000, 1010); // offset 1000, limit 10

Correct approach:

const { data, error } = await supabase
.from('items')
.select('*')
.gt('id', lastCursorId)
.limit(10);

Root cause:

Misunderstanding that offset skips rows inefficiently and is unstable with changing data.

#2 Using a non-unique or non-indexed column as cursor causing duplicate or missing data.

Wrong approach:

const { data, error } = await supabase
.from('items')
.select('*')
.gt('name', lastName)
.limit(10);

Correct approach:

const { data, error } = await supabase
.from('items')
.select('*')
.gt('id', lastId) // id is unique and indexed
.limit(10);

Root cause:

Not ensuring cursor column uniquely identifies rows and is indexed for performance.

#3 Ignoring sorting order when using cursor pagination leading to wrong data sequence.

Wrong approach:

const { data, error } = await supabase
.from('items')
.select('*')
.order('created_at', { ascending: false })
.gt('id', lastId)
.limit(10);

Correct approach:

const { data, error } = await supabase
.from('items')
.select('*')
.order('created_at', { ascending: false })
.lt('created_at', lastCreatedAt) // cursor matches sorting column
.limit(10);

Root cause:

Not matching cursor column with sorting order causes incorrect pagination.

Key Takeaways

Pagination splits large data into small pages to improve speed and user experience.

Offset pagination is simple but slow and unstable with big or changing data.

Cursor pagination uses unique values to fetch next pages efficiently and consistently.

Proper cursor choice and query design are essential for reliable pagination with filters and sorting.

Optimizing pagination with indexes, caching, and UX patterns makes apps fast and user-friendly.