Open Stack

Hi Jay,

On Wed, May 25, 2011 at 03:40:27PM -0400, Jay Pipes wrote:
> 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.

FWIW, this is the same approach I'm using in burrow, except where
order is roughly insert time (no guaranteed strict ordering when it's
sharded). This is simple and scales well.

> 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.

Assuming you have an index on marker for efficiency, I think the query
makes perfect sense. Can you explain a bit more why you need the offset
if we are ok with the result set for a given page changing? From the
total set, we're returning everything after the last seen marker,
ordering those rows larger (which should be a no-op with the index),
and then limit by an optional limit parameter. Seems ok to me.

> 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.

I think not being consistent is OK. If you are viewing a list of images
and then you see a new one pop up on refresh after another insert
(using the same marker), I think this is perfectly fine. It certainly
helps keep things simpler on the backends. Doesn't seem like a big
issue for swift so far, and I don't anticipate issues with burrow.

-Eric