openstack team mailing list archive

[Jay Pipes <jaypipes@xxxxxxxxx>]

On Wed, May 25, 2011 at 3:32 PM, Greg Holt <gholt@xxxxxxxxxxxxx> wrote:
> select w from x where y > marker order by y limit z
>
> This gives you consistent pagination, means the database doesn't have to count matching rows to find an offset, and means you could shard by y later in life if you need to scale that much.

Sorry, that doesn't make any sense to me. An offset is part of the
LIMIT X OFFSET Y clause. Your query above would return ALL the rows
that match WHERE y > marker. That's not what we want. We want a
segment of those rows.

In addition, your SQL expression does not return consistent result
sets if the Y column does not have an increasing value over time. New
records inserted into the table will pop into random pages of results
if the Y column does not have a strictly increasing ordering.

-jay