Range pagination vs keyset pagination for a large Supabase table under concurrent writes

Direct answer

Yes – range() (LIMIT/OFFSET) with a deterministic ORDER BY still suffers from duplicates or gaps when rows are inserted or deleted between page requests. Keyset pagination avoids this drift but requires an index on the sort column(s) plus a unique tiebreaker. An exact COUNT(*) on a large filtered table can be expensive; consider caching or approximating the count instead of querying it on every page.

1. Does range() with deterministic ordering still risk duplicates or gaps?

Confirmed fact: PostgreSQL (and therefore PostgREST/Supabase) evaluates LIMIT/OFFSET by scanning rows in the specified order and skipping the first OFFSET rows. If a concurrent INSERT adds a row before the current offset, the scan will see an extra row and shift everything down, causing a duplicate on the next page. If a concurrent DELETE removes a row before the offset, the scan will skip fewer rows, causing a gap. Adding a unique tiebreaker (e.g., ORDER BY sort_col, id) only makes the order stable; it does not change the positional nature of OFFSET.

Likely explanation: The drift is purely a function of the changing physical position of rows relative to the offset value, not of the sort stability.

2. Is keyset pagination practical for arbitrary user‑chosen sort columns?

Confirmed fact: Keyset (cursor) pagination works efficiently when the WHERE clause can use an index: WHERE (sort_col, tiebreaker) > (last_seen_sort, last_seen_tiebreaker) ORDER BY sort_col, tiebreaker LIMIT n. This requires a B‑tree index on the sort column(s) and the tiebreaker (commonly the primary key).

Likely explanation: If the user can pick any column, you must either maintain an index on that column (or a covering composite index) or fall back to offset pagination for those ad‑hoc sorts. Creating many indexes may impact write performance, so evaluate the write‑read trade‑off.

3. Is requesting an exact count on every page request a meaningful performance risk?

Confirmed fact: SELECT COUNT(*) FROM table WHERE … triggers a full index or table scan in PostgreSQL unless the predicate matches a partial index that can be satisfied solely from the index. As the filtered set grows, latency grows linearly.

Likely explanation: For large tables under concurrent writes, the count query can add hundreds of milliseconds to seconds per request, which is often unacceptable for a pager UI. Approximate counts from pg_stat_user_tables or a periodically refreshed summary table are common mitigations.

Recommended steps for this case

1. Adopt keyset pagination for the primary, user‑facing sort (e.g., created_at or id). Ensure a composite index exists on (sort_column, id).
2. If users need to sort on arbitrary columns without indexes, expose a fallback to offset pagination but warn about possible drift and performance degradation at high offsets.
3. Replace per‑page COUNT(*) with either:
   • A cached count refreshed via a background job (e.g., materialized view or summary table).
   • An approximate count from pg_stat_user_tables.reltuples when exactness is not required.
4. Validate with EXPLAIN ANALYZE on a representative high‑offset query to see the "Rows Removed by Filter" cost, and on the keyset query to confirm index usage.

Missing diagnostic detail

If you can confirm whether an index exists (or can be added) on the arbitrary sort columns users may choose, the recommendation between keyset and offset for those columns becomes clear. Without that detail, a safe default is to keep offset pagination for ad‑hoc sorts and keyset for the primary, indexed sort.