Take a look at these links. It should give you a clue to avoid LIMIT / OFFSET.
https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/ http://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way Regards, Walter On Sep 29, 2016 19:19, "Guyren Howe" <guy...@gmail.com> wrote: > > On Sep 29, 2016, at 16:14 , Colin Morelli <colin.more...@gmail.com> wrote: > > Well then...just like that you made me feel like a total idiot! Hah. > > I guess that would work fine. I just need to encode some additional > information in the pagination links that the API returns (a pagination > "marker" would be a combination of created_at and uuid). > > I know this question is virtually impossible to answer without more > information, but based on your gut - do you think it would make sense to > define a compound index across (created_at, uuid), or do you think just an > index on created_at is enough, if we can assume that *most* records > (>80%) won't have collisions on their created_at timestamp? > > > Quite likely just the created_at will be sufficient, although I like to > add other fields to the index since you're going to have it anyway, if you > have occasion to often filter the list on some other field. > > But you don't need the UUID field because even if there is collision, it > will be a small number of records. > > On Thu, Sep 29, 2016 at 7:09 PM Guyren Howe <guy...@gmail.com> wrote: > >> On Sep 29, 2016, at 16:03 , Colin Morelli <colin.more...@gmail.com> >> wrote: >> > >> > Hey list, >> > >> > I'm using UUID primary keys in my application. I need to add >> pagination, and am trying to avoid OFFSET/LIMIT. I do have a created_at >> timestamp that I could use, but it's possible for multiple records to be >> created at the same timestamp (postgres offers millisecond precision here, >> I believe?) >> > >> > Is there an efficient way to filter based on the time component of UUID >> v1s? WHERE id > 'some-uuid' doesn't seem to work, as it looks like it's >> just performing a lexicographic sort on the hex representation of the UUID. >> Or, alternatively, does anyone have other suggestions on paginating large >> data sets? >> >> Why not just sort on (created_at, uuid) (ie us the UUID just to force a >> complete ordering)? >> >> >
