PageNumberPagination or CursorPagination when rows shift during a zero-downtime backfill

Resolution

To minimize silent data shifts for forward-paging clients during a zero-downtime backfill, CursorPagination is the documented approach. To avoid breaking existing client contracts, maintain PageNumberPagination on the current viewset and expose the new cursor-based ordering via a separate endpoint or a query parameter toggle. Ensure the ordering field is unique by appending the primary key as a tiebreaker and create the index using a concurrent build operation to avoid table locks.

Technical Analysis

The instability in PageNumberPagination stems from its reliance on OFFSET. When a backfill inserts or updates rows, the relative position of records shifts, causing clients to see duplicate items or skip records entirely as they move from page to page.

Confirmed Behaviors:

• CursorPagination uses keyset pagination, encoding the last seen value into an opaque cursor, which remains stable regardless of row shifts.
• DRF's CursorPagination requires a unique, indexed ordering; it does not automatically provide a tiebreaker for non-unique columns.
• By default, CursorPagination does not return a total count, which is a breaking change for clients relying on PageNumberPagination's count metadata.

Implementation Steps

1. Preserve the Contract: Keep the existing viewset configured with PageNumberPagination.
2. Introduce the New Path:
   • Option A (Separate Endpoint): Create a new endpoint (e.g., /api/items/stream/) that reuses the same serializer but employs CursorPagination.
   • Option B (Query Parameter): Implement a toggle (e.g., ?pagination=cursor) within the view to switch paginators dynamically. This requires rigorous testing to ensure identical filtering and ordering logic.
3. Establish Unique Ordering: Define a composite ordering to guarantee uniqueness. For example, use ['-created_at', '-id'].
4. Zero-Downtime Indexing: Build the index concurrently to prevent blocking writes during the backfill. In PostgreSQL, this is achieved via:

   CREATE INDEX CONCURRENTLY idx_items_cursor ON items (created_at DESC, id DESC);

5. Verification: Run a forward-paging test while executing the backfill script. Assert that no records are duplicated or omitted across page boundaries.

Assumptions and Verification

This recommendation assumes the use of a database that supports concurrent index creation (like PostgreSQL). Because DRF defaults for page_size and max_page_size are version-dependent, you must verify these against your specific installed release notes.

Required Diagnostic: Is the current ordering column already unique and indexed? If so, a new index may be unnecessary; if not, the composite index described above is mandatory for CursorPagination to function.