Pagination in QoreDB: OFFSET/LIMIT and Its Real Limits

A production table does not fit inside a window. Past a few tens of thousands of rows, loading it in one shot saturates the client's memory, the interface freezes, and the network carries data no one will ever look at. The classic answer is one word: pagination. QoreDB is no exception, but its particular constraint is that it drives a dozen engines — PostgreSQL, MySQL, SQL Server, MongoDB… — that do not express pagination the same way. The design choice is to impose a common model at the core, then translate it into each engine's native dialect.

A shared model: TableQueryOptions and PaginatedQueryResult

Every table browse goes through a single request struct, defined in qore-core/src/types.rs. TableQueryOptions carries the page number, the page size, the sort column, the column filters and a full-text search term. On the way out, PaginatedQueryResult returns not only the current page's rows but also the total number of rows matching the query, the current page, the page size used and the total page count, computed as total_rows.div_ceil(page_size).

impl TableQueryOptions {
    /// Effective page number
    pub fn effective_page(&self) -> u32 {
        self.page.unwrap_or(0)
    }

    /// Effective page size
    pub fn effective_page_size(&self) -> u32 {
        self.page_size.unwrap_or(50).clamp(1, 10000)
    }

    /// SQL OFFSET for pagination
    pub fn offset(&self) -> u64 {
        let page = self.effective_page();
        let zero_indexed_page = if page > 0 { page - 1 } else { 0 };
        zero_indexed_page as u64 * self.effective_page_size() as u64
    }
}

Two values are worth noting. The default page size is 50 rows, and it is bounded by clamp(1, 10000): you cannot ask for zero rows, and you cannot request a million in a single call. The React grid reuses the same value — pageSize: 50 in DataGrid.tsx — and the InstantApi panel explicitly rejects any size outside the [1, 10000] range. That upper bound is not cosmetic: it guarantees a single page can never blow up memory, whatever engine sits behind it.

One pagination dialect per engine

The core reasons in page / page_size / offset, but each driver translates those three numbers into its engine's syntax. For PostgreSQL and MySQL, it is the classic LIMIT … OFFSET … pair, assembled in pg_compat.rs:

let data_sql = format!(
    "SELECT * FROM {}{}{} LIMIT {} OFFSET {}",
    table_ref, where_sql, order_sql, page_size, offset
);

SQL Server has no LIMIT. Its driver (sqlserver.rs) uses the standard OFFSET … ROWS FETCH NEXT … ROWS ONLY syntax, available since SQL Server 2012. That form requires an ORDER BY clause: when the user has chosen no sort, QoreDB injects an ORDER BY (SELECT NULL) to stay syntactically valid without imposing an arbitrary order.

let data_sql = format!(
    "SELECT * FROM {}{}{} OFFSET {} ROWS FETCH NEXT {} ROWS ONLY",
    table_ref, where_sql, order_sql, offset, page_size
);

MongoDB, finally, is not relational at all. The driver translates the same TableQueryOptions into a find cursor parameterized by skip and limit, through a FindOptions::builder() — .skip(Some(offset)) and .limit(Some(page_size as i64)). Three engines, three syntaxes, one intent. That is precisely the DataEngine trait contract: the rest of the application only knows query_table and always gets back a PaginatedQueryResult, without knowing whether the engine wrote OFFSET, FETCH NEXT or .skip().

Two costs worth naming: COUNT(*) and deep OFFSET

Showing “page 3 of 128” means knowing the total. QoreDB recomputes it on every page load: before the data query, each driver runs a COUNT(*) carrying exactly the same filters as the page. On the PostgreSQL side:

let count_sql = format!(
    "SELECT COUNT(*)::bigint AS cnt FROM {}{}",
    table_ref, where_sql
);

The same pattern appears everywhere: count_documents(filter) on MongoDB, SELECT COUNT(*) on SQL Server. The total is always exact and consistent with the active filters, but it has a price. On a large table with no index useful to the filter, counting rows can cost more than reading the page itself — and that cost is paid on every navigation. The second cost is intrinsic to OFFSET: to return the page starting at offset N, the engine must produce and then discard the first N rows. Jumping to page 5,000 at 50 rows per page means asking the engine to walk 250,000 rows before returning 50. The cost of OFFSET grows linearly with depth.

The trait's default fallback, and its traps

The DataEngine trait provides a default implementation of query_table, so a new driver compiles before it has even written its pagination. That fallback simply calls preview_table and derives the total from the number of rows returned:

async fn query_table(
    &self,
    session: SessionId,
    namespace: &Namespace,
    table: &str,
    options: TableQueryOptions,
) -> EngineResult<PaginatedQueryResult> {
    let page = options.effective_page();
    let page_size = options.effective_page_size();
    let result = self
        .preview_table(session, namespace, table, page_size)
        .await?;
    let total = result.rows.len() as u64;
    Ok(PaginatedQueryResult::new(result, total, page, page_size))
}

A deliberate trade-off

OFFSET / LIMIT pagination is not the fastest at depth, and QoreDB does not pretend otherwise. Its advantages are that it is universal, exact on the total, and translatable into every engine without rewriting the interface. For the case where pagination is not enough — walking or exporting an entire table without paging through it — QoreDB relies on a different mechanism: cursor-based streaming with bounded memory, exposed separately by the trait through execute_stream. Two tools, two uses: pagination to navigate, streaming to read everything. The through-line stays the same — tell the engine's truth rather than mask its costs.