<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Mauricio Ibáñez Deana on Medium]]></title>
        <description><![CDATA[Stories by Mauricio Ibáñez Deana on Medium]]></description>
        <link>https://medium.com/@mauricio.ibanez?source=rss-d56c4955ecaa------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*ggLNFsqxY3M39aBJUzu9-w.jpeg</url>
            <title>Stories by Mauricio Ibáñez Deana on Medium</title>
            <link>https://medium.com/@mauricio.ibanez?source=rss-d56c4955ecaa------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 10:36:26 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mauricio.ibanez/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[SQL Database Pagination Methods: Offset-Limit vs. Cursor-Based]]></title>
            <link>https://medium.com/enviame-engineering/sql-database-pagination-methods-offset-limit-vs-cursor-based-af066fb621e5?source=rss-d56c4955ecaa------2</link>
            <guid isPermaLink="false">https://medium.com/p/af066fb621e5</guid>
            <category><![CDATA[performance-efficiency]]></category>
            <category><![CDATA[offset-limit]]></category>
            <category><![CDATA[cursor-based]]></category>
            <category><![CDATA[real-world-application]]></category>
            <category><![CDATA[pagination-techniques]]></category>
            <dc:creator><![CDATA[Mauricio Ibáñez Deana]]></dc:creator>
            <pubDate>Thu, 20 Feb 2025 12:41:59 GMT</pubDate>
            <atom:updated>2025-02-20T12:41:59.632Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Introduction</strong></h3><p>Pagination is a widely-used technique for managing large datasets by breaking them into smaller, manageable segments. This approach improves user experience by reducing load times and simplifying navigation, while also optimizing system performance and data transfer efficiency.</p><p>In reporting scenarios requiring efficient data processing, choosing the right pagination method is critical. This article explores two prominent SQL database pagination techniques — <strong>offset-limit</strong> and <strong>cursor-based</strong> pagination — detailing their mechanisms, advantages, disadvantages, and practical applications.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZuFHA5Y2IX6LPK46ZpFYGw.jpeg" /><figcaption><em>AI generated mage</em></figcaption></figure><h3>Offset-Limit pagination</h3><p>Offset-limit pagination is one of the simplest and most commonly adopted techniques. It operates by defining a starting point (<strong>offset</strong>) and the number of records to retrieve (<strong>limit</strong>). For a given <strong>page number</strong> and <strong>page size</strong>, offset and limit are calculated as follows:</p><ul><li><strong>Offset </strong>= (page number — 1) * page size</li><li><strong>Limit </strong>= page size</li></ul><p>For instance, to retrieve the third page of a dataset with a page size of 10, the offset would be 20 (i.e., skipping the first 20 records), and the limit would be 10.</p><p><strong>Advantages</strong></p><ul><li><strong>Ease of Implementation</strong>: Offset-limit pagination is intuitive and straightforward, requiring minimal setup. Its widespread support across database systems makes it ideal for rapid prototyping or simple applications.</li></ul><p><strong>Disadvantages</strong></p><ul><li><strong>Performance Degradation</strong>: As the offset increases, the database must scan and discard all preceding records to reach the starting point. This leads to <strong>progressively slower query times for deeper pages,</strong> making it resource-intensive for large datasets.</li><li><strong>Data Consistency</strong>: If the dataset changes between requests — due to insertions or deletions — pagination results may become inconsistent, potentially causing duplicate records or skipping others entirely..</li></ul><h3>Cursor-Based Pagination</h3><p>Cursor-based pagination employs a marker, or “cursor”, to track the position within a dataset. Rather than relying on an offset and limit, it uses a unique identifier (e.g., an ID or timestamp) to mark the last retrieved record and fetch subsequent entries from that point onward.</p><p><strong>Advantages:</strong></p><ul><li><strong>Performance Efficiency</strong>: By avoiding the need to scan prior records, cursor-based pagination offers superior performance, particularly for large datasets. Query times remain consistent regardless of dataset size or position.</li><li><strong>Data Consistency</strong>: The cursor ensures continuity even if the dataset changes between requests, preventing skipped or duplicated records and maintaining integrity in dynamic environments.</li><li><strong>Scalability</strong>: This method excels in applications handling vast data volumes, as its efficiency does not degrade with scale.</li></ul><p><strong>Disadvantages:</strong></p><ul><li><strong>Implementation Complexity</strong>: Cursor-based pagination requires more sophisticated logic to manage cursors, track state, and handle unique identifiers, which may involve composite indexes for complex filtering.</li><li><strong>Dependency on Unique Indexes</strong>: It typically relies on a unique identifier, such as a primary key, for simplicity. Custom sorting or filtering (e.g., by time or other criteria) demands additional composite indexes, increasing complexity.</li></ul><h3>Comparison</h3><h4>Offset-Limit Pagination</h4><ul><li><strong>Access to Specific Page</strong>: Yes ✅ — Enables direct navigation to any page by calculating the offset, offering straightforward random access.</li><li><strong>Implementation Difficulty</strong>: Simple ✅ — Leverages basic SQL LIMIT and OFFSET clauses, making it easy to implement and understand.</li><li><strong>Handling Large Data Volumes</strong>: Inefficient ⚠️⚠️⚠️— Performance worsens as the dataset grows, due to the need to scan all preceding records for each query..</li><li><strong>Consistency with Data Changes</strong>: No ⚠️ — Dataset modifications between requests can lead to inconsistencies, such as missing or repeated records.</li></ul><h4>Cursor-based Pagination</h4><ul><li><strong>Access to Specific Pages</strong>: No/Difficult ❌ — Optimized for sequential fetching rather than random access. While possible with additional logic, random access is outside the scope of typical use cases.</li><li><strong>Implementation Difficulty</strong>: Complex ⚠️ — Demands careful management of cursors and state, often requiring server-side logic or client-side storage.</li><li><strong>Handling Large Data Volumes</strong>: Efficient ✅✅✅ — Maintains consistent query performance by avoiding unnecessary scans, ideal for large-scale datasets.</li><li><strong>Consistency with Data Changes</strong>: Yes ✅ — Tracks the last position with a cursor, ensuring continuity despite insertions or deletions.</li></ul><h3><strong>Real-World Use Cases</strong></h3><p>In certain backend modules, we leverage pagination to process large datasets efficiently, fetching data in batches of thousands of rows for spreadsheet-based reporting. Sequential access is key in these scenarios, making cursor-based pagination a natural fit. However, for use cases requiring random page access — such as user-facing interfaces — offset-limit pagination remains practical despite its limitations.</p><h4>Code Example: Cursor-Based Pagination</h4><p>Below is a simplified code snippet demonstrating cursor-based pagination in JavaScript, adaptable to most SQL databases.</p><p>Usecase/Logic</p><pre><br>CHUNK_SIZE = 1000<br>let cursor = {}<br><br>// continue while data is still available<br>for (let offset = 0; offset &lt; qtyRows || qtyRows === undefined; offset += CHUNK_SIZE) {<br>  // fetch data<br>  const data = await this.sqlRepo.fetchData(batchId, CHUNK_SIZE, cursor)<br>  // Exit if no more data<br>  if (!data.length) break<br>  // Update cursor to the last row&#39;s position<br>  const lastRow = data[data.length - 1]<br>  cursor.rowNum = lastRow.rowNum<br>  // Process each row (e.g., write to spreadsheet)<br>    for (let row of data) {<br>        await this.spreadsheetRepo.addRow(row)<br>    }<br>}</pre><p>Repository Method</p><pre><br>async fetchData(batchId, limit, cursor) {<br>  const rows = await this.MyModel.findAll({ // ORM for SQL DB<br>    where: {<br>      batchId: batchId,<br>      rowNum: { [Op.gt]: cursor.rowNum ?? 0 }, // Greater than last cursor position<br>    },<br>    order: [<br>      [&#39;batch_id&#39;, &#39;ASC&#39;], <br>      [&#39;row_num&#39;, &#39;ASC&#39;],<br>    ],<br>    limit: limit,<br>  })<br>  return rows<br>}</pre><p><em>Considerations:</em></p><ul><li><em>If the total row count (qtyRows) is unknown, loop until no data is returned.</em></li><li><em>Requires a unique composite index (e.g., batch_id and row_num) for efficiency.</em></li><li><em>While applicable to all SQL databases, vendors with native cursor support (e.g., PostgreSQL) may offer optimized alternatives.</em></li></ul><h4>Performance Comparison</h4><p>Testing with a dataset of 100,000 rows, paginated into chunks of 1,000 rows each, demonstrated significant differences in execution time. These measurements were conducted on specific hardware, and performance may vary depending on the system configuration, database optimization, and workload:</p><ul><li><strong>Offset-Limit Pagination: </strong>140 seconds<br>Execution time grows exponentially as the offset increases, reflecting the need to scan all prior records.</li><li><strong>Cursor-Based Pagination</strong>: 30 seconds<br>Maintains near-constant performance by leveraging the cursor to avoid redundant scans.</li></ul><p>Maintains near-constant performance by leveraging the cursor, avoiding redundant scans.</p><p>This disparity highlights offset-limit’s inefficiency for large datasets, where performance degradation becomes impractical.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/1*J1iX3no8PZqhSNTkNFV9yg.png" /></figure><h3>Conclusion</h3><p>For large datasets requiring sequential access — such as our reporting workflows — <strong>offset-limit</strong> <strong>pagination</strong> proved inadequate due to escalating execution times. Switching to <strong>cursor-based pagination </strong>significantly enhanced efficiency, reducing processing times and improving scalability.</p><p>However, in scenarios demanding random page access, offset-limit remains a viable, simpler option despite its performance trade-offs.</p><p>Selecting the appropriate method depends on the specific requirements of your application, balancing ease of use, performance, and data consistency.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=af066fb621e5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/enviame-engineering/sql-database-pagination-methods-offset-limit-vs-cursor-based-af066fb621e5">SQL Database Pagination Methods: Offset-Limit vs. Cursor-Based</a> was originally published in <a href="https://medium.com/enviame-engineering">Envíame Engineering Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>