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

We need a pagination strategy for a Heroku‑hosted PostgreSQL table that will grow beyond one million rows and must serve API responses under 200 ms. The two documented approaches are offset pagination (LIMIT/OFFSET) and keyset pagination (WHERE id > last_id using a server‑side cursor).

Constraints include Heroku’s default 20‑connection pool, pgBouncer running in transaction‑pooling mode which can close long‑lived cursors, and the requirement for a stable, monotonic ordering column to avoid gaps or duplicates. Counting total rows for offset pagination is expensive, while keyset pagination eliminates the count but may hold cursors longer than the pool permits.

Given these trade‑offs, which method provides predictable latency and connection‑pool safety at scale?

• Does keyset pagination with explicit cursors stay within the 20‑connection limit under typical request rates?
• Can offset pagination be made viable by limiting maximum offset depth and skipping total‑count queries?
• What monitoring signals should we watch to detect cursor‑induced pool exhaustion early?