$ cat /posts/supabase-pagination-efficient-data-loading-techniques.md

Supabase Pagination: Efficient Data Loading Techniques

drwxr-xr-x2026-01-265 min0 views

Supabase Pagination Efficient Data Loading Techniques

Pagination divides large datasets into manageable chunks reducing initial load times, improving application performance, and enhancing user experience by loading only needed data instead of fetching thousands of records simultaneously. Without pagination, applications loading entire tables consume excessive bandwidth, slow down rendering, overwhelm client memory, and create poor experiences especially on mobile devices with limited resources. This comprehensive guide covers understanding pagination strategies and their tradeoffs, implementing offset-based pagination with LIMIT and OFFSET, building cursor-based pagination for consistent results, creating infinite scroll functionality, implementing load more buttons, handling edge cases and empty states, optimizing pagination queries with indexes, combining pagination with filtering and search, and building reusable pagination components in React. Pagination becomes essential when displaying lists including blog posts, user feeds, search results, product catalogs, comment threads, or any dataset exceeding 50-100 items where loading everything upfront degrades performance. Before proceeding, understand basic queries and filtering techniques.

Pagination Strategies Comparison

Strategy	Pros	Cons	Best For
Offset-Based	Simple, page numbers	Slow for large offsets	Small datasets
Cursor-Based	Consistent, fast	No page numbers	Large datasets, feeds
Keyset	Very fast, consistent	Complex queries	High-performance apps
Infinite Scroll	Smooth UX	Hard to reach end	Social feeds
Load More	User controlled	Manual clicking	Mobile apps

Offset-Based Pagination

sqloffset_pagination.sql

-- Basic offset pagination
select *
from posts
order by created_at desc
limit 10 offset 0;  -- Page 1

select *
from posts
order by created_at desc
limit 10 offset 10;  -- Page 2

select *
from posts
order by created_at desc
limit 10 offset 20;  -- Page 3

-- Formula: offset = (page - 1) * page_size

-- JavaScript client implementation
import { supabase } from './supabaseClient'

const PAGE_SIZE = 10

async function fetchPage(pageNumber) {
  const from = (pageNumber - 1) * PAGE_SIZE
  const to = from + PAGE_SIZE - 1

  const { data, error, count } = await supabase
    .from('posts')
    .select('*', { count: 'exact' })
    .order('created_at', { ascending: false })
    .range(from, to)

  const totalPages = Math.ceil(count / PAGE_SIZE)

  return {
    data,
    currentPage: pageNumber,
    totalPages,
    totalItems: count,
    hasNextPage: pageNumber < totalPages,
    hasPreviousPage: pageNumber > 1
  }
}

// Usage
const page1 = await fetchPage(1)
const page2 = await fetchPage(2)

Cursor-Based Pagination

sqlcursor_pagination.sql

-- Cursor pagination using created_at timestamp
select *
from posts
where created_at < '2026-01-20 10:00:00'
order by created_at desc
limit 10;

-- Next page uses last item's created_at as cursor
select *
from posts
where created_at < '2026-01-19 15:30:00'  -- Last item from previous page
order by created_at desc
limit 10;

-- Cursor pagination with ID (more reliable)
select *
from posts
where id < 'last-post-id'
order by created_at desc, id desc
limit 10;

-- JavaScript implementation
async function fetchPostsCursor(cursor = null, pageSize = 10) {
  let query = supabase
    .from('posts')
    .select('*')
    .order('created_at', { ascending: false })
    .order('id', { ascending: false })
    .limit(pageSize)

  if (cursor) {
    // Cursor is the created_at of last item
    query = query.lt('created_at', cursor)
  }

  const { data, error } = await query

  return {
    data,
    nextCursor: data.length > 0 ? data[data.length - 1].created_at : null,
    hasMore: data.length === pageSize
  }
}

// Usage
const firstPage = await fetchPostsCursor()
const secondPage = await fetchPostsCursor(firstPage.nextCursor)

React Pagination Component

javascriptPagination.jsx

// Pagination.jsx - Reusable pagination component
import { useState, useEffect } from 'react'
import { supabase } from './supabaseClient'

