Database Reference
In-Depth Information
Paginating over multiple partitions
Returning to our original
user_status_updates
table, we might in certain situations,
for an administrative interface, for instance, display all of the status updates in the system.
In this case, we will certainly want the ability to paginate, as the total collection of status
updates will get very large.
As in our previous example, let's use a page size of three. The query for the first page is
simple enough:
SELECT * FROM "user_status_updates"
LIMIT 3;
As expected, we'll get back
bob
's first three status updates:
For the second page, things get a bit more complicated. We know that the last row we re-
trieved was from
bob
's partition, but we're not sure if there are any more status updates for
bob
. In case there are, we will ask for the next page of
bob
's partition:
SELECT * FROM "user_status_updates"
WHERE "username" = 'bob'
AND id > 3f9e9350-e8f7-11e3-9211-5f98e903bf02;
In this query, we ask for any status updates in
bob
's partition that have an
id
that is greater
than the last
id
we saw in the previous page. As it turns out, there is one more row: