SPFx and Large Lists — Avoiding the 5000-Item View Threshold

SharePoint's 5,000-item list view threshold is one of the oldest pain points in the platform — and it still catches developers off guard, usually in production, usually at the worst possible time.
Your query works perfectly during development with 200 items. Three months later, the list has grown to 8,000 items and users start seeing "The attempted operation is prohibited because it exceeds the list view threshold."
This article explains why the threshold exists, how to avoid triggering it, and how to implement cursor-based pagination with infinite scroll for large lists.

🗺️ Why the Threshold Exists

SharePoint enforces a 5,000-item limit on single query results to protect SQL Server performance. Returning more than 5,000 rows in a single query requires a full table scan on the content database — at scale, that blocks other operations across the farm.

The threshold applies to:

Queries that filter on non-indexed columns
Queries that sort on non-indexed columns
Queries that request more than 5,000 items without paging through an indexed column

It does not apply to:

Queries that filter on indexed columns (the query uses an index, not a table scan)
Paginated queries using $skiptoken that page through indexed columns
Search-based queries (search has its own index and does not use the list view threshold)

✅ Rule 1 — Index Your Filter Columns

If your SPFx web part filters or sorts a list by a specific column, that column must be indexed in SharePoint — especially once the list grows beyond a few thousand items.

Index a column via PnPjs:

// Set index on a column programmatically (run once during provisioning)
await sp.web.lists
  .getByTitle('Projects')
  .fields
  .getByInternalNameOrTitle('Status')
  .update({ Indexed: true });

Index a column manually: List Settings → Columns → click the column → check "Indexed column."

Once indexed, OData filters on that column work safely regardless of list size:

// Safe for lists with 100,000 items — Status is indexed
const activeProjects = await sp.web.lists
  .getByTitle('Projects')
  .items
  .filter(`Status eq 'Active'`)
  .select('Id', 'Title', 'Status', 'Owner')
  .orderBy('Created', false) // Created is always indexed
  .top(100)();

Always filter on indexed columns and always sort on indexed columns. ID, Created, Modified, and Author are indexed by default. Any custom column you regularly filter or sort on should be indexed.

✅ Rule 2 — Use RowLimit with Paging, Not Top

If you need to display a large dataset page by page, use getListItemChangesSinceToken or PnPjs's built-in getPaged() method — not .top(5000).

PnPjs paged query:

import { SPFI } from '@pnp/sp';
import '@pnp/sp/webs';
import '@pnp/sp/lists';
import '@pnp/sp/items';

export async function getAllItemsPaged(
  sp: SPFI,
  listName: string,
  pageSize: number = 100
): Promise<any[]> {
  const allItems: any[] = [];

  // Get the first page
  let page = await sp.web.lists
    .getByTitle(listName)
    .items
    .select('Id', 'Title', 'Status', 'Created')
    .orderBy('ID', true) // ID is always indexed — safe for large lists
    .top(pageSize)
    .getPaged();

  allItems.push(...page.results);

  // Continue paging until all items are retrieved
  while (page.hasNext) {
    page = await page.getNext();
    allItems.push(...page.results);
  }

  return allItems;
}

getPaged() uses $skiptoken under the hood — a cursor-based pagination mechanism that SharePoint handles safely regardless of list size, as long as the orderBy column is indexed.

🧩 Infinite Scroll Component

For user-facing lists, loading all items upfront is rarely the right UX. Infinite scroll loads the next page as the user scrolls to the bottom — showing results immediately and fetching more only when needed.

src/services/PagedListService.ts

import { SPFI } from '@pnp/sp';
import '@pnp/sp/webs';
import '@pnp/sp/lists';
import '@pnp/sp/items';
import { IPagedResult } from '@pnp/sp/items';

export interface IListPage<T> {
  items: T[];
  hasMore: boolean;
  loadNext: (() => Promise<IListPage<T>>) | null;
}

