PostgREST pagination latency during high offset requests

Direct Answers

1. Threshold for non‑linear latency spikes
There is no single “magic” number that applies to every Supabase deployment. In practice the planner begins to flip from an Index Scan that can skip rows efficiently to a Bitmap Heap Scan or even a Seq Scan when the cost of skipping the requested rows exceeds the cost of a full scan. That inflection point depends on:

• Average row width (larger rows increase the cost of discarding them).
• Coverage of the index (a covering index can keep the scan to the heap).
• Available work_mem and shared_buffers (larger buffers allow more rows to be kept in memory).
• Statistical freshness and correlation of the data.

On modest instances (e.g., 2‑4 GB RAM, 10‑20 GB tables) you will typically notice a linear degradation once the offset reaches a few tens of thousands of rows. On well‑provisioned hardware with large work_mem the same behaviour may only appear when the offset is in the hundreds of thousands or millions.

2. Using pg_stat_statements to differentiate overhead
When you run identical queries that differ only in the OFFSET value, pg_stat_statements will aggregate the following metrics per query ID:

• total_time – overall CPU + I/O time.
• shared_blks_read – blocks fetched from disk.
• shared_blks_hit – blocks served from shared_buffers.
• blk_read_time – time spent reading blocks.

To isolate the offset cost you can:

1. Enable track_io_timing and track_functions=pl in postgresql.conf and reload.
2. Run a controlled benchmark:

   EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM big_table ORDER BY id LIMIT 100 OFFSET X;

3. Reset pg_stat_statements before each run or filter by queryid to isolate the offset effect.
4. Observe the growth of shared_blks_read per 10k‑row increment. Linear growth indicates pure offset overhead; a sudden jump in total_time with little change in block counts indicates a planner switch to a more expensive scan type.
5. Cross‑check with EXPLAIN ANALYZE to confirm whether the plan changed to a Bitmap Heap Scan or Seq Scan.

Practical Recommendations

• Prefer keyset pagination (e.g., WHERE id > last_seen_id ORDER BY id LIMIT 100) for deep traversal. Supabase’s SDK allows this via .range() with a cursor or by using the Range-Unit: items header in PostgREST.
• If you must use offset, keep it below the plan‑flip point identified in your own tests (often <10 k rows on a typical Supabase instance).
• Consider adding a covering index on the fields used in the ORDER BY and WHERE to keep the heap scan minimal.
• Monitor pgbouncer pool usage if you’re on transaction pooling; long offset queries can hold a connection and increase queue latency.

Question for You

What is the average row width and which indexes cover the ORDER BY column in your table? Knowing this helps fine‑tune work_mem and anticipate the offset threshold for your specific workload.