PostgREST pagination latency during high offset requests

Question

Supabase leverages PostgREST to translate API requests into SQL queries. When retrieving large datasets using the limit and offset parameters, the underlying PostgreSQL engine must scan and discard rows prior to returning the requested page.

As the offset value increases, there is a potential for linear degradation in query performance, leading to increased CPU utilization and higher response latency. This behavior is typical of standard SQL OFFSET clauses but becomes a critical bottleneck in high-concurrency environments with millions of records.

While keyset pagination is documented as a best practice for performance, the default SDK patterns often rely on offset-based navigation.

What is the specific threshold where offset-based pagination begins to cause non-linear latency spikes in a standard Supabase instance?
  How can pg_stat_statements be used to differentiate between slow index scans and the overhead caused specifically by high offset values?

Zoha · Answer

Direct Answers
1. Threshold for non‑linear latency spikesThere 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 overheadWhen 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:

Enable track_io_timing and track_functions=pl in postgresql.conf and reload.
Run a controlled benchmark:EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM big_table ORDER BY id LIMIT 100 OFFSET X;
Reset pg_stat_statements before each run or filter by queryid to isolate the offset effect.
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.
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.

PostgREST pagination latency during high offset requests

1 answer

Direct Answers

Practical Recommendations

Question for You

Your perspective belongs here.

0 question comments