export async function getFirstPage<T>(
  sp: SPFI,
  listName: string,
  selectFields: string[],
  filterExpr: string | null,
  pageSize: number = 50
): Promise<IListPage<T>> {
  let query = sp.web.lists
    .getByTitle(listName)
    .items
    .select(...selectFields)
    .orderBy('ID', true)
    .top(pageSize);

  if (filterExpr) {
    query = query.filter(filterExpr);
  }

  const paged = await query.getPaged<T>();

  return {
    items: paged.results,
    hasMore: paged.hasNext,
    loadNext: paged.hasNext
      ? async () => {
          const next = await paged.getNext();
          return {
            items: next.results,
            hasMore: next.hasNext,
            loadNext: next.hasNext
              ? async () => {
                  const n2 = await next.getNext();
                  return { items: n2.results, hasMore: n2.hasNext, loadNext: null };
                }
              : null
          };
        }
      : null
  };
}

src/components/InfiniteScrollList.tsx

import * as React from 'react';
import { SPFI } from '@pnp/sp';
import { Spinner, Button } from '@fluentui/react-components';
import { getFirstPage, IListPage } from '../../services/PagedListService';
import { makeStyles, tokens } from '@fluentui/react-components';

const useStyles = makeStyles({
  container: { display: 'flex', flexDirection: 'column', gap: tokens.spacingVerticalS },
  item: { padding: tokens.spacingVerticalS, background: tokens.colorNeutralBackground2, borderRadius: tokens.borderRadiusMedium },
  loadMore: { alignSelf: 'center', marginTop: tokens.spacingVerticalM }
});

interface IInfiniteScrollListProps {
  sp: SPFI;
  listName: string;
}

interface IProjectItem {
  Id: number;
  Title: string;
  Status: string;
}

const InfiniteScrollList: React.FC<IInfiniteScrollListProps> = ({ sp, listName }) => {
  const styles = useStyles();
  const [items, setItems] = React.useState<IProjectItem[]>([]);
  const [page, setPage] = React.useState<IListPage<IProjectItem> | null>(null);
  const [loading, setLoading] = React.useState(true);
  const [loadingMore, setLoadingMore] = React.useState(false);

  React.useEffect(() => {
    getFirstPage<IProjectItem>(sp, listName, ['Id', 'Title', 'Status'], null, 50)
      .then(firstPage => {
        setItems(firstPage.items);
        setPage(firstPage);
        setLoading(false);
      });
  }, []);

  const handleLoadMore = async () => {
    if (!page?.loadNext) return;
    setLoadingMore(true);
    const nextPage = await page.loadNext();
    setItems(prev => [...prev, ...nextPage.items]);
    setPage(nextPage);
    setLoadingMore(false);
  };

  if (loading) return <Spinner label="Loading..." />;

  return (
    <div className={styles.container}>
      {items.map(item => (
        <div key={item.Id} className={styles.item}>
          <strong>{item.Title}</strong> — {item.Status}
        </div>
      ))}

      {page?.hasMore && (
        <Button
          className={styles.loadMore}
          onClick={handleLoadMore}
          disabled={loadingMore}
        >
          {loadingMore ? 'Loading...' : `Load more`}
        </Button>
      )}
    </div>
  );
};

export default InfiniteScrollList;

⚠️ What Still Triggers the Threshold

Even with the above patterns, these operations will hit the threshold on unindexed columns:

.orderBy('CustomColumn', true) where CustomColumn is not indexed
.filter("CustomColumn eq 'value'") where CustomColumn is not indexed
.top(5001) — requesting more than 5,000 items in a single page
CAML queries without a <RowLimit> on a large list

The safest pattern for large lists: always filter on ID or indexed columns, always use getPaged(), and always set <RowLimit> in CAML queries.

📂 GitHub Source

View full SPFx project on GitHub:SPFx large list paging solution — PnPjs cursor-based pagination with infinite scroll

GitHub

✅ Summary

The 5,000-item threshold applies to queries on unindexed columns — index every column you filter or sort on.
ID, Created, Modified, and Author are indexed by default — always sort on ID for threshold-safe paging.
Use getPaged() and getNext() for cursor-based pagination — never use .top(5001).
Implement infinite scroll with a "Load More" button for large user-facing lists — avoid loading all items upfront.
In CAML queries, always include <RowLimit> and page using ListItemCollectionPosition.
Search-based queries via sp.search() bypass the threshold entirely — use them when filtering on unindexed text content.

Happy coding!