Offset vs Keyset Pagination on Heroku Postgres: Which Scales Past 10⁶ Rows?

Answer: Keyset Pagination Is the Viable Choice

For Heroku Postgres tables exceeding 1 million rows with a 200ms latency target, keyset pagination using a stateless WHERE clause (e.g., WHERE id > last_seen_id ORDER BY id LIMIT 50) is the only approach that scales predictably within connection limits.

Why Offset Pagination Fails at Scale

• Row scanning cost: PostgreSQL must sequentially traverse all rows up to the offset, making OFFSET 100,000 queries expensive regardless of indexes. On Heroku Postgres, this typically exceeds 200ms.
• COUNT(*) bottleneck: Calculating total rows for pagination metadata requires a full index scan, adding 50–200ms on large tables.
• No natural ceiling: Even with a maximum offset cap (e.g., 50,000 rows), deep pagination still triggers latency spikes as the offset approaches the limit.

Why Keyset Pagination Works

• Constant-time access: The query WHERE id > last_seen_id ORDER BY id LIMIT 50 uses an index seek, executing in O(log n) time regardless of table size.
• No cursor dependency: Heroku’s pgBouncer in transaction-pooling mode closes server-side cursors after each transaction. A stateless keyset query avoids this entirely—each request uses a fresh, short-lived connection.
• Connection safety: With 20 pooled connections, keyset pagination never exceeds the pool since each request completes within a single transaction.

Implementation Steps

1. Ensure a stable ordering column: Use a non-null, unique, auto-incrementing id (serial/bigserial) or a created_at timestamp with a unique tiebreaker.
2. Query pattern:

   SELECT * FROM items WHERE id > $1 ORDER BY id LIMIT 50;

   Pass the last id from the current page as the parameter.
3. Avoid COUNT(*): Return only the page data. If total counts are needed, compute them asynchronously or cache them separately.
4. Monitor latency: Use EXPLAIN ANALYZE to verify index usage and measure actual execution time.

Monitoring Signals for Cursor-Induced Pool Exhaustion

• pg_stat_activity showing idle in transaction sessions approaching the 20-connection limit.
• pgBouncer logs reporting pool error: all connections are in use.
• Response times creeping upward during pagination, indicating connection contention.

Verification Commands

-- Test keyset performance on your 1M+ row table
EXPLAIN ANALYZE SELECT * FROM items WHERE id > 1000000 ORDER BY id LIMIT 50;

-- Check current connection state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Caveats and Assumptions

• This assumes your id column is indexed (primary key or explicit index).
• Keyset pagination does not support jumping to arbitrary pages; users must paginate sequentially.
• If you require total row counts, implement a separate SELECT COUNT(*) FROM items cached in Redis or calculated via reltuples in pg_class.

Action: Adopt keyset pagination with stateless WHERE clauses. Offset pagination should be limited to recent pages (offset < 10,000) or replaced entirely for deep navigation.