[Openstack] Getting pagination right

Jay Pipes jaypipes at gmail.com
Wed May 25 19:40:27 UTC 2011


On Wed, May 25, 2011 at 3:32 PM, Greg Holt <gholt at rackspace.com> 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




More information about the Openstack mailing list