function PaginatedPosts() {
  const [posts, setPosts] = useState([])
  const [currentPage, setCurrentPage] = useState(1)
  const [totalPages, setTotalPages] = useState(0)
  const [loading, setLoading] = useState(false)
  
  const PAGE_SIZE = 10

  useEffect(() => {
    fetchPosts()
  }, [currentPage])

  async function fetchPosts() {
    setLoading(true)

    const from = (currentPage - 1) * PAGE_SIZE
    const to = from + PAGE_SIZE - 1

    const { data, error, count } = await supabase
      .from('posts')
      .select('*', { count: 'exact' })
      .order('created_at', { ascending: false })
      .range(from, to)

    if (error) {
      console.error('Error:', error)
    } else {
      setPosts(data)
      setTotalPages(Math.ceil(count / PAGE_SIZE))
    }

    setLoading(false)
  }

  function nextPage() {
    if (currentPage < totalPages) {
      setCurrentPage(prev => prev + 1)
    }
  }

  function previousPage() {
    if (currentPage > 1) {
      setCurrentPage(prev => prev - 1)
    }
  }

  function goToPage(page) {
    setCurrentPage(page)
  }

  if (loading) return <div>Loading...</div>

  return (
    <div>
      {/* Posts list */}
      <div className="posts-list">
        {posts.map(post => (
          <div key={post.id} className="post">
            <h3>{post.title}</h3>
            <p>{post.content}</p>
          </div>
        ))}
      </div>

      {/* Pagination controls */}
      <div className="pagination">
        <button 
          onClick={previousPage} 
          disabled={currentPage === 1}
        >
          Previous
        </button>

        {/* Page numbers */}
        <div className="page-numbers">
          {Array.from({ length: totalPages }, (_, i) => i + 1).map(page => (
            <button
              key={page}
              onClick={() => goToPage(page)}
              className={currentPage === page ? 'active' : ''}
            >
              {page}
            </button>
          ))}
        </div>

        <button 
          onClick={nextPage} 
          disabled={currentPage === totalPages}
        >
          Next
        </button>
      </div>

      <div className="pagination-info">
        Page {currentPage} of {totalPages}
      </div>
    </div>
  )
}

export default PaginatedPosts

Infinite Scroll Implementation

javascriptInfiniteScroll.jsx

// InfiniteScroll.jsx - Load more on scroll
import { useState, useEffect, useRef } from 'react'
import { supabase } from './supabaseClient'

function InfiniteScrollPosts() {
  const [posts, setPosts] = useState([])
  const [loading, setLoading] = useState(false)
  const [hasMore, setHasMore] = useState(true)
  const [cursor, setCursor] = useState(null)
  
  const observerTarget = useRef(null)
  const PAGE_SIZE = 10

  useEffect(() => {
    loadMorePosts()
  }, [])

  useEffect(() => {
    const observer = new IntersectionObserver(
      entries => {
        if (entries[0].isIntersecting && hasMore && !loading) {
          loadMorePosts()
        }
      },
      { threshold: 1 }
    )

    if (observerTarget.current) {
      observer.observe(observerTarget.current)
    }

    return () => {
      if (observerTarget.current) {
        observer.unobserve(observerTarget.current)
      }
    }
  }, [hasMore, loading, cursor])

  async function loadMorePosts() {
    setLoading(true)

    let query = supabase
      .from('posts')
      .select('*')
      .order('created_at', { ascending: false })
      .limit(PAGE_SIZE)

    if (cursor) {
      query = query.lt('created_at', cursor)
    }

    const { data, error } = await query

    if (error) {
      console.error('Error:', error)
    } else {
      setPosts(prev => [...prev, ...data])
      
      if (data.length < PAGE_SIZE) {
        setHasMore(false)
      } else {
        setCursor(data[data.length - 1].created_at)
      }
    }

    setLoading(false)
  }

  return (
    <div>
      <div className="posts-list">
        {posts.map(post => (
          <div key={post.id} className="post">
            <h3>{post.title}</h3>
            <p>{post.content}</p>
          </div>
        ))}
      </div>

      {/* Intersection observer target */}
      {hasMore && <div ref={observerTarget} className="loading-trigger" />}
      
      {loading && <div className="loading">Loading more...</div>}
      {!hasMore && <div className="end">No more posts</div>}
    </div>
  )
}

export default InfiniteScrollPosts

Load More Button

javascriptLoadMore.jsx

// LoadMore.jsx - Manual load more button
import { useState, useEffect } from 'react'
import { supabase } from './supabaseClient'

function LoadMorePosts() {
  const [posts, setPosts] = useState([])
  const [loading, setLoading] = useState(false)
  const [hasMore, setHasMore] = useState(true)
  const [page, setPage] = useState(1)
  
  const PAGE_SIZE = 10

  useEffect(() => {
    fetchPosts()
  }, [])

  async function fetchPosts() {
    setLoading(true)

    const from = (page - 1) * PAGE_SIZE
    const to = from + PAGE_SIZE - 1

    const { data, error } = await supabase
      .from('posts')
      .select('*')
      .order('created_at', { ascending: false })
      .range(from, to)

    if (error) {
      console.error('Error:', error)
    } else {
      setPosts(prev => [...prev, ...data])
      setHasMore(data.length === PAGE_SIZE)
    }

    setLoading(false)
  }

  function handleLoadMore() {
    setPage(prev => prev + 1)
    fetchPosts()
  }

  return (
    <div>
      <div className="posts-list">
        {posts.map(post => (
          <div key={post.id} className="post">
            <h3>{post.title}</h3>
            <p>{post.content}</p>
          </div>
        ))}
      </div>

      {hasMore && (
        <button 
          onClick={handleLoadMore} 
          disabled={loading}
          className="load-more-btn"
        >
          {loading ? 'Loading...' : 'Load More'}
        </button>
      )}

      {!hasMore && posts.length > 0 && (
        <div className="end-message">You've reached the end</div>
      )}
    </div>
  )
}

export default LoadMorePosts

Pagination with Filters

javascriptFilteredPagination.jsx

// FilteredPagination.jsx - Pagination with search and filters
import { useState, useEffect } from 'react'
import { supabase } from './supabaseClient'

function FilteredPaginatedPosts() {
  const [posts, setPosts] = useState([])
  const [currentPage, setCurrentPage] = useState(1)
  const [totalPages, setTotalPages] = useState(0)
  const [searchTerm, setSearchTerm] = useState('')
  const [category, setCategory] = useState('all')
  
  const PAGE_SIZE = 10

  useEffect(() => {
    fetchPosts()
  }, [currentPage, searchTerm, category])

  async function fetchPosts() {
    const from = (currentPage - 1) * PAGE_SIZE
    const to = from + PAGE_SIZE - 1

    let query = supabase
      .from('posts')
      .select('*', { count: 'exact' })
      .order('created_at', { ascending: false })
      .range(from, to)

    // Apply search filter
    if (searchTerm) {
      query = query.ilike('title', `%${searchTerm}%`)
    }

    // Apply category filter
    if (category !== 'all') {
      query = query.eq('category', category)
    }

    const { data, error, count } = await query

    if (!error) {
      setPosts(data)
      setTotalPages(Math.ceil(count / PAGE_SIZE))
    }
  }

  function handleSearch(e) {
    setSearchTerm(e.target.value)
    setCurrentPage(1)  // Reset to page 1 on new search
  }

  function handleCategoryChange(e) {
    setCategory(e.target.value)
    setCurrentPage(1)  // Reset to page 1 on filter change
  }

  return (
    <div>
      {/* Filters */}
      <div className="filters">
        <input
          type="text"
          placeholder="Search posts..."
          value={searchTerm}
          onChange={handleSearch}
        />
        <select value={category} onChange={handleCategoryChange}>
          <option value="all">All Categories</option>
          <option value="tech">Technology</option>
          <option value="design">Design</option>
          <option value="business">Business</option>
        </select>
      </div>

      {/* Posts list */}
      <div className="posts-list">
        {posts.map(post => (
          <div key={post.id}>
            <h3>{post.title}</h3>
          </div>
        ))}
      </div>

      {/* Pagination */}
      <div className="pagination">
        <button 
          onClick={() => setCurrentPage(p => p - 1)}
          disabled={currentPage === 1}
        >
          Previous
        </button>
        <span>Page {currentPage} of {totalPages}</span>
        <button 
          onClick={() => setCurrentPage(p => p + 1)}
          disabled={currentPage === totalPages}
        >
          Next
        </button>
      </div>
    </div>
  )
}

export default FilteredPaginatedPosts

Pagination Best Practices

Use Cursor Pagination for Large Datasets: Offset pagination becomes slow with large offsets; use cursor-based for better performance
Always Include ORDER BY: Consistent ordering prevents duplicate or missing items across pages
Index Sort Columns: Create indexes on columns used in ORDER BY for fast queries
Reasonable Page Sizes: Use 10-50 items per page; smaller for mobile, larger for desktop
Show Loading States: Display loading indicators during fetch to improve perceived performance
Handle Empty States: Show helpful messages when no results exist
Reset Page on Filter Changes: Return to page 1 when users change search or filter criteria

Performance Tip: For datasets with millions of rows, offset pagination with large offsets (e.g., OFFSET 100000) requires scanning all previous rows. Use cursor-based pagination for consistent performance. Combine with full-text search for complete data exploration.

Common Issues

Duplicate Items Across Pages: Ensure consistent ORDER BY with unique columns (created_at + id)
Slow Pagination Queries: Add indexes on columns used in ORDER BY and WHERE clauses
Total Count Expensive: Use count: 'exact' only when needed; estimate for very large tables
Infinite Scroll Not Triggering: Check IntersectionObserver threshold and ensure hasMore flag is accurate

Next Steps

Add TypeScript: Build type-safe pagination with TypeScript
Combine with Search: Integrate full-text search with pagination
Use in Projects: Apply pagination in React apps or Next.js projects
Optimize Queries: Learn advanced filtering for better performance

Conclusion

Pagination divides large datasets into manageable chunks improving application performance, reducing bandwidth consumption, and enhancing user experience by loading only necessary data. By implementing offset-based pagination for simple page navigation, cursor-based pagination for large datasets with consistent performance, infinite scroll for social feed experiences, and load more buttons for user-controlled loading, you create efficient data browsing interfaces. Always include ORDER BY clauses for consistent results, create indexes on sort columns for query performance, use reasonable page sizes balancing user experience with performance, handle loading and empty states gracefully, and reset pagination when filters change. Cursor-based pagination provides superior performance for large datasets avoiding the sequential scan issues of large offsets while maintaining result consistency when data changes between requests. Pagination becomes essential for any dataset exceeding 50-100 items where loading everything simultaneously degrades performance and user experience. Continue building production applications with TypeScript integration, search features, and framework integrations.