OFFSET

The OFFSET clause specifies how many rows to skip before returning rows from a query. It is commonly used with LIMIT for pagination.

For example:

SELECT *
FROM system_metrics
ORDER BY cpu_util DESC
LIMIT 10
OFFSET 10;

It selects all columns from rows ranked 11th to 20th (by descending cpu_util) from the system_metrics table.

The cost of OFFSET grows as later pages skip more rows. For large result sets, use keyset pagination with an ordering that uniquely identifies each row. A timestamp alone is not sufficient when multiple rows can have the same timestamp.

Efficient Pagination Using Timestamps

Suppose (ts, host, idc) uniquely orders the rows in system_metrics. Sort all three columns and record their values from the last row of each page.

First Page (Latest 10 Records):

SELECT *
FROM system_metrics
ORDER BY ts DESC, host DESC, idc DESC
LIMIT 10;

If the last row of the first page is ('2024-07-01 16:03:00', 'host2', 'idc_b'), fetch the next page with a composite cursor:

SELECT *
FROM system_metrics
WHERE ts < '2024-07-01 16:03:00'
   OR (ts = '2024-07-01 16:03:00' AND host < 'host2')
   OR (ts = '2024-07-01 16:03:00' AND host = 'host2' AND idc < 'idc_b')
ORDER BY ts DESC, host DESC, idc DESC
LIMIT 10;

Use cursor columns that are stable and unique for the query. If (ts, host, idc) is not unique, add another tie-breaker column to both the ORDER BY clause and cursor predicate. This method avoids repeatedly scanning and skipping earlier pages, making pagination more efficient on large result sets.