<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Wei Chen</title>
    <description>The latest articles on DEV Community by Wei Chen (@weichen31490).</description>
    <link>https://dev.to/weichen31490</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4211715%2Fcffd7c97-fec2-4c25-b9c1-dd4e67561a43.png</url>
      <title>DEV Community: Wei Chen</title>
      <link>https://dev.to/weichen31490</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/weichen31490"/>
    <language>en</language>
    <item>
      <title>Stop Using OFFSET for Large PostgreSQL Tables: A Practical Guide to Cursor Pagination</title>
      <dc:creator>Wei Chen</dc:creator>
      <pubDate>Thu, 19 Nov 2026 21:27:37 +0000</pubDate>
      <link>https://dev.to/weichen31490/stop-using-offset-for-large-postgresql-tables-a-practical-guide-to-cursor-pagination-355o</link>
      <guid>https://dev.to/weichen31490/stop-using-offset-for-large-postgresql-tables-a-practical-guide-to-cursor-pagination-355o</guid>
      <description>&lt;p&gt;Pagination looks like a solved problem until your API starts slowing down on page 500.&lt;/p&gt;

&lt;p&gt;The first version usually looks harmless:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;20&lt;/span&gt; &lt;span class="k"&gt;OFFSET&lt;/span&gt; &lt;span class="mi"&gt;10000&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;It returns 20 rows. The SQL is simple. The frontend knows how to request page 501.&lt;/p&gt;

&lt;p&gt;But there's a hidden cost: PostgreSQL still needs to process the rows it skips. As the offset grows, the database does more work to return the same number of results.&lt;/p&gt;

&lt;p&gt;There's also a correctness problem. If new records arrive while a user moves between pages, records can appear twice or get skipped.&lt;/p&gt;

&lt;p&gt;For activity feeds, transaction histories, audit logs, and large API result sets, &lt;strong&gt;cursor-based pagination (also called keyset pagination) is often a better choice.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Here's how to implement it correctly, where it helps, and where traditional pagination is still useful.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Why OFFSET Becomes Expensive
&lt;/h2&gt;

&lt;p&gt;Consider a hypothetical &lt;code&gt;orders&lt;/code&gt; table with millions of records.&lt;/p&gt;

&lt;p&gt;An API endpoint returns 20 orders at a time:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;20&lt;/span&gt; &lt;span class="k"&gt;OFFSET&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The first page is straightforward.&lt;/p&gt;

&lt;p&gt;Now imagine a customer navigating much further into the order history:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;20&lt;/span&gt; &lt;span class="k"&gt;OFFSET&lt;/span&gt; &lt;span class="mi"&gt;100000&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Even with a suitable index, PostgreSQL generally needs to traverse the skipped index entries before returning the requested rows.&lt;/p&gt;

&lt;p&gt;A large offset doesn't mean the database can jump directly to the desired record.&lt;/p&gt;

&lt;p&gt;PostgreSQL's documentation explicitly notes that skipped rows still have to be computed and that large offsets can be inefficient.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://www.postgresql.org/docs/current/queries-limit.html" rel="noopener noreferrer"&gt;Reference: PostgreSQL LIMIT and OFFSET documentation&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;The other problem is consistency.&lt;/p&gt;

&lt;p&gt;Imagine the first page contains these records:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Page 1: 100, 99, 98, 97, 96
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Before the next request, a new record with ID 101 is inserted.&lt;/p&gt;

&lt;p&gt;The next query uses &lt;code&gt;OFFSET 5&lt;/code&gt;, producing:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Page 2: 96, 95, 94, 93, 92
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Record 96 appears twice.&lt;/p&gt;

&lt;p&gt;Deletes or changes to sort values can create similar pagination problems, including missed records.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. How Cursor Pagination Works
&lt;/h2&gt;

&lt;p&gt;Instead of telling PostgreSQL how many rows to skip, tell it where to continue.&lt;/p&gt;

&lt;p&gt;Let's start with a simple example using IDs:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="mi"&gt;96&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;5&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Here, &lt;code&gt;96&lt;/code&gt; is the cursor, representing the last record returned by the previous page.&lt;/p&gt;

&lt;p&gt;The database can use an index to find records below that value rather than counting through everything before it.&lt;/p&gt;

&lt;p&gt;The API response might look like this:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight json"&gt;&lt;code&gt;&lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="nl"&gt;"data"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="nl"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mi"&gt;95&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="nl"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mi"&gt;94&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="nl"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mi"&gt;93&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="nl"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mi"&gt;92&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="nl"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mi"&gt;91&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;
  &lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nl"&gt;"next_cursor"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"91"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nl"&gt;"has_more"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kc"&gt;true&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The client passes &lt;code&gt;next_cursor&lt;/code&gt; to request the following page.&lt;/p&gt;

&lt;p&gt;This approach keeps the amount of pagination-related work more predictable as users move deeper into the dataset.&lt;/p&gt;

&lt;p&gt;However, using IDs as cursors only makes sense when the ordering is based on IDs.&lt;/p&gt;

&lt;p&gt;Most applications need something more flexible.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Use a Composite Cursor for Stable Ordering
&lt;/h2&gt;

&lt;p&gt;Suppose the API displays orders by creation time:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Timestamps aren't necessarily unique.&lt;/p&gt;

&lt;p&gt;Two orders can share the same &lt;code&gt;created_at&lt;/code&gt; value. If the cursor uses only the timestamp, the API risks skipping records that have the same timestamp as the last item on the previous page.&lt;/p&gt;

&lt;p&gt;The solution is to add a unique tie-breaker.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Now every record has a deterministic position.&lt;/p&gt;

&lt;p&gt;The next-page query becomes:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nv"&gt;$1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nv"&gt;$2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;21&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The parameters represent the timestamp and ID of the last returned record.&lt;/p&gt;

&lt;p&gt;PostgreSQL supports this row-comparison syntax. The comparison evaluates columns from left to right, using the next column when earlier values are equal.&lt;/p&gt;

&lt;p&gt;This example assumes both cursor columns are non-null.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://www.postgresql.org/docs/current/functions-comparisons.html" rel="noopener noreferrer"&gt;Reference: PostgreSQL Row Constructor Comparisons&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Why &lt;code&gt;LIMIT 21&lt;/code&gt; instead of 20?&lt;/p&gt;

&lt;p&gt;Because requesting one extra row provides an inexpensive way to determine whether another page exists.&lt;/p&gt;

&lt;p&gt;If 21 rows are returned, send the first 20 and set &lt;code&gt;has_more&lt;/code&gt; to &lt;code&gt;true&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;The cursor should come from the 20th returned record, not the extra record.&lt;/p&gt;

&lt;p&gt;That small detail prevents accidentally skipping an item.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Build the Right Index
&lt;/h2&gt;

&lt;p&gt;Cursor pagination isn't automatically fast.&lt;/p&gt;

&lt;p&gt;The index must support the filtering and ordering strategy.&lt;/p&gt;

&lt;p&gt;For the query above:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;idx_orders_created_id&lt;/span&gt;
&lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;A B-tree index in this order can support efficient traversal of the results.&lt;/p&gt;

&lt;p&gt;For an application where users only retrieve their own orders, the query might instead be:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;user_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nv"&gt;$1&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nv"&gt;$2&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nv"&gt;$3&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;21&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;A more appropriate index would be:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;idx_orders_user_created_id&lt;/span&gt;
&lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;user_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The leading &lt;code&gt;user_id&lt;/code&gt; column helps PostgreSQL narrow the search to one user's records before traversing them in the requested order.&lt;/p&gt;

&lt;p&gt;This follows an important principle: &lt;strong&gt;design indexes around real query patterns, not individual columns in isolation.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The trade-off is write overhead. Every additional index consumes storage and requires maintenance during inserts, updates, and deletes.&lt;/p&gt;

&lt;p&gt;Don't add several pagination indexes without understanding which queries need them.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Design the API Contract Carefully
&lt;/h2&gt;

&lt;p&gt;A clean cursor-pagination endpoint might look like:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight http"&gt;&lt;code&gt;&lt;span class="na"&gt;GET /api/orders?limit&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mi"&gt;20&lt;/span&gt;&lt;span class="o"&gt;&amp;amp;&lt;/span&gt;&lt;span class="n"&gt;cursor&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="n"&gt;eyJ&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The response:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight json"&gt;&lt;code&gt;&lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="nl"&gt;"data"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nl"&gt;"pagination"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="nl"&gt;"next_cursor"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"eyJ..."&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="nl"&gt;"has_more"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kc"&gt;false&lt;/span&gt;
  &lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The cursor can contain the timestamp and ID:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight json"&gt;&lt;code&gt;&lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="nl"&gt;"created_at"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"2026-11-19T10:30:00Z"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nl"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mi"&gt;8421&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Encode the cursor into a URL-safe token so callers don't need to understand its internals.&lt;/p&gt;

&lt;p&gt;Base64 encoding is convenient, but it isn't encryption or tamper protection.&lt;/p&gt;

&lt;p&gt;For public APIs, consider signing cursor payloads or validating decoded values carefully.&lt;/p&gt;

&lt;p&gt;Also, keep these rules in mind:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Enforce a maximum page size to protect database resources.&lt;/li&gt;
&lt;li&gt;Use parameterized queries instead of concatenating cursor values into SQL.&lt;/li&gt;
&lt;li&gt;Return a clear client error for malformed cursors.&lt;/li&gt;
&lt;li&gt;Ensure the cursor matches the filters and sort order used for the request.&lt;/li&gt;
&lt;li&gt;Treat cursors as navigation markers, not proof of authorization.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;A cursor should never allow a client to bypass tenant or user-level access controls.&lt;/p&gt;

&lt;h2&gt;
  
  
  6. Understand the Consistency Trade-Off
&lt;/h2&gt;

&lt;p&gt;Cursor pagination solves some problems with changing datasets, but it doesn't create a permanent snapshot.&lt;/p&gt;

&lt;p&gt;Consider an order that was originally on page three.&lt;/p&gt;

&lt;p&gt;If its sorting timestamp changes before the user reaches that page, the record might move across the cursor boundary.&lt;/p&gt;

&lt;p&gt;It could be missed or encountered again.&lt;/p&gt;

&lt;p&gt;For feeds ordered by immutable creation timestamps, this is usually manageable. For datasets sorted by frequently changing fields, the behavior requires more thought.&lt;/p&gt;

&lt;p&gt;There are several alternatives:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Use immutable ordering fields.&lt;/strong&gt; This is often the simplest choice for event streams, audit logs, and creation histories.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Introduce a snapshot boundary.&lt;/strong&gt; Capture a fixed upper boundary when pagination begins and apply it to later requests. This can exclude newer records, although it doesn't freeze existing records against updates or deletions.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Use snapshot-based reads or materialized results.&lt;/strong&gt; For exports and compliance workflows that require an exact point-in-time dataset, stronger consistency mechanisms may be necessary.&lt;/p&gt;

&lt;p&gt;These choices have different costs in storage, database resources, and implementation complexity.&lt;/p&gt;

&lt;p&gt;The correct decision depends on whether the product needs a convenient browsing experience or an exact historical result set.&lt;/p&gt;

&lt;h2&gt;
  
  
  7. Measure Before and After
&lt;/h2&gt;

&lt;p&gt;I wouldn't replace every OFFSET query just because cursor pagination sounds more efficient.&lt;/p&gt;

&lt;p&gt;First, inspect the actual workload.&lt;/p&gt;

&lt;p&gt;PostgreSQL provides &lt;code&gt;EXPLAIN ANALYZE&lt;/code&gt; to evaluate execution plans:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;EXPLAIN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;ANALYZE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="no"&gt;BUFFERS&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;20&lt;/span&gt; &lt;span class="k"&gt;OFFSET&lt;/span&gt; &lt;span class="mi"&gt;100000&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Compare that with a cursor query using a boundary that represents a similar depth in the dataset.&lt;/p&gt;

&lt;p&gt;Look for:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Actual execution time across shallow and deep pages.&lt;/li&gt;
&lt;li&gt;Rows processed and index access patterns.&lt;/li&gt;
&lt;li&gt;Shared buffer hits and reads.&lt;/li&gt;
&lt;li&gt;Sort operations and sequential scans.&lt;/li&gt;
&lt;li&gt;API latency at p50, p95, and p99 under representative traffic.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;No benchmark numbers are provided here because results depend on table size, indexes, data distribution, cache state, and concurrent activity.&lt;/p&gt;

&lt;p&gt;Measure the behavior in your own environment before claiming a performance improvement.&lt;/p&gt;

&lt;h2&gt;
  
  
  8. When OFFSET Is Still the Better Choice
&lt;/h2&gt;

&lt;p&gt;Cursor pagination adds complexity.&lt;/p&gt;

&lt;p&gt;It makes jumping directly to page 50 harder because navigation depends on a known cursor boundary.&lt;/p&gt;

&lt;p&gt;OFFSET can still be appropriate for small administrative tables, relatively static datasets, and interfaces that require numbered pages or random page navigation.&lt;/p&gt;

&lt;p&gt;Here's the practical comparison:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Requirement&lt;/th&gt;
&lt;th&gt;OFFSET&lt;/th&gt;
&lt;th&gt;Cursor&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Simple implementation&lt;/td&gt;
&lt;td&gt;Easier&lt;/td&gt;
&lt;td&gt;More involved&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Direct page-number navigation&lt;/td&gt;
&lt;td&gt;Yes&lt;/td&gt;
&lt;td&gt;Not naturally&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Deep pagination efficiency&lt;/td&gt;
&lt;td&gt;Often worse&lt;/td&gt;
&lt;td&gt;Often better&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Behavior during inserts&lt;/td&gt;
&lt;td&gt;Can duplicate or skip&lt;/td&gt;
&lt;td&gt;More stable with immutable keys&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Index design&lt;/td&gt;
&lt;td&gt;Important&lt;/td&gt;
&lt;td&gt;Important&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Infinite scrolling&lt;/td&gt;
&lt;td&gt;Supported&lt;/td&gt;
&lt;td&gt;Usually a better fit&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;If a dataset contains a few hundred rows and queries are already fast, cursor pagination may provide little benefit.&lt;/p&gt;

&lt;p&gt;Complexity is a cost too.&lt;/p&gt;

&lt;h2&gt;
  
  
  Final Thoughts
&lt;/h2&gt;

&lt;p&gt;Pagination isn't just a frontend concern. It affects database load, API reliability, and whether users see consistent results.&lt;/p&gt;

&lt;p&gt;Three lessons are worth remembering:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;OFFSET is simple, but deep pagination can become expensive.&lt;/strong&gt; Measure where that cost starts to matter.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;A reliable cursor needs deterministic ordering.&lt;/strong&gt; Use a unique tie-breaker and an index that matches your query.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Cursor pagination isn't a snapshot.&lt;/strong&gt; Understand how inserts, deletes, and updates affect the user experience.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;For large append-heavy datasets, cursor pagination is often a strong default. For small tables and page-number navigation, OFFSET may still be the simpler engineering decision.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;How do you handle pagination in APIs where records are frequently inserted or updated?&lt;/strong&gt;&lt;/p&gt;




</description>
      <category>postgres</category>
      <category>backend</category>
      <category>database</category>
      <category>webdev</category>
    </item>
  </channel>
</rss